Sequences in SQL Server .

SQL Server 2011 has introduced sequences that has been in Oracle for years .
In Oracle , the sequence was used to generate unique numbers to maintain ( surrogate ) primary keys.

SQL Server / Sybase have used identity column to generate unique numbers.
Unlike identity column , sequence is not tied to a table .

It eases the pain , when migrating the data from Oracle to SQL Server or vice versa.Wonder , if Oracle would come up with 'identity column' in the future releases.

See the examples below ...

C:\>sqlcmd -S ZMOHIDEEN\SQL11
1> use test2011
2> go


Changed database context to 'test2011'.


1> CREATE TABLE T (
2> TID int,
3> FName varchar(50) NOT NULL,
4> LName varchar(50) NOT NULL,
5> );
6> GO

1>
2> select * from t;
3> go

TID      FName        LName
------ -------------- -------------------
(0 rows affected)


1> CREATE SEQUENCE SEQ_T
2> START WITH 2000
3> INCREMENT BY 1
4> MAXVALUE 5000
5> CACHE 20
6>
7> GO

1> INSERT INTO T (
2> TID, FName, LName
3> ) VALUES (
4> NEXT VALUE FOR SEQ_T, 'Zahir', 'Mohideen'
5> );
6> go


(1 rows affected)


1> select * from t;
2> go


TID            FName                    LName
----------- ---------------------- ------------------------------
2000            Zahir              Mohideen
(1 rows affected)


1> INSERT INTO T (
2> TID, FName, LName
3> ) VALUES (
4> NEXT VALUE FOR SEQ_T, 'N', 'Mohideen'
5> );
6> go
(1 rows affected)

1> select * from t;
2> go

TID             FName                         LName
----------- -------------------------------   --------------
2000        Zahir                             Mohideen
2001        N                                 Mohideen

(2 rows affected)