Optimized Methods for Assigning Unique Incremental Values to NULL Columns in SQL Server

Problem Context and Common Misconceptions

During database maintenance and data migration, there is often a need to populate unique identifier values for specific columns in existing table records. When the target column contains a large number of NULL values, developers may attempt to use subqueries based on aggregate functions to achieve automatic numbering, for example:

UPDATE prices SET interfaceID = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices) 
       WHERE interfaceID IS null

The fundamental issue with this approach is that the subquery (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices) is evaluated only once during the entire execution of the UPDATE statement. Regardless of how many rows match the WHERE condition, all updated rows receive the same interfaceID value, violating uniqueness constraints. This single-evaluation mechanism is an inherent characteristic of the UPDATE statement in SQL standards, not an implementation defect of specific database products.

Core Solution: Variable Incrementation Technique

To assign consecutive unique identifiers to multiple NULL-valued rows within a single query, it is necessary to introduce intermediate variables to track the current maximum allocated value. SQL Server supports modifying variable values simultaneously within UPDATE statements, providing key support for solving this problem:

declare @i int  = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices)

update prices
set interfaceID  = @i , @i = @i + 1
where interfaceID is null

The working principle of this solution can be divided into three logical stages:

1. Initial Value Calculation: Obtain the maximum existing interfaceID value in the table via SELECT ISNULL(MAX(interfaceID),0) + 1 and add 1 as the starting point. The ISNULL function ensures that when all interfaceID values in the table are NULL, the starting value defaults to 1.
2. Row-by-Row Update Loop: The WHERE condition of the UPDATE statement filters all records where interfaceID is NULL. For each matching record, interfaceID = @i in the SET clause assigns the current variable value to the column, immediately followed by @i = @i + 1 incrementing the variable by 1.
3. Atomicity Guarantee: The entire UPDATE operation executes within a single transaction, ensuring that intermediate states are not read by other sessions during multi-row updates.

Technical Details and Extended Discussion

The basic solution described above may require adjustments and optimizations based on specific application scenarios:

Data Consistency in Concurrent Environments: In high-concurrency scenarios, multiple sessions may execute similar update operations simultaneously. To avoid duplicate value conflicts, consider the following enhancement measures:

BEGIN TRANSACTION
-- Acquire exclusive lock to prevent other sessions from reading or modifying
SELECT @maxID = ISNULL(MAX(interfaceID),0) FROM prices WITH (UPDLOCK, HOLDLOCK)

DECLARE @i INT = @maxID + 1

UPDATE prices
SET interfaceID = @i, @i = @i + 1
WHERE interfaceID IS NULL

COMMIT TRANSACTION

By using explicit transactions combined with UPDLOCK and HOLDLOCK hints, it is possible to ensure that other sessions cannot insert new records or modify existing interfaceID values during the update process, thereby guaranteeing global uniqueness of generated values.

Performance Optimization Considerations: When the number of records requiring updates is extremely large (e.g., exceeding millions of rows), the variable incrementation method may cause rapid growth of transaction logs. In such cases, batch processing can be implemented:

DECLARE @batchSize INT = 10000
DECLARE @i INT = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices)
DECLARE @rowsAffected INT = 1

WHILE @rowsAffected > 0
BEGIN
    UPDATE TOP (@batchSize) prices
    SET interfaceID = @i, @i = @i + 1
    WHERE interfaceID IS NULL
    
    SET @rowsAffected = @@ROWCOUNT
END

This batch update strategy decomposes large transactions into multiple smaller transactions, reducing lock contention and log growth pressure while maintaining operational continuity.

Alternative Approaches Comparison

In addition to the variable incrementation method, developers may consider other technical approaches:

ROW_NUMBER() Window Function: In SQL Server 2008 and later versions, CTE (Common Table Expressions) combined with ROW_NUMBER() can be used:

WITH NumberedRows AS (
    SELECT interfaceID, 
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 
           ISNULL(MAX(interfaceID) OVER (), 0) AS NewID
    FROM prices
    WHERE interfaceID IS NULL
)
UPDATE NumberedRows
SET interfaceID = NewID

This method features syntax more aligned with declarative programming styles but may generate significant temporary storage overhead when processing extremely large datasets.

Identity Column Reset Technique: If business logic permits, the most concise solution is to directly modify the table structure, setting interfaceID as an IDENTITY column:

-- Execute after backing up existing data
ALTER TABLE prices
ADD NewInterfaceID INT IDENTITY(1,1)

UPDATE prices
SET interfaceID = NewInterfaceID
WHERE interfaceID IS NULL

-- Remove temporary column and rebuild constraints

This approach requires more complex schema changes but offers optimal performance and simplest maintenance logic.

Practical Recommendations and Conclusion

When selecting a specific implementation approach, the following factors should be comprehensively considered: database version compatibility, data volume size, concurrent access patterns, business continuity requirements, and operational complexity. For most OLTP scenarios, single-statement UPDATE based on variables provides the best balance—it requires no temporary tables or complex transaction control while guaranteeing atomicity and efficiency of operations.

Key implementation points include: always verifying logical correctness in test environments, particularly boundary conditions (such as all-table NULL values, existing maximum values approaching integer upper limits, etc.); assessing lock impact before execution in production environments; considering adding CHECK constraints to prevent future NULL value insertions. By mastering these core technologies, developers can efficiently address common numbering assignment problems in data initialization, migration, and repair tasks.