sql server add identity key property online

Assign Identity Property Online.

-- **** Warning : this approach works only when the base table is not partitioned.

-- **** Warning : Applies only when the table has no foreign keys

-- **** Warning : make sure you try the switch operation on lab server , both the schemas (nullability) should be same

use tempdb

-------------------------------------------------------------- 

--1 Create the table for simulation

create TABLE  MY_TRANS

(iid int not null, 

  nage int, 

  CreatedDate datetime 

) 

GO 

-------------------------------------------------------------- 

--2 insert few records 

insert into MY_TRANS select 1,10,'2012-dec-01' 

insert into MY_TRANS select 2,20,'2012-dec-02' 

insert into MY_TRANS select 3,30,'2012-dec-03' 

insert into MY_TRANS select 4,40,'2012-dec-04' 

insert into MY_TRANS select 5,50,'2012-dec-05' 

-------------------------------------------------------------- 

-- 3 create another table with the same schema as original table but WITH identity

CREATE TABLE [MY_TRANS_temp] 

(iid int  identity(1,1) not null , 

  nage int, 

  CreatedDate datetime 

) 

-------------------------------------------------------------- 

select * from MY_TRANS

-------------------------------------------------------------- 

BEGIN TRAN

-- 4 -Move the entire data from original table to temp table 

alter table MY_TRANS switch partition 1 to MY_TRANS_temp 

 

-------------------------------------------------------------- 

-- 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 check the table rows and table schema

select *from MY_TRANS 

sp_help MY_TRANS 

-- 7 drop the tbl_y 

--DROP TABLE MY_TRANS_Y  -- for failback operation

--- COMMIT TRAN -- once everything is good then commit the transaction.