SQL Server Sequence Number

re: SQL Server Sequence Number

Kash — Wed, 03 Oct 2012 20:02:42 GMT

Sequence feature which has been in Oracle for a number of years is now available in MS SQL 2012. A Sequence object is an object that provides functionality similar to Identity (Autonumber) column

The sequence object can be used with more than one table which is not possible in identity object. This is useful when you have parent-child tables and you want to know the value before you insert records.

Kash Mughal

Senior DBA and Trainer

www.sqlserver2012tutorial.com

re: SQL Server Sequence Number

Ayyappan Thangaraj — Mon, 18 Jul 2011 22:20:09 GMT

Now, It is in SQL Server Denali...:)

Sequence Object

re: SQL Server Sequence Number

tdwils — Mon, 11 Dec 2006 21:01:29 GMT

You can avoid accumulating data in the sequence table by putting the INSERT statement inside a nested transaction with a save point that you then rollback.

begin tran

save tran seq

insert into <tablename> default values

set @NewSeqValue = scope_identity()

rollback tran seq

commit

FAQ: SQL Server and Sequence Objects/Generators

Euan Garden's BLOG — Fri, 08 Dec 2006 23:07:56 GMT

SQL Server 2005 includes a lot of changes to help make moving from Oracle to SQL Server easier, these

Microsoft SQL Server Development Customer Advisory Team - Simulating Sequence Objects in SQL Server

Professional Association for SQL Server (PASS) SIG — Tue, 18 Apr 2006 20:10:33 GMT

re: SQL Server Sequence Number

howardy — Thu, 13 Apr 2006 22:06:28 GMT

The method you described certainly will work just fine. The key of this post is to perform the generation of sequence number prior to DML inserts through an identity column based dummy table to improve concurrency.

There can be many variation, and enhancements in different implementations. The sample code is just for illustrative purpose :)

One option can be using the extra field (of course a larger size) to hold additional information of the sequence, such as min/max, and loopback flag - through a dummy record. And the stored procedure "GetNewSeqVal..." can be enhanced to perform some checks.

re: SQL Server Sequence Number

Adam Machanic — Thu, 13 Apr 2006 05:45:12 GMT

I'm a bit confused about Option 2. Why did you add the second column? Why not just have a single column and do:

INSERT <tablename>
DEFAULT VALUES

?

Does the additional column help with something in this scenario?

re: SQL Server Sequence Number

Andy Mackie — Tue, 11 Apr 2006 10:52:55 GMT

Very useful! But ... why can't SQL Server 2005 just implement sequences natively ? That would make life so much simpler, and avoid the need for workarounds such as this. Now if you could get it into 2005 SP1, that would be great :-).