Change identity column to non-identity column:

Thursday, March 26, 2009 |

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

0 comments: