THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

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.

/* STEP 1 create objects */

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)
);

 

 

/* STEP 2 parent child inserts using IDENTITY property */

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 */

delete from @parentTableI;

 

insert into deleteMe.parentTableI (colA)

output inserted.parentTablePK, inserted.colA into @parentTableI

values ('x');

 

insert into deleteMe.childTableI (childTableFK, colB)

select @parentTablePK, 'y' from @parentTableI;

 

 

/* 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');

 

/* STEP 5 cleanup */

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

drop sequence deleteMe.oneByOne;

drop schema deleteMe;

Published Tuesday, January 01, 2013 5:07 PM by John Paul Cook

Attachment(s): sequences.txt

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement