QuickQuestion: Can I add column with IDENTITY property to existing table with data?

QuickQuestion series is a series of short posts in which I answer database related questions asked by my colleagues, friends, and co-workers, mainly application developers.

Today’s question:

Can I add column with IDENTITY property to existing table with data?

What the IDENTITY property is?

It is a property of column which says that the values for this column will be automatically generated by SQL Server engine. During creation, you can specify seed and increment values. The seed value is used for the very first row inserted into the table. The increment value is used to create next value based on the previous row inserted into the table. By default, the seed and increment are set to 1. You must specify both the seed and increment or neither. Only one column with IDENTITY property is allowed per table.

Creating column with IDENTITY property

To create a column with IDENTITY property you need to use such syntax in column definition:

IDENTITY [ (seed , increment) ]

You can create a table with IDENTITY column this way:

CREATE TABLE dbo.TableWithIdentity (
    Id int IDENTITY(1,1) NOT NULL,
    Column1 varchar(50)  NOT NULL
);

Or you can create a new column for the existing empty table:

CREATE TABLE dbo.TableWithoutIdentity (
    Column1 varchar(50)  NOT NULL
);

ALTER TABLE dbo.TableWithoutIdentity
ADD Id int IDENTITY(1,1) NOT NULL;

But, will that work for a table with data?

Let’s try…

As we all know from our experience, when adding a new column to the existing table it is safer to make it nullable. We can test whether it works in this way:

CREATE TABLE dbo.IdentityTest (
    Col varchar(50)
);
GO

-- Generate 50 rows with some data
INSERT INTO dbo.IdentityTest (Col)
SELECT CONVERT(varchar(50), NEWID());
GO 50

ALTER TABLE dbo.IdentityTest
ADD Id int IDENTITY(1,1) NULL;

Unfortunately, it doesn’t work.

Msg 8147, Level 16, State 1, Line 9
Could not create IDENTITY attribute on nullable column 'Id', table 'dbo.IdentityTest'.

The identifier column must not allow for NULL values. OK then, let’s try with NOT NULL. Will values be automatically generated for all existing rows?

CREATE TABLE dbo.IdentityTest (
    Col varchar(50)
);
GO

-- Generate 50 rows with some data
INSERT INTO dbo.IdentityTest (Col)
SELECT CONVERT(varchar(50), NEWID());
GO 50

ALTER TABLE dbo.IdentityTest
ADD Id int IDENTITY(1,1) NOT NULL;

After execution we immediately can spot message we’re looking for:

Commands completed successfully.

Now we can check data in the table.

Success! We already know the answer to the given question. We can do it! But wait a minute… are these rows ordered? Let’s check.

Nope. They’re not. We could foresee that. SQL Server doesn’t know about any particular order for this data set. What if we would have an index on the Col column?

--DROP TABLE dbo.IdentityTest
CREATE TABLE dbo.IdentityTest (
    Col varchar(50)
);
GO
-- Generate 50 rows with some data
INSERT INTO dbo.IdentityTest (Col)
SELECT CONVERT(varchar(50), NEWID());
GO 50

CREATE CLUSTERED INDEX ix_IdentityTest_Col  ON dbo.IdentityTest (Col);
GO

ALTER TABLE dbo.IdentityTest
ADD Id int IDENTITY(1,1) NOT NULL;
GO

SELECT * FROM dbo.IdentityTest ORDER BY Id;
SELECT * FROM dbo.IdentityTest ORDER BY Col;

HA! Now values for the Id column were generated respectively to the row order in the clustered index.

Summary

After few simple checks, we know how it works. It is possible to add a new column with IDENTITY property to the existing table and values will be automatically populated. It’s even possible to try to force order in which IDENTITY values should be generated.

Last but not least, you cannot add a column with IDENTITY property to the table which already has one such column. Only one IDENTITY column can be created per table.

-Marek