Alter Column to be an Identity

[Ambsgirl]

I'm trying to programmatically alter a column as an identity. I can change it successfully through Enterprise Manager so I'd imagine there must be a way to actually write a script to do it, isn't there? This does not work: ALTER TABLE DI_BATCHES alter column DB_BATCH_ID int IDENTITY (1, 1). Anyone know how to do this?

 

[SQLBill]

Enterprise Manager does some funny stuff behind the scenes. So it doesn't always do what you think it might be doing. For instance, you think it's just altering the column. It might instead make a new table with that column set up as an Identity, copy the data from the original table to the new one, drop the original table, and then rename the new table.

Might be hard to believe, but that's what it does if you have it reorder columns. So it might be doing that in this case also.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLSister]

Yep Bill is right, that is what it is doing and what you would have to do manually to do the same thing through SQL.

Questions about posting. See faq183-874

 

[Ambsgirl]

Huh... that is so not the answer I was hoping for! But thanks for clearing that up for me. At least now I can move on and resign myself to extra code.