Generating next sequence number in sql server

Question

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

What version of SQL Server? 2012 has sequences, previous versions have IDENTITY columns. And it would be helpful if you explain why you want to write your own code for this anyway? — Pondlife
– Pondlife, Commented Nov 27, 2012 at 15:09
Here's how to do it in 2012: stackoverflow.com/a/39774566/3347858 — Tony L.
– Tony L., Commented Oct 6, 2016 at 16:37

podiluska · Accepted Answer · 2012-11-27 14:44:57Z

2

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.

answered Nov 27, 2012 at 14:44

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kudlur Over a year ago

But my question was about "Is that UPDATE statement atomic?.. If so.. then it is by default in an implicit transaction. If that is true.. then why wrap in a transaction? And where can it deadlock?" Sorry.. If my original question was not clear. Thanks

Kapil Khandelwal · Accepted Answer · 2012-11-27 14:45:21Z

2

Why do you not use Identity column ?

IDENTITY [ (seed ,increment) ]

IDENTITY (Property)

Eg.

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

answered Nov 27, 2012 at 14:45

Kapil Khandelwal

16.2k3 gold badges49 silver badges55 bronze badges

Comments

Floyd · Accepted Answer · 2013-12-10 09:14:45Z

1

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

edited Dec 10, 2013 at 9:14

Floyd

1,92812 silver badges20 bronze badges

answered Jan 18, 2013 at 13:55

user1988754

111 silver badge2 bronze badges

Comments

Barguast · Accepted Answer · 2013-01-08 17:34:39Z

0

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.

answered Jan 8, 2013 at 17:34

Barguast

6,2669 gold badges49 silver badges78 bronze badges

Collectives™ on Stack Overflow

Generating next sequence number in sql server

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related