Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

15.1. Procedural Solutions

The usual replacement for renumbering is to move the data from the current table to a temporary working table with an auto-increment on it. This will close up gaps, and if you do it with a cursor, you can pick the sort order. In SQL Server dialect, it usually looked like this:

CREATE TABLE #temptable
(row_num INTEGER IDENTITY (1, 1) PRIMARY KEY NOT NULL,
   cola INTEGER NOT NULL,
   colb INTEGER NOT NULL,
..);

-- insert the transactions
INSERT INTO #temptable (cola, colb, ..)
SELECT cola, colb, ..
  FROM Mytable -- same structure as #temptable
 ORDER BY cola;

The # prefix creates a local temporary table that disappears at the end of the session. IDENTITY is the dialect syntax for their auto-increment. And, yes, this eats up ...