SQL Server: Sequences (Autonumber)

Learn how to create and drop sequences in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server, you can create an autonumber field by using sequences. A sequence is an object in SQL Server (Transact-SQL) that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Create Sequence

You may wish to create a sequence in SQL Server to handle an autonumber field.

Syntax

The syntax to create a sequence in SQL Server (Transact-SQL) is:

CREATE SEQUENCE [schema.]sequence_name
  [ AS datatype ]
  [ START WITH value ]
  [ INCREMENT BY value ]
  [ MINVALUE value | NO MINVALUE ]
  [ MAXVALUE value | NO MAXVALUE ]
  [ CYCLE | NO CYCLE ]
  [ CACHE value | NO CACHE ];

AS datatype

It can be BIGINT, INT, TINYINT, SMALLINT, DECIMAL, or NUMERIC. If datatype is not specified, the sequence will default to a BIGINT datatype.

START WITH value

The starting value that the sequence returns intially.

INCREMENT BY value

It can be either a positive or negative value. If a positive value is specified, the sequence will be an ascending sequence of values. If a negative value is specified, the sequence will be a descending sequence of values.

MINVALUE value

The minimum value allowed for the sequence.

NO MINVALUE

It means that there is no minimum value specified for the sequence.

MAXVALUE value

The maximum value allowed for the sequence.

NO MAXVALUE

It means that there is no maximum value specified for the sequence.

CYCLE

It means that the sequence will start over once it has completed the sequence.

NO CYCLE

It means that the sequence will raise an error when it has completed the sequence. It will not start the sequence over again.

CACHE value

It caches the sequence numbers to minimize disk IO.

NO CACHE

It does not cache the sequence numbers.

Example

Let's look at an example of how to create a sequence in SQL Server (Transact-SQL).

For example:

CREATE SEQUENCE contacts_seq
  AS BIGINT
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 99999
  NO CYCLE
  CACHE 10;

This would create a sequence object called contacts_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 10 values for performance. The maximum value that the sequence number can be is 99999 and the sequence will not cycle once that maximum is reached.

So you can simplify your CREATE SEQUENCE statement as follows:

CREATE SEQUENCE contacts_seq
  START WITH 1
  INCREMENT BY 1;

Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to use the NEXT VALUE FOR command.

For example:

SELECT NEXT VALUE FOR contacts_seq;

This would retrieve the next value from contacts_seq. The nextval statement needs to be used in a SQL statement. For example:

INSERT INTO contacts
(contact_id, last_name)
VALUES
(NEXT VALUE FOR contacts_seq, 'Smith');

This INSERT statement would insert a new record into the contacts table. The contact_id field would be assigned the next number from the contacts_seq sequence. The last_name field would be set to 'Smith'.

Drop Sequence

Once you have created your sequence in SQL Server (Transact-SQL), you might find that you need to remove it from the database.

Syntax

The syntax to a drop a sequence in SQL Server (Transact-SQL) is:

DROP SEQUENCE <strong>sequence_name</strong>;

sequence_name

The name of the sequence that you wish to drop.

Example

Let's look at an example of how to drop a sequence in SQL Server (Transact-SQL).

For example:

DROP SEQUENCE contacts_seq;

This example would drop the sequence called contacts_seq.

Properties of Sequence

Once you have created your sequence in SQL Server (Transact-SQL), you might want to view the properties of the sequence.

Syntax

The syntax to a view the properties of a sequence in SQL Server (Transact-SQL) is:

SELECT *
FROM sys.sequences
WHERE name = '<strong>sequence_name'</strong>;

sequence_name

The name of the sequence that you wish to view the properties for.

Example

Let's look at an example of how to view the properties of a sequence in SQL Server (Transact-SQL).

For example:

SELECT *
FROM sys.sequences
WHERE name = 'contacts_seq';

This example would query the sys.sequences system view and retrieve the information for the sequence called contacts_seq.

The sys.sequences view contains the following columns: