Using Sequence in SQL Server 2012

Continuing my series on SQL Server 2012, today we will explore Sequence. Sequence is an object in SQL Server 2012 which can be used to generate customized sequence numbers. Sequence is an independent object and is not associated with any table by default. When you are inserting data into the table, we can make use of sequence values from the sequence object.

Consider the following Sequence and data

create sequence my_seq
    as int
    start with 1
    increment by 1

GO

The above creates the sequence named my_seq which has initial value of 1 and will increment by 1

create table sales(sales_id int, product_id int,qty int,sales_amount decimal(12,2))

GO

Let us add some data to this table generating sales_id value using sequence

insert into sales(sales_id,product_id,qty,sales_amount)
select next value for my_seq,10001,3,1200 union all
select next value for my_seq,10002,2,600 union all
select next value for my_seq,10003,10,200 union all
select next value for my_seq,10001,200,1200 union all
select next value for my_seq,10001,10,1200

In the above insert statement, values are taken from sequence for sales_id column and every time a value is selected from the sequence, its value gets incremented by 1

Now run this select statement and see the result

select * from sales

OUTPUT

If you want to restart the sequence values, you can use ALTER sequence statement

alter sequence my_seq restart with 1

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan