Another long-waited feature SEQUENCE is included in the latest version of SQL server code named DENALI. Sequence is now an object in each database and is similar to IDENTITY in functionality. Sequence is an object that has start value, increment value and end value defined in them and this sequence can be added to a column whenever required rather than defining an identity column individually for tables.
Datatypes for which the sequence can be defined are tinyint, smallint, int, bigint, decimal and numeric.
-- Creating a Sequence with name SEQ1 with 1 as its start value, 5 million as its max value and gets incremented by 1 USE [LEKS] GO CREATE SEQUENCE [dbo].[Seq1] AS [int] START WITH 1 INCREMENT BY 1 MAXVALUE 5000000 GO
Once the sequence is created, the metadata for that sequence can be queried from the system catalog sys.sequences. Now lets create a sample table and populate the table with some data that uses the created sequence.
-- Creating a table for applying the SEQUENCE created CREATE TABLE TSeq1(col1 int, col2 varchar(50)); GO -- Inserting values into table TSEQ1 ( Inserting 10000 rows) INSERT TSeq1(col1, col2) VALUES (NEXT VALUE FOR SEQ1, 'Seq Test by Leks') go 10000 -- select from the table TSEQ1 and compare it with current value in Sequence select MAX(col1) from TSEQ1 go select current_value from sys.sequences go -- checking the next value for sequence select next value for dbo.seq1 go
Now let us try to change the start value for the sequence and lets see if there is any change to the underlying table. Always remember that SEQUENCE doesnt need to always start from 1 you can change this setting using the min value / start with switch.
ALTER SEQUENCE dbo.seq1 RESTART WITH 20; -- Inserting values into table TSEQ1 (Inserting 1000 rows) INSERT TSeq1(col1, col2) VALUES (NEXT VALUE FOR SEQ1, 'Seq Test by Leks') Go 1000 -- select from the table TSEQ1 and compare it with current value in Sequence Select MAX(col1) from TSEQ1 -- will still be 10000 Go Select current_value from sys.sequences -- must be changed Go -- checking the next value for sequence select next value for dbo.seq1 -- must be changed go -- run a select on the table TSEQ1 select * from tseq1
and this is how the records in table looks after the change to the sequence is done.
This is more helpful in terms of reseeding a value or breaking the chain in terms of the values inserted which is always a problem in identity ( though it can be done reseeding process in an identity column always requires extra attention from DBAs).
Some notable points about Sequence:
1.Where does the next value for my sequence come from?
The next value for a sequence is from Buffer (memory) and not from disk.
2. How does Rollback / commit transaction work with this? ( Interesting question)
This seems to be bit interesting so lets jump to an example here , we will use the same sequence and table as above.
The last value we inserted was 1019, so the next value should be 1020. Now lets start the begin and rollback Tran
begin tran INSERT TSeq1(col1, col2) VALUES (NEXT VALUE FOR SEQ1, 'Seq Test by Leks') -- it should be doing 1020 value for col1 now rollback tran
Now we havent inserted a value in table , so still the last value in our table is 1019. But lets look into our sequence metadata
select current_value from sys.sequences
Gives me 1020
-- checking the next value for sequence select next value for dbo.seq1 -- must be changed go
Gives me 1021
Perform another insert without any transaction blocks
INSERT TSeq1(col1, col2) VALUES (NEXT VALUE FOR SEQ1, 'Seq Test by Leks')
And look into the table
AHhhhhhhhhhhhhhh the next value inserted in to the table is 1021 and that has missed 1020 in the sequence. So look to avoid sequence inserts inside begin/rollback .sequence is unaware of the difference between begin/rollback and begin/commit.
3. Can I grant permissions on sequence and what are they?
Yes you can grant permissions on a sequence like any other objects in a database to any of the database principal. The permissions are Alter, control, references, update, Take Ownership and View definition.
Look into the books online entry for this sequence in DENALI from here http://msdn.microsoft.com/en-us/library/ff878058%28v=SQL.110%29.aspx . Performance and working principle in detail will be covered in the future articles.