SQL Server 2005 - Working with IDENTITY column

[Anusha 1980]

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.

[Mycroft Holmes]

Just add the field, SQL server will insert the ID values for existing records (It does in 2008 and I think it did in 2005). If not add the field, use row_number() to update the value and then add the identity constraint.

[Niladri_Biswas]

Suppose I have a table declare @tbl1 table(name varchar(20), address varchar(50)) insert into @tbl1 select 'John','302 ABC Street' union all select 'Smith','412 DEF Street' select * from @tbl1 The output is

name address
John 302 ABC Street
Smith 412 DEF Street

Now I want to insert identity Change the table definition by adding an identity field like this

declare @tbl1 table(id int identity,name varchar(20), address varchar(50))

The output is

id name address
1 John 302 ABC Street
2 Smith 412 DEF Street

Else go ahead with the Row_Number() function(since sql server 2005 onwards)

select ROW_NUMBER() over(order by name) id, * from @tbl1

:)

Niladri Biswas

[Lost User]

Just add the IDENTITY column, SQL Server will fill the values automatically.