Sequence is a table-independent structure similar to the identity logic in SQL Server. You can use the sequence in more than one table.
You may want to read the article “Identitiy Insert On SQL Server” for more information on Identity.
With Sequence, numeric and sequential numbers are generated from certain values. Sequential numbers can be generated for different tables using the same sequence in multiple tables.
It would be more appropriate to go through an example to know the Sequence better.
For example, in the following example, we create a sequence.
|
1 2 3 4 5 6 7 8 |
CREATE SEQUENCE SeqExample --Sequence name is SeqExample AS INTEGER --Data type is INT START WITH 1 --Starts from 1 INCREMENT BY 1 --Sequence value increases one by one MINVALUE 1 --min value is 1 MAXVALUE 99 --max value is 99 NO CYCLE --When the Sequence value reaches 99, it does not start again from 1 NO CACHE; --it does not keep the sequence numbers in the cache. |
If we created CACHE 30 instead of NO CACHE, it would cache numbers from 1 to 30, and when it reached 30, it would cache the numbers from 30 to 60 again.
Giving a CACHE value to make less IO seems to be good in terms of performance, but it can be seen that it doesn’t noticeably improve performance, but the numbers in the cache may be lost if the server unexpectedly shuts down.
After creating the Sequence, let’s start using it.
We can use it as follows during the table creation.
|
1 2 3 4 |
CREATE TABLE SeqTable ( ID int not null CONSTRAINT seqconst DEFAULT NEXT value FOR SeqExample, City varchar(20) not null ) |
Or we can add it to an existing table as follows.
|
1 2 3 4 |
ALTER TABLE SeqTable ADD CONSTRAINT seqconst DEFAULT NEXT value FOR SeqExample FOR ID |
Since we’re linking the sequence to the table when we create the table, the above ALTER TABLE statement will give an error as follows.
Msg 1781, Level 16, State 1, Line 1
Column already has a DEFAULT bound to it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.
Or, instead of adding to the table, we can get this value from the sequence in the insert clause.
Although we’re linking the sequence to the table while creating the Table, the following query won’t fail.
Even if there is a sequence linked to the table, we can add a record to the table as follows using same or another sequence.
|
1 2 |
INSERT INTO [dbo].[SeqTable] ([ID],[City]) VALUES (NEXT VALUE FOR SeqExample,'ANKARA') INSERT INTO [dbo].[SeqTable] ([ID],[City]) VALUES (NEXT VALUE FOR SeqExample,'ISTANBUL') |
We can re-program the Sequence to start after a certain value by ALTER.
|
1 2 |
ALTER SEQUENCE SeqExample RESTART WITH 70; |
After we’ve changed the Sequence, let’s add another record.
|
1 2 |
INSERT INTO [dbo].[SeqTable] ([ID],[City]) VALUES (NEXT VALUE FOR SeqExample,'IZMIR') Select * FROM [dbo].[SeqTable] |
As you can see, since we added the IZMIR record after reprograming the sequence, the ID value of IZMIR was 70.
You can see the existing sequences as follows.
|
1 |
SELECT name,current_value FROM sys.sequences |
