Using SQL Server sequence objects

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’s 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.

The sequence object was introduced to SQL Server with the rollout of SQL Server 2012. In part 1 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.

Using default values

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.

The default data type for a sequence object is bigint. 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.

The MINVALUE and MAXVALUE default values are set based on the sequence object’s data type. For instance when a sequence object is defined as a bigint the MINVALUE is set to -9,223,372,036,854,775,808 and the MAXVALUE is set to 9,223,372,036,854,775,807.

The last one worth mentioning, is the START WITH 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 value is positive or negative. If the INCREMENT BY parameter is negative, then the START WITH 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’s data type.

For a complete list of all sequence object parameter defaults refer to the Microsoft Documentation.

Using a sequence object to support multiple tables

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.

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 table, and service issues are stored in the ServiceIssue table. The code in Listing 1 is used to create the IssueNumber sequence object and the HardwareIssue and ServiceIssue tables.

Listing 1: Creating Hardware and Service table

USE tempdb;
GO
-- Create Sequence Object
CREATE SEQUENCE IssueNumber
      START WITH 1
      INCREMENT BY 1; 
GO
-- Create tables to track issues
CREATE TABLE HardwareIssue (
IssueNumber INT NOT NULL 
      CONSTRAINT [DF_HardwareIssueNumber] DEFAULT 
        (NEXT VALUE FOR IssueNumber),
IssueDescription VARCHAR (1000), 
IssueDate DATETIME);
CREATE TABLE ServiceIssue (
IssueNumber INT NOT NULL 
      CONSTRAINT [DF_ServiceIssueNumber] DEFAULT 
         (NEXT VALUE FOR IssueNumber),
IssueDescription VARCHAR (1000), 
issueDate DATETIME);
GO

A default constraint was also defined with each IssueNumber column, which references the IssueNumber sequence object. The IssueNumber column will be automatically populated when a new row is added by having a default value.

Run the code in Listing 2 to test that each of these tables will automatically populate the IssueNumber columns using the IssueNumber sequence object.

Listing 2: Inserting a Few Rows into both test tables

INSERT INTO HardwareIssue (IssueDescription, IssueDate)
   VALUES ('Bad power supply', getdate());
INSERT INTO ServiceIssue (IssueDescription, IssueDate)
   VALUES ('Unable to contact vendor for service',getdate());
INSERT INTO HardwareIssue (IssueDescription, IssueDate)
   VALUES ('Disk drive getting errors',getdate());
INSERT INTO ServiceIssue (IssueDescription, IssueDate)
   VALUES ('Looking for help with Server',getdate());
SELECT * FROM HardwareIssue;
SELECT * FROM ServiceIssue;

Output from running Listing 2 can be found in Report 1.

Report 1: Output from running Listing 2.

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 value without providing that value on an INSERT statement.In Report 1, the first set of records displayed are from the HardwareIssue table, and the second set of records are from the ServiceIssue table. By looking at the IssueNumber column in the output, you can see that each value is unique across both tables.

Recycling sequence numbers

Sequence numbers can be recycled by setting the CYCLE option. When the CYCLE 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.

To demonstrate recycling a sequence number, I’ll create a new sequence object using the code in Listing 3.

Listing 3: Creating sequence object

USE tempdb;
GO
CREATE SEQUENCE RecycleEvery2
      START WITH 1
      INCREMENT BY 1
          MINVALUE 1
          MAXVALUE 2
          CYCLE; 
GO

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 in the CREATE SEQUENCE statement tells SQL Server that this sequence object needs to be recycled after it reaches the maximum value.

When the CYCLE option is not specified at creation, as in Listing 1, the sequence number defaults to NO CYCLE. If a sequence object is defined to not cycle, the GET NEXT VALUE function will produce the error in Report 2 when the maximum or minimum value is reached.

Report 2: Error when min or max value is reached

To show how the sequence numbers get recycled, the code in Listing 4 can be run.

Listing 4: Testing out recycling sequence numbers

USE tempdb;
GO
CREATE TABLE RecycleTest (ID INT);
INSERT INTO RecycleTest (ID) VALUES (NEXT VALUE FOR RecycleEvery2);
INSERT INTO RecycleTest (ID) VALUES (NEXT VALUE FOR RecycleEvery2);
INSERT INTO RecycleTest (ID) VALUES (NEXT VALUE FOR RecycleEvery2);
INSERT INTO RecycleTest (ID) VALUES (NEXT VALUE FOR RecycleEvery2);
SELECT * FROM RecycleTest;

The output in Report 3 is produced when Listing 4 is run.

Report 3: Output when Listing 4 is executed

Restarting sequence numbersIn Report 3, the ID values increment up to 2, which is the MAXVALUE setting for the RecycleBy2 sequence object. When the maximum value is reached, the CYCLE option causes the sequence numbers to restart at the MINVALUE, which is 1.

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 clause in an ALTER SEQUENCE statement. The code in Listing 5 shows how to restart a sequence number at a specific value, in this case, 10.

Listing 5: Restarting a sequence number

USE tempdb;
GO
ALTER SEQUENCE IssueNumber 
      RESTART WITH 10;
GO

When the sequence number is restarted, the next sequence number generated will be the same as the RESTART value. This can be verified by running the code in Listing 6 and reviewing the results in Report 4.

Listing 6: Testing out Restarting a sequence number

USE tempdb;
GO
INSERT INTO ServiceIssue (IssueDescription, IssueDate)
   VALUES ('Resetting Issue number test',getdate());
SELECT * FROM ServiceIssue;
GO

Report 4 shows that the number restarts.

Report 4: Verifying the sequence number restarted.

Populating non-numeric columns with a sequence number

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.

