Microsoft has released Community Technology Preview copies of the next generation SQL SERVER enterprise database product called DENALI. In blogs, peoples are mentioning like Denali is the code name for SQL SERVER 2011.
In this article we are going to look into the most awaited cool features called Sequence Number generation.
Sequence is said to be a User Defined Schema object that generates the numeric values based on the values specified while creating a sequence. It can be generated either in ascending or descending order. Most important thing is, sequence is not associated with tables. Sequence is used across multiple rows and tables.
Simple Syntax:-
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 }
Example:-
CREATE SEQUENCE Schema.SequenceName
AS int
START WITH 1
INCREMENT BY 1;
How to read the Sequence Numbers?
Following syntax will help you to read the sequence number generation.
SELECT
(NEXT VALUE FOR SequenceName) AS [NextVal from Numbers],
(NEXT VALUE FOR SequenceName1) AS [NextVal from Integers]
How to Drop the Sequence?
So Simple !!
Drop Sequence SequenceName
Note:
If the start point is not mentioned while creating a sequence, it will take the upper bound of the datatype as default. For eg:- -2147483648 will be the upper bound for the number sequences.
There are still many more cool features available in this version. They will be covered in following articles. This is just an introduction!