Adding an identity to an existing column [SQL Server]

Question

I need to change the primary key of a table to an identity column, and there's already a number of rows in table.

I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database.

What's the SQL command to alter the column to have an identity property?

Answer 1

You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc

Answer 2

I don't believe you can alter an existing column to be an identity column using tsql. However, you can do it through the Enterprise Manager design view.

Alternatively you could create a new row as the identity column, drop the old column, then rename your new column.

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED

Answer 3

There isn't one, sadly; the IDENTITY property belongs to the table rather than the column.

The easier way is to do it in the GUI, but if this isn't an option, you can go the long way around of copying the data, dropping the column, re-adding it with identity, and putting the data back.

See here for a blow-by-blow account.

Answer 4

You can't alter the existing columns for identity.

You have 2 options,

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column

In this Approach you can retain the existing data values on the newly created identity column.

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

In this approach you can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

See the following Microsoft SQL Server Forum post for more details:

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/04d69ee6-d4f5-4f8f-a115-d89f7bcbc032

Answer 5

By design there is no simple way to turn on or turn off the identity feature for an existing column. The only clean way to do this is to create a new column and make it an identity column or create a new table and migrate your data.

If we use SQL Server Management Studio to get rid of the identity value on column "id", a new temporary table is created, the data is moved to the temporary table, the old table is dropped and the new table is renamed.

Use Management Studio to make the change and then right click in the designer and select "Generate Change Script".

You will see that this is what SQL server in doing in the background.

Answer 6

you can't do it like that, you need to add another column, drop the original column and rename the new column or or create a new table, copy the data in and drop the old table followed by renaming the new table to the old table

if you use SSMS and set the identity property to ON in the designer here is what SQL Server does behind the scenes. So if you have a table named [user] this is what happens if you make UserID and identity

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION

GO

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

Having said that there is a way to hack the system table to accomplish it by setting the bitwise value but that is not supported and I wouldn't do it

Answer 7

how can i add user specific data to an identity column of any table..

Answer 8

In SQL 2005 and above (Enterprise and Developer Editions only), there is a very cool trick to solving this problem for large tables (e.g. a billion rows) which can save you hours of downtime.

I've used it to convert, in under 5 seconds, a column of a of a 2.5 billion row table from IDENTITY to a non-IDENTITY (in order to run a multi-hour query whose query plan worked better for non-IDENTITY columns), and then restored the IDENTITY setting, again in less than 5 seconds.

Here's the trick: you can use SQL Server's ALTER TABLE...SWITCH statement to change the schema of a table without changing the data, meaning you can replace a table with an IDENTITY with an identical table schema, but without an IDENTITY column. The same trick works to add IDENTITY to an existing column.

Normally, ALTER TABLE...SWITCH is used to efficiently replace a full partition in a partitioned table with a new, empty partition. But it can also be used in non-partitioned tables too.

Here's a code sample of how it works.

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- see same records
 SELECT * FROM Test;

This is obviosuly more involved than the solutions above, but if your table is large this can be a real life-saver. There are some caveats:

• you'll need to drop foriegn keys before you do the switch and restore them after.
• same for WITH SCHEMABINDING functions, views, etc.
• new table's indexes need to match exactly (same columns, same order, etc.)
• old and new tables need to be on the same filegroup.
• I mentioned this above, but this only works on the Enterprise or Developer editions of SQL Server 2005 (or 2008 or later).

There's a good article on TechNet detailing the requirements above.

Answer 9

@Justin

Need some help going the other direction. From a non-identity to an identity column on a large table.

The integer column of the source table wants it to be not null. This requires it to have a default. I made it 0 but then when I did the switch the column was set to zero and not a unique identity value.

Thanks in advance, Linda Rawson