Why can't "NEXT VALUE FOR" be used in a case statement

I was writing an INSTEAD OF INSERT trigger and hit an error on this code:

CASE 
   WHEN Inserted.KeyId IS NOT NULL THEN Inserted.KeyId
   ELSE NEXT VALUE FOR SomeSchema.NextKeyId
END

The error was:


NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

Reading up shows that this is a fairly hard limitation, which I guess I can accept, but I would like to know why this limitation in is place.

Seems odd to me that it can't just go call that method for the part of the set that matches that branch of the case statement.

Can someone explain why a CASE statement does not allow NEXT VALUE FOR?

To fully answer your question one needs to know intimate details of the SQL Server SQL engine implementation, and most (all?) people who have that knowledge likely have signed all sorts of NDAs and confidentiality agreements. What follows is my speculation.

Typically the scope of NEXT VALUE is the entire SQL statement where it is referenced. This probably means that the sequence value is generated and made available independently of other features of that SQL statement. This has a semantic conflict with the conditional use of NEXT VALUE in the situations described by the error message. In other words, the user doesn't know if the next sequence value has or has not been consumed, depending on some nondeterministic condition, but at the same time it has already been generated independently.

So my guess is that the restriction aims to prevent this semantic ambiguity. What you can do instead is (in pseudocode)

vNext = NEXT VALUE FOR mySeq;
...
CASE 
  WHEN Inserted.KeyId IS NOT NULL THEN Inserted.KeyId
  ELSE vNext
END

which doesn't have that ambiguity -- it is explicitly said that one sequence value is consumed every time, though it may or may not be actually used.

StackExchange Database Administrators Q#172774, answer score: 4