As we know Sequence is new enhanced feature introduced in SQL Server 2012. SEQUENCE work similarly to an IDENTITY value, but where the IDENTITY value is scoped to a specific column in a specific table, the Sequence Object is scoped to the entire database and controlled by application code.  This can allow us to synchronize seed values across multiple tables that reference one another in a parent child relationship.  Or, with a little bit of code we can also take control on whether or not the next value is used or saved for the next INSERT should the current transaction be rolled back where the IDENTITY value is lost and creates a gap when an INSERT is rolled backed. Here are some key differences as

Sequence

Identity

A SQL Server sequence object generates sequence of numbers just like an identity column in sql tables. But the advantage of sequence numbers is the sequence number object is not limited with single SQL table. IDENTITY is a table specific.
The status of the sequence object can be viewed by querying the DMV sys.sequences as shown below.
SELECT
Name,start_value,minimum_value,maximum_value
,current_value

FROM
sys.sequences

System Function @@IDENTITY

can use for the last-inserted identity value

Sequence is an object.
Example :
CREATE
SEQUENCE
MySequesnceName

AS
INT

START
WITH 1

INCREMENT
BY 1

MINVALUE 1

MAXVALUE 1000

NO
CYCLE

NO
CACHE

Identity is a property in a table.

Example :

CREATE
TABLE
TblIdentityChk

(

ID
INT
Identity (1,1),

CUSTNAME
Varchar(50)

)


You can obtain the new value before using it in an INSERT statement You cannot obtain the new value in your application before using it
In the sequence, you do not need to insert new ID, you can view the new ID directly.

Example :

SELECT NEXT VALUE FOR MySequesnceName

If you need a new ID from an identity column you need to
insert and then get new ID.

Example :

Insert into TblIdentityChk Values(‘TEST CUSTOMER’)

GO

SELECT @@IDENTITY AS ‘Identity’

–OR

Select SCOPE_IDENTITY() AS‘Identity’

You can add or remove a default constraint defined for a column with an expression that generates a new sequence value (extension to the standard) You cannot add or remove the property from an existing column
You can generate new values in an UPDATE statement, let see example as
UPDATE
TableName
SET
IDD
=
Next
Values
for
MySequesnceName
You cannot generate new values in an UPDATE statement when needed, rather only in INSERT statements
In the sequence, you can simply add one property to make it a cycle.

Example :

ALTER SEQUENCE MySequesnceName

CYCLE;

You cannot perform a cycle in identity column. Meaning, you cannot restart the counter after a
particular interval.
The semantics of defining ordering in a multi-row insert are very clear using an OVER clause (extension to the standard), and are even allowed in SELECT INTO statements The semantics of defining ordering in a multi-row insert are confusing, and in SELECT INTO statements are actually not guaranteed
Sequence can be easily cached by just setting cache property of
sequence. It also improves the performance.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

CACHE 3;

You cannot cache Identity column property.
The sequence is not table dependent so you can easily remove it

–Let Insert With Sequence object

INSERT INTO TblBooks([ID],[BookName])
VALUES (NEXT VALUE FOR MySequesnceName, ‘MICROSOFT SQL SERVER 2012′)

GO

-Now Insert Second value without Sequence object

INSERT INTO TblBooks([ID],[BookName])
VALUES (2, ‘MICROSOFT SQL SERVER 2012′)

GO

You cannot remove the identity column from the table directly.
You can define minimum and maximum values, whether to allow cycling, and a cache size option for performance

Example :

ALTER SEQUENCE MySequesnceName

MAXVALUE 2000;

You cannot define: minimum and maximum values, whether to allow cycling, and caching options
You can obtain a whole range of new sequence values in one shot using the stored procedure sp_sequence_get_range (extension to the standard), letting the application assign the individual values for increased performance You cannot obtain a whole range of new identity values in one shot, letting the application assign the individual values
You can reseed as well as change the step size.

Example :

ALTER SEQUENCE MySequesnceName

RESTART WITH 7

INCREMENT BY 2;

You can reseed it but cannot change the step size.

Example :

DBCC CHECKIDENT (TblIdentityChk,RESEED, 4)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s