CODEWITHSUNDEEP
sqlsql-serversequence
kudlur
kudlur

Reputation: 1213

Generating next sequence number in sql server

I have a need to generate a sequence number to be used as a primary key (in a legacy system)

I want to know if the following solution suffers from concurrency during race conditions.

CREATE TABLE SequenceGenerator
(
    Sequence INT
)

INSERT INTO SequenceGenerator SELECT 0

And here is the stored procedure that i will be calling whenever i need the next sequence number

CREATE PROCEDURE GetNextSequence
AS
    SET NOCOUNT ON
    DECLARE @NextSequence INT
    UPDATE SequenceGenerator SET
    @NextSequence = Sequence,
    Sequence = Sequence + 1
    RETURN @NextSequence + 1

Thanks

Upvotes: 2

Views: 12732

Answers (4)

user1988754
user1988754

Reputation: 11

This will do the trick, you wont run into concurrency problems.

CREATE TABLE SequenceGenerator(Sequence INT)
GO
INSERT INTO SequenceGenerator SELECT 0
GO

DECLARE @NextSequence INT

UPDATE SequenceGenerator 
SET @NextSequence = Sequence = Sequence + 1

@NextSequence will have incremented value

Upvotes: 1

Barguast
Barguast

Reputation: 6196

It's not pretty, but I've had the following working in SQL Server 2008 R2 for years, and it's never let me down:

I have a table with a 'Name' and 'LastValue' column:

CREATE PROCEDURE [dbo].[ReserveSequence]
@Name varchar(100),
@Count int,
@FirstValue bigint OUTPUT
AS 
BEGIN
SET NOCOUNT ON;

DECLARE @Results TABLE ([LastValue] bigint)

UPDATE [Sequences]
SET [LastValue] = [LastValue] + @Count
OUTPUT INSERTED.LastValue INTO @Results
WHERE [Name] = @Name;

SELECT TOP 1 @FirstValue = [LastValue] + 1 FROM @Results;
RETURN 0;
END

That said, I'd be using SEQUENCE objects if you're targetting 2012 or later.

Upvotes: 0

Kapil Khandelwal
Kapil Khandelwal

Reputation: 16144

Why do you not use Identity column ?

IDENTITY [ (seed ,increment) ]

IDENTITY (Property)

Eg.

CREATE TABLE SequenceGenerator
(
    Sequence INT IDENTITY(1,1)
)

Upvotes: 2

podiluska
podiluska

Reputation: 51514

Yes.

Is there some reason you can't an identity field (ie: identity(1,1)) instead?

If you want to use the above code, you should certainly wrap it in a serializable transaction and handle any deadlock situations.

Upvotes: 2

Related Questions