How to Reset Primary Key in Sql Server

Thank you for your question.

Remove foreign key reference (if any) from YourTabe and set Is Identity to No in column properties windows in Microsoft SQL Server Management Studio. Execute the bellow SQL using Management Studio.

USE YourDataBase

SELECT [YourTableId],ROW_NUMBER() OVER (ORDER BY YourTableId ) AS RowNumber
    into #tempTable from [YourTable]

DECLARE your_table_cursor CURSOR FOR
    SELECT [YourTableId], RowNumber
    FROM #tempTable

OPEN your_table_cursor

DECLARE @YourTableId int
DECLARE @RowNumber int

FETCH NEXT FROM your_table_cursor
INTO @YourTableId, @RowNumber

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE  [YourTable]
        SET [YourTableId] = @RowNumber
        WHERE [YourTableId] = @YourTableId

    FETCH NEXT FROM your_table_cursor
    INTO @YourTableId, @RowNumber
END


CLOSE your_table_cursor
DEALLOCATE your_table_cursor

DROP TABLE #tempTable

Set Is Identity to Yes and set Identity increment to 1 and Identity Seed to 1 in column properties windows in Management Studio. Then set reference.

Thanks,
Mamun