August 13, 2014

SEQUENCE Vs IDENTITY in SQL Server 2012

A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted. Sequences, unlike identity columns, are not associated with specific tables. Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.
CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

1)Independent from table.
Example : 

Example : CREATE SEQUENCE [dbo].[Sequence_ID]
 AS [int]
 START WITH 1
 INCREMENT BY 1 
 MINVALUE 1
 MAXVALUE 1000
 NO CYCLE
 NO CACHE 

http://raresql.com/tag/sequence-vs-identity-in-sql-server-2012/
http://www.c-sharpcorner.com/UploadFile/ff2f08/identity-vs-sequence-object-in-sql-server/
http://msdn.microsoft.com/en-IN/library/ff878091.aspx

No comments: