SQL Server: Sequences (Autonumber)

 

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:

ColumnExplanation
nameSequence name that was assigned in CREATE SEQUENCE statement
object_idObject ID
principal_idOwner of the sequence
schema_idSchema ID where the sequence was created
parent_object_idID of the parent object
typeSO
type_descSEQUENCE_OBJECT
create_dateDate/time when the sequence was created
modify_dateDate/time when the sequence was last modified
is_ms_shipped0 or 1
is_published0 or 1
is_schema_published0 or 1
start_valueStarting value for sequence
incrementValue used to increment sequence
minimum_valueMinimum value allowed for sequence
maximum_valueMaximum value allowed for sequence
is_cycling0 or 1. 0=NO CYCLE, 1=CYCLE
is_cached0 or 1, 0=NO CACHE, 1=CACHE
cache_sizeCache size if is_cached = 1
system_type_idSystem type ID for sequence
user_type_idUser type ID for sequence
precisionMaximum precision for sequence's datatype
scaleMaximum scale for sequence's datatype
current_valueLast value returned by the sequence
is_exhausted0 or 1. 0=More values available in sequence. 1=No values available in sequence

No comments:

Post a Comment

SQL Server: Joins

  SQL Server:   Joins This SQL Server tutorial explains how to use   JOINS , both INNER and OUTER JOINS, in SQL Server (Transact-SQL) with s...