SEQUENCE in SQL Server

Sequence is a set of integers that are ordered by order. Sequences are often used in databases because meeting the requirements of many applications is to require each row in a table to contain a unique value similar to the primary key.

The article will give you the syntax and examples of how to create and delete sequences in SQL Server.

CREATE SEQUENCE (Create Sequence)

Syntax

To create a sequence, we have the following syntax:

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

Parameters:

1. AS datatype: can be BIGINT, INT, TINYINT, SMALLINT, DECIMAL, or NUMERIC types. If you do not specify a specific type, the program will default to your datatype in BIGINT form.
2. START WITH value: The value that the sequence returns.
3. INCREMENT BY value : A sequence of rules for increasing / decreasing sequence, which can be positive or negative. If the value here is positive, the sequence will be a sequence of incremental values. The opposite is negative, the sequence will decrease.
4. MINVALUE value : The smallest value in the string.
5. NO MINVALUE : Do not specify the minimum value.
6. MAXVALUE value : Maximum value in the string.
7. NO MAXVALUE : Do not specify the maximum value.
8. CYCLE : Sequence will start from the beginning when it completes the sequence.
9. NO CYCLE : Sequence will fail at the end of the string, it will not start again when completed.
10. CACHE value: Save in cache (cache) to minimize disk IO.
11. NO CACHE: Do not save in cache.

For example

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

Here we have created a sequence named contacts_seq , starting from value 1, each of the following values ​​increases by one unit (ie 2, 3, 4 .). The string will store about 10 values ​​into the cache. The maximum value in the sequence is 99999 and it will not restart the string after the sequence ends at the largest value.

You can also execute the above command simply as follows:

 CREATE SEQUENCE contacts_seq 
 START WITH 1 
 INCREMENT BY 1; 

So now you've created a sequence that simulates an autonumber field. Next, to output a value from this sequence we use the NEXT VALUE FOR command

 SELECT NEXT VALUE FOR contacts_seq; 

This statement will retrieve the next value from contacts_seq. Then use the necessary commands for your execution. For example:

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

This INSERT statement inserts a new record into the contact table. The contact_id field will be assigned the next number from contacts_seq, the last_name field will be 'Smith'.

DROP SEQUENCE (Delete Sequence)

Once a sequence has been created, there will also be cases where you want to remove the sequence from the database for several reasons.

Syntax

To delete a sequence, we have the following syntax:

 DROP SEQUENCE sequence_name; 

Parameters:

sequence_name: The sequence name you want to delete.

For example

 DROP SEQUENCE contacts_seq; 

By executing this command, you have just removed the contacts_seq string from the database.

Sequence properties

To test sequence properties, we have the following syntax:

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

Parameters:

sequence_name: The name of the string to test the property.

For example

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

This example queries information from the sys.sequences system and retrieves the result for the contacts_seq string .

Sys.sequences system includes the following columns:

COLLECTION NOTE name of the sequence created in the statement CREATE SEQUENCE object_id ID of the principal_id object of the sequence ID principal (numeric value) schema_id Schema ID of sequence parent_object_id ID of the parent object type SO type_desc SEQUENCE_OBJECT create_date Date / Time created sequence with the CREATE SEQUENCE command modify_date Date / Time of last modification sequence is_ms_shipped Value 0 or 1 is_published Value 0 or 1 is_schema_published Value 0 or 1 start_value Start value of the sequence increment Rule value of sequence increase / decrease minimum_value Minimum value in string maximum_value Maximum value in is_cycling string Value 0 or 1. 0 = NO CYCLE, 1 = CYCLE is_cached Value 0 or 1, 0 = NO CACHE, 1 = CACHE cache_size Memory size buffer when is_cached = 1 system_type_id ID system of sequence user_type_id ID worry at sequence precision user Maximum accuracy for data type of maximum range scale for the sequence of sequence current_value Last value retrieved from sequence is_exhausted Value 0 or 1. 0 = Multiple values in sequence. 1 = No value

 

Previous article: Declare variables in SQL Server

Next lesson: FUNCTION (Function) in SQL Server