Update with function for value only seems to evaluate the function once

Question

I'm trying to change all the NULLs in an INT column to an incrementing value using a statement like this:

UPDATE [TableName] SET [ColumnName] = dbo.FunctionName() WHERE [ColumnName] IS NULL

where the function looks like this:

CREATE FUNCTION FunctionName() RETURNS INT AS
BEGIN
    RETURN (select MAX(ColumnName) + 1 from TableName)
END

but the result of this is that all the values that were NULL are now set to the same value (i.e. 1 greater than the max before the statement was called).

Is this the expected behaviour? If so, how would I go about getting the result I'm after?

I'd like to avoid using an auto-increment column as all I really want is uniqueness, the incrementing is just a way of getting there.

Answer 1

Yes, this is by design, it is called Halloween protection. To achieve what you want (that is a one time) use an updatable CTE:

with cte as (
SELECT Column,
ROW_NUMBER() OVER () as rn
FROM Table
WHERE Column IS NULL)
UPDATE t
SET t.Column = m.max + t.rn
FROM cte t
JOIN (
SELECT MAX(Column) as max 
FROM Table) m;