{"id":1889,"date":"2019-04-30T17:50:41","date_gmt":"2019-04-30T10:50:41","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1889"},"modified":"2020-04-11T20:12:14","modified_gmt":"2020-04-11T13:12:14","slug":"sql-server-sequence","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-sequence\/","title":{"rendered":"SQL Server Sequence"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the SQL Server Sequence objects to generate a sequence of numeric values based on a specified specification.<\/p>\n\n\n\n

What is a sequence #<\/a><\/h2>\n\n\n\n

A sequence is simply a list of numbers, in which their orders are important. For example, the {1,2,3} is a sequence while the {3,2,1} is an entirely different sequence.<\/p>\n\n\n\n

In SQL Server, a sequence is a user-defined schema-bound object that generates a sequence of numbers according to a specified specification. A sequence of numeric values can be in ascending or descending order at a defined interval and may cycle if requested.<\/p>\n\n\n\n

SQL Server CREATE SEQUENCE statement #<\/a><\/h2>\n\n\n\n

To create a new sequence object, you use the CREATE SEQUENCE<\/code> statement as follows:<\/p>\n\n\n

CREATE<\/span> SEQUENCE<\/span> [schema_name.] sequence_name  \n    [ AS<\/span> integer_type ]  \n    [ START<\/span> WITH<\/span> start_value ]  \n    [ INCREMENT<\/span> BY<\/span> increment_value ]  \n    [ { MINVALUE<\/span> [ min_value ] } | { NO<\/span> MINVALUE<\/span> } ]  \n    [ { MAXVALUE [ max_value ] } | { NO<\/span> MAXVALUE } ]  \n    [ CYCLE<\/span> | { NO<\/span> CYCLE<\/span> } ]  \n    [ { CACHE<\/span> [ cache_size ] } | { NO<\/span> CACHE<\/span> } ];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s examine the syntax in detail:<\/p>\n\n\n\n
sequence_name #<\/a><\/h3>\n\n\n\n
Specify a name for the sequence which is uniquely in the current database.<\/p>\n\n\n\n
AS integer_type #<\/a><\/h3>\n\n\n\n
Use any valid integer type for the sequence e.g., TINYINT<\/code>, SMALLINT<\/code>, INT<\/a><\/code>, BIGINT<\/code>, or DECIMAL<\/a><\/code> and NUMERIC<\/code> with a scale of 0. By default, the sequence object uses BIGINT<\/code>.<\/p>\n\n\n\n
START WITH start_value #<\/a><\/h3>\n\n\n\n
Specify the first value that the sequence returns. The start_value<\/code> must be between the range (min_value<\/code>, max_value<\/code>).<\/p>\n\n\n\n
The start_value<\/code> defaults to the min_value<\/code> in an ascending sequence and max_value<\/code> in a descending sequence.<\/p>\n\n\n\n
INCREMENT BY increment_value #<\/a><\/h3>\n\n\n\n
Specify the increment_value<\/code> of the sequence object when you call the NEXT VALUE FOR<\/code> function.<\/p>\n\n\n\n
If increment_value<\/code> is negative, the sequence object is descending; otherwise, the sequence object is ascending. Note that the increment_value<\/code> cannot be zero.<\/p>\n\n\n\n
[ MINVALUE min_value | NO MINVALUE ] #<\/a><\/h3>\n\n\n\n
Specify the lower bound for the sequence object. It defaults to the minimum value of the data type of the sequence object i.e., zero for TINYINT<\/code> and a negative number for all other data types.<\/p>\n\n\n\n
[ MAXVALUE max_value | NO MAXVALUE] #<\/a><\/h3>\n\n\n\n
Specify the upper bound for the sequence object. It defaults to the maximum value of the data type of the sequence object.<\/p>\n\n\n\n
[ CYCLE | NO CYCLE ] #<\/a><\/h3>\n\n\n\n
Use CYCLE<\/code> if you want the value of the sequence object to restart from the min_value<\/code> for the ascending sequence object, or max_value<\/code> for the descending sequence object or throw an exception when its min_value<\/code> or max_value<\/code> is exceeded. SQL Server uses NO CYCLE<\/code> by default for new sequence objects.<\/p>\n\n\n\n
[ CACHE cache_size ] | NO CACHE ] #<\/a><\/h3>\n\n\n\n
Specify the number of values to cache to improve the performance of the sequence by minimizing the number of disk I\/O required to generate sequence numbers. By default, SQL Server uses NO CACHE<\/code> for new sequence objects.<\/p>\n\n\n\n
SQL Server Sequence examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of creating sequences.<\/p>\n\n\n\n
A) Creating a simple sequence example #<\/a><\/h3>\n\n\n\n
The following statement uses the CREATE SEQUENCE<\/code> statement to create a new sequence named item_counter<\/code> with the type of integer (INT<\/code>), which starts from 10 and increments by 10:<\/p>\n\n\n
CREATE<\/span> SEQUENCE<\/span> item_counter\n    AS<\/span> INT<\/span>\n    START<\/span> WITH<\/span> 10<\/span>\n    INCREMENT<\/span> BY<\/span> 10<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can view the sequence object under in the Programmability > Sequences<\/strong> as shown in the following picture:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The following statement returns the current value of the item_counter<\/code> sequence:<\/p>\n\n\n
SELECT<\/span> NEXT<\/span> VALUE<\/span> FOR<\/span> item_counter;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
Current_value\n-------------<\/span>\n10\n\n(1 row affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the NEXT VALUE FOR<\/code> function generates a sequence number from the item_counter<\/code> sequence object.<\/p>\n\n\n\n
Each time you execute the following statement again, you will see that the value of the item_counter<\/code> will be incremented by 10:<\/p>\n\n\n
SELECT<\/span> NEXT<\/span> VALUE<\/span> FOR<\/span> item_counter;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This time the output is:<\/p>\n\n\n
Current_value\n-------------<\/span>\n20\n\n(1 row affected)    \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
B) Using a sequence object in a single table example #<\/a><\/h3>\n\n\n\n
First, create a new schema named procurement<\/code>:<\/p>\n\n\n
CREATE<\/span> SCHEMA<\/span> procurement;\nGO\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> TABLE<\/span> procurement.purchase_orders(\n    order_id INT<\/span> PRIMARY KEY<\/span>,\n    vendor_id int<\/span> NOT<\/span> NULL<\/span>,\n    order_date date<\/span> NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, create a new sequence object named order_number<\/code> that starts with 1 and is incremented by 1:<\/p>\n\n\n
CREATE<\/span> SEQUENCE<\/span> procurement.order_number \nAS<\/span> INT<\/span>\nSTART<\/span> WITH<\/span> 1<\/span>\nINCREMENT<\/span> BY<\/span> 1<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> procurement.purchase_orders\n    (order_id,\n    vendor_id,\n    order_date)\nVALUES<\/span>\n    (NEXT<\/span> VALUE<\/span> FOR<\/span> procurement.order_number,1<\/span>,'2019-04-30'<\/span>);\n\n\nINSERT<\/span> INTO<\/span> procurement.purchase_orders\n    (order_id,\n    vendor_id,\n    order_date)\nVALUES<\/span>\n    (NEXT<\/span> VALUE<\/span> FOR<\/span> procurement.order_number,2<\/span>,'2019-05-01'<\/span>);\n\n\nINSERT<\/span> INTO<\/span> procurement.purchase_orders\n    (order_id,\n    vendor_id,\n    order_date)\nVALUES<\/span>\n    (NEXT<\/span> VALUE<\/span> FOR<\/span> procurement.order_number,3<\/span>,'2019-05-02'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, view the content of the procurement.purchase_orders<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n    order_id, \n    vendor_id, \n    order_date\nFROM<\/span> \n    procurement.purchase_orders;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
C) Using a sequence object in multiple tables example #<\/a><\/h3>\n\n\n\n
First, create a new sequence object:<\/p>\n\n\n
CREATE<\/span> SEQUENCE<\/span> procurement.receipt_no\nSTART<\/span> WITH<\/span> 1<\/span>\nINCREMENT<\/span> BY<\/span> 1<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create procurement.goods_receipts<\/code> and procurement.invoice_receipts<\/code> tables:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> procurement.goods_receipts\n(\n    receipt_id   INT<\/span>\tPRIMARY KEY<\/span> \n        DEFAULT<\/span> (NEXT<\/span> VALUE<\/span> FOR<\/span> procurement.receipt_no), \n    order_id     INT<\/span> NOT<\/span> NULL<\/span>, \n    full_receipt BIT<\/span> NOT<\/span> NULL<\/span>,\n    receipt_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    note NVARCHAR<\/span>(100<\/span>),\n);\n\n\nCREATE<\/span> TABLE<\/span> procurement.invoice_receipts\n(\n    receipt_id   INT<\/span> PRIMARY KEY<\/span>\n        DEFAULT<\/span> (NEXT<\/span> VALUE<\/span> FOR<\/span> procurement.receipt_no), \n    order_id     INT<\/span> NOT<\/span> NULL<\/span>, \n    is_late      BIT<\/span> NOT<\/span> NULL<\/span>,\n    receipt_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    note NVARCHAR<\/span>(100<\/span>)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that both tables have the receipt_id<\/code> whose values are derived from the procurement.receipt_no<\/code> sequence.<\/p>\n\n\n\n
Third, insert some rows into both tables without supplying the values for the receipt_id<\/code> columns:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> procurement.goods_receipts(\n    order_id, \n    full_receipt,\n    receipt_date,\n    note\n)\nVALUES<\/span>(\n    1<\/span>,\n    1<\/span>,\n    '2019-05-12'<\/span>,\n    'Goods receipt completed at warehouse'<\/span>\n);\nINSERT<\/span> INTO<\/span> procurement.goods_receipts(\n    order_id, \n    full_receipt,\n    receipt_date,\n    note\n)\nVALUES<\/span>(\n    1<\/span>,\n    0<\/span>,\n    '2019-05-12'<\/span>,\n    'Goods receipt has not completed at warehouse'<\/span>\n);\n\nINSERT<\/span> INTO<\/span> procurement.invoice_receipts(\n    order_id, \n    is_late,\n    receipt_date,\n    note\n)\nVALUES<\/span>(\n    1<\/span>,\n    0<\/span>,\n    '2019-05-13'<\/span>,\n    'Invoice duly received'<\/span>\n);\nINSERT<\/span> INTO<\/span> procurement.invoice_receipts(\n    order_id, \n    is_late,\n    receipt_date,\n    note\n)\nVALUES<\/span>(\n    2<\/span>,\n    0<\/span>,\n    '2019-05-15'<\/span>,\n    'Invoice duly received'<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, query data from both tables:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> procurement.goods_receipts;\nSELECT<\/span> * FROM<\/span> procurement.invoice_receipts;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Sequence vs. Identity columns #<\/a><\/h2>\n\n\n\n
Sequences, different from the identity columns<\/a>, are not associated with a table. The relationship between the sequence and the table is controlled by applications. In addition, a sequence can be shared across multiple tables.<\/p>\n\n\n\n
The following table illustrates the main differences between sequences and identity columns:<\/p>\n\n\n\n
Property\/Feature<\/b><\/td>Identity<\/b><\/td>Sequence Object<\/b><\/td><\/tr>
Allow specifying minimum and\/or maximum increment values<\/td>No<\/td>Yes<\/td><\/tr>
Allow resetting the increment value<\/td>No<\/td>Yes<\/td><\/tr>
Allow caching increment value generating<\/td>No<\/td>Yes<\/td><\/tr>
Allow specifying starting increment value<\/td>Yes<\/td>Yes<\/td><\/tr>
Allow specifying increment value<\/td>Yes<\/td>Yes<\/td><\/tr>
Allow using in multiple tables<\/td>No<\/td>Yes<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
When to use sequences #<\/a><\/h2>\n\n\n\n
You use a sequence object instead of an identity column in the following cases:<\/p>\n\n\n\n
After that, insert<\/a> three rows into the procurement.purchase_orders<\/code> table and uses values generated by the procurement.order_number<\/code> sequence:<\/p>\n\n\n
Next, create a new table<\/a> named orders<\/code>:<\/p>\n\n\n