Sunday, May 13, 2012

removing identity property taking more time

How to remove the identity property.

to remove the identity property from a table , when you do through SSMS --> right click
on table --> select column --> properties --> * remove identity property.

But internally it will copy all the data into a temp table using insert into and then renames the existing , till this whole operation completes it holds an exclusive lock to not be modified by any other transactions.

but for larger tables it takes lot of time to copy the data and blocks the transactions.

we can replace the insert into with SWITCH command to copy the data and make it fast.
below are the steps.
-- 1 Create the table for simulation
--2 insert few records
--3 create another table with the same schema as original table but without any identity
--4 Move the entire data from original table to temp table
--5 Rename the existing table to tbl_y table
--6 Rename the temp tale to existing table
--7 drop the tbl_y
--------------------------------------------------------------
--1 Create the table for simulation
CREATE TABLE  MY_TRANS
(iid int identity(1,1),
  nage int,
  CreatedDate datetime
)
GO
--------------------------------------------------------------
--2 insert few records
insert into MY_TRANS select 10,'2012-dec-01'
GO 5
--------------------------------------------------------------
-- 3 create another table with the same schema as original table but without any identity
CREATE TABLE [MY_TRANS_temp]
(iid int  not null,
  nage int,
  CreatedDate datetime
)
--------------------------------------------------------------
-- 4 -Move the entire data from original table to temp table
alter table MY_TRANS switch partition 1 to MY_TRANS_temp
GO
 
--------------------------------------------------------------
-- 5 Rename the existing table to tbl_y table
EXEC SP_RENAME MY_TRANS , MY_TRANS_Y
-- 6 Rename the temp tale to existing table
EXEC sp_rename MY_TRANS_TEMP , MY_TRANS
-- 7 drop the tbl_y
DROP TABLE MY_TRANS_Y
 
 

1 comment:

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate t...

Contributors