Update T-SQL with consecutive numbering starting from specific number and order by other column first

Question

I'm on SQL Server 2014 with this one.

What is the fastest way to update a column with certain values (where ID = 0), starting from a specific value (highest + 1 from the same column) AND ordered by another column (timestamp) first?

This is the data:

       ID  timestamp
    -------------------------------
 1    101  2020-01-01 11:02:14.235
 2    102  2020-01-01 12:05:04.123
 3    103  2020-01-01 13:20:50.457
 4    104  2020-01-01 14:20:53.447
 5    105  2020-01-01 15:25:25.125
 6      0  2020-01-01 16:25:14.447
 7      0  2020-01-02 20:26:01.147
 8      0  2020-01-01 17:18:39.987
 9      0  2020-01-01 19:14:14.014
10      0  2020-01-01 18:10:10.000

This is how it should turn out:

     ID  timestamp
 -------------------------------
 1  101  2020-01-01 11:02:14.235
 2  102  2020-01-01 12:05:04.123
 3  103  2020-01-01 13:20:50.457
 4  104  2020-01-01 14:20:53.447
 5  105  2020-01-01 15:25:25.125
 6  106  2020-01-01 16:25:14.447
 8  107  2020-01-01 17:18:39.987
10  108  2020-01-01 18:10:10.000
 9  109  2020-01-01 19:14:14.014
 7  110  2020-01-02 20:26:01.147

So ordering by timestamp first, then populate the numbers. Preferably not using a cursor or temp table for the sake of speed (its a lot of tables and a lot of databases).

Answer 1

An updatable CTE, using ROW_NUMBER and a windowed MAX to get the new ID values, would seem like the easiest solution here:

CREATE TABLE dbo.YourTable (ID int,
                            [timestamp] datetime2(3)); --This is a bad name for a column, as timestamp is a synonym for rowversion

INSERT INTO dbo.YourTable (ID, [timestamp])
VALUES (101,'2020-01-01T11:02:14.235'),
       (102,'2020-01-01T12:05:04.123'),
       (103,'2020-01-01T13:20:50.457'),
       (104,'2020-01-01T14:20:53.447'),
       (105,'2020-01-01T15:25:25.125'),
       (  0,'2020-01-01T16:25:14.447'),
       (  0,'2020-01-02T20:26:01.147'),
       (  0,'2020-01-01T17:18:39.987'),
       (  0,'2020-01-01T19:14:14.014'),
       (  0,'2020-01-01T18:10:10.000');
GO
WITH CTE AS(
    SELECT ID,
           ROW_NUMBER() OVER (PARTITION BY CASE ID WHEN 0 THEN 0 END ORDER BY [timestamp]) + MAX(ID) OVER () AS NewID
    FROM dbo.YourTable)
UPDATE CTE
SET ID = NewID
WHERE ID = 0;
GO

SELECT *
FROM dbo.YourTable;
GO

Note: As the number you had on the far left of your data doesn't appear to be a column the "order" of your data will be lost; but then it wasn't being retained in the first place as the only way to guarantee the order of data in an RDBMS is an ORDER BY that creates a unique "set" for each row.