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