Generally, IDENTITY columns are used when developing new tables so that when records are inserted into a table, they are assigned the appropriate values.

Hence if ever the need arises to insert an IDENTITY column into an existing question, the big question is – can it be done?

Is it really possible to add an IDENTITY column to an existing table?

The answer is quite simple – Yes!

The only restrictions that ALTER TABLE enforces upon us are that a new column must be added and that it should be of a data-type compatible with IDENTITY Columns. To demonstrate these points, I will perform two experiments shown below.

Modifying an existing column to an IDENTITY column

Other than recreating the entire table, an existing column cannot be modified to an IDENTITY column. Copy-Paste the query below into SSMS and simply parse it by using Ctrl+ F5 key combination or the blue check mark on the SSMS toolbar. The following error will be encountered:

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (RecordId INT,
                            IdentityValue VARCHAR(20)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (3, 'Three'),
       (2, 'Two'),
       (1, 'One'),
       (0, 'Zero'),
       (4, 'Four'),
       (5, 'Five'),
       (6, 'Six');
GO

--3. Check the values inserted into the table
SELECT RecordId, IdentityValue
FROM dbo.tIdentity;
GO

--4. Alter the table to add an IDENTITY Column
ALTER TABLE dbo.tIdentity
    ALTER COLUMN RecordId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED;
GO    

--4. Check the values inserted into the table
SELECT RecordId, IdentityValue
FROM dbo.tIdentity;
GO

----4b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
--GO

--4. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'IDENTITY'.
*/

Tip: If your design ever needs to be modified to convert an existing column into an IDENTITY column, this can be achieved using the following high-level steps:

1. Create an identical table under a different name, but with an IDENTITY column (e.g. tmp_tIdentity)
   
2. Set IDENTITY_INSERT to ON for this new table
   
3. Pump data from the old table to the new table (i.e. from tIdentity to tmp_tIdentity)
   
4. Set IDENTITY_INSERT to OFF for this new table
   
5. Drop the old table
   
6. Rename the newly created table (i.e. rename from tmp_tIdentity to tIdentity)

Adding a new Identity column to an existing table

While existing columns cannot be converted to IDENTITY columns, new IDENTITY column can always be added to an existing table (provided there are no other IDENTITY columns on it).

The below script quite simply creates a table and then add an IDENTITY column to it. As can be seen from the results below, although the records were inserted randomly, the identity values are assigned based on the way in which the records are ordered. This is an important consideration, as we will see in part 02 of this post. In this case, we have a clustered key on the RecordId column and therefore, the values in the IDENTITY column follow the same order as the RecordId.

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (RecordId INT PRIMARY KEY CLUSTERED,
                            IdentityValue VARCHAR(20)
                           );
GO

--2. Add some test data
INSERT INTO dbo.tIdentity (RecordId, IdentityValue)
VALUES (3, 'Three'),
       (5, 'Five'),
       (1, 'One'),
       (0, 'Zero'),
       (4, 'Four'),
       (2, 'Two'),
       (6, 'Six');
GO

--3. Check the values inserted into the table
SELECT RecordId, IdentityValue
FROM dbo.tIdentity;
GO

--4. Alter the table to add an IDENTITY Column
ALTER TABLE dbo.tIdentity
    ADD IdentityId INT IDENTITY(1,1);
GO    

--4. Check the values inserted into the table
SELECT RecordId, IdentityId, IdentityValue
FROM dbo.tIdentity;
GO

----4b.One may also want to check the value of IDENT_CURRENT
--SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
--GO

--4. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
--Before adding the IDENTITY value
RecordId    IdentityValue
----------- --------------
0           Zero
1           One
2           Two
3           Three
4           Four
5           Five
6           Six

--After adding the IDENTITY value
RecordId    IdentityId  IdentityValue
----------- ----------- --------------
0           1           Zero
1           2           One
2           3           Two
3           4           Three
4           5           Four
5           6           Five
6           7           Six
*/

Conclusion (Part 01)

The above experiments show that Microsoft SQL Server allows us to define IDENTITY columns on existing tables, under certain restrictions. This can be a very useful feature in case an original, legacy design needs to be changed for a universal adoption of IDENTITY columns in your product.

Until we meet next time,

Be courteous. Drive responsibly.