SQL: Adding IDENITY property to Existing Column

--Adding IDENITY property to Existing Column
--Steps to Add Identity
1.Add new Column with IDENTITY
2.Drop Constraint (if Any)
3.Drop the Old Column
4.Rename the New Column with Old Column Name


--Existing Table
CREATE TABLE Employee
(
Emp_Id INT PRIMARY KEY,
Emp_Name VARCHAR(100)
)

--1St Add a New Column with Identity
ALTER TABLE Employee
ADD EmpNew_ID INT IDENTITY (1,1)


--2Nd Drop The Primary Key Constraints(if Primary key is exist)
ALTER TABLE Employee
DROP CONSTRAINT PK__Employee__262359AB07020F21

--3Rd Drop the Old ID from Table
ALTER TABLE Employee
DROP COLUMN Emp_ID

--4Th Rename the New Column with OLD Column Name
SP_RENAME 'Employee.EmpNew_ID','Emp_Id','COLUMN'



--Now enjoy the Column & Table with new Flavour of IDENTITY.Really I had so much curisity to know when I got this from one
--of my friend as Co-worker.Firstly I though that it is so simple as we alter any column of any table but after 20-30 min.I
--realize that this is the proper way for accomplish the Task.But , be sure that that table donot have any data and always consider
--for Primary key ,if necessary first drop the Primary key the proceed.May be the cadinal position of the changed column.



--Show the Table with New Form
SELECT * FROM TEST_TABLE


















--Posted By: javadevelopersguide
--Link:-http://javadevelopersguide.blogspot.com