TSQL ID generation

I have a question regarding locking in TSQL. Suppose I have a the following table:

A(int id, varchar name)

where id is the primary key, but is NOT an identity column.

I want to use the following pseudocode to insert a value into this table:

lock (A)
  uniqueID = GenerateUniqueID()  开发者_如何学Go
  insert into A values (uniqueID, somename)
unlock(A)

How can this be accomplished in terms of T-SQL? The computation of the next id should be done with the table A locked in order to avoid other sessions to do the same operation at the same time and get the same id.

If you have custom logic that you want to apply in generating the ids, wrap it up into a user defined function, and then use the user defined function as the default for the column. This should reduce concurrency issue similarly to the provided id generators by deferring the generation to the point of insert and piggy backing on the insert locking behavior.

create table ids  (id int, somval varchar(20))
Go
Create function GenerateUniqueID()
returns int as 
Begin
    declare @ret int
    select @ret =  max(isnull(id,1)) * 2 from ids
    if @ret is null set @ret = 2  
    return @ret
End
go
alter table ids add Constraint DF_IDS Default(dbo.GenerateUniqueID())  for Id

There are really only three ways to go about this.

1. Change the ID column to be an IDENTITY column where it auto increments by some value on each insert.

2. Change the ID column to be a GUID with a default constraint of NEWID() or NEWSEQUENTIALID(). Then you can insert your own value or let the table generate one for you on each insert.

3. On each insert, start a transaction. Then get the next available ID using something like select max(id)+1 . Do this in a single sql statement if possible in order to limit the possibility of a collision.

On the whole, most people prefer option 1. It's fast, easy to implement, and most people understand it.

I tend to go with option 2 with the apps I work on simply because we tend to scale out (and up) our databases. This means we routinely have apps with a multi-master situation. Be aware that using GUIDs as primary keys can mean your indexes are routinely trashed.

I'd stay away from option 3 unless you just don't have a choice. In which case I'd look at how the datamodel is structured anyway because there's bound to be something wrong.

1. You use the NEWID() function and you do not need any locking mechanism

2. You tell a column to be IDENTITY and you do not need any locking mechanism

3. If you generate these IDs manually and there is a chance parallel calls could generate the same IDs then something like this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

@NextID = GenerateUniqueID()

WHILE EXISTS (SELECT ID FROM A WHERE ID = @NextID)
BEGIN
  @NextID = GenerateUniqueID()
END

INSERT INTO A (ID, Text) VALUES (@NextID , 'content')

COMMIT TRANSACTION

@Markus, you should look at using either IDENTITY or NEWID() as noted in the other answers. if you absolutely can't, here's an option for you...

DECLARE @NewID INT

BEGIN TRAN

SELECT  @NewID = MAX(ID) + 1
FROM    TableA (tablockx)

INSERT  TableA
        (ID, OtherFields)
VALUES  (@NewID, OtherFields)

COMMIT TRAN

If you're using SQL2005+, you can use the OUTPUT clause to do what you're asking, without any kind of lock (The table Test1 simulates the table you're inserted into, and since OUTPUT requires a temp table and not a variable to hold the results, #Result will do that):

create table test1( test INT)

create table #result (LastValue INT)


insert into test1
output INSERTED.test into #result(test)
select GenerateUniqueID()

select LastValue from #result

Just to update an old post. It is now possible with SQL Server 2012 to use a feature called Sequence. Sequences are created in much the same way a function and it is possible to specify the range, direction(asc, desc) and rollover point. After which it's possible to invoke the NEXT VALUE FOR method to generate the next value in the range.

See the following documentation from Microsoft.

http://technet.microsoft.com/en-us/library/ff878091.aspx