SQL Server: Quick Way to Remove the Identity Property

To remove the identity property from a table , when you do through SSMS --> right click on the 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.

Follow these 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