Introduction to SQL Server sequence objects

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.

What is a sequence object?

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 NEXT VALUE FOR 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.

There are many different reasons for using a sequence object to generate sequence numbers.  Here is a partial list of reasons why a sequence object might be useful:

• Requirements call for more than one column in a table to be populated with a generated sequence number.
• There is a need to keep sequence numbers synchronized across multiple tables.
• An application requires the sequence number value to be obtained before a row is inserted into a table.
• Need to have sequence numbers automatically recycled based on minimum and maximum values.
• Business rules require a sequence object criterion to be changed over time.
• Requires using a sequence number value to be stored in data types other than numeric.
• Need to have a nullable column but yet still be populated with sequence numbers.
• Want multiple sequence numbers to be acquired with code at the same time.

To better understand the basics of creating and using sequence objects, let me provide a few examples.

Creating a sequence object

Before you can start generating sequence numbers, a sequence object needs to be defined. A sequence object is created using a CREATE SEQUENCE statement. Below is syntax for this statement, as found in the Microsoft documentation:

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } 
    [ ; ]

Refer to the Microsoft documentation for a complete explanation of each of the parameters.

A sequence object has a few more options for generating a sequence number than an identity column. Most notable are the CYCLE and CACHE options. The CYCLE option allows for sequence numbers to be rolled over after reaching a maximum or minimum value. Whereas the CACHE option improves performance when retrieving sequence numbers.

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.

USE tempdb;
GO
CREATE SEQUENCE ComplaintNumber
      AS INT
      START WITH 0
      INCREMENT BY 1; 
GO

Listing 1: TSQL to create a simple sequence object

The code in Listing 1 creates a sequence object named ComplaintNumber. 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’t specify MINVALUE or the MAXVALUE, 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’s defined data type.

NEXT VALUE FOR function

SQL Server provides the NEXT VALUE FOR 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 SELECT statement, a new sequence number value is generated for every row in the result set produced by the SELECT statement. A different value will be generated for each row inserted using an INSERT statement. For an UPDATE statement, this function will generate a new value for each row updated. For procedural statements, like DECLARE and SET, a new value is generated for each statement.

The NEXT VALUE FOR 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 INSERT or UPDATE statement, that sequence number will be lost. Therefore if you don’t want missing sequence numbers, make sure you don’t select sequence numbers without using them. To explore how the NEXT VALUE FOR function works, here are some examples.

Using NEXT VALUE FOR function in an INSERT Statement

Before showing how to use this function in an INSERT statement, you will need to create a table in which to insert rows. The example uses the ComplaintNumber sequence object created in Listing 1, so create a table named Complaint to capture different complaints. The code in Listing 2 creates this table.

USE tempdb;
GO
CREATE TABLE Complaint (
ComplaintID INT,
ComplaintDescription VARCHAR (1000), 
ComplaintDate DATETIME);
GO

Listing 2: Creating Complaint table

For each new complaint, a row will be added to this table. To create the first complaint record, run the code in Listing 3.

USE tempdb;
GO
INSERT INTO dbo.Complaint (ComplaintID, 
     ComplaintDescription, 
     ComplaintDate) 
    VALUES (NEXT VALUE FOR dbo.ComplaintNumber, 
         'First Complaint',getdate());
SELECT * FROM dbo.Complaint;

Listing 3: Populating first Complaint row using ComplaintNumber sequence object

The code in Listing 3 used the NEXT VALUE FOR function in the VALUES clause of the INSERT statement to retrieve the next sequence number available from the ComplaintName object. Report 1 shows the output from Listing 3.

Report 1: Output when the code in Listing 3 is run

By looking at the output in Report 1, you can see the first ComplaintID has a value of 0. This number happens to be the same as the START WITH value specified in the CREATE SEQUENCE statement found in Listing 1. The first time a sequence object is referenced, using the NEXT VALUE FOR function, the START WITH value associated with the CREATE SEQUENCE statement will be the sequence number returned. A new sequence number will be generated for all subsequent NEXT VALUE FOR calls based on the last sequence number generated and the INCREMENT value associated with the sequence object referenced.

Using the NEXT VALUE FOR function in a SELECT statement

When the NEXT VALUE FOR clause is used in a SELECT 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 NEXT VALUE FOR function is used in the outer SELECT statement to feed the two row result set into an INSERT statement.

Listing 4: Code to populate two more rows in the Complaint table.

USE tempdb;
GO 
INSERT INTO dbo.Complaint
    SELECT NEXT VALUE FOR ComplaintNumber, 
                 'Complaint ' + Number,
                  getdate() 
    FROM (SELECT * FROM (SELECT '1' AS Number UNION SELECT '2') AS A) AS B;
SELECT * FROM Complaint;
GO

When the code in Listing 4 is run, the output in Report 2 is displayed.

Report 2: Output when running the code in Listing 4.

By reviewing the output in Report 2, you can see there are now three rows in the Complaint table. The two new rows added have ComplaintID values 1 and 2. This example shows two things. First, it shows that it produces two values when passing two rows to the NEXT VALUE FOR function. Secondly, it shows how the INCREMENT value was used to increase the sequence number by 1 for each new sequence number generated.

Using the NEXT VALUE FOR in an UPDATE statement

When an UPDATE statement is used in conjunction with the NEXT VALUE FOR function, every row updated will assign a different sequence number. This can be seen by running the code in Listing 5.

