How can I reset IDENTITY column of my table in SQL Server? [duplicate]

Question

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(ORDER BY profile_id) AS RN
    FROM 
        Profiles
)
UPDATE TBL
SET profile_id = RN

ERROR:

Cannot update identity column 'profile_id'.

Answer 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.