How Can I Reset Identity Column Of My Table In Sql Server?

How can I reset my IDENTITY column in my already populated table? I tried something like this, but it's not working WITH TBL AS ( SELECT *, ROW_NUMBER() OVER(

Solution 1:

Using DBCC command "CHECKIDENT" you can reset the identity value of the column in a table.

For example, Identity column has 100 as the last identity. If we want next row to have an identity as 201 then you can achieve this using following DBCC command -

DBCC CHECKIDENT (N'TableName', RESEED, 34);

If the identity column has to start with an identity of 1 with the next insert then the table should be reseeded with the identity to 0.

But do remember in doing so you might violate the data integrity, the uniqueness of the table records.

Solution 2:

As far as I know, you can't UPDATE an identity column. If you want to leave the identity (after changing its values) one way is to create another specular table (same fields) and transfer all data into it. Something like this (at the end you'll have a table with a new IDENTITY field).

Original table

CREATETABLE TESTID (id iNTIDENTITY(1,1), DESCR VARCHAR(100), PROFILEID INT)
INSERTINTO TESTID VALUES ('bbbb', 888);
INSERTINTO TESTID VALUES ('Ccccc', 777);

New table

CREATETABLE TESTID2 (id INTIDENTITY(1,1), DESCR VARCHAR(100), PROFILEID INT)

Data transfer

SET IDENTITY_INSERT TESTID2 ON
INSERT INTO TESTID2 (ID, DESCR, PROFILEID)
SELECT100+ROW_NUMBER() OVER (ORDERBY PROFILEID) AS RN, DESCR, PROFILEID FROM TESTID
 SET IDENTITY_INSERT TESTID2 OFF

Test

INSERTINTO TESTID2 VALUES ('xxxx',999);
 SELECT*FROM TESTID;


id          DESCR              PROFILEID
----------- ----------------- -----------101         Ccccc               777102         bbbb                888103         xxxx                999

If you want, you can change identity starting value using

DBCC checkident ('TESTID2', reseed, 4000)
INSERTINTO TESTID2 VALUES ('yyy',111)

id          DESCR                      PROFILEID
----------- -------------------------- -----------101         Ccccc                      777102         bbbb                       888103         xxxx                       9994001        yyy                        111

At the end, drop your original table and rename the new one.