Tuesday, March 17, 2015

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.
Post a 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 the Mi...

Contributors