#0272 – SQL Server – IDENTITY columns – Myths – IDENTITY columns cannot be added to existing tables – Part 01


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.

Advertisements

5 thoughts on “#0272 – SQL Server – IDENTITY columns – Myths – IDENTITY columns cannot be added to existing tables – Part 01

  1. Pingback: #0273 – SQL Server – IDENTITY columns – Myths – IDENTITY columns cannot be added to existing tables – Part 02 | SQLTwins by Nakul Vachhrajani

  2. Pingback: #0390 – SQL Server – SSIS – OLE DB Destination – Table Fast Load – Keep Identity option | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s