Sunday, June 10, 2012

sequence in sql server 2012

Finally, Microsoft introduced sequence object in SQL Server 2012 release . A sequence is a user defined, schema bound object that will generate a sequence of numeric values (in ascending or descending order) according to specification. Unlike identity columns, a sequence is created independent of any table. A few interesting differences between the two are;
  • A Sequence object is independent of any table, whereas the Identity column property is table specific
  • Unlike Identity, you can generate a new sequence value before using it in an insert Statement
  • You can define both the minimum & maximum values, as well as cycling & cache size options for a sequence
  • Unlike Identity, a sequence object will increment its value only when it is explicitly called
Let's create a sequence object and see how it works.

CREATE SEQUENCE dbo.emp_sequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
CACHE 20 CYCLE;
GO
SELECT NEXT VALUE FOR dbo.emp_sequence as first_emp
GO
SELECT NEXT VALUE FOR dbo.emp_sequence as next_emp
GO
OUTPUT
first_emp
-----------
1

(1 row(s) affected)

next_emp
-----------
2

(1 row(s) affected)


Now let's see how a sequence object can be used with table and how can act as alternative to identity. For this, we are going to use the same sequence we created in the earlier example, so we need to reset the initial value of sequence

ALTER SEQUENCE dbo.emp_sequence
RESTART WITH 1
INCREMENT BY 1;
GO
CREATE TABLE #employee (emp_name VARCHAR(10)); 
INSERT INTO #employee
VALUES  ('Ajay'), ('Vijay'), ('Sanjay')
GO
SELECT    NEXT VALUE FOR dbo.emp_sequence AS seq_no, emp_name 
FROM    #employee;
GO
DROP TABLE #employee
OUTPUT
(3 row(s) affected)
seq_no      emp_name
----------- ------------
1           Ajay
2           Vijay
3           Sanjay

(3 row(s) affected)

0 comments:

Post a Comment