Suppose you work for a manufacturing business that builds a product called Widget. The Widget item is produced in two different plants. One plant is in Seattle, while the other plant is in Hong Kong. Each Widget manufactured has a serial number that indicates which plant produced it. The serial number uses the following format: NNNN-C. Where NNNN is an integer number and C is a character. Additionally, the NNNN portion of the serial number needs to be a different number for each item regardless of which plant produced it, and the C portion of the serial number is either an S or H depending on where it was manufactured.

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, a Part table, inserts a few rows in the Part table, and then displays the rows inserted into the Part table.

Listing 7: Generating Serial Numbers

USE tempdb;
GO
CREATE SEQUENCE GenSerialNumber
      START WITH 0
      INCREMENT BY 1 
GO
CREATE TABLE Part (PartName Char(20), SerialNumber CHAR(6), 
         ManufactureDT DATETIME);
GO
DECLARE @City VARCHAR(65) = 'Hong Kong';
INSERT INTO Part
        SELECT 'Widget', 
           RIGHT(CAST(NEXT VALUE FOR GenSerialNumber 
                  + 10000 AS CHAR(5)),4) + '-' + 
              CASE WHEN @CITY = 'Hong Kong' THEN 'H'
                   WHEN @CITY = 'Seattle' THEN 'S' END, 
           GETDATE();
SET @City = 'Seattle';
INSERT INTO Part
        SELECT 'Widget', 
           RIGHT(CAST(NEXT VALUE FOR GenSerialNumber 
                 + 10000 AS CHAR(5)),4) + '-' + 
              CASE WHEN @CITY = 'Hong Kong' THEN 'H'
                   WHEN @CITY = 'Seattle' THEN 'S' END, 
           GETDATE();
-- Display rows inserted
SELECT * FROM Part;
GO

Output in Report 5 is created when Listing 7 is executed.

Report 5: Output from the final SELECT statement in Listing 7

OVER clause and sequence object

The NEXT VALUE FOR function provides an optional OVER clause. Using the OVER clause provides a way to order the assignment of sequence numbers by groups of values. When using the NEXT VALUE FOR function with the OVER clause, not all subclauses of the OVER clause are supported. Only the ORDER BY subclause clause is supported. Using the OVER clause with the NEXT VALUE FOR function along with a sequence object is useful in breaking up a set into different groups.

You might be thinking, this is exactly what the NTILE function could be used for. This is true, but the NTILE function starts at 1 and increments by 1. You don’t have those constraints with the sequence object.

To show how the OVER clause, in conjunction with the NEXT VALUE FOR 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.

The sequence number to support numbering by 100 is created by running Listing 8.

Listing 8: Creating sequence object to support grouping example

USE tempdb;
GO
CREATE SEQUENCE GroupBy3
      START WITH 100
      INCREMENT BY 100
          MINVALUE 100
          MAXVALUE 300
          CYCLE; 
GO

The GroupBy3 sequence object created in Listing 8 will generate 3 different group numbers: 100, 200, and 300. The MINVALUE, MAXVALUE, and CYCLE clauses support cycling through these 3 group values over and over again as new sequence numbers are requested.

To show the OVER clause in action, run the code in Listing 9.

Listing 9: Grouping by 100’s

USE tempdb;
GO
SELECT NEXT VALUE FOR GroupBy3 OVER (ORDER BY object_id) 
         as groupnum,name, object_id
FROM (SELECT top 10 name, object_id FROM sys.objects) AS O
ORDER BY groupnum;

Report 6 shows the output when Listing 9 is executed. Note that the results will vary here except for the groupnum column.

Report 6: Output from Listing 9

If 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 clause associated with the NEXT VALUE FOR function might create grouping sets with different numbers of members. See how groupnum 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.

Report 7: Second execution of Listing 9

In order to have the code produce exactly the same results each time, the sequence object used in conjunction with the OVER clause will need to be restarted. The code in Listing 10 uses the ALTER SEQUENCE command to restart the GroupBy3 sequence object then runs the same code as used in Listing 9.Now groupnum 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 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 300 had 4 rows.

Listing 10: Restarting Sequence object to get consistent results

USE tempdb;
ALTER SEQUENCE GroupBy3
      RESTART WITH 100
      INCREMENT BY 100
          MINVALUE 100
          MAXVALUE 300
          CYCLE; 
GO
SELECT NEXT VALUE FOR GroupBy3 OVER (ORDER BY object_id) 
       as groupnum,name, object_id
FROM (SELECT top 10 name, object_id FROM sys.objects) AS O
ORDER BY groupnum;

By restarting the sequence number at 100 each time, the SELECT statement in Listing 10 produces exactly the same results each time it is run. I’ll leave it up to you to test out Listing 10 to verify that the code creates consistent results each time it is run.

The caching option

The CACHE option is available to reduce the amount of I/O that occurs when generating sequence numbers. When the CACHE 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.

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.

There are three different settings possible for the CACHE options. These three options can be seen by reviewing the IssueNumber sequence object using Object Explorer, as shown in Figure 1.

Figure 1: Sequence object IssueNumber specifications

The IssueNumber sequence object uses the Default size for the cache. The default size is not specified in the Microsoft documentation of sequence objects. The documentation says the default size for the CACHE option is determined by the Database Engine. It also states that the method for calculating the default size might change over time.

The second cache size option is No cache. 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.

The last option is Cache size, 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.

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 Management section in the Microsoft Documentation.

Expanding your knowledge of SQL Server sequence objects

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’t 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.

 

The post Using SQL Server sequence objects appeared first on Simple Talk.

from Simple Talk https://ift.tt/3CklhKM
via