11

I have a table about 10 million rows. We just imported it from another database with SQL Server Management Studio. It creates table but without identity and primary key on primary key column.

I could add the primary key but couldn't add identity. It's all the time timing out when I'm doing it in designer. Even I set time out settings to 0.

I need to create probably another column set primary key and identity, copy data from old, delete old column and rename new one.

Can anyone show me what will be the best way to do it for such big tables, without additional overheating?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
German
  • 740
  • 2
  • 10
  • 24
  • So something like this gave you a timeout? `alter table TableName add ID int identity(1, 1) primary key` – Tim Lehner Dec 28 '11 at 16:29
  • 1
    You can use `ALTER TABLE ... SWITCH` to do this almost instantly. Even on a 10 million row table. – Martin Smith Dec 28 '11 at 16:31
  • possible duplicate of [how to set auto increment after creating a table without any data loss?](http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss) – Martin Smith Dec 28 '11 at 16:32
  • @TimLehner: probably not - he said it gives him timeouts in the visual designer - which tries to re-create the table in the new format and copy over all data - all of 10 million rows...... with your `ALTER TABLE` statement, it should be a breeze! – marc_s Dec 28 '11 at 16:35

4 Answers4

15

You cannot add IDENTITY to an existing column. It just cannot be done.

You'll need to create a new column of type INT IDENTITY and then drop the old column you don't need anymore (and possibly rename the new column to the old name - if that's needed)

Also: I would not do this in the visual designer - this will try to recreate the table with the new structure, copy over all data (all 10 millions rows), and then drop the old table.

It's much more efficient to use straight T-SQL statements - this will do an "in-place" update, non-destructive (no data is lost), and it doesn't need to copy around 10 millions rows in the process...

ALTER TABLE dbo.YourTable
  ADD NewID INT IDENTITY(1,1) NOT NULL

When you add a new column of type INT IDENTITY to your table, then it will be automatically populated with consecutive numbers. You cannot stop this from happening, and you also cannot update the values later on.

Neither of those options is really very useful, in the end - you might end up with different ID values.... to do this right, you'd have to:

  • create the new table ahead of time, with the proper structure and the IDENTITY already in place
  • then turn on SET IDENTITY_INSERT (yourtable) ON on that table to allow values to be inserted into the identity column
  • copy over this data from the original source
  • turn off identity insert again: SET IDENTITY_INSERT (yourtable) OFF

Only with this approach will you be able to get the same ID's in an IDENTITY column in your new table.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    You can to all intents and purposes add `identity` to an existing column by creating a new table with the same structure except for the identity property. Switching the old table into the new table then dropping the old table and renaming the table. This leaves the data pages untouched. – Martin Smith Dec 28 '11 at 16:39
  • I disagree with you. I just tried to add primary key and identity column on a table which has 800K rows in designer mode. And it completed successfully. – German Dec 28 '11 at 16:39
  • @MartinSmith: and that works with two non-partitioned tables, too?? – marc_s Dec 28 '11 at 16:41
  • @Martin Smith: Can you clairfy what you mean by "Switching the old table into the new table" while "keeping the data pages untouched" ? – Andomar Dec 28 '11 at 16:46
  • 2
    @Andomar - It just alters the table metadata. Doesn't have to copy all the rows into a new table or add a column to all the existing rows. [Example code here](http://stackoverflow.com/a/6086661/73226) – Martin Smith Dec 28 '11 at 16:47
  • 1
    @German, its true you can't add an identity column to an existing table. If you used SSMS (or a similar tool) it may *appear* that you added it to an existing table, but behind the scenes a new table was create and the rows were imported - perhaps a distinction without a difference in many cases. – E.J. Brennan Dec 30 '11 at 17:06
4

You can add IDENTITY to an existing column. It just can be done. in SSMS Just go to tools-->options-->Designers-->Table And Database Designers and uncheck option Prevent Saving changes that require table re-creation.

Now add identity in designer mode to the required column and save.

4

Okay. I was able to add identity to the existing primary column with 10 million records. Took me about 30 mins.

The steps:

  1. Change database to single user mode (to make sure no other connections to databse, can cause lock)

  2. Open table in designer mode

  3. Make change. Do not save

  4. Click Generate Change Script button (usually on the left right above the Object Explorer)

  5. Copy generated script
  6. Close designer window (you can run only one instance at the time)
  7. Open new window

  8. Execute script

  9. Done. Now your column has identity :)

German
  • 740
  • 2
  • 10
  • 24
2

One way to set an identity column is during an insert with the option set identity_insert. For example, to change id in this table to identity:

create table YourTable (id int, value varchar(50))

You could use this script:

-- Create empty table as a copy of the original
select top 0 * into YourTable_New from YourTable

-- Drop the old ID column
alter table YourTable_New drop column id

-- Add a new ID column with identity
alter table YourTable_New add id int identity

-- Copy the old values into the identity column
set identity_insert YourTable_New on 
insert YourTable_New (id, value) select id, value from YourTable
set identity_insert YourTable_New off

-- Drop the old table and rename the new one
drop table YourTable
exec sp_RENAME 'YourTable_New' , 'YourTable'
Andomar
  • 232,371
  • 49
  • 380
  • 404