{"id":92801,"date":"2021-11-15T19:35:50","date_gmt":"2021-11-15T19:35:50","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92801"},"modified":"2021-11-30T19:09:15","modified_gmt":"2021-11-30T19:09:15","slug":"using-sql-server-sequence-objects","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/using-sql-server-sequence-objects\/","title":{"rendered":"Using SQL Server sequence objects"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Introduction to SQL Server sequence objects<\/a><\/li>\n
  2. Using SQL Server sequence objects<\/a><\/li>\n
  3. How to return multiple sequence numbers with sp_sequence_get_range<\/a><\/li>\n
  4. How to replace an identity column with a sequence number<\/a><\/li>\n<\/ol>\n\n

    A SQL Server sequence object is an object that can be used to produce a series of integer values based on a starting point and an increment value. They are similar to identity columns but are more flexible. It\u2019s possible to use multiple sequences to populate more than one column in a table. One sequence object can also be used across multiple tables in a database. This article walks you through using SQL Server sequence objects.<\/p>\n

    The sequence object was introduced to SQL Server with the rollout of SQL Server 2012. In part 1<\/a> of the sequence object series, I discussed the basics of using the sequence object. This article covers the more advanced topics like how to control caching and cycling sequence numbers and more.<\/p>\n

    Using default values<\/h2>\n

    When creating a sequence object, only the sequence name argument is required. For any parameter not specified, the default value for that option will be used. I will discuss a few of those default values that might surprise you.<\/p>\n

    The default data type for a sequence object is bigint<\/code>. Using a sequence object to populate a smaller integer column data type might cause an error. The error occurs when the sequence number generated is larger or smaller thn supported by the target column data type.<\/p>\n

    The MINVALUE<\/code> and MAXVALUE<\/code> default values are set based on the sequence object\u2019s data type. For instance when a sequence object is defined as a bigint<\/code> the MINVALUE<\/code> is set to -9,223,372,036,854,775,808 and the MAXVALUE<\/code> is set to 9,223,372,036,854,775,807.<\/p>\n

    The last one worth mentioning, is the START<\/code> WITH<\/code> parameter. This parameter identifies the first sequence number that will be generate. The default value for starting value is determined by the wheter the INCREMENT<\/code> value is positive or negative. If the INCREMENT<\/code> BY<\/code> parameter is negative, then the START<\/code> WITH<\/code> value is set to maximum value of data type for the sequence object. For a sequence objects that count up, the default starting value is set to the minimum value for the object\u2019s data type.<\/p>\n

    For a complete list of all sequence object parameter defaults refer to the Microsoft Documentation<\/a>.<\/p>\n

    Using a sequence object to support multiple tables<\/h2>\n

    One of the advantages of using sequence objects is that one sequence object can be used in multiple tables. Suppose you have a business requirement to track issues, where each issue requires a unique issue number. Additionally, the issue number needs to be stored in different tables based on the type of issue.<\/p>\n

    To test out these requirements, assume there are two different types of issues to track: hardware and service. The hardware issues are stored in the HardwareIssue <\/em>table, and service issues are stored in the ServiceIssue <\/em>table. The code in Listing 1 is used to create the IssueNumber<\/em> sequence object and the HardwareIssue <\/em>and ServiceIssue <\/em>tables.<\/p>\n

    Listing 1: Creating Hardware <\/em>and Service <\/em>table<\/strong><\/p>\n

    USE tempdb;\r\nGO\r\n-- Create Sequence Object\r\nCREATE SEQUENCE IssueNumber\r\n      START WITH 1\r\n      INCREMENT BY 1; \r\nGO\r\n-- Create tables to track issues\r\nCREATE TABLE HardwareIssue (\r\nIssueNumber INT NOT NULL \r\n      CONSTRAINT [DF_HardwareIssueNumber] DEFAULT \r\n        (NEXT VALUE FOR IssueNumber),\r\nIssueDescription VARCHAR (1000), \r\nIssueDate DATETIME);\r\nCREATE TABLE ServiceIssue (\r\nIssueNumber INT NOT NULL \r\n      CONSTRAINT [DF_ServiceIssueNumber] DEFAULT \r\n         (NEXT VALUE FOR IssueNumber),\r\nIssueDescription VARCHAR (1000), \r\nissueDate DATETIME);\r\nGO<\/pre>\n
    A default constraint was also defined with each IssueNumber<\/code> column, which references the IssueNumber <\/em>sequence object. The IssueNumber column will be automatically populated when a new row is added by having a default value.<\/p>\n
    Run the code in Listing 2 to test that each of these tables will automatically populate the IssueNumber<\/code> columns using the IssueNumber <\/em>sequence object.<\/p>\n
    Listing 2: Inserting a Few Rows into both test tables<\/strong><\/p>\n
    INSERT INTO HardwareIssue (IssueDescription, IssueDate)\r\n   VALUES ('Bad power supply', getdate());\r\nINSERT INTO ServiceIssue (IssueDescription, IssueDate)\r\n   VALUES ('Unable to contact vendor for service',getdate());\r\nINSERT INTO HardwareIssue (IssueDescription, IssueDate)\r\n   VALUES ('Disk drive getting errors',getdate());\r\nINSERT INTO ServiceIssue (IssueDescription, IssueDate)\r\n   VALUES ('Looking for help with Server',getdate());\r\nSELECT * FROM HardwareIssue;\r\nSELECT * FROM ServiceIssue;<\/pre>\n
    Output from running Listing 2 can be found in Report 1.<\/p>\n
    Report 1: Output from running Listing 2.<\/strong><\/p>\n
    \"Image<\/p>\n
    This example showed one way a sequence object can be used to populate columns in multiple tables. But it also showed how by using a default constraint, you can automatically generate the IssueNumber<\/code> value without providing that value on an INSERT<\/code> statement.In Report 1, the first set of records displayed are from the HardwareIssue <\/em>table, and the second set of records are from the ServiceIssue <\/em>table. By looking at the IssueNumber <\/em>column in the output, you can see that each value is unique across both tables.<\/p>\n
    Recycling sequence numbers<\/h2>\n
    Sequence numbers can be recycled by setting the CYCLE<\/code> option. When the CYCLE<\/code> option is turned on the sequence number will restart once the maximum or minimum values is reached, depending on whether the increment value is a positive or negative integer value.<\/p>\n
    To demonstrate recycling a sequence number, I\u2019ll create a new sequence object using the code in Listing 3.<\/p>\n
    Listing 3: Creating sequence object <\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nCREATE SEQUENCE RecycleEvery2\r\n      START WITH 1\r\n      INCREMENT BY 1\r\n\t  MINVALUE 1\r\n\t  MAXVALUE 2\r\n\t  CYCLE; \r\nGO<\/pre>\n
    In Listing 3, the sequence object created is named RecycleEvery2. This sequence object will start at 1 and will recycle the value when it reaches 2. The keyword CYCLE<\/code> in the CREATE<\/code> SEQUENCE<\/code> statement tells SQL Server that this sequence object needs to be recycled after it reaches the maximum value.<\/p>\n
    When the CYCLE<\/code> option is not specified at creation, as in Listing 1, the sequence number defaults to NO<\/code> CYCLE<\/code>.<\/em> If a sequence object is defined to not cycle, the GET<\/code> NEXT<\/code> VALUE<\/code> function will produce the error in Report 2 when the maximum or minimum value is reached.<\/p>\n
    Report 2: Error when min or max value is reached<\/strong><\/p>\n
    \"Image<\/p>\n
    To show how the sequence numbers get recycled, the code in Listing 4 can be run.<\/p>\n
    Listing 4: Testing out recycling sequence numbers<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nCREATE TABLE RecycleTest (ID INT);\r\nINSERT INTO RecycleTest (ID) VALUES (NEXT VALUE FOR RecycleEvery2);\r\nINSERT INTO RecycleTest (ID) VALUES (NEXT VALUE FOR RecycleEvery2);\r\nINSERT INTO RecycleTest (ID) VALUES (NEXT VALUE FOR RecycleEvery2);\r\nINSERT INTO RecycleTest (ID) VALUES (NEXT VALUE FOR RecycleEvery2);\r\nSELECT * FROM RecycleTest;<\/pre>\n
    The output in Report 3 is produced when Listing 4 is run.<\/p>\n
    Report 3: Output when Listing 4 is executed<\/strong><\/p>\n
    \"Image<\/p>\n
    Restarting sequence numbersIn Report 3, the ID<\/code> values increment up to 2, which is the MAXVALUE<\/code> setting for the RecycleBy2<\/code> sequence object. When the maximum value is reached, the CYCLE<\/code> option causes the sequence numbers to restart at the MINVALUE<\/code>, which is 1.<\/p>\n
    Sequence numbers can restart at any sequence number within the range of values defined by the sequence number data type. Care should be used in restarting sequence numbers because it may cause duplicate values if the sequence object populates a column in a table. Restarting a sequence number is performed by using the RESTART<\/code> clause in an ALTER<\/code> SEQUENCE<\/code> statement. The code in Listing 5 shows how to restart a sequence number at a specific value, in this case, 10.<\/p>\n
    Listing 5: Restarting a sequence number<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nALTER SEQUENCE IssueNumber \r\n      RESTART WITH 10;\r\nGO<\/pre>\n
    When the sequence number is restarted, the next sequence number generated will be the same as the RESTART<\/code> value. This can be verified by running the code in Listing 6 and reviewing the results in Report 4.<\/p>\n
    Listing 6: Testing out Restarting a sequence number<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nINSERT INTO ServiceIssue (IssueDescription, IssueDate)\r\n   VALUES ('Resetting Issue number test',getdate());\r\nSELECT * FROM ServiceIssue;\r\nGO<\/pre>\n
    Report 4 shows that the number restarts.<\/p>\n
    Report 4: Verifying the sequence number restarted.<\/strong><\/p>\n
    <\/p>\n
    Populating non-numeric columns with a sequence number<\/h2>\n
    An identity column only supports integer values, but the generated sequence number can be used to populate other data type columns like a character data type. This is possible because the generation of a sequence number is accomplished prior to the row actually being inserted or updated. A sequence number value can be manipulated with code so it can be used to populate other data types besides an integer.<\/p>\n
    Suppose you work for a manufacturing business that builds a product called Widget<\/em>. The Widget<\/em> item is produced in two different plants. One plant is in Seattle, while the other plant is in Hong Kong. Each Widget<\/em> manufactured has a serial number that indicates which plant produced it. The serial number uses the following format: NNNN-C. Where NNNN<\/em> is an integer number and C is a character. Additionally, the NNNN<\/em> portion of the serial number needs to be a different number for each item regardless of which plant produced it, and the C<\/em> portion of the serial number is either an S<\/em> or H<\/em> depending on where it was manufactured.<\/p>\n
    To demonstrate how you might use a sequence object to meet these business requirements, run the code in Listing 7. This code creates a sequence object named GenSerialNumber<\/em>, a Part <\/em>table, inserts a few rows in the Part <\/em>table, and then displays the rows inserted into the Part <\/em>table.<\/p>\n
    Listing 7: Generating Serial Numbers<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nCREATE SEQUENCE GenSerialNumber\r\n      START WITH 0\r\n      INCREMENT BY 1 \r\nGO\r\nCREATE TABLE Part (PartName Char(20), SerialNumber CHAR(6), \r\n         ManufactureDT DATETIME);\r\nGO\r\nDECLARE @City VARCHAR(65) = 'Hong Kong';\r\nINSERT INTO Part\r\n\tSELECT 'Widget', \r\n           RIGHT(CAST(NEXT VALUE FOR GenSerialNumber \r\n                  + 10000 AS CHAR(5)),4) + '-' + \r\n              CASE WHEN @CITY = 'Hong Kong' THEN 'H'\r\n                   WHEN @CITY = 'Seattle' THEN 'S' END, \r\n           GETDATE();\r\nSET @City = 'Seattle';\r\nINSERT INTO Part\r\n\tSELECT 'Widget', \r\n           RIGHT(CAST(NEXT VALUE FOR GenSerialNumber \r\n                 + 10000 AS CHAR(5)),4) + '-' + \r\n              CASE WHEN @CITY = 'Hong Kong' THEN 'H'\r\n                   WHEN @CITY = 'Seattle' THEN 'S' END, \r\n           GETDATE();\r\n-- Display rows inserted\r\nSELECT * FROM Part;\r\nGO<\/pre>\n
    Output in Report 5 is created when Listing 7 is executed.<\/p>\n
    Report 5: Output from the final SELECT statement in Listing 7<\/strong><\/p>\n
    <\/p>\n
    OVER clause and sequence object<\/h2>\n
    The NEXT<\/code> VALUE<\/code> FOR<\/code> function provides an optional OVER<\/code> clause. Using the OVER<\/code> clause provides a way to order the assignment of sequence numbers by groups of values. When using the NEXT<\/code> VALUE<\/code> FOR<\/code> function with the OVER<\/code> clause, not all subclauses of the OVER<\/code> clause are supported. Only the ORDER<\/code> BY<\/code> subclause clause is supported. Using the OVER<\/code> clause with the NEXT<\/code> VALUE<\/code> FOR<\/code> function along with a sequence object is useful in breaking up a set into different groups.<\/p>\n
    You might be thinking, this is exactly what the NTILE<\/code> function could be used for. This is true, but the NTILE<\/code> function starts at 1 and increments by 1. You don\u2019t have those constraints with the sequence object.<\/p>\n
    To show how the OVER<\/code> clause, in conjunction with the NEXT<\/code> VALUE<\/code> FOR<\/code> function works, assume there is a requirement to break up a set of objects into three different groups where each group is assigned a unique group number. The first group will be assigned the group number of 100, the second group 200, and the last group gets 300 as the assigned group number.<\/p>\n
    The sequence number to support numbering by 100 is created by running Listing 8.<\/p>\n
    Listing 8: Creating sequence object to support grouping example<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nCREATE SEQUENCE GroupBy3\r\n      START WITH 100\r\n      INCREMENT BY 100\r\n\t  MINVALUE 100\r\n\t  MAXVALUE 300\r\n\t  CYCLE; \r\nGO<\/pre>\n
    The GroupBy3 sequence object created in Listing 8 will generate 3 different group numbers: 100, 200, and 300. The MINVALUE<\/code>, MAXVALUE<\/code>, and CYCLE<\/code> clauses support cycling through these 3 group values over and over again as new sequence numbers are requested.<\/p>\n
    To show the OVER<\/code> clause in action, run the code in Listing 9.<\/p>\n
    Listing 9: Grouping by 100\u2019s<\/strong><\/p>\n
    USE tempdb;\r\nGO\r\nSELECT NEXT VALUE FOR GroupBy3 OVER (ORDER BY object_id) \r\n         as groupnum,name, object_id\r\nFROM (SELECT top 10 name, object_id FROM sys.objects) AS O\r\nORDER BY groupnum;<\/pre>\n
    Report 6 shows the output when Listing 9 is executed. Note that the results will vary here except for the groupnum<\/code> column.<\/p>\n
    Report 6: Output from Listing 9<\/strong><\/p>\n
    \"Image<\/p>\n
    \nIf the code in Listing 7 is run a second time, you will see the results shown in Report 7.I specifically only returned 10 rows in the subquery in Listing 9. I did this to show how the OVER<\/code> clause associated with the NEXT<\/code> VALUE<\/code> FOR<\/code> function might create grouping sets with different numbers of members. See how groupnum<\/code> 100 has 4 rows, whereas each of the other groups only have 3 rows. This happened because the 10-row set of objects is not evenly divisible by 3.<\/p>\n
    Report 7: Second execution of Listing 9<\/strong><\/p>\n
    \"An<\/p>\n
    \nIn order to have the code produce exactly the same results each time, the sequence object used in conjunction with the OVER<\/code> clause will need to be restarted. The code in Listing 10 uses the ALTER<\/code> SEQUENCE<\/code> command to restart the GroupBy3<\/code> sequence object then runs the same code as used in Listing 9.Now groupnum<\/code> 200 has 4 rows. Why did this occur? This happened because the last sequence number value of 100 was stored in metadata when the Groupby3<\/code> function was used the first time. Therefore when Listing 8 was run a second time, the first group number generated was 200, and the last group number generated was 200. If Listing 9 runs a third time, you would see that groupnum<\/code> 300 had 4 rows.<\/p>\n
    Listing 10: Restarting Sequence object to get consistent results<\/strong><\/p>\n
    USE tempdb;\r\nALTER SEQUENCE GroupBy3\r\n      RESTART WITH 100\r\n      INCREMENT BY 100\r\n\t  MINVALUE 100\r\n\t  MAXVALUE 300\r\n\t  CYCLE; \r\nGO\r\nSELECT NEXT VALUE FOR GroupBy3 OVER (ORDER BY object_id) \r\n       as groupnum,name, object_id\r\nFROM (SELECT top 10 name, object_id FROM sys.objects) AS O\r\nORDER BY groupnum; <\/pre>\n
    By restarting the sequence number at 100 each time, the SELECT<\/code> statement in Listing 10 produces exactly the same results each time it is run. I\u2019ll leave it up to you to test out Listing 10 to verify that the code creates consistent results each time it is run.<\/p>\n
    The caching option<\/h2>\n
    The CACHE<\/code> option is available to reduce the amount of I\/O that occurs when generating sequence numbers. When the CACHE<\/code> option is enabled, information is stored in memory to reduce the amount of I\/O written to the system metadata as sequence numbers are generated. The number of sequence numbers that can be generated without I\/O to metadata is determined by the cache size.<\/p>\n
    Caching sequence numbers uses very little memory. Only two numbers are needed in memory to support caching, the last sequence number used and the number of values left in the cache. The size of those numbers is determined by the data type of the sequence object.<\/p>\n
    There are three different settings possible for the CACHE<\/code> options. These three options can be seen by reviewing the IssueNumber<\/em> sequence object using Object Explorer, as shown in Figure 1.<\/p>\n
    \"An<\/p>\n
    Figure 1: Sequence object IssueNumber <\/em>specifications<\/strong><\/p>\n
    The IssueNumber<\/code> sequence object uses the Default size<\/em> for the cache. The default size is not specified in the Microsoft documentation<\/a> of sequence objects. The documentation says the default size for the CACHE<\/code> option is determined by the Database Engine. It also states that the method for calculating the default size might change over time.<\/p>\n
    The second cache size option is No cache<\/em>. When this option is specified, no memory is used to cache sequence values, and I\/O will occur to the system metadata to maintain the last sequence number used each time a sequence number is generated.<\/p>\n
    The last option is Cache size<\/em>, which uses memory to minimize I\/O as new sequence numbers are generated. The constant associated with this option determines how many sequence numbers can be generated before the last generated sequence number is stored in the system metadata which uses I\/O.<\/p>\n
    When SQL Server is stopped, the last sequence number generated in memory is written to the metadata so unused sequence numbers associated with the cache are not lost. Keep in mind that if SQL Server were to crash, this writing of the last sequence number would not happen, causing sequence numbers to be skipped. To understand more about how the Database manages the cache, refer to the Cache<\/a> Management section in the Microsoft Documentation.<\/p>\n
    Expanding your knowledge of SQL Server sequence objects<\/h2>\n
    The sequence object was introduced with the rollout of SQL Server 2012. Sequence objects can populate one or more columns in a single table and synchronize a series of generated numbers across multiple tables. Sequence objects provide more functionality for automatically generating numbers than an identity column. Next time you find identity columns don\u2019t provide the features you need to generate a series of numbers, consider using the sequence object to see if it meets your auto-numbering requirements.<\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    SQL Server sequence objects have several properties that control how they behave. Greg Larson explains the options of using SQL Server sequence objects.…<\/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-92801","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\/92801","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=92801"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92801\/revisions"}],"predecessor-version":[{"id":93000,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92801\/revisions\/93000"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=92801"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=92801"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=92801"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=92801"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}