Change identity column to non-identity column:

I seen one question in one of the forum how can we efficiently remove identity from the column. Below was my answer there.

--create one table with identity column

CREATE TABLE DemoIdentity

(

ID INT IDENTITY(1,1),

NAME VARCHAR(10)

)

--insert few records for testing

INSERT INTO DemoIdentity

SELECT 'Ritesh' UNION ALL

SELECT 'Rajan'

--checking the records

SELECT * FROM DemoIdentity

--creating one table with same structure but without identity

CREATE TABLE DemoIdentity2

(

ID INT NOT NULL,

Name VARCHAR(10)

)

--using swich concept of partition

ALTER TABLE DemoIdentity SWITCH TO DemoIdentity2

--droping original table

DROP TABLE DemoIdentity

--renaming duplicate table with original table's name

EXEC sp_rename 'DemoIdentity2','DemoIdentity'

--checking finally

SELECT * FROM DemoIdentity

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com