Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

How to Reset Identity Column Value In SQL Server without Delete Records.

4.67/5 (2 votes)
21 Dec 2010CPOL 19.9K  
Some considerations:- Use a table variable instead of #tempTable. Temporary tables create an actual table in the temp database, that needs to be dropped afterwards. When something bad happens, the table will not be dropped.- Use a FAST_FORWARD cursor, when it is read-only. For large tables,...
Some considerations:
- Use a table variable instead of #tempTable. Temporary tables create an actual table in the temp database, that needs to be dropped afterwards. When something bad happens, the table will not be dropped.
- Use a FAST_FORWARD cursor, when it is read-only. For large tables, it performs a lot better.
- Try not to use a cursor. Cursors are the last option to consider.
- Try not to update table design. Resetting identity columns and foreign key constraints are easely forgotten after code like this. Have a look at the query SQL management studio performs when doing a table design change(it creates another table, and uses a transaction).

A better performing query (without my last consideration in mind) would be:

SQL
USE YourDataBase

DECLARE @TmpTable TABLE (id int, rowNumber int)

INSERT INTO @TmpTable
SELECT Id,ROW_NUMBER() OVER (ORDER BY Id ) AS RowNumber FROM MyTable

UPDATE MyTable SET ID = tmp.rowNumber
FROM MyTable t
INNER JOIN @TmpTable tmp ON tmp.id = t.ID


Or without variables:

SQL
WITH cte_Temp(Id, RowNumber)
AS
(
    SELECT Id,ROW_NUMBER() OVER (ORDER BY Id ) AS RowNumber FROM MyTable
)
UPDATE MyTable SET ID = tmp.rowNumber
FROM MyTable t
INNER JOIN cte_Temp tmp ON tmp.id = t.ID

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)