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

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:

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:

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