Menu
I am using SQL Server 2008 Enterprise. I want to add an identity column (as unique clustered index and primary key) to an existing table. Integer based auto-increasing by 1 identity column is ok. Any solutions?
BTW: my most confusion is for existing rows, how to automatically fill-in new identity column data?
thanks in advance, George
734
You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.
Syntax for SQL Server Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) . VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned a unique value.
You can not update identity column. SQL Server does not allow to update the identity column unlike what you can do with other columns with an update statement.
you can use -
alter table <mytable> add ident INT IDENTITY This adds ident column to your table and adds data starting from 1 and incrementing by 1.
To add clustered index -
CREATE CLUSTERED INDEX <indexName> on <mytable>(ident) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
