Click on the banner to learn about and purchase my database training on Azure

SQL Server 2012 - Working with Sequences and IDENTITY Comparisons

Views: 8.072 views
Reading Time: 5 minutes

Hello everybody!
Good Morning!

Today I came across a situation where the use of the sequences, old known to me from Oracle and that from SQL Server 2012 were introduced in Microsoft DBMS, saw a perfect solution to the problem I was facing.

Introduction - Detailing the Problem

This problem consisted of a table with tens of millions of records that has a sequential number for control and uniqueness of the record and now a need has arisen to create another sequential number in that same independent table where existing records should be NULL and that would be started in 1, to control another type of information and only to be populated when a given event occurs (unlike the existing sequence that is generated with each insertion in the table)

Another situation that should be avoided at all costs is that never two sessions can take the same sequence and write to the table and this possibility is quite possible, since this table has multiple inserts in parallel sessions per second.

In this case, using IDENTITY would not be possible since IDENTITY is applied to all records in the table, which would not be the case. Using ranking structures like ROW_NUMBER could even be feasible if it were not so heavy to calculate this ranking with each new insertion (besides the possibility of two sessions taking the same sequential).

Differences Between Sequence and IDENTITY

It is an independent object, which can be used to populate any column (including more than one in the same table) of integer type (int, bigit, smallint, tinyint, 0-scale decimal or 0-scale numeric), of one or more tablesIs associated with a column of a table.
It is populated when called. That is, it can be populated with each insertion or only when some condition is metPopulates on each insert
When started on an already populated table, previous records will not changeWhen started in the table, the entire column is populated with the sequential
Must be called manually to generate sequentialThe sequential is automatically generated
Has separate permissionsNo additional permissions beyond table
Minimum and maximum value can be set (Ex: From 1 to 100)The maximum value is the limit of the column data type.
The sequence can be restarted automatically upon reaching the maximum value (CYCLE parameter)When maximum value is reached, no more records can be entered
A new sequence can be generated in UPDATE commands if required.Sequential is generated only on INSERT of data
Available from SQL Server 2012Available from SQL Server 6.0 (SQL 95)
The current value of the sequence can be The current value of the sequence can be queried through the sys.sequences view.querying through the sys.identity_columns view
String value can be resetString value cannot be reset

How to create a sequence

To solve the problem described above, I had the idea of ​​creating a sequence, which is a database object specially created for this kind of need. Unlike IDENTITY, you can use more than one sequence in the table and previous records are not changed. These were the reasons that led me to use this feature of SQL Server to solve this situation.

Let's see now how to create a sequence:

The parameters explained:

  • START WITH: Defines the starting sequence number.
  • MINVALUE and MAXVALUE: Delimits the SEQUENCE limit to its respective maximum and minimum value. If the value is not defined, the maximum and minimum value of the chosen data type will be assigned.
  • INCREMENT BY: Defines the amount that will be incremented in sequence. In the example above, 1 will be incremented by 1.
  • CYCLE: The CYCLE property allows you to start a cycle again once the MINVALUE and MAXVALUE property is reached. That is, when reaching the value set in MAXVALUE, the sequence will start again at the value of the parameter MINVALUE (when this occurs, duplicate values ​​will be generated in the sequence, because the whole range has already been traversed)
  • CACHE: When using this parameter, SQL Server pre-allocates the sequence numbers by the CACHE property, the default value for which is 15, meaning that the values ​​of the next 15 available values ​​will be allocated in memory until they are used and the sequence already treats these numbers as used. When all CACHE numbers are used, 15 new values ​​are allocated to memory again and the cycle follows. It is worth remembering that if the instance is restarted, the numbers that are in the cache are lost and this “hole” is left in the sequence.

How to return the next number in a sequence

Returning the next number in a sequence is a very simple task:

SQL Server - Sequence Next Value For

However, you should keep in mind that unlike IDENTITY, each insert you must call sequence to return the sequential and insert into the table or create a default constraint to automate this task.

SQL Server - Sequence x Identity

How to return the next number of a sequence automatically

Although most people (including me) use the sequence manually with each insert to return the next sequence, this can be automated using a DEFAULT CONSTRAINT in the table:

SQL Server - Sequence NEXT VALUE FOR Automatic

How to reset the value of sequence

At times, the sequence counter must be reset or changed to a specific value. For this we can use ALTER SEQUENCE for this task:

In the example above, we are resetting the string value to 1.

SQL Server - Sequence Restart

Retrieving current value of sequence without increasing sequence

To retrieve the current value of sequence simply perform a query of the sys.sequences view:

SQL Server - Sequence Current Value

How to change a sequence

Changing a sequence follows the same parameters as the creation, and can be changed at any time.


How to delete a sequence

Removing a sequence from the SQL Server database is as simple as any other database object and can be done using the DROP statement:

Sequence permissions

As already commented, sequence are independent objects in the database and therefore have independent permissions as well:

CREATE SEQUENCE: To create a sequence, you must have CREATE SEQUENCE, ALTER, or CONTROL permission on the schema. Role users db_owner and db_ddladmin can create, change, and drop sequences, and role users db_owner and db_datawriter can use sequence to return the next sequence number.

Grant Example:

ALTER SEQUENCE: To change a sequence, you must have ALTER permission on the schema.

Grant Example:

DROP SEQUENCE: To delete a sequence, you must have ALTER or CONTROL permission on the schema.

Demonstration of a single sequence for more than one table

Test Source Code


That's it folks!
Hope you enjoyed and see you in the next post.

Note: Need to use a sequence in a user defined function, either scalar, aggregate or table-valued and is not able? See the solution in the post Using sequences in user defined functions in SQL Server : )

sql server sequence how to use work restart reset create delete change return get current value next value

sql server sequence how to use work restart reset create delete change return get current value next value