Sequences in SQL Server 2012

Bandi
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 1522 red flag
Rating: 4 out of 5  
 1 vote(s)

Sequences are introduced in SQL Server 2012, to generate sequential number according to the definition. Also, it is independent of any other objects. As comparison to IDENTITY, Identity property is specific to single table where as Sequence produces numbers to different objects.

Introduction

Sequences are newly introduced in SQL Server 2012. It is a database object bound to schema, which generates sequential number according to the definition.

Unlike IDENTITY property, the SEQUENCE object will generate sequential numeric values for different objects rather than a single table.


Sample for Sequence Creation

CREATE SEQUENCE SeqObj
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 5
NO CYCLE
NO CACHE;

SELECT NEXT VALUE FOR SeqObj -- 1
GO
SELECT NEXT VALUE FOR SeqObj --2
GO
SELECT NEXT VALUE FOR SeqObj -- 3
GO
SELECT NEXT VALUE FOR SeqObj --4 
GO
SELECT NEXT VALUE FOR SeqObj -- 5
GO
When the sequnce exceeds the max value and sequence definition has 'NO CYCLE' option, then the below error occurs.
-- When the maxvalue crossed, it will throw error because of NOCYCLE option 
SELECT NEXT VALUE FOR SeqObj
GO 
Error message:
/*
Msg 11728, Level 16, State 1, Line 1
The sequence object 'SeqObj' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
*/

CYCLE option with MIN and MAX values

CREATE SEQUENCE SeqObj1
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 3
CYCLE
NO CACHE;
 
SELECT NEXT VALUE FOR SeqObj1  -- 1
GO SELECT NEXT VALUE FOR SeqObj1 --2
GO SELECT NEXT VALUE FOR SeqObj1 -- 3
GO

If you specify CYCLE option, then it will re-start the cycle startin gfrom MINVALUE option value when it reaches MAXVALUE

SELECT NEXT VALUE FOR SeqObj1  -- 0
GO


CACHE / NO CACHE option

NO CACHE: We can specify this option if we don’t want the Sequence to use the CACHING.

CACHE: Whenever the system/DB Server crashes, it will start the sequence value from CACHE value…

CREATE SEQUENCE SeqObj
AS INT
START WITH 1
INCREMENT BY 1
CACHE 50;
 
SELECT NEXT VALUE FOR SeqObj
GO 48

-- Restart the SQL sever instance

SELECT NEXT VALUE FOR SeqObj

GO

-- If SQL server stops abnormally it will take next sequence

as Cache value + 1

-- To stop SQL server abnormally you can kill the SQL server Process in Task manager. DO NOT do it in production/critical severs

SELECT NEXT VALUE FOR SeqObj --51 but not the 49

GO DROP SEQUENCE SeqObj GO

Altering a Sequence: 

ALTER SEQUENCE SeqObj
RESTART WITH 2
INCREMENT BY 1;

Note: Data type can’t be changed by alter statement. And “START WITH” cannot be used in an ALTER SEQUENCE command.

Conclusion

Sequences are introduced in SQL Server 2012, it is independent of a single object, generated sequential numeric values.

Page copy protected against web site content infringement by Copyscape

About the Author

Bandi
Full Name: Chandrika Bandi
Member Level: Platinum
Member Status: Member,MVP
Member Since: 7/23/2013 5:52:37 AM
Country: India
[B]Mark This Response as Answer[/B] -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Sheonarayan on: 5/7/2015 | Points: 25
Good one Bandi !
Posted by: Hami on: 7/21/2015 | Points: 25
hi dear.
create table student
(id int constraint pk perimary key (id),
name nvarchar(50)
)
i used sequence for my id column it works good
1. alex
2.hami
3.john
but the problem is when i deleted 2.hami and insert PETER
1.alex
3.john
4.PETER
this is not the scenario that i want from sequence
what i am looking for is this :
1.alex
2.PETER
3.john
can i achived this without writting trigger ?
sqlserver find missing (deleted) id and set it to new row...
thanks alot
hop best.

Login to post response

Comment using Facebook(Author doesn't get notification)