SQL Server 2012: DENALI series: SEQUENCE OBJECT – Part 2

Hi Friends,

My bad; what I should be blogging as Part 1, I am doing as PART 2. Yesterday, I blogged on how SEQUENCE objects in DENALI behave within transactions. You can see that here;

Today, I want to show you how you can use SEQUENCE numbers instead of IDENTITY columns to insert global serial values in your tables.

First create the SEQUENCE object:

USE tempdb
GO
 
 
CREATE SEQUENCE dbo.TestID
AS INT
MINVALUE 1
NO MAXVALUE
START WITH 1;

Next, create two tables:

create table t1
(
orderID integer default (next value for dbo.TestID),
OrderDesc varchar(50)
)
 
create table t2
(
orderID integer default (next value for dbo.TestID),
OrderDesc varchar(50)
)

In the above code, you can observe that we have  put the NEXT VALUE statement as a default for OrderID column. This is how the next number of a SEQUENCE object would be inserted for every new record.

Let us insert some data and test:

insert into t1 (OrderDesc) values('AMit')
GO
insert into t2(OrderDesc) values('AMit')
GO

Run the above code 3 or 4 times.

And check what has been inserted:

select * from t1
GO
select * from t2
GO

Here is the output:

This is how you can use SEQUENCE object as a global serial number generator, common to multiple tables. In my next post I shall try to show you a few more things with SEQUENCE objects.