How to replace an identity column with a sequence number

Comments 0

Share to social media

The series so far:

  1. Introduction to SQL Server sequence objects
  2. Using SQL Server sequence objects
  3. How to return multiple sequence numbers with sp_sequence_get_range
  4. How to replace an identity column with a sequence number

Microsoft introduced the sequence number objects starting with SQL Server 2012. A sequence object generates sequence numbers based on starting and increment values, similar to an identity column, but it has additional features. Over time, you might find that the additional benefits of a sequence number have you wanting to replace an identity column with a sequence number. This article demonstrates two options for replacing an identity column with a sequence number.

Why replace the identity column

An Identity column might not be flexible enough to support all business requirements an application might need around a series of identity values. When this occurs, you might find the sequence number object more flexible. Here are a few of the reasons why you might want to change an identity column to a sequence number:

  • Need to know the sequence number prior to inserting a record.
  • Need to share sequence numbers across tables.
  • Need to cycle through a series of numbers, over and over again.
  • Need to retrieve multiple sequence numbers at the same time without gaps.
  • Need to be able to change the specification for the sequence number over time.
  • Need to store a sequence number in a data type other than numeric.
  • Need to allow a sequence number column to be nullable.

Converting identity column to sequence number

If you decide to change an identity column to a sequence number, there is more than one way to accomplish the conversion. This article demonstrates two different options. One option is to modify your table to add a sequence number column and then delete the identity column. Another option is to create another table that uses a sequence object and then use the ALTER TABLE SWITCH operation. This article explores both of these options and provides examples of how these options can be used to replace an identity column with a sequence number.

Option 1: Modify existing table to use sequence number

Replacing an identity column in an existing table using a column populated with a sequence number requires a few steps. To show you how this example works, first create a couple of sample tables.

The first sample table is named Sample and can be created using the script in Listing 1.

Listing 1: Code to create Sample table.

Running the code from Listing 1 produces the rows shown in Report 1.

Report 1: Rows generated in the Sample table

An image showing the results of Listing 1. ID ObjectName 1 First, 2 Ssecond, 3 Third

The Sample table has the identity column that will be changed to use a sequence number.

When swapping out an identity column for a column populated with a sequence number, you need to be careful not to mess up the tables with foreign key references to the identity column of the table being modified. Therefore, this example has a second table named Sample2 created in Listing 2. Sample2 simulates a table with a foreign key reference to the primary key associated with the ID column in the first table.

Listing 2: Creating Sample2 table

Step 1: Creating sequence number column

The first step to replacing an identity column with a sequence number is to create a new column in the Sample table to store the sequence number column. For this example, the sequence number column has the same data type as the original identity column, an INT data type. The code in Listing 3 is used to add the new sequence number column to the Sample table.

Listing 3: Added sequence number column

Step 2: Updating SequenceNumber column

Once the new sequence number column has been added to the Sample table, the next step is to update the new column with the appropriate value for all the existing records. The new SequenceNumber column will be populated with the same values as the ID column. Therefore to update the SequenceNumber column, run the code in Listing 4.

Listing 4: Updating the SequenceNumber column

The output in Report 2 is produced after running Listing 4.

Report 2: Sample table

An images showing the results of Listing 4. ID SampleName SequenceNumber 1 First 1, 2 Second 2, 3 Third 3

Step 3: Identifying and removing foreign key references

The identity column of the Sample table has a primary key named PK_Sample_ID associated with it. This primary key needs to be removed from the ID column and then moved to the new SequenceNumber column. Before this can be done, any foreign keys that reference this primary key need to be dropped. To find all the foreign keys that refer to the PK_Sample_ID primary key, run the code in Listing 5.

Listing 5: Finding all foreign keys

When the code in Listing 5 is run, the output in Report 3 is generated.

Report 3: Output created when Listing 5 is run.

An image showing the result of Listing 5 ForeignKeyTable Constraint_name Sample2 FK_Sample2_ID

To remove the only foreign key identified, the script in Listing 6 can be run.

Listing 6: Removing foreign key

Keep in mind any foreign keys dropped should be recreated, so consider retaining the foreign key information.

Step 4: Removing primary key from identity column

With all the foreign keys removed, the primary key on the Sample table can be dropped using the script in Listing 7.

Listing 7: Removing primary key

Step 5: Dropping identity column

With the primary key removed, the identity column can be dropped using the code in Listing 8.

Listing 8: Removing identity column.

Step 6: Renaming SequenceNumber column

For the sequence number column to have the same name as the deleted identity column, it must be renamed. The script in Listing 9 performs this rename operation.

Listing 9: Renaming SequenceNumber column

Step 7: Adding NOT NULL requirement and primary key

Identity columns are defined to have a NOT NULL requirement. Therefore to make the new sequence number column mirror the original identity column properties, the NOT NULL requirement must be added to the sequence number column. The script in Listing 10 alters the ID column to not allow nulls and adds a new primary key constraint to replace the primary key deleted in step 4.

Listing 10: Adding NOT NULL requirement and primary key

Step 8: Creating sequence number object

Because the Sample table already has some rows, the highest value for the new ID column needs to be determined. This value will be used to set the starting value for the new sequence number object. The starting value for the sequence number object will be the maximum value for the ID column in the Sample table plus the increment value for the sequence object (which in this example will be 1, the same as the original identity increment value). The dynamic TSQL code in Listing 11 can be used to identify the highest ID column value and create the new sequence number object with the correct START value.

Listing 11: Creating Sequence number object

Care should be used when executing dynamic SQL to ensure you don’t cause a SQL injection issue. Therefore before executing any dynamic SQL, make sure you are not potentially opening the door for SQL injection issues.

Step 9: Setting a default value for the new ID column

When new rows are added to an identity column field, the value for the identity column is automatically populated with the next identity value by default. To get the new ID column to automatically populate with a sequence number value, a default constraint needs to be added to the new ID column. This constraint can be added by using the code in Listing 12.

Listing 12: Setting the ID column default value

Step 10: Restoring foreign keys

In Step 3, the one foreign key constraint that referenced the primary key on the Sample table was deleted. This step adds back that deleted foreign key reference, using the code in Listing 13.

Listing 13: Restoring foreign key

Testing out inserting new rows

With the identity column swapped out with a column populated by the sequence number object, all that is left to do is test out the new schema definition. This testing verifies that the new ID column is populated with the next sequence number every time a new row is added. The testing can be done by running the code in Listing 14.

Listing 14: Inserting three new rows into the Sample table

When the code in Listing 14 is run, the results in Report 4 are created.

Report 4: Output when Listing 14 is executed

Image showing the results of running Listing 14. SmpleName ID, First 1, Second 2, Third 3, Fourth 4, Fifth 5, Sixth 6

By reviewing the output in Report 4, you can see that the last three rows inserted got the next three sequential sequence numbers (4, 5, and 6). You might also notice that the position of the ID column is no longer in the first ordinal position in the table as the original ID column was.

Option 2: Replacing identity column using SWITCH operation

Another option to replace an identity column is to use the ALTER TABLE SWITCH operation. This technique is often used with table partitioning, which is out of scope for this article, but it is useful in this scenario. The SWITCH operation doesn’t move the data. Instead, it switches the partition between the source and target tables. This process simplifies the migration, but some requirements need to be followed. Below are those requirements as found in the Microsoft Documentation:

  • Both tables must exist before the SWITCH operation. The table from which the partition is being moved (the source table) and the table that is receiving the partition (the target table) must exist in the database before you perform the switch operation.
  • The receiving partition must exist and it must be empty. Whether you are adding a table as a partition to an already existing partitioned table, or moving a partition from one partitioned table to another, the partition that receives the new partition must exist and it must be an empty partition.
  • The receiving nonpartitioned table must exist and it must be empty. If you are reassigning a partition to form one nonpartitioned table, the table that receives the new partition must exist and it must be an empty nonpartitioned table.
  • Partitions must be on the same column. If you are switching a partition from one partitioned table to another, both tables must be partitioned on the same column.
  • Source and target tables must share the same filegroup. The source and the target table of the ALTER TABLE…SWITCH statement must reside in the same filegroup, and their large-value columns must be stored in the same filegroup. Any corresponding indexes, index partitions, or indexed view partitions must also reside in the same filegroup. However, the filegroup can be different from that of the corresponding tables or other corresponding indexes.

Cleanup and recreating of artifacts from Option 1

Before learning how to use the SWITCH operation to replace an identity column with a column populated with a sequence number, clean the database artifacts created in Option 1 and recreate the sample tables and sequence object. To perform this cleanup and recreation of objects, execute the code in Listing 15.

Listing 15: Cleanup and recreation of sample tables

Step 1: Creating new table that uses sequence number

To use the SWITCH operation the target table needs to be created. Use the code in Listing 16 to create the new table that uses a sequence number to populate the ID column instead of an identity specification.

Listing 16: Create a new table

Step 2: Removing foreign key references

The SWITCH operation will fail if the primary key on the table being switched is referenced by any foreign keys. Therefore all foreign keys to the Sample table need to be removed first. Sample2 has a foreign key reference to the primary key, which will be removed by running the code in Listing 6.

Step 3: Switching tables

Once all the foreign key constraints have been removed, the SWITCH operation can be performed. The code in Listing 17 will perform the SWITCH operation.

Listing 17: Switch tables

When the switch is performed, the partition between the source (Sample) and target (Sample_New) tables is switched. Only the metadata is changed; the data was not moved. The output of the SELECT statement in Listing 17 can be found in Report 5. This output verifies that rows from the Sample table are now associated with the Sample_New table.

Report 5: Rows in Sample_New table

Image showing results of Listing 17. ID SampleName, 1 First, 2 Second, 3 Third

Step 4: Drop old table and rename new tables

Once the SWITCH operation has been performed, the old Sample table does not contain any rows. Therefore it can be deleted, and the new table can be renamed. The delete and rename can be performed by executing the code in Listing 18.

Listing 18: Drop old and rename new

Step 5: Recreating deleted foreign key references

The final step of the migration is to recreate the foreign key deleted earlier. This key can be recreated by running the code in Listing 13.

Verifying migration

To verify that this migration was successful, you can run the code in Listing 14. When this code is executed, it should produce the same results as shown in Report 4, with one exception. ID column is now in ordinal position 1. I’ll leave it up to you to run this code and to verify that the SWITCH operation was successful in migrating from an identity column to a column populated with a sequence number object.

Restricting sequence numbers from Being updated

Identity column values cannot be updated, whereas columns populated with sequence numbers can be updated. If you want to make sure sequence numbers cannot be updated, then an AFTER UPDATE trigger will need to be created. To verify that the existing Sample table allows updates to the ID column, run the code in Listing 19.

Listing 19: Updating ID column

The code in Listing 19 runs successfully and produces the output in Report 6.

Report 6: Output when Listing 17 is run

Image showing results of Listing 19. ID SampleName, 101 First, 102 Second, 103 Third, 104 Fourth, 105 Fifth, 106 Sixth

By reviewing Report 6, you can see that the ID values were all updated from their original values. The code in Listing 19 contains BEGIN TRAN and END TRAN statements to roll back these updates for the next test.

To make sure an UPDATE statement to the ID column in the Sample table cannot be performed, the AFTER UPDATE trigger in Listing 20 needs to be created.

Listing 20: UPDATE trigger

To test out if this trigger works, run the code in Listing 21.

Listing 21: Testing if trigger keeps ID column from being updated.

When the code in Listing 21 is run, the error message in Report 7 is produced

Report 7: Error when Listing 21 is executed

Image showing error message after running Listing 21. Msg 5000 Level 16, State1. Procedure trg_UpdateSample Line 13 (Batch start line 2) failed: update performed on ID column. The transaction ended in the trigger. The batch has been aborted.

By creating the AFTER UPDATE trigger trg_UpdateSample, the code in Listing 21 could not update the ID column.

Replacing identity an column with a sequence number

Over time, you may find out that an identity column needs to be swapped out and replaced with a sequence number. This article provided a couple of examples of how to perform that swap. If you plan to replace an identity column with a column populated with a sequence number value, keep in mind sequence number columns can be updated. To restrict sequence columns from being updated, an AFTER UPDATE trigger needs to be defined to restrict the updates.

Load comments

About the author

Greg Larsen

See Profile

Greg started working in the computer industry in 1982. In 1985, he got his first DBA job, and since then he has held six different DBA jobs and managed a number of different database management systems. Greg has moved on from being a full-time DBA and is now an adjunct professor at St. Martins University and does part-time consulting work. He has published numerous articles in SQL Server Magazine, and many online web sites dedicated to SQL Server. He is a former SQL Server MVP and holds a number of Microsoft Certification. Greg can be reached at gregalarsen@msn.com.

Greg Larsen's contributions