SQL Server Sequences

This article describes how to create an autonumber field with T-SQL Sequences (since SQL Server 2012) as well as the substitute method or generating a unique id using a temp table in SQL Server 2000. Also included, a short demo of how to create a Sequence in Oracle in SQL Developer. Sequences are a nice way to create a unique ID for a table quickly, particularly when creating a table that already has an IDENTITY column.

Creating a Unique ID Before SQL Server 2012 Sequences

As mentioned above, attempting to create a temp table with an Identity column and a column that is a Primary Key (identity) column will result in an error.

To create a unique ID without creating a table with an Identity column, a temp table could be created with an INT column populated with 0 for all rows. Updating the Column, in this case "ID" with a variable set to 0 + 1, the table would then be populated with a sequential set of IDs as seen below.

Oracle Sequences

Oracle has had the ability to create a Sequence in a database since version 6. The syntax to create a Sequence in SQL Server version 2012 and up is about the same as shown here in SQL Developer. Retrieving the Next Value is also similar - without the use of DUAL.

SQL Server Sequences

As of SQL Server 2012, users may create a Sequence in a database with a few restrictions. It can be defined as any integer data type: Int, Big Int, Decimal with a Scale of Zero, etc. A sample below shows the Error Message generated when trying to create a Sequence with a decimal data type with a scale greater than 0.

This sample shows a Sequence created with a Decimal data type with a Scale of 0, a starting value of 1 which increments by 1. The maximum value is 900, and No Cycle indicates that the sequence should not restart at it's minimum value once all values have been used. The Cache value of 10 indicates that memory will cache the current sequence value + the next 9 values only for use.

The Select Next Value statement will provide the Next value of the sequence, but it will also increment the value by 1 and you will lose that value from the sequence. Notice how the Next Value is incremented in the SELECT statements executed one after another in the example below.

Restarting the Sequence with an ALTER statement will reset it back to the Minimum value that it was created with (in this example, it was given a MINVALUE of 1).

Alternate method of viewing the current values of a sequence: use sys.sequences to view the values of your sequence by filtering with the Name variable as shown below. Using a select statement on this table will not increment the sequence as using "next value" will in a select statement.

Usage of Sequences in a Table

To utilize a sequence in a table, simply create the table with a Unique or Primary Key and then write an INSERT statement using Next Value for SequenceName (with data, if needed) to populate it.

The table is created and populated sucessfully with the Sequence values. No Order By statement was used, so the sequence is applied to the data as it was provided in the select statement.