question

umashanker.testing avatar image
umashanker.testing asked

How to add identity column for table having i billion data?,add identity column in a table having i billion data

I want to assign value from 1-1000.... Is it possible in same table?,I want to set identity for existing data also.If new column is created its create new value suppose 1.But i Want to assign identity value to existing data also.Is it possible if yes how ?
alter-table
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
sqlaj 1 avatar image
sqlaj 1 answered
The range (lowest value to highest value) for the column is based on the data type. Review the numeric data types here and chose one appropriate for you solution. http://msdn.microsoft.com/en-us/library/ms187752.aspx Setting the identity for existing data will occur when you add the new column. If you use "Auto Increment", as soon as you save the changes SQL Server will add identities for each row in the table. From what I remember SQL Server generates the next value of the identity column based on the seed and increment when the column is created. See more here about Identity columns. http://msdn.microsoft.com/en-us/library/ms186775.aspx I would highly recommend "TESTING" the process on a different server, even if the current server is not production. You need to determine the amount of time it will take. Remember the table will be locked for the duration of the action being taken.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

But how to number existing records from 1
0 Likes 0 ·
As @sqlaj said... "Setting the identity for existing data will ocur when you add the new column"
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.