CODEWITHSUNDEEP
sql-server-2005identity
user201807
user201807

Reputation: 545

SQL Server 2005 - Working with IDENTITY column

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.

Upvotes: 1

Views: 833

Answers (2)

HLGEM
HLGEM

Reputation: 96552

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.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146429

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.

Upvotes: 5

Related Questions