SQL Server: Sequences (Autonumber)
Learn how to create and drop sequences in SQL Server (Transact-SQL) with syntax and examples.
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.
You may wish to create a sequence in SQL Server to handle an autonumber field.
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.
- 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.
Let’s look at an example of how to create a sequence in SQL Server (Transact-SQL).
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.
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’.
Once you have created your sequence in SQL Server (Transact-SQL), you might find that you need to remove it from the database.
The syntax to a drop a sequence in SQL Server (Transact-SQL) is:
DROP SEQUENCE sequence_name;
- The name of the sequence that you wish to drop.
Let’s look at an example of how to drop a sequence in SQL Server (Transact-SQL).
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.
The syntax to a view the properties of a sequence in SQL Server (Transact-SQL) is:
SELECT * FROM sys.sequences WHERE name = 'sequence_name';
- The name of the sequence that you wish to view the properties for.
Let’s look at an example of how to view the properties of a sequence in SQL Server (Transact-SQL).
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:
|name||Sequence name that was assigned in CREATE SEQUENCE statement|
|principal_id||Owner of the sequence|
|schema_id||Schema ID where the sequence was created|
|parent_object_id||ID of the parent 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|