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?
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.
Okay. I was able to add identity to the existing primary column with 10 million records. Took me about 30 mins.
The steps:
Change database to single user mode (to make sure no other connections to databse, can cause lock)
Open table in designer mode
Make change. Do not save
Click Generate Change Script button (usually on the left right above the Object Explorer)
- Copy generated script
- Close designer window (you can run only one instance at the time)
- Open new window
Execute script
Done. Now your column has identity :)
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.
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'
