Within SQL Server, SEQUENCE is a numeric order that has been defined by it’s creator. The SEQUENCE can be generated in either an ascending or descending order, it can have intervals (gaps) within it’s numbers and it can restart itself once it reaches a defined point.
SEQUENCE isn’t bound to a particular table or column, but it is bound to a schema. SEQUENCE’s can be called across mulitple tables and multipple statements and the sequence would continue from where it had previously finished (unless a range had been reserved already).
Creating a SEQUENCE:
Creates a SEQUENCE that starts at 1 and then increases by 1 each time it’s called.
|
1 2 3 4 |
CREATE SEQUENCE dbo.SQ_SequenceOne START WITH 1 INCREMENT BY 1; GO |
This SEQUENCE is the same as the one above, however, it now has minimum value and a maximum value. Once the maximum value is reached the SEQUENCE will restart at the MINVALUE (note that you have to use the keyword CYCLE if you want this behaviour).
|
1 2 3 4 5 6 7 |
CREATE SEQUENCE dbo.SQ_SequenceTwo START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 3 CYCLE; GO |
SEQUENCEs can increment in negative values (-1, -2, -3 … ), increment in intervals (5, 10, 15 …) and they can start at any number (-500, 0, 200 …). The default data type for SEQUENCEs is BIGINT, however, you can specify a data type if you wish by using the AS keyword after you name the sequence:
|
1 2 3 4 |
CREATE SEQUENCE dbo.SQ_SequenceThree AS TINYINT START WITH 1 INCREMENT BY 1; GO |
Using a SEQUENCE:
To call the next value within a SEQUENCE, you use the following syntaax:
|
1 2 3 4 5 6 |
SELECT Name , NEXT VALUE FOR dbo.SQ_SequenceOne as SEQ_1 , NEXT VALUE FOR dbo.SQ_SequenceTwo as SEQ_2 FROM #davesSQLblog |
Both sequences started at 1 and then incremented by 1 when there next values were called. When dbo.SQ_SequenceTwo gets to 3 it restarts at 1 and repeats, however, dbo.SQ_SequenceOne continues to 12. If we ran our SELECT statement again we would see that dbo.SQ_SequenceOne would start at 13 and end on 24 as it isn’t set to CYCLE.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Name SEQ_1 SEQ_2 ------------------------- -------------------- -------------------- Sterling Archer 1 1 Lana Kane 2 2 Cheryl Tunt 3 3 Pam Poovey 4 1 Ray Gillette 5 2 Dr. Krieger 6 3 Woodhouse 7 1 Barry Dillon 8 2 Brett Bunsen 9 3 Cyril Figgis 10 1 Malory Archer 11 2 Major Nikolai Jakov 12 3 |
Dropping a SEQUENECE:
Once you have finished with a SEQUENCE and no longer require it in your database, use the following syntax:
|
1 2 3 4 |
DROP SEQUENCE dbo.SQ_SequenceOne; DROP SEQUENCE dbo.SQ_SequenceTwo; DROP SEQUENCE dbo.SQ_SequenceThree; GO |
Further reading:
The above article only scratches the surface of SEQUENCE. You can read more on MSDN:
- CREATE SEQUENCE (Creating them)
- sp_sequence_get_range (Reserve a range of values)
- NEXT VALUE FOR (getting the next value)
Leave a Comment