Adding a sequence number to existing rows

Question

I have a table with an OrderSequence column. The schema looks roughly like this:

id 
Name 
OrderSequence

I have an array of ids ["1", "2", "3", "4"] from an application, and I want to set the OrderSequence for sorting purposes. I don't want to loop through the rows from the application, executing 'n' SQL queries one at a time. How can I do this using a stored procedure?

Sample data

id Name OrderSequence
1  A    0
2  B    0
3  C    0

Desired result

id Name OrderSequence
1  A    1
2  B    2
3  C    3

Answer 1

You can accomplish this using ROW_NUMBER() syntax, as mentioned by @Kin :

USE Tempdb;
CREATE TABLE dbo.testRowNum
(
    ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Name VARCHAR(255)
    , SequenceNum INT NULL
);
INSERT INTO testRowNum (Name) VALUES ('A'), ('B'), ('C');

SELECT *
FROM dbo.testRowNum;

UPDATE dbo.testRowNum
SET SequenceNum = x.RowNum
FROM dbo.testRowNum 
    INNER JOIN (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM dbo.testRowNum) x ON testRowNum.ID = x.ID
WHERE SequenceNum IS NULL;

SELECT *
FROM dbo.testRowNum;

Output:

If you want only update certain rows, you can use a Table Variable (or a #temp table if you are updating a LOT of rows), as such:

DECLARE @IDsToUpdate TABLE
(
    ID INT
);

INSERT INTO @IDsToUpdate VALUES (1),(3);

UPDATE dbo.testRowNum
SET SequenceNum = x.RowNum
FROM dbo.testRowNum 
    INNER JOIN (
        SELECT t.ID
            , ROW_NUMBER() OVER (ORDER BY t.ID) AS RowNum 
        FROM dbo.testRowNum t
            INNER JOIN @IDsToUpdate i ON t.ID = i.ID
        ) x ON testRowNum.ID = x.ID
WHERE SequenceNum IS NULL;

The example above results in the following output:

---

An improvement (based on a suggestion by Daniel Hutmacher) avoids the join and results in a more efficient query plan (no join or Halloween protection):

WITH Numbered AS
(
    SELECT 
        SequenceNum,
        RowNum =
            ROW_NUMBER() OVER (
                ORDER BY ID)
    FROM dbo.testRowNum
)
UPDATE Numbered
SET SequenceNum = RowNum;

Or, when using the @IDsToUpdate table:

WITH Numbered AS
(
    SELECT 
        SequenceNum,
        RowNum =
            ROW_NUMBER() OVER (
                ORDER BY ID)
    FROM dbo.testRowNum
    WHERE EXISTS
    (
        SELECT * 
        FROM @IDsToUpdate AS I
        WHERE I.ID = dbo.testRowNum.ID
    )
)
UPDATE Numbered
SET SequenceNum = RowNum;

Answer 2

Define your stored procedure to take a table valued parameter (TVP). The TVP will have two columns, one for ID and one for OrderSequence. Populate this TVP in your application, setting just the IDs you wish to amend. The SP can perform an UPDATE by joining to the TVP. This is more efficient than a cursor or multiple SQL calls.