t-sqlsql-server-2008

How to add a new identity column to a table in SQL Server?

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

Solution

  • 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)