Yesterday I posted blog post on the subject SQL SERVER – 2011 – Introduction to SEQUENCE – Simple Example of SEQUENCE and I received comment where user was not clear about difference between SEQUENCE and IDENTITY.

The reality is that SEQUENCE not like IDENTITY. There is very clear difference between them. Identity is about single column. Sequence is always incrementing and it is not dependent on any table.

Here is the quick example of the same.

USE AdventureWorks2008R2
GO
CREATE SEQUENCE [Seq]
AS [int]
START WITH 1
INCREMENT BY 1
MAXVALUE 20000
GO
-- Run five times
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
GO
-- Clean Up
DROP SEQUENCE [Seq]
GO

Here is the resultset.

Reference: Pinal Dave (http://blog.SQLAuthority.com)