SQL Server 2012 Sequences

SQL Server 2012 sequences provide Oracle style flexibility in doing parent child inserts in situations where the IDENTITY property would otherwise be used. When the IDENTITY property is used to populate a primary key in a parent table, some type of after the fact operation is necessary to find out what the just inserted primary key is. THis value is then used as a foreign key value in the inserts into the child tables. Many people prefer knowing the primary key in advance before the insert into the parent table is executed.

Look at the code sample below and decide for yourself if it is cleaner to know the parent table's primary key in advance.

The scope of the IDENTITY property is an individual table. The scope of a SEQUENCE is the schema. Sequences are found in SSMS under Programmibility.

Keep in mind that when using either SEQUENCE or IDENTITY inside a transaction that their values are never rolled back if a transaction fails or is rolled back. Rollbacks will cause gaps in the numbers generated by either IDENTITY or SEQUENCE. Please don't infer there is something wrong with gaps - it's just an observation. You can intentionally cause gaps if you want. Both IDENTITY and SEQUENCE have an INCREMENT property which will cause gaps whenever the INCREMENT is something other than 1 or -1.

Both in Oracle and SQL Server, SEQUENCE values can be cached. Instead of getting one sequence value at a time, a batch of sequence values can be obtained and loaded into a cache. Appropriate sizing of a sequence cache can improve performance. Depending on your specific circumstances, you may find that using sequences (even without sequence caching) instead of IDENTITY results in a measureable performance improvement.

Since this is our first blog post of 2013, consider having an open mind to trying something new in SQL Server 2012. Have you made any SQL Server New Year's resolutions, such as perhaps not running a script when your database is set to master? I almost did that today when testing the script!

P.S. Thanks to reader London DBA for a correction to step 3.

create schema deleteMe;
go

declare @parentTablePK int;
declare @childTablePK int;

declare @parentTableI table (
    parentTablePK int
  , colA char(1)
);

create sequence deleteMe.oneByOne
start with 1
increment by 1;

create table deleteMe.parentTableI (
    parentTablePK int not null primary key identity
  , colA char(1)
);

create table deleteMe.parentTableS (
    parentTablePK int not null primary key
  , colA char(1)
);

create table deleteMe.childTableI (
    childTablePK int not null primary key identity
  , childTableFK int
  , colB char(1)
  , foreign key (childTableFK) references deleteMe.parentTableI(parentTablePK)
);

create table deleteMe.childTableS (
    childTablePK int not null primary key
  , childTableFK int
  , colB char(1)
  , foreign key (childTableFK) references deleteMe.parentTableS(parentTablePK)
);

insert into deleteMe.parentTableI (colA) values ('x');

select @parentTablePK = SCOPE_IDENTITY();

insert into deleteMe.childTableI (childTableFK, colB) values (@parentTablePK, 'y');

/* STEP 3 parent child inserts using IDENTITY property and return values */

/* STEP 4 parent child inserts using a SEQUENCE */

select @parentTablePK = next value for deleteMe.oneByOne;

set @childTablePK = next value for deleteMe.oneByOne;

insert into deleteMe.parentTableS (parentTablePK, colA) values (@parentTablePK, 'x');

insert into deleteMe.childTableS (childTablePK, childTableFK, colB) values (@childTablePK,@parentTablePK, 'y');

drop table deleteMe.childTableS;
drop table deleteMe.childTableI;
drop table deleteMe.parentTableS;
drop table deleteMe.parentTableI;

drop sequence deleteMe.oneByOne;

drop schema deleteMe;