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
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 |
Nice tip! Thanks
ReplyDelete