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