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 |
