23

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

bobs
  • 21,844
  • 12
  • 67
  • 78
George2
  • 44,761
  • 110
  • 317
  • 455

3 Answers3

51

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)
Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
  • Clustering by an identity column could create a hot-spot of contention at the end of the table. I would review your 'need' to cluster with an identity column. – Rawheiser Sep 13 '10 at 20:07
  • @Rawheiser: I assumed identity columns generally made for good candidates for a clustered index. If you have found this not to be the case, do you have any further info on the topic you could point me toward? – mwolfe02 Jan 03 '13 at 20:55
  • 1
    For inserts only there really isn't a hot spot issue. But if the most frequently updated rows are the ones most recently added - clustering will group them together and make a blocking hot spot. I would cluster based on the queries that are used to get at the data. If you always search by last and first name - use that. An Identity column can be added as a last column to uniquely identify one of the multiple John Smiths. Round-Robin Partitioning a table can be used to spread out a hot spot as well. – Rawheiser Jan 10 '13 at 00:09
0

have 1 approach in mind, but not sure whether it is feasible at your end or not. But let me assure you, this is a very effective approach. You can create a table having an identity column and insert your entire data in that table. And from there on handling any duplicate data is a child's play. There are two ways of adding an identity column to a table with existing data:

Create a new table with identity, copy data to this new table then drop the existing table followed by renaming the temp table.

Create a new column with identity & drop the existing column

For reference the I have found 2 articles : http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/ http://cavemansblog.wordpress.com/2009/04/02/sql-how-to-add-an-identity-column-to-a-table-with-data/

NG.
  • 5,695
  • 2
  • 19
  • 30
0

Not always you have permissions for DBCC commands.

Solution #2:

create table #tempTable1 (Column1 int)
declare @new_seed varchar(20) = CAST((select max(ID) from SomeOtherTable) as varchar(20))
exec (N'alter table #tempTable1 add ID int IDENTITY('+@new_seed+', 1)')
Alexander Shapkin
  • 1,126
  • 1
  • 13
  • 11