Hi all, this is just a documented version from http://stackoverflow.com/ question and the best answers.
We can't alter the existing columns for identity.
Approach 1
We have 2 options,
1. Create a new table with identity and drop the existing table
2. Create a new column with identity and drop the existing column
Option 1:
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'
Option 2:
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'
Approach 2:
In SQL 2005 and above, there's a trick to solve this problem without changing the table's data pages. This is important for large tables where touching every data page can take minutes or hours. The trick also works even if the identity column is a primary key, is part of a clustered or non-clustered index, or other gotchas which can trip up the the simpler "add/remove/rename column" solution.
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.
I've used this trick 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 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 obviously more involved than the solutions in other answers, but if your table is large this can be a real life-saver. There are some caveats:
• you'll need to drop foreign 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 file group.
• only works on SQL Server 2005 or later
• I previously believed that this trick only works on the Enterprise or Developer editions of SQL Server (because partitions are only supported in Enterprise and Developer versions), but Mason G. Zhwiti in his comment below says that it also works in SQL Standard Edition too. I assume this means that the restriction to Enterprise or Developer doesn't apply to ALTER TABLE...SWITCH.
There's a good article on TechNet detailing the requirements above.
Just collecting notes and sharing..
Source: stackoverflow.com