Listing 5: Using NEXT VALUE FOR function with UPDATE statement

USE tempdb;
GO
UPDATE Complaint
        SET ComplaintID = NEXT VALUE FOR ComplaintNumber,
        ComplaintDate = getdate();
SELECT * FROM Complaint;
GO

Report 3 contains the output, when Listing 5 is run.

Report 3: Output from Listing 5

Report 3 shows that the three existing rows in the Complaint table were updated with new ComplaintID’s. This example shows how each row updated got a different generated sequence number to update the ComplaintID column.

Using a sequence object as a constraint

The examples so far have shown how to use the sequence object and the NEXT VALUE FOR function to generate sequence numbers within SELECT, INSERT, and UPDATE statements. Another was to use the NEXT VALUE FOR function is in a constraint. By using the NEXT VALUE FOR 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 Complaint table and uses the ComplaintNumber sequence object to define a constraint on the ComplaintID column and then inserts some rows into the recreated table.

Listing 6: Using a sequence object in constraint

USE tempdb;
GO
DROP TABLE Complaint;  
GO
CREATE TABLE Complaint (
ComplaintID INT NOT NULL 
      CONSTRAINT [DF_ComplaintID] 
             DEFAULT (NEXT VALUE FOR ComplaintNumber),
ComplaintDescription VARCHAR (1000), 
ComplaintDate DATETIME);
GO
INSERT INTO Complaint (ComplaintDescription, ComplaintDate)
   VALUES ('Complaint 1',getdate()),
          ('Complaint 2',getdate());
SELECT * FROM Complaint;
GO

Report 4 is generated by the SELECT statement in Listing 6.

Report 4: Output from the SELECT statement in Listing 6

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.

Altering a sequence number

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

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 ComplaintNumber sequence object will be altered using the code in Listing 7.

Listing 7: Altering Complaint sequence object

USE tempdb;
GO
ALTER SEQUENCE dbo.ComplaintNumber
      RESTART WITH 10000
      INCREMENT BY 10; 
GO
INSERT INTO dbo.Complaint (ComplaintID, 
          ComplaintDescription, ComplaintDate) 
    VALUES (NEXT VALUE FOR dbo.ComplaintNumber, 
         'First complaint with new numbering scheme',getdate());
INSERT INTO dbo.Complaint (ComplaintID, 
         ComplaintDescription, ComplaintDate) 
    VALUES (NEXT VALUE FOR dbo.ComplaintNumber, 
         'Second complaint with new numbering scheme',getdate());
GO
SELECT * FROM dbo.Complaint;
GO

When the code in Listing 7 is run t,he output in Report 5 is created.

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

Being able to easily change a sequence object with an ALTER 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.

Sequence object Information

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 Programmability folder, then expand the Sequences folder as shown in Figure 1.

.

Figure 1: Expanded Sequence Folder

By reviewing Figure 1, you can see there is one sequence object that has been defined in tempdb. To review the actual specifications for this sequence object just double-click it.

Figure 2 displays the ComplaintNumber object properties.

Figure 2: Sequence object specifications for ComplaintNumber

Figure 2 shows all the specifications for the ComplaintNumber sequence object. You can also use Object Explore to modify a sequence object. I’ll leave it up to you to test that out.

Another way to display the sequence object information is to use the system view sys.sequences. This view displays one row of information for each sequence number object defined in a database. The code in Listing 5 uses the sys.sequences view to display some of the metadata columns for the sequence objects I have defined in tempdb.

Listing 8: Using the sys.sequences system view

USE tempdb;
GO
SELECT name, create_date, start_value, increment 
FROM sys.sequences;

Report 6 shows the results when Listing 8 is run.

Report 6: Sequence objects defined in tempdb

I only showed a few columns that are available when using the sys.sequences view in Report 6. For a complete list of available columns review the Microsoft documentation.

Dropping a sequence object

Once a sequence object has been created, you might find the need to remove it from the database. The DROP SEQUENCE 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.

In order to demonstrate dropping my ComplaintNumber sequence object, I must first drop the DF_Constraint that I created in Listing 6. Listing 9 contains the code to drop the sequence object along with dropping the one constraint.

Listing 9: Dropping a sequence objcct

USE tempdb;
GO
ALTER TABLE Complaint DROP CONSTRAINT DF_ComplaintID;
GO
DROP SEQUENCE ComplaintNumber;
GO

Rollback issues

Each time the NEXT VALUE FOR 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.

Limitations

As with most SQL Server features, there are limitations. Before using sequence objects, you need to be aware of the sequence object and NEXT VALUE FOR function limitations. Below is a partial list of some of the key limitations:

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

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.

If the NEXT VALUE FOR function references the same sequence object multiple times in the same SELECT statement, then the same value will be returned for each reference.

The NEXT VALUE FOR 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.

NEXT VALUE FOR function cannot be used in statements that contain TOP or OFFSET clauses or when the ROWCOUNT option is set.

Cannot use the NEXT VALUE FOR clause in a WHERE statement.

For a complete list of limitations for sequence objects and the NEXT VALUE FOR function, refer to the SQL Server Microsoft documentation.

Exploring the sequence number object

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

If you liked this article, you might also like SQL Server identity column – Simple Talk (red-gate.com).

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

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