alter column to turn identity on

Question

alter column to turn identity on

K Currie

SSCommitted

Points: 1662
More actions
July 14, 2010 at 11:27 am

#139788

I'm trying to alter a table by making an existing column an identity column. If I try to do this by right clicking on the table and selecting Modify, I get a timeout expired message when saving my change. If I try to do this by writing an alter query I get an incorrect syntax message.
alter table dbo.mytable alter column mycolumn [int] identity(1, 1) not null
Any tips on how I can successfully accomplish this?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

how many records are in the table? The timeout is probably happening because SQL is creating a tmp table and then copying the data from the main table to the tmp table. if you go through the modify table like you did, you can hit script to see what it is doing. You can also run

alter table myTable add field2 int identity(1,1) not null

Why are you adding an identity column anyway?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

K Currie SSCommitted Points: 1662 More actions · Answer 2

The column should have been identity in the first place, but it wasn't. Way too many rows in table and definitely timing out because of this. Took a different plan of action and created a new column instead - not how I wanted to do it, but it works!