SEQUENCE–How to create Identity Key across tables in SQL Server “Denali” ?
SQL Server “Denali” introduces a new feature called “SEQUENCE”. SEQUENCE is a user-defined object that generates a sequence of numeric values according to specified options.
Its not bound to a table like IDENTITY, You can use SEQUENCE across tables
Supported Data Types for creating SEQUENCE are as follows:
The above code creates a simple SEQUENCE with starting number as 1 and increments by 1
To get the next number in SEQUENCE, you need to use “NEXT VALUE” option
The below code might syntactically look same as the SEQUENCE "- RunningNumbers, but if you look at the output, its entirely different
If you see the output from RunningNumbers2, you will be getting a Negative value, its due to not specifying the Start number, so make sure you specify a Positive Start Number if you don’t want your table keys to Negative
Other options available in Creating Sequence are as follows:
You can specify Minimum Value and Maximum Value and whether to Cycle the numbers when reaching maximum value or not
If you see the below output, for every 5 records, the IDs are recycling and starts from 1 again
Restarting the Sequence
To restart the Sequence, you need to ALTER the sequence with RESTART option and start value
Since we restarted the Sequence with 1, Now we are getting output as 1 for RunningNumber2
Find the available Sequences in a database
You can Sys.Sequence system view to query the list of available Sequences in a database
How to use it as Identity across tables ?
Create tables for each reqion
Insert data to table using Sequences
Query the inserted data
If you see the Union output from 4 different tables, we are able to create a identity column across 4 tables, This will be very helpful when you horizontally partition data across tables
Delete the Sequences
I hope you all find this information about Sequences useful and informative !!!