Altering a column to be an Identity | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Altering a column to be an Identity

Example:
CREATE TABLE [dbo].[DFtemp] (
[abcd] [varchar] (100),
[idkey] [int]
) ON [PRIMARY]
GO I then insert values:
insert dftemp values (”,1)
insert dftemp values (”,3)
insert dftemp values (”,5) I would now like to make the [idkey] an IDENTITY column with a seed and increment. I would like to retain the idkey values that were already inserted.
My IDENTITY increment would therefore start at ‘6’ for the next record.
Can I achieve this without dropping and recreating the column?
I know it can be done using Enterprise Manager but can it be scripted with T-SQL?
With regard to the TSQL statement: alter table Authors
drop column id_AuthorNo alter table Author
add id_AuthorNo int not null Identity(1,1)
Reviewhttp://www.windowsitpro.com/Article/ArticleID/22080/22080.html?Ad=1 article for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Look for DBCC in BOL
Madhivanan Failing to plan is Planning to fail
If you want to retain the values, that already exists in the column, use Enterprise Manager to define the IDENTITY property on that column. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

]]>