2012 – Sequence

One of the new T-SQL features in SQL 2012 is the Sequence object.

Books Online – Sequence

The object is used to generate identity values for a table. One advantage is that a value can be generated without having to first make an insert into a table.

To create:

create sequence dbo.TestSequence
as int
start with 1
increment by 1
cycle
cache 10

So we can specify the value the sequence starts with, and by how much it gets incremented. We can specify CYCLE to reuse values once we get to the largest value allowed by the data type, or NO CYCLE to raise an exception once the limit is reached.
We can also specify CACHE to obtain a specified number of values at once.

There are two ways to use the Sequence. We can go to the Sequence on each insert to get the next value, or we can use the Sequence as part of the table definition.

— Not part of table definition

create table TestTable1 (
RecordNumber int not null primary key clustered,
DisplayName varchar(25) not null
)
go

insert into TestTable1 (RecordNumber, DisplayName)
values (next value for dbo.TestSequence, ‘Record One’)

insert into TestTable1 (RecordNumber, DisplayName)
values (next value for dbo.TestSequence, ‘Record Two’)

select * from TestTable1

go

— Part of table definition

create table TestTable2 (
RecordNumber int not null primary key clustered default(next value for dbo.TestSequence),
DisplayName varchar(25) not null
)
go

insert into TestTable2 (DisplayName)
values (‘Record Three’)

select * from TestTable2

go

We can query the catalog view to see all Sequences:
select * from sys.sequences

We can also use ALTER SEQUENCE to change the Sequence, and DROP SEQUENCE to remove it from the database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: