Sequence Generators in T-SQL

Question

We have an Oracle application that uses a standard pattern to populate surrogate keys. We have a series of extrinsic rows (that have specific values for the surrogate keys) and other rows that have intrinsic values. We use the following Oracle trigger snippet to determine what to do with the Surrogate key on insert:

'IF :NEW.SurrogateKey IS NULL THEN

SELECT SurrogateKey_SEQ.NEXTVAL INTO :NEW.SurrogateKey FROM DUAL;

END IF;'

If the supplied surrogate key is null then get a value from the nominated sequence, else pass the supplied surrogate key through to the row.

I can't seem to find an easy way to do this is T-SQL. There are all sorts of approaches, but none of which use the notion of a sequence generator like Oracle and other SQL-92 compliant DBs do.

Anybody know of a really efficient way to do this in SQL Server T-SQL? BTW we're using SQL Server 2008 if that's any help.

TIA, Paolo

Answer 1

You may want to look at IDENTITY. This gives you a column for which the value will be determined when you insert the row.

This may mean that you have to insert the row, and determine the value afterwards, using SCOPE_IDENTITY().

There is also an article on simulating Oracle Sequences in SQL Server here: http://www.sqlmag.com/Articles/ArticleID/46900/46900.html?Ad=1

Answer 2

Identity is one approach, although it will generate unique identifiers at a per table level.

Another approach is to use unique identifiers, in particualr using NewSequantialID() that ensues the generated id is always bigger than the last. The problem with this approach is you are no longer dealing with integers.

The closest way to emulate the oracle method is to have a separate table with a counter field, and then write a user defined function that queries this field, increments it, and returns the value.

Answer 3

slight problem - SQL Server won't let me call UPDATE inside a function. It has to be a function surely otherwise you can't do:

insert into my_table select nextval('sequence_here'), column1,column2 from old_table;

Answer 4

Here is a way to do it using a table to store your last sequence number. The stored proc is very simple, most of the stuff in there is because I'm lazy and don't like suprises should I forget something so...here it is:

----- Create the sequence value table.

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[SequenceTbl] ( [CurrentValue] [bigint] ) ON [PRIMARY]

GO

---

-----------------Create the stored procedure

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE procedure [dbo].[sp_NextInSequence](@SkipCount BigInt = 1) AS

BEGIN

BEGIN TRANSACTION

DECLARE @NextInSequence BigInt;

IF NOT EXISTS
(
  SELECT
    CurrentValue
  FROM
    SequenceTbl
)

INSERT INTO SequenceTbl (CurrentValue) VALUES (0);

SELECT TOP 1
  @NextInSequence = ISNULL(CurrentValue, 0) + 1
FROM
  SequenceTbl WITH (HoldLock);

UPDATE SequenceTbl WITH (UPDLOCK)
  SET CurrentValue = @NextInSequence + (@SkipCount - 1);

COMMIT TRANSACTION

RETURN @NextInSequence END; GO

---

--------Use the stored procedure in Sql Manager to retrive a test value.

declare @NextInSequence BigInt

exec @NextInSequence = sp_NextInSequence;

--exec @NextInSequence = sp_NextInSequence ;

select NextInSequence = @NextInSequence;

-----Show the current table value.

select * from SequenceTbl;

The astute will notice that there is a parameter (optional) for the stored proc. This is to allow the caller to reserve a block of ID's in the instance that the caller has more than one record that needs a unique id - using the SkipCount, the caller need make only a single call for however many IDs are needed. The entire "IF EXISTS...INSERT INTO..." block can be removed if you remember to insert a record when the table is created. If you also remember to insert that record with a value (your seed value - a number which will never be used as an ID), you can also remove the ISNULL(...) portion of the select and just use CurrentValue + 1. Now, before anyone makes a comment, please note that I am a software engineer, not a dba! So, any constructive criticism concerning the use of "Top 1", "With (HoldLock)" and "With (UPDLock)" is welcome. I don't know how well this will scale but this works OK for me so far...