How to create a unique custom key
Scenario: I have a fairly general table (data) that has an ID column. The data in this table is grouped (say, by city).
Users need an ID to print on paper forms, etc. Users can only access their own data, so if they use the ID column for this purpose, they will see odd numbers (for example, a New York user can see 1,37,2028 ... as the listed keys.
Ideally they would see 1,2,3 ... (or something similar)
The problem is of course concurrency, this is a web application where you cannot just have something like: UserId = Select Count (*) + 1 from Data Where City = 'New York'
Has anyone come up with some clever ways to solve this problem?
Updated - from comments below. I think I want something like the SP below. Not really sure how recursion is supposed to work in a CATCH block.
ALTER PROCEDURE [dbo].[DataContainer_Insert]
@SomeData varchar(max),
@DataContainerId int out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
SELECT @UserId = MAX(UserId) From DataContainer
INSERT INTO DataContainer (UserId, SomeData)
VALUES (@UserId, SomeData)
SELECT @DataContainerId = scope_identity()
END TRY
BEGIN CATCH
--try again
exec DataContainer_Insert @DataContainerId, @SomeData
END CATCH
END
a source to share
Ok, you could use your idea UserId = Select Count(*)+1 from Data Where City='New York'
if you added a spanning constraint for the city and UserId (to ensure that there is only ever one instance of a specific Id and City). Then you just need to be able to handle constraint violations (by resubmitting).
a source to share
In related situations, if I come back from the server-side data obtained using GROUP BY
in a SELEC
T statement and you need something like id
for each row, I use ROW_NUMBER () (see http://msdn.microsoft.com/en-us /library/ms186734.aspx ). This is also fine in the case of data paging (see a simple example at http://weblogs.asp.net/Firoz/archive/2005/06/12/411949.aspx ). This required SQL Server 2005 or higher. You didn't mention which SQL Server you were using.
a source to share
As a general note, you can do something like
newUserId = Select MAX(UserId)+1 from Data Where City='New York'
INSERT INTO data (...) VALUES (newUserId, ...)
even in a concurrent access scenario (like a web application) if you are using the correct concurrency control, i.e. (a) locking, or (b) transactions with the appropriate isolation level.
See question 1994771 for an in-depth analysis of how you can do something like this with SQL Server.
a source to share