How to create unique user key

Question

Scenario: I have a fairly generic table (Data), that has an identity column. The data in this table is grouped (lets say by city).

The users need an identifier in order for printing on paper forms, etc. The users can only access their cites data, so if they use the identity column for this purpose they will see odd numbers (e.g. a 'New York' user might see 1,37,2028... as the listed keys.

Idealy they would see 1,2,3... (or something similar)

The problem of course is concurrency, this being a web application you can't just have something like: UserId = Select Count(*)+1 from Data Where City='New York'

Has anyone come up with any cunning ways around this problem?

Updated - from the comments below I think I want something like the SP below. Not entirely sure how the recursion should work in the 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

Answer 1

In a close situations if I send back from the server data produced using GROUP BY in the SELECT statement and need have something like id of every row, I use ROW_NUMBER() (see http://msdn.microsoft.com/en-us/library/ms186734.aspx). This is also good in case of data paging (see a simple example in http://weblogs.asp.net/Firoz/archive/2005/06/12/411949.aspx). Of cause this required SQL Server 2005 or higher. You don't written which SQL Server you used.

Answer 2

Well, you could use your UserId = Select Count(*)+1 from Data Where City='New York' idea if you add a spanning uniqueness constraint on city and UserId (to guarantee that there would only ever be 1 instance of a particular Id and City). Then you just need to be able to handle the constraint violations (by resubmitting).

Answer 3

As a general remark: 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 (e.g. a web application), as long as you employ proper concurrency control, i.e. (a) locking or (b) transactions with an appropriate isolation level.

See question 1994771 for an in-depth analysis of how you can do something like this with SQL Server.