Change data type

[Dimitris Nikolakakis]

I have a table named Addressbook with fields:
ID int
Name nvarchar

I want to change the type of ID to:

int IDENTITY 1,1

Is there any way to do it from SQL Query Analyzer?

Thanks
Dimitris

 

[dbahooker]

do it in enterprise manager and then hit the 'change script' button.

it will give you the full alter statement you need.

-Aaron

 

[Sylvain Lafontaine]

Cannot be done in a single step. The easiest way to do this would be to use
Enterprise Manager; however, if you can't use it; you will have to add a new
column with the identity attribut, set IDENTITY INSERT ON, copy the data
from the old column to the new column then delete the old one and rename the
new column to the old name. Instead of adding a new column, you can also
create a new table, copy all the data over, delete the old table and rename
the new table with the old name.

Of course, you must not forget to remove all constraints first and recreate
them at the end. The following article show you how to this to remove an
identity column, which is something very similar to adding an identity
column:

http://www.sqlmag.com/articles/index.cfm?articleid=22081&
http://www.sqlmag.com/Files/23/22081/Listing_03.txt