{"id":92718,"date":"2021-10-25T14:47:33","date_gmt":"2021-10-25T14:47:33","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92718"},"modified":"2021-11-30T19:10:13","modified_gmt":"2021-11-30T19:10:13","slug":"introduction-to-sql-server-sequence-objects","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/introduction-to-sql-server-sequence-objects\/","title":{"rendered":"Introduction to SQL Server sequence objects"},"content":{"rendered":"<p><strong>The series so far:<\/strong><\/p>\n<ol>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/introduction-to-sql-server-sequence-objects\/\">Introduction to SQL Server sequence objects<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/using-sql-server-sequence-objects\/\">Using SQL Server sequence objects<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/how-to-return-multiple-sequence-numbers-with-sp_sequence_get_range\/\">How to return multiple sequence numbers with sp_sequence_get_range<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/how-replace-identity-column-sequence-number\/\">How to replace an identity column with a sequence number<\/a><\/li>\n<\/ol>\n\n<p>A sequence object is an object that can be used to generate a sequence of integer numbers based on starting and increment values. An identity column is similar to a sequence, but the sequences object has some additional features; for example, it can be used across multiple tables. The sequence object was reintroduced with SQL Server 2012. This article will explore the basics of creating and using a sequence object, while part 2 of this series will explore the more advanced features of the sequence object.<\/p>\n<h2>What is a sequence object?<\/h2>\n<p>As the name implies, a sequence object is an object that creates a series of sequence numbers. The object is created with a set of sequencing criteria and is bound to a schema. A sequence object is defined as a numeric data type that generates sequence numbers that fall within the boundaries of the defined data type. The big difference between an identity column and a sequence object is that sequence numbers are generated with application code outside the scope of a transaction. Sequence numbers are generated by using the <code>NEXT VALUE FOR<\/code> function. Because sequence numbers are generated with code, the generated values can be used across multiple tables or columns in a single table instead of when rows are inserted into a table. Another difference is that they can be automatically recycled based on specifications defined with the sequence object.<\/p>\n<p>There are many different reasons for using a sequence object to generate sequence numbers.\u00a0 Here is a partial list of reasons why a sequence object might be useful:<\/p>\n<ul>\n<li>Requirements call for more than one column in a table to be populated with a generated sequence number.<\/li>\n<li>There is a need to keep sequence numbers synchronized across multiple tables.<\/li>\n<li>An application requires the sequence number value to be obtained before a row is inserted into a table.<\/li>\n<li>Need to have sequence numbers automatically recycled based on minimum and maximum values.<\/li>\n<li>Business rules require a sequence object criterion to be changed over time.<\/li>\n<li>Requires using a sequence number value to be stored in data types other than numeric.<\/li>\n<li>Need to have a nullable column but yet still be populated with sequence numbers.<\/li>\n<li>Want multiple sequence numbers to be acquired with code at the same time.<\/li>\n<\/ul>\n<p>To better understand the basics of creating and using sequence objects, let me provide a few examples.<\/p>\n<h2>Creating a sequence object<\/h2>\n<p>Before you can start generating sequence numbers, a sequence object needs to be defined. A sequence object is created using a <code>CREATE SEQUENCE<\/code> statement. Below is syntax for this statement, as found in the Microsoft documentation:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE SEQUENCE [schema_name . ] sequence_name\r\n\u00a0\u00a0\u00a0 [ AS [ built_in_integer_type | user-defined_integer_type ] ]\r\n\u00a0\u00a0\u00a0 [ START WITH &lt;constant&gt; ]\r\n\u00a0\u00a0\u00a0 [ INCREMENT BY &lt;constant&gt; ]\r\n\u00a0\u00a0\u00a0 [ { MINVALUE [ &lt;constant&gt; ] } | { NO MINVALUE } ]\r\n\u00a0\u00a0\u00a0 [ { MAXVALUE [ &lt;constant&gt; ] } | { NO MAXVALUE } ]\r\n\u00a0\u00a0\u00a0 [ CYCLE | { NO CYCLE } ]\r\n\u00a0\u00a0\u00a0 [ { CACHE [ &lt;constant&gt; ] } | { NO CACHE } \r\n    [ ; ]<\/pre>\n<p>Refer to the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/create-sequence-transact-sql?view=sql-server-ver15\">Microsoft documentation<\/a> for a complete explanation of each of the parameters.<\/p>\n<p>A sequence object has a few more options for generating a sequence number than an identity column. Most notable are the <code>CYCLE<\/code> and <code>CACHE<\/code> options. The <code>CYCLE<\/code> option allows for sequence numbers to be rolled over after reaching a maximum or minimum value. Whereas the <code>CACHE<\/code> option improves performance when retrieving sequence numbers.<\/p>\n<p>To explore using a sequence object, start by creating a sequence object. The sequence object will be used to track complaints, where each complaint has a different integer value assigned. To create this sequence object, run the TSQL code in Listing 1.<\/p>\n<pre class=\"lang:c# theme:vs2012\">USE tempdb;\r\nGO\r\nCREATE SEQUENCE ComplaintNumber\r\n      AS INT\r\n      START WITH 0\r\n      INCREMENT BY 1; \r\nGO<\/pre>\n<p><strong>Listing 1: TSQL to create a simple sequence object<\/strong><\/p>\n<p>The code in Listing 1 creates a sequence object named <em>ComplaintNumber.<\/em> This sequence object is defined as an integer data type and will create sequence numbers starting at 0 and increment the sequence number generated by 1 each time this sequence object is used. Since I didn\u2019t specify <code>MINVALUE<\/code> or the <code>MAXVALUE<\/code>, this schema object has no minimum or maximum value. But in reality, when a sequence object is created without minimum or maximum value, the database engine assigns those values based on the range of numbers supported by the sequence object\u2019s defined data type.<\/p>\n<h2>NEXT VALUE FOR function<\/h2>\n<p>SQL Server provides the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function, so TSQL code can be written to retrieve sequence number values from a sequence object. The numeric values returned by this function depend on the type of statement in which this function is used. When this function is used in a <code>SELECT<\/code> statement, a new sequence number value is generated for every row in the result set produced by the <code>SELECT<\/code> statement. A different value will be generated for each row inserted using an <code>INSERT<\/code> statement. For an <code>UPDATE<\/code> statement, this function will generate a new value for each row updated. For procedural statements, like <code>DECLARE<\/code> and <code>SET<\/code>, a new value is generated for each statement.<\/p>\n<p>The <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function retrieves the next sequence number for a given sequence object. Once a value is returned, the metadata to track the last sequence number used is updated. If a sequence number is retrieved and not used in an <code>INSERT<\/code> or <code>UPDATE<\/code> statement, that sequence number will be lost. Therefore if you don\u2019t want missing sequence numbers, make sure you don\u2019t select sequence numbers without using them. To explore how the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function works, here are some examples.<\/p>\n<h2>Using NEXT VALUE FOR function in an INSERT Statement<\/h2>\n<p>Before showing how to use this function in an <code>INSERT<\/code> statement, you will need to create a table in which to insert rows. The example uses the <em>ComplaintNumber <\/em>sequence object created in Listing 1, so create a table named <em>Complaint <\/em>to capture different complaints. The code in Listing 2 creates this table.<\/p>\n<pre class=\"lang:c# theme:vs2012\">USE tempdb;\r\nGO\r\nCREATE TABLE Complaint (\r\nComplaintID INT,\r\nComplaintDescription VARCHAR (1000), \r\nComplaintDate DATETIME);\r\nGO<\/pre>\n<p><strong>Listing 2: Creating Complaint table<\/strong><\/p>\n<p>For each new complaint, a row will be added to this table. To create the first complaint record, run the code in Listing 3.<\/p>\n<pre class=\"lang:c# theme:vs2012\">USE tempdb;\r\nGO\r\nINSERT INTO dbo.Complaint (ComplaintID, \r\n     ComplaintDescription, \r\n     ComplaintDate) \r\n    VALUES (NEXT VALUE FOR dbo.ComplaintNumber, \r\n         'First Complaint',getdate());\r\nSELECT * FROM dbo.Complaint;<\/pre>\n<p><strong>Listing 3: Populating first <em>Complaint<\/em> row using<em> ComplaintNumber <\/em>sequence object<\/strong><\/p>\n<p>The code in Listing 3 used the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function in the <code>VALUES<\/code> clause of the <code>INSERT<\/code> statement to retrieve the next sequence number available from the <em>ComplaintName <\/em>object. Report 1 shows the output from Listing 3.<\/p>\n<p><strong>Report 1: Output when the code in Listing 3 is run<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-92719\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/10\/word-image-16.png\" alt=\"Image showing first row added to table using sequence object\" width=\"314\" height=\"32\" \/><\/p>\n<p>By looking at the output in Report 1, you can see the first <code>ComplaintID<\/code> has a value of 0. This number happens to be the same as the <code>START<\/code> <code>WITH<\/code> value specified in the <code>CREATE<\/code> <code>SEQUENCE<\/code> statement found in Listing 1. The first time a sequence object is referenced, using the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function, the <code>START<\/code> <code>WITH<\/code> value associated with the <code>CREATE<\/code> <code>SEQUENCE<\/code> statement will be the sequence number returned. A new sequence number will be generated for all subsequent <code>NEXT<\/code> <code>VALUE FOR<\/code> calls based on the last sequence number generated and the <code>INCREMENT<\/code> value associated with the sequence object referenced.<\/p>\n<h2>Using the NEXT VALUE FOR function in a SELECT statement<\/h2>\n<p>When the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> clause is used in a <code>SELECT<\/code> statement, the function will produce a different sequence number for each row returned. In Listing 4, a sub-query is used to produce a recordset that contains two rows. The <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function is used in the outer <code>SELECT<\/code> statement to feed the two row result set into an <code>INSERT<\/code> statement.<\/p>\n<p><strong>Listing 4: Code to populate two more rows in the <em>Complaint <\/em>table.<\/strong><\/p>\n<pre class=\"lang:c# theme:vs2012\">USE tempdb;\r\nGO \r\nINSERT INTO dbo.Complaint\r\n    SELECT NEXT VALUE FOR ComplaintNumber, \r\n                 'Complaint ' + Number,\r\n                  getdate() \r\n    FROM (SELECT * FROM (SELECT '1' AS Number UNION SELECT '2') AS A) AS B;\r\nSELECT * FROM Complaint;\r\nGO<\/pre>\n<p>When the code in Listing 4 is run, the output in Report 2 is displayed.<\/p>\n<p><strong>Report 2: Output when running the code in Listing 4.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-92720\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/10\/table-description-automatically-generated-2.png\" alt=\"Image showing rows added using the sequence object\" width=\"314\" height=\"68\" \/><\/p>\n<p>By reviewing the output in Report 2, you can see there are now three rows in the <em>Complaint <\/em>table. The two new rows added have <code>ComplaintID<\/code> values 1 and 2. This example shows two things. First, it shows that it produces two values when passing two rows to the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function. Secondly, it shows how the <code>INCREMENT<\/code> value was used to increase the sequence number by 1 for each new sequence number generated.<\/p>\n<h2>Using the NEXT VALUE FOR in an UPDATE statement<\/h2>\n<p>When an <code>UPDATE<\/code> statement is used in conjunction with the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function, every row updated will assign a different sequence number. This can be seen by running the code in Listing 5.<\/p>\n<p><strong>Listing 5: Using NEXT VALUE FOR function with UPDATE statement<\/strong><\/p>\n<pre class=\"lang:c# theme:vs2012\">USE tempdb;\r\nGO\r\nUPDATE Complaint\r\n\tSET ComplaintID = NEXT VALUE FOR ComplaintNumber,\r\n        ComplaintDate = getdate();\r\nSELECT * FROM Complaint;\r\nGO<\/pre>\n<p>Report 3 contains the output, when Listing 5 is run.<\/p>\n<p><strong>Report 3: Output from Listing 5<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-92721\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/10\/application-table-description-automatically-gene.png\" alt=\"Image showing the results of adding rows using a sequence object\" width=\"311\" height=\"68\" \/><\/p>\n<p>Report 3 shows that the three existing rows in the <code>Complaint<\/code> table were updated with new <code>ComplaintID\u2019s<\/code><em>. <\/em>This example shows how each row updated got a different generated sequence number to update the <code>ComplaintID<\/code> column.<\/p>\n<h2>Using a sequence object as a constraint<\/h2>\n<p>The examples so far have shown how to use the sequence object and the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function to generate sequence numbers within <code>SELECT<\/code>, <code>INSERT<\/code>, and <code>UPDATE<\/code> statements. Another was to use the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function is in a constraint. By using the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function in a constraint, you can automatically populate table column as rows are inserted, similar to how an identity column works. The code in Listing 6 drops and recreates the <em>Complaint <\/em>table and uses the <code>ComplaintNumber<\/code> sequence object to define a constraint on the <code>ComplaintID<\/code> column and then inserts some rows into the recreated table.<\/p>\n<p><strong>Listing 6: Using a sequence object in constraint<\/strong><\/p>\n<pre class=\"lang:c# theme:vs2012\">USE tempdb;\r\nGO\r\nDROP TABLE Complaint;  \r\nGO\r\nCREATE TABLE Complaint (\r\nComplaintID INT NOT NULL \r\n      CONSTRAINT [DF_ComplaintID] \r\n             DEFAULT (NEXT VALUE FOR ComplaintNumber),\r\nComplaintDescription VARCHAR (1000), \r\nComplaintDate DATETIME);\r\nGO\r\nINSERT INTO Complaint (ComplaintDescription, ComplaintDate)\r\n   VALUES ('Complaint 1',getdate()),\r\n          ('Complaint 2',getdate());\r\nSELECT * FROM Complaint;\r\nGO<\/pre>\n<p>Report 4 is generated by the SELECT statement in Listing 6.<\/p>\n<p><strong>Report 4: Output from the SELECT statement in Listing 6<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-92722\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/10\/table-description-automatically-generated-3.png\" alt=\"Image showing the rows added when using a default constraing\" width=\"310\" height=\"50\" \/><\/p>\n<p>This example shows how a constraint can automatically populate a table column with a sequence number value. If you want to use a sequence object to produce sequence numbers when rows are inserted into tables without writing application code, then using a constraint is the way to go.<\/p>\n<h2>Altering a sequence number<\/h2>\n<p>One of the advantages of using a sequence object over an identity column is that a sequence object can be altered. In contrast, an identity column specifications can\u2019t be altered once it is created. Being able to alter an existing sequence object is a great thing, especially when business requirements change, and there is a lot of application code that already uses a sequence object.<\/p>\n<p>For a business requirement that requires a sequence object to be altered, assume management has changed their mind on the numbering scheme for complaints. Now they want all new complaints to start at 10000, with every complaint after that to be increment by 10. To meet these new requirements, the <code>ComplaintNumber<\/code> sequence object will be altered using the code in Listing 7.<\/p>\n<p><strong>Listing 7: Altering <em>Complaint <\/em>sequence object<\/strong><\/p>\n<pre class=\"lang:c# theme:vs2012 \">USE tempdb;\r\nGO\r\nALTER SEQUENCE dbo.ComplaintNumber\r\n      RESTART WITH 10000\r\n      INCREMENT BY 10; \r\nGO\r\nINSERT INTO dbo.Complaint (ComplaintID, \r\n          ComplaintDescription, ComplaintDate) \r\n    VALUES (NEXT VALUE FOR dbo.ComplaintNumber, \r\n         'First complaint with new numbering scheme',getdate());\r\nINSERT INTO dbo.Complaint (ComplaintID, \r\n         ComplaintDescription, ComplaintDate) \r\n    VALUES (NEXT VALUE FOR dbo.ComplaintNumber, \r\n         'Second complaint with new numbering scheme',getdate());\r\nGO\r\nSELECT * FROM dbo.Complaint;\r\nGO<\/pre>\n<p>When the code in Listing 7 is run t,he output in Report 5 is created.<\/p>\n<p><strong>Report 5: Output created when code in Listing 7 is executed.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-92723\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/10\/graphical-user-interface-text-application-descr-5.png\" alt=\"Image showing rows inserted after changing the sequence object properties\" width=\"439\" height=\"80\" \/><\/p>\n<p>Being able to easily change a sequence object with an <code>ALTER<\/code> statement provides you a quick method to change the sequence object specification without changing any application code or to drop and recreate the table that uses a sequence object.<\/p>\n<h2>Sequence object Information<\/h2>\n<p>If you need to determine which sequence objects are defined in a database you have a few ways to do that. The first way is to use Object Explorer. To show a list of sequence objects in a database expand the <em>Programmability<\/em> folder, then expand the <em>Sequences<\/em> folder as shown in Figure 1.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-92724\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/10\/graphical-user-interface-application-description-3.png\" alt=\"Image showing the sequence object in SQL Server Management Studio\" width=\"274\" height=\"459\" \/> .<\/p>\n<p><strong>Figure 1: Expanded Sequence Folder<\/strong><\/p>\n<p>By reviewing Figure 1, you can see there is one sequence object that has been defined in <em>tempdb<\/em>. To review the actual specifications for this sequence object just double-click it.<\/p>\n<p>Figure 2 displays the <em>ComplaintNumber <\/em>object properties.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-92725\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/10\/graphical-user-interface-application-description-4.png\" alt=\"Image showing the properties of a sequence object\" width=\"734\" height=\"669\" \/><\/p>\n<p><strong>Figure 2: Sequence object specifications for <em>ComplaintNumber<\/em><\/strong><\/p>\n<p>Figure 2 shows all the specifications for the <em>ComplaintNumber <\/em>sequence object. You can also use Object Explore to modify a sequence object. I\u2019ll leave it up to you to test that out.<\/p>\n<p>Another way to display the sequence object information is to use the system view <code>sys.sequences<\/code>. This view displays one row of information for each sequence number object defined in a database. The code in Listing 5 uses the <code>sys.sequences<\/code> view to display some of the metadata columns for the sequence objects I have defined in <em>tempdb<\/em>.<\/p>\n<p><strong>Listing 8: Using the <em>sys.sequences <\/em>system view <\/strong><\/p>\n<pre class=\"lang:c# theme:vs2012\">USE tempdb;\r\nGO\r\nSELECT name, create_date, start_value, increment \r\nFROM sys.sequences;<\/pre>\n<p>Report 6 shows the results when Listing 8 is run.<\/p>\n<p><strong>Report 6: Sequence objects defined in <em>tempdb<\/em><\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-92726\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/10\/word-image-17.png\" alt=\"Image showing the sequences using sys.sequences\" width=\"360\" height=\"49\" \/><\/p>\n<p>I only showed a few columns that are available when using the <code>sys.sequences<\/code> view in Report 6<em>. <\/em>For a complete list of available columns review the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-catalog-views\/sys-sequences-transact-sql?view=sql-server-ver15\">Microsoft documentation<\/a>.<\/p>\n<h2>Dropping a sequence object<\/h2>\n<p>Once a sequence object has been created, you might find the need to remove it from the database. The <code>DROP<\/code> <code>SEQUENCE<\/code> command is used to drop a sequence object. To remove the sequence object from a database, you need to remove all the objects that reference a sequence object, and then you can drop the sequence object. If all the objects that reference a sequence object are not dropped prior to dropping the sequence number, an error will occur.<\/p>\n<p>In order to demonstrate dropping my <em>ComplaintNumber <\/em>sequence object, I must first drop the <em>DF_Constraint <\/em>that I created in Listing 6. Listing 9 contains the code to drop the sequence object along with dropping the one constraint.<\/p>\n<p><strong>Listing 9: Dropping a sequence objcct<\/strong><\/p>\n<pre class=\"lang:c# theme:vs2012\">USE tempdb;\r\nGO\r\nALTER TABLE Complaint DROP CONSTRAINT DF_ComplaintID;\r\nGO\r\nDROP SEQUENCE ComplaintNumber;\r\nGO<\/pre>\n<h2>Rollback issues<\/h2>\n<p>Each time the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function is called, it returns the next sequence number. This process of requesting the next sequence is done outside the scope of the current transaction. Since the metadata to track the last sequence number is done outside the code of a transaction, that means when a transaction fails, is not committed, and\/or is rolled back, the last sequence number stored in the metadata will not be rolled back. Rollbacks are a reason why sequence numbers might go missing.<\/p>\n<h2>Limitations<\/h2>\n<p>As with most SQL Server features, there are limitations. Before using sequence objects, you need to be aware of the sequence object and <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function limitations. Below is a partial list of some of the key limitations:<\/p>\n<p>A column populated with a sequence number value is not protected from being updated, like an identity column. Suppose you need to make sure your sequence number populated columns are not updated. In that case, you should consider placing an update trigger on columns populated by a sequence number to make sure they can\u2019t be updated.<\/p>\n<p>Sequence numbers may not be unique. For example, when a sequence object is recycled, it may reuse sequence numbers that have been generated before. If you want to make sure your columns populated with a sequence number contain unique values, then a unique index or constraint should be added to your columns.<\/p>\n<p>If the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> function references the same sequence object multiple times in the same <code>SELECT<\/code> statement, then the same value will be returned for each reference.<\/p>\n<p>The <code>NEXT VALUE FOR<\/code> function is not allowed in check constraints, default objects (deprecated), computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, derived tables, or return statements. It can be used in default constraints, as shown in the example in this article.<\/p>\n<p><code>NEXT VALUE FOR<\/code> function cannot be used in statements that contain <code>TOP<\/code><strong> or\u00a0<\/strong><code>OFFSET<\/code><strong> clauses<\/strong> or when the\u00a0<code>ROWCOUNT<\/code>\u00a0option is set.<\/p>\n<p>Cannot use the <code>NEXT<\/code> <code>VALUE<\/code> <code>FOR<\/code> clause in a <code>WHERE<\/code> statement.<\/p>\n<p>For a complete list of limitations for <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/sequence-numbers\/sequence-numbers?view=sql-server-ver15\">sequence objects<\/a> and the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/next-value-for-transact-sql?view=sql-server-ver15\">NEXT VALUE FOR<\/a> function, refer to the SQL Server Microsoft documentation.<\/p>\n<h2>Exploring the sequence number object<\/h2>\n<p>Prior to SQL Server 2012, the only automated method SQL Server provided to populate a column with a sequential number was to make that column an identity column.\u00a0 Since the rollout of SQL Server 2012, a sequence object can also be used to generate sequence to populated column values in one or more tables. Sequence number values are obtained with TSQL code. Business requirements determine whether or not an identity column or a sequence object should be used to generate a series of generated numbers.\u00a0 Next time you need a series of generated numbers for a table column, determine if the sequence object might be more appropriate than using an identity column. The next article dives into the <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/using-sql-server-sequence-objects\/\">properties of the sequence object<\/a>.<\/p>\n<p><em>If you liked this article, you might also like\u00a0<a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/sql-server-identity-column\/\">SQL Server identity column &#8211; Simple Talk (red-gate.com)<\/a>.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server sequence objects can be used in place of identity columns. In this article, Greg Larsen explains how to set up and use sequence objects.&hellip;<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143525],"tags":[5134],"coauthors":[11330],"class_list":["post-92718","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92718","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=92718"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92718\/revisions"}],"predecessor-version":[{"id":93001,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92718\/revisions\/93001"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=92718"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=92718"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=92718"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=92718"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}