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:

1_SQL_Server2012_DENALI_series_SEQUENCE_OBJECT_Part2

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.

 

 

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

4 Comments on “SQL Server 2012: DENALI series: SEQUENCE OBJECT – Part 2”

  1. There is no such keyword in Sql server 2008 or it a custom object.I m new in sql server.so please guide me..

  2. HI Rajesh,

    SEQUENCE is not available in 2008 or R2. Its a new feature and is avilable in SQL Server 2012 (DENALI) only.

Leave a Reply

Your email address will not be published. Required fields are marked *