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 sequence_name;

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 = 'sequence_name';

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:

Column	Explanation
name	Sequence name that was assigned in CREATE SEQUENCE statement
object_id	Object ID
principal_id	Owner of the sequence
schema_id	Schema ID where the sequence was created
parent_object_id	ID of the parent object
type	SO
type_desc	SEQUENCE_OBJECT
create_date	Date/time when the sequence was created
modify_date	Date/time when the sequence was last modified
is_ms_shipped	0 or 1
is_published	0 or 1
is_schema_published	0 or 1
start_value	Starting value for sequence
increment	Value used to increment sequence
minimum_value	Minimum value allowed for sequence
maximum_value	Maximum value allowed for sequence
is_cycling	0 or 1. 0=NO CYCLE, 1=CYCLE
is_cached	0 or 1, 0=NO CACHE, 1=CACHE
cache_size	Cache size if is_cached = 1
system_type_id	System type ID for sequence
user_type_id	User type ID for sequence
precision	Maximum precision for sequence's datatype
scale	Maximum scale for sequence's datatype
current_value	Last value returned by the sequence
is_exhausted	0 or 1. 0=More values available in sequence. 1=No values available in sequence

SQL Server Tutorial