{"id":93083,"date":"2022-01-04T17:59:41","date_gmt":"2022-01-04T17:59:41","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93083"},"modified":"2026-04-16T09:37:03","modified_gmt":"2026-04-16T09:37:03","slug":"how-replace-identity-column-sequence-number","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/how-replace-identity-column-sequence-number\/","title":{"rendered":"Replace a SQL Server Identity Column with a Sequence Number: Two Methods"},"content":{"rendered":"<p><strong>SQL Server IDENTITY columns are simple and automatic but inflexible: they cannot be reset without a table rebuild, cannot be shared across tables, and cannot be pre-allocated in bulk. SQL Server SEQUENCE objects provide a more flexible alternative that supports all of these scenarios. <\/strong><\/p>\n<p><strong>This article covers two complete methods for replacing an existing IDENTITY column with a SEQUENCE number: Option 1 is a step-by-step in-place column modification (10 steps, including foreign key handling, column renaming, and setting a default constraint from the SEQUENCE object). Option 2 uses the ALTER TABLE SWITCH operation to perform the same migration more efficiently by swapping the table with a pre-built replacement. Both methods include complete T-SQL code.<\/strong><\/p>\n<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>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.<\/p>\n<h2>Why replace the identity column<\/h2>\n<p>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:<\/p>\n<ul>\n<li>Need to know the sequence number prior to inserting a record.<\/li>\n<li>Need to share sequence numbers across tables.<\/li>\n<li>Need to cycle through a series of numbers, over and over again.<\/li>\n<li>Need to retrieve multiple sequence numbers at the same time without gaps.<\/li>\n<li>Need to be able to change the specification for the sequence number over time.<\/li>\n<li>Need to store a sequence number in a data type other than numeric.<\/li>\n<li>Need to allow a sequence number column to be nullable.<\/li>\n<\/ul>\n<h2>Converting identity column to sequence number<\/h2>\n<p>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 <code>ALTER<\/code> <code>TABLE SWITCH<\/code> 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.<\/p>\n<h2>Option 1: Modify existing table to use sequence number<\/h2>\n<p>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.<\/p>\n<p>The first sample table is named <em>Sample<\/em> and can be created using the script in Listing 1.<\/p>\n<p><strong>Listing 1: Code to create <em>Sample <\/em>table.<\/strong><\/p>\n<pre class=\"lang:ps theme:powershell-ise\">USE tempdb;\nGO\nCREATE TABLE Sample (ID int identity(1,1) NOT NULL, \n    SampleName varchar(30) NOT NULL,\n    CONSTRAINT PK_Sample_ID PRIMARY KEY CLUSTERED(ID ASC));\nINSERT INTO Sample(SampleName) VALUES ('First'),\n                          ('Second'),\n                          ('Third');\nSELECT * FROM Sample;<\/pre>\n<p>Running the code from Listing 1 produces the rows shown in Report 1.<\/p>\n<p><strong>Report 1: Rows generated in the <em>Sample <\/em>table<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"88\" height=\"64\" class=\"wp-image-93094\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/an-image-showing-the-results-of-listing-1-id-obje-1.png\" alt=\"An image showing the results of Listing 1. ID ObjectName 1 First, 2 Ssecond, 3 Third\" \/><\/p>\n<p>The <em>Sample <\/em>table has the identity column that will be changed to use a sequence number.<\/p>\n<p>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. <em>Sample2 <\/em>simulates a table with a foreign key reference to the primary key associated with the ID column in the first table.<\/p>\n<p><strong>Listing 2: Creating <em>Sample2<\/em> table<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nCREATE TABLE Sample2 (ID int,\nCONSTRAINT FK_Sample2_ID FOREIGN KEY (ID) REFERENCES Sample(ID));<\/pre>\n<h3>Step 1: Creating sequence number column<\/h3>\n<p>The first step to replacing an identity column with a sequence number is to create a new column in the <em>Sample <\/em>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 <em>Sample<\/em> table.<\/p>\n<p><strong>Listing 3: Added sequence number column <\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nALTER TABLE Sample ADD SequenceNumber int NULL;\nGO<\/pre>\n<h3>Step 2: Updating <em>SequenceNumber<\/em>\u00a0column<\/h3>\n<p>Once the new sequence number column has been added to the <em>Sample <\/em>table, the next step is to update the new column with the appropriate value for all the existing records. The new <em>SequenceNumber <\/em>column will be populated with the same values as the ID column. Therefore to update the <em>SequenceNumber <\/em>column, run the code in Listing 4.<\/p>\n<p><strong>Listing 4: Updating the <em>SequenceNumber <\/em>column<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nUPDATE Sample\n   Set SequenceNumber = ID;\nSELECT * FROM Sample;<\/pre>\n<p>The output in Report 2 is produced after running Listing 4.<\/p>\n<p><strong>Report 2: <em>Sample <\/em>table<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"199\" height=\"68\" class=\"wp-image-93095\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/an-images-showing-the-results-of-listing-4-id-sam-1.png\" alt=\"An images showing the results of Listing 4. ID SampleName SequenceNumber 1 First 1, 2 Second 2, 3 Third 3\" \/><\/p>\n<h3>Step 3: Identifying and removing foreign key references<\/h3>\n<p>The identity column of the <em>Sample <\/em>table has a primary key named <em>PK_Sample_ID<\/em> associated with it. This primary key needs to be removed from the <code>ID<\/code> column and then moved to the new <code>SequenceNumber<\/code> 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 <em>PK_Sample_ID <\/em>primary key, run the code in Listing 5.<\/p>\n<p><strong>Listing 5: Finding all foreign keys<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nSELECT FK.TABLE_NAME as ForeignKeyTable,\n      C.CONSTRAINT_NAME as Constraint_Name \nFROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C\n    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK \n    ON C.CONSTRAINT_NAME =Fk.CONSTRAINT_NAME\n    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK \n    ON C.UNIQUE_CONSTRAINT_NAME=PK.CONSTRAINT_NAME \n    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU \n    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME \n    INNER JOIN (\n        SELECT i1.TABLE_NAME, i2.COLUMN_NAME\n        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1\n            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 \n     ON i1.CONSTRAINT_NAME =i2.CONSTRAINT_NAME \n        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'\n    ) PT ON PT.TABLE_NAME = PK.TABLE_NAME\nWHERE PK.TABLE_NAME = 'Sample' and PT.COLUMN_NAME = 'ID';<\/pre>\n<p>When the code in Listing 5 is run, the output in Report 3 is generated.<\/p>\n<p><strong>Report 3: Output created when Listing 5 is run. <\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"190\" height=\"36\" class=\"wp-image-93096\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/an-image-showing-the-result-of-listing-5-foreignke-1.png\" alt=\"An image showing the result of Listing 5 ForeignKeyTable Constraint_name Sample2 FK_Sample2_ID\" \/><\/p>\n<p>To remove the only foreign key identified, the script in Listing 6 can be run.<\/p>\n<p><strong>Listing 6: Removing foreign key<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nALTER TABLE Sample2 DROP CONSTRAINT FK_Sample2_ID;<\/pre>\n<p>Keep in mind any foreign keys dropped should be recreated, so consider retaining the foreign key information.<\/p>\n<h3>Step 4: Removing primary key from identity column<\/h3>\n<p>With all the foreign keys removed, the primary key on the <em>Sample <\/em>table can be dropped using the script in Listing 7.<\/p>\n<p><strong>Listing 7: Removing primary key<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nALTER TABLE Sample\nDROP CONSTRAINT PK_Sample_ID;\nGO<\/pre>\n<h3>Step 5: Dropping identity column<\/h3>\n<p>With the primary key removed, the identity column can be dropped using the code in Listing 8.<\/p>\n<p><strong>Listing 8: Removing identity column.<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nALTER TABLE Sample\nDROP COLUMN ID ;\nGO<\/pre>\n<h3>Step 6: Renaming <em>SequenceNumber c<\/em>olumn<\/h3>\n<p>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.<\/p>\n<p><strong>Listing 9: Renaming <em>SequenceNumber <\/em>column<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nEXEC sp_rename 'Sample.SequenceNumber',\n'ID', 'COLUMN';\nGO<\/pre>\n<h3>Step 7: Adding NOT NULL requirement and primary key<\/h3>\n<p>Identity columns are defined to have a <code>NOT<\/code> <code>NULL<\/code> requirement. Therefore to make the new sequence number column mirror the original identity column properties, the <code>NOT<\/code> <code>NULL<\/code> requirement must be added to the sequence number column. The script in Listing 10 alters the <code>ID<\/code> column to not allow nulls and adds a new primary key constraint to replace the primary key deleted in step 4.<\/p>\n<p><strong>Listing 10: Adding NOT NULL requirement and primary key<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nALTER TABLE Sample ALTER COLUMN [ID] int NOT NULL;\nGO\nALTER TABLE Sample\n   ADD CONSTRAINT PK_Sample_ID PRIMARY KEY CLUSTERED (ID ASC) ;\nGO<\/pre>\n<h3>Step 8: Creating sequence number object<\/h3>\n<p>Because the <em>Sample <\/em>table already has some rows, the highest value for the new <code>ID<\/code> 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 <code>ID <\/code>column in the <em>Sample<\/em> 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 <code>ID<\/code> column value and create the new sequence number object with the correct <code>START<\/code> value.<\/p>\n<p><strong>Listing 11: Creating Sequence number object<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nDECLARE @NewStartValue int;\nDECLARE @IncrementValue int = 1;\nDECLARE @CMD nvarchar(1000);\nSELECT @NewStartValue = MAX(ID) + @IncrementValue FROM Sample;\nSET @CMD = 'CREATE SEQUENCE Sample_SequenceNumber AS INT START WITH ' +\n           RTRIM(CAST(@NewStartValue as CHAR)) + \n           ' INCREMENT BY ' + RTRIM(CAST(@IncrementValue AS CHAR));\nEXEC sp_executesql @CMD\nGO<\/pre>\n<p>Care should be used when executing dynamic SQL to ensure you don\u2019t cause a SQL injection issue. Therefore before executing any dynamic SQL, make sure you are not potentially opening the door for SQL injection issues.<\/p>\n<h3>Step 9: Setting a default value for the new ID column<\/h3>\n<p>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 <code>ID<\/code> column to automatically populate with a sequence number value, a default constraint needs to be added to the new <code>ID<\/code> column. This constraint can be added by using the code in Listing 12.<\/p>\n<p><strong>Listing 12: Setting the ID column default value<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nALTER TABLE Sample\nADD CONSTRAINT ID_Default \n   DEFAULT (NEXT VALUE FOR Sample_SequenceNumber) FOR ID;\nGO<\/pre>\n<h3>Step 10: Restoring foreign keys<\/h3>\n<p>In Step 3, the one foreign key constraint that referenced the primary key on the <em>Sample <\/em>table was deleted. This step adds back that deleted foreign key reference, using the code in Listing 13.<\/p>\n<h2><strong>Listing 13: Restoring foreign key<\/strong><\/h2>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nALTER TABLE Sample2\n   ADD CONSTRAINT FK_Sample2_ID FOREIGN KEY (ID) REFERENCES Sample(ID);<\/pre>\n<h3>Testing out inserting new rows<\/h3>\n<p>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 <code>ID<\/code> 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.<\/p>\n<p><strong>Listing 14: Inserting three new rows into the <em>Sample <\/em>table<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO \nINSERT INTO Sample (SampleName)    \n   VALUES ('Fourth'),\n          ('Fifth'),\n          ('Sixth');\nSELECT * FROM Sample;<\/pre>\n<p>When the code in Listing 14 is run, the results in Report 4 are created.<\/p>\n<p><strong>Report 4: Output when Listing 14 is executed<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"95\" height=\"115\" class=\"wp-image-93097\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/image-showing-the-results-of-running-listing-14-s.png\" alt=\"Image showing the results of running Listing 14. SmpleName ID, First 1, Second 2, Third 3, Fourth 4, Fifth 5, Sixth 6\" \/><\/p>\n<p>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 <code>ID<\/code> column is no longer in the first ordinal position in the table as the original <code>ID<\/code> column was.<\/p>\n<h2>Option 2: Replacing identity column using SWITCH operation<\/h2>\n<p>Another option to replace an identity column is to use the <code>ALTER<\/code> <code>TABLE<\/code> <code>SWITCH<\/code> 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 <code>SWITCH<\/code> operation doesn\u2019t 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 <a href=\"https:\/\/docs.microsoft.com\/en-us\/previous-versions\/sql\/sql-server-2008-r2\/ms191160(v=sql.105)\">Microsoft Documentation<\/a>:<\/p>\n<ul>\n<li><strong><em>Both tables must exist before the SWITCH operation<\/em><\/strong><em>. 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.<\/em><\/li>\n<li><strong><em>The receiving partition must exist and it must be empty<\/em><\/strong><em>. 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.<\/em><\/li>\n<li><strong><em>The receiving nonpartitioned table must exist and it must be empty.<\/em><\/strong><em>\u00a0If 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.<\/em><\/li>\n<li><strong><em>Partitions must be on the same column.<\/em><\/strong><em>\u00a0If you are switching a partition from one partitioned table to another, both tables must be partitioned on the same column.<\/em><\/li>\n<li><strong><em>Source and target tables must share the same filegroup.<\/em><\/strong><em>\u00a0The source and the target table of the ALTER TABLE&#8230;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.<\/em><\/li>\n<\/ul>\n<h3>Cleanup and recreating of artifacts from Option 1<\/h3>\n<p>Before learning how to use the <code>SWITCH<\/code> 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.<\/p>\n<p><strong>Listing 15: Cleanup and recreation of sample tables<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">USE tempdb;\nGO\nDROP TABLE Sample2;\nDROP TABLE Sample;\nDROP SEQUENCE Sample_SequenceNumber;\nGO\nCREATE TABLE Sample (ID int identity(1,1) NOT NULL, \n      SampleName varchar(30) NOT NULL,\n      CONSTRAINT PK_Sample_ID PRIMARY KEY CLUSTERED(ID ASC));\nINSERT INTO Sample(SampleName) \n   VALUES ('First'),\n          ('Second'),\n          ('Third');\nCREATE TABLE Sample2 (ID int,\nCONSTRAINT FK_Sample2_ID FOREIGN KEY (ID) REFERENCES Sample(ID));\nGO\nUSE tempdb;\nGO\nDECLARE @NewStartValue int;\nDECLARE @IncrementValue int = 1;\nDECLARE @CMD nvarchar(1000);\nSELECT @NewStartValue = MAX(ID) + @IncrementValue FROM Sample;\nSET @CMD = 'CREATE SEQUENCE Sample_SequenceNumber AS INT START WITH ' +\n           RTRIM(CAST(@NewStartValue as CHAR)) + \n           ' INCREMENT BY ' + RTRIM(CAST(@IncrementValue AS CHAR));\nEXEC sp_executesql @CMD\nGO<\/pre>\n<h3>Step 1: Creating new table that uses sequence number<\/h3>\n<p>To use the <code>SWITCH<\/code> 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 <code>ID<\/code> column instead of an identity specification.<\/p>\n<p><strong>Listing 16: Create a new table<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nCREATE TABLE Sample_New (\n   ID int NOT NULL DEFAULT NEXT VALUE FOR Sample_SequenceNumber, \n   SampleName varchar(30) NOT NULL,\n   CONSTRAINT PK_Sample_New_ID PRIMARY KEY CLUSTERED(ID ASC)\n );\n GO<\/pre>\n<h3>Step 2: Removing foreign key references<\/h3>\n<p>The <code>SWITCH<\/code> operation will fail if the primary key on the table being switched is referenced by any foreign keys. Therefore all foreign keys to the <em>Sample<\/em> table need to be removed first. <em>Sample2 <\/em>has a foreign key reference to the primary key, which will be removed by running the code in Listing 6.<\/p>\n<h3>Step 3: Switching tables<\/h3>\n<p>Once all the foreign key constraints have been removed, the <code>SWITCH<\/code> operation can be performed. The code in Listing 17 will perform the <code>SWITCH<\/code> operation.<\/p>\n<p><strong>Listing 17: Switch tables<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nALTER TABLE Sample SWITCH TO Sample_New;\nSELECT * FROM Sample_New;<\/pre>\n<p>When the switch is performed, the partition between the source (<em>Sample<\/em>) and target (<em>Sample_New<\/em>) tables is switched. Only the metadata is changed; the data was not moved. The output of the <code>SELECT<\/code> statement in Listing 17 can be found in Report 5. This output verifies that rows from the <em>Sample <\/em>table are now associated with the <em>Sample_New <\/em>table.<\/p>\n<p><strong>Report 5: Rows in <em>Sample_New<\/em> table<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"104\" height=\"68\" class=\"wp-image-93098\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/image-showing-results-of-listing-17-id-samplename-1.png\" alt=\"Image showing results of Listing 17. ID SampleName, 1 First, 2 Second, 3 Third\" \/><\/p>\n<h3>Step 4: Drop old table and rename new tables<\/h3>\n<p>Once the <code>SWITCH<\/code> operation has been performed, the old <em>Sample<\/em> 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.<\/p>\n<p><strong>Listing 18: Drop old and rename new<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nDROP TABLE Sample\nEXEC sp_rename N'Sample_New',N'Sample';<\/pre>\n<h3>Step 5: Recreating deleted foreign key references<\/h3>\n<p>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.<\/p>\n<h3>Verifying migration<\/h3>\n<p>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. <em>ID <\/em>column is now in ordinal position 1. I\u2019ll leave it up to you to run this code and to verify that the <code>SWITCH<\/code> operation was successful in migrating from an identity column to a column populated with a sequence number object.<\/p>\n<h2>Restricting sequence numbers from Being updated<\/h2>\n<p>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 <code>AFTER<\/code> <code>UPDATE<\/code> trigger will need to be created. To verify that the existing <em>Sample <\/em>table allows updates to the ID column, run the code in Listing 19.<\/p>\n<p><strong>Listing 19: Updating ID column <\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO \nBEGIN TRAN\nUPDATE Sample\n   SET ID = ID + 100;\nSELECT * FROM Sample;\nROLLBACK TRAN;\nGO<\/pre>\n<p>The code in Listing 19 runs successfully and produces the output in Report 6.<\/p>\n<p><strong>Report 6: Output when Listing 17 is run<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"107\" height=\"118\" class=\"wp-image-93099\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/image-showing-results-of-listing-19-id-samplename.png\" alt=\"Image showing results of Listing 19. ID SampleName, 101 First, 102 Second, 103 Third, 104 Fourth, 105 Fifth, 106 Sixth\" \/><\/p>\n<p>By reviewing Report 6, you can see that the ID values were all updated from their original values. The code in Listing 19 contains <code>BEGIN<\/code> <code>TRAN<\/code> and END <code>TRAN<\/code> statements to roll back these updates for the next test.<\/p>\n<p>To make sure an <code>UPDATE<\/code> statement to the <code>ID<\/code> column in the <em>Sample <\/em>table cannot be performed, the <code>AFTER<\/code> <code>UPDATE<\/code> trigger in Listing 20 needs to be created.<\/p>\n<p><strong>Listing 20: UPDATE trigger<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb\nGO\nCREATE TRIGGER trg_UpdateSample\nON Sample\nAFTER UPDATE\nAS\nBEGIN\n   SET NOCOUNT ON;\n   DECLARE @OriginalID int\n   DECLARE @UpdatedID int\n   SELECT @OriginalID =[ID] FROM deleted\n   SELECT @UpdatedID =[ID] FROM inserted\n   IF @OriginalID &lt;&gt; @UpdatedID\n   BEGIN\n      RAISERROR('Failed: Update performed on ID column', 16, 1);\n      ROLLBACK TRANSACTION\n   END\nEND\nGO<\/pre>\n<p>To test out if this trigger works, run the code in Listing 21.<\/p>\n<p><strong>Listing 21: Testing if trigger keeps ID column from being updated.<\/strong><\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE tempdb;\nGO\nUPDATE Sample\n\tSET ID = ID + 100\nWHERE ID = 1;\nGO<\/pre>\n<p>When the code in Listing 21 is run, the error message in Report 7 is produced<\/p>\n<p><strong>Report 7: Error when Listing 21 is executed<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"759\" height=\"78\" class=\"wp-image-93100\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/image-showing-error-message-after-running-listing-1.png\" alt=\"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.\" \/><\/p>\n<p>By creating the <code>AFTER<\/code> <code>UPDATE<\/code> trigger <em>trg_UpdateSample, <\/em>the code in Listing 21 could not update the <code>ID<\/code> column<em>.<\/em><\/p>\n<h2>Replacing identity an column with a sequence number<\/h2>\n<p>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 <code>AFTER<\/code> <code>UPDATE<\/code> trigger needs to be defined to restrict the updates.<\/p>\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: How to replace an identity column with a sequence number<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. What is the difference between SQL Server IDENTITY and SEQUENCE?<\/h3>\n            <div class=\"faq-answer\">\n                <p>IDENTITY is a column property that automatically generates incrementing integer values for INSERT operations. It is tied to a specific table and column &#8211; values cannot be shared, pre-allocated, or reset without rebuilding the table. SEQUENCE is a separate database object that generates a sequence of numeric values independently of any specific table. It can be shared across multiple tables, pre-allocated in batches using sp_sequence_get_range, reset, or modified without table-level changes. SEQUENCE provides flexibility that IDENTITY cannot.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. Can I change an IDENTITY column to use a SEQUENCE without data loss?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Yes. The approach in this article preserves all existing data. The key steps are: add a new column for the sequence number, populate it with existing identity values, drop the primary key, drop the identity column, rename the sequence column to the original column name, recreate the primary key, create the SEQUENCE object starting from the current max value, and add a default constraint that calls the SEQUENCE. Foreign keys must be temporarily removed and recreated. Full step-by-step T-SQL is provided in this article.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. What is the ALTER TABLE SWITCH method for replacing an IDENTITY column?<\/h3>\n            <div class=\"faq-answer\">\n                <p>ALTER TABLE SWITCH is a metadata-only operation that swaps the storage of two tables with identical schemas (or near-identical with partition alignment). To use it for IDENTITY-to-SEQUENCE migration: create a new table with the same schema but using SEQUENCE instead of IDENTITY, switch the data from the old table to the new one (almost instant, no data movement), drop the old table, and rename the new table. This is faster than Option 1 for large tables but requires more preparation (building the target table first).<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">4. Why would I use a SQL Server SEQUENCE instead of IDENTITY?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Common reasons: (1) you need to share a key sequence across multiple tables (e.g., a single order number sequence shared by purchase orders and sales orders); (2) you need to pre-allocate a batch of sequence numbers for application-level assignment; (3) you need to reset the counter or change the increment without dropping and recreating the table; (4) you need the next value before the row is inserted (NEXT VALUE FOR can be called in any expression, not just INSERT). IDENTITY handles the simple incrementing-key-per-table case and should be kept for that.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to replace a SQL Server IDENTITY column with a SEQUENCE number using two methods: step-by-step column modification (with foreign key handling) and the ALTER TABLE SWITCH operation. Full T-SQL code at each step.&hellip;<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[53,143525,143539],"tags":[5134],"coauthors":[11330],"class_list":["post-93083","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn","category-theory-and-design","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93083","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=93083"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93083\/revisions"}],"predecessor-version":[{"id":109936,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93083\/revisions\/109936"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93083"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93083"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93083"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93083"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}