CODEWITHSUNDEEP
t-sqlsequence
PaoloFCantoni
PaoloFCantoni

Reputation: 977

Sequence Generators in T-SQL

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? By the way, we're using SQL Server 2008 if that's any help.

Upvotes: 2

Views: 6372

Answers (3)

Bruce
Bruce

Reputation: 457

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 surprises 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 <skipcount>;

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...

Upvotes: 0

Rad
Rad

Reputation: 8381

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.

Upvotes: 0

Matthew Farwell
Matthew Farwell

Reputation: 61695

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

Upvotes: 2

Related Questions