SQL SERVER – 2011 – SEQUENCE is not IDENTITY

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Sun, 30 Jan 2011 01:30:19 +0000 Indexed on 2011/01/30 7:28 UTC
Read the original article Hit count: 529

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)


Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

© SQL Authority or respective owner

Related posts about Pinal Dave

Related posts about PostADay