What is sequence? SQL Server sequences allow you to store a sequence of numbers. You are in control of how that sequence operates. You control where it starts, where it ends and the quantity that the sequence is incremented by. It is similar to Identity and GUIDs. Sequences do not need to be put in any column or they can be put in many columns.
create sequence newTestSeq as int start with 7 increment by 1 minvalue 1 maxvalue 9 cycle -- no cycle -- select next value for newTestSeq as NextValue -- Results after each SELECT -- 7, 8, 9, 1, 2, 3, 4 ... -- drop sequence newTestSeq
Notice that the cycle goes back to the minvalue, not the start value. You can have a look at the underlying table with the following. We will not show the result here.
select * from sys.sequences
If we decide not to set the minimum value then the minimum value will be the minimum value of the data type we are using, which will be about negative 4 billion if we are using INT. The start value will be about negative 4 billion, along with the start value. With INT, the maximum value will be the maximum value of the data type which is about 4 billion.
How do we use sequence in practice? Let’s use an example of a table called [Names] that has only one column called [FirstName]. We already have a few rows in this table but we want to add another column to the table. We will add a new column called [NextNumer]. We will give it a default constraint of next value. Here is the code to do that.
alter table dbo.Names add NextNumber int constraint DF_Names DEFAULT NEXT VALUE FOR newTestSeq
When we look at the table with a select statement we notice that the new column has NULL for each of the values in the new column.
We could have created our column another way. Currently the column and constraint exits. Let’s back out of that. How do we do that? We need to drop the constraint, drop the column, add the column and then add the constraint. Here is the code that does that.
alter table Names drop DF_Names; -- drop constraint alter table Names drop column NextNumber; -- drop column alter table Names add NextNumber int; -- add column alter table Names add constraint DF_Names default next value for newTestSeq for NextNumber -- add constraint
Insert a New Row
We will now insert a new row without providing any data for the new column we created called NextNumber.
begin tran select * from Names; insert into Names (FirstName) values ('Julie'); select * from Names; rollback tran
Here is the screen shot in SSMS. The next number was 7 because we have previously experimented and ran it a few times. That’s okay for our test example.
What happens if we run the above code again? It rolls back the transaction of adding a new row. Does it roll back the sequence numbers?
No. The sequence itself does not get undone. You can see that it has gone up to 8.
Cannot Manually Update a System Catalog
SQL Server will not allow you to manually update the current_value of a sequence. If you try to run the following code you will get the error shown below that.
update sys.sequences set current_value = 2 where name = 'newTestSeq';
Msg 259, Level 16, State 1, Line 2 Ad hoc updates to system catalogs are not allowed.
Can Re-set a Sequence
While you cannot just manually update a sequence, you can alter it. What is the code for that? It is called re-setting. We use the syntax alter sequence MySeqName.
alter sequence newTestSeq restart with 1; alter sequence newTestSeq increment by 2; select next value for newTestSeq; select next value for newTestSeq;
Here are the results in SSMS.
Existing NULL Values
What about the existing NULL values? Can we update them to a sequence number? We currently have three NULLs in our table.
select * from Names; -- start withe all NULL in NextNumber begin tran update Names set NextNumber = next value for newTestSeq where FirstName = 'Sally'; select * from Names; update Names set NextNumber = next value for newTestSeq; select * from Names; rollback tran -- rollback does NOT re-set current number so do it here for our second example. alter sequence newTestSeq restart with 1; select * from Names; -- start withe all NULL in NextNumber begin tran update Names set NextNumber = next value for newTestSeq where FirstName = 'Sally'; select * from Names; update Names set NextNumber = next value for newTestSeq where NextNumber is NULL; select * from Names; rollback tran
Here is the results in SSMS. Notice that we are using a WHERE clause with our update statement. Notice the values for Sally in the two examples. Also note that we can use where NextNumber is NULL. Using that where clause preserves the value that Sally had.