SQL Server Sequence

 in SQL Server  tagged sequence / increment / identity by

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.

Using Sequence

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.

Another Way

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.