SQL Server 2005 - Working with IDENTITY column

Question

Hi,

I am working on SQL Server 2005 tables. Here I need to add a column named ‘ID’ as ‘IDENTITY’ column (With starting and incrementing values as 1, 1).

Now my problem is that these tables already have thousands of records. So could you please suggest the best and easy way to perform this job?

Many Thanks,

Regards.

Anusha.

Answer 1

If you add an identity column all the existing records will get calculated incremental values based on the seed value you establish on the new column.

Answer 2

First make sure you havea a good backup.

Here is an example:

alter table mydatabase.dbo.mytest 
add id int identity (1,1)

Table will be locked up until it finishes adding the tidentity columns so don;t do this during high peak hours. As always test of dev first.

If you want to change an existing column to an identity that is harder.