Restrictions to NEXT VALUE FOR

Sequence object was introduced with SQL Server 2012. However there are last minute changes from RC0 to RTM. NEXT VALUE FOR function is used get the next value for the SEQUENCE object.

The NEXT VALUE FOR function is now disallowed when used in statements with

  • DISTINCT
  • UNION / UNION ALL
  • EXCEPT
  • NTERSECT
  • TOP
  • OFFSET
  • when the ROWCOUNT option is set

The NEXT VALUE FOR function is now disallowed in conditional expressions:

  • CASE
  • CHOOSE
  • COALESCE
  • IIF
  • ISNULL
  • NULLIF

The full list of restrictions are documented in BOL.

Usage of NEXT VALUE FOR only allowed in the DEFAULT constraint for the target column and NEXT VALUE FOR cannot be used with the MERGE statement. If it is used with MERGE statement following error will be generated.

Msg 11742, Level 15, State 1, Procedure sampleProc Line 21 NEXT VALUE FOR function can only be used with MERGE if it is defined within a default constraint on the target table for insert actions. :

Comments

Popular posts from this blog

GROUP_CONCAT or LISTAGG

An Error Occurred during decryption when creating a linked server.

Cannot show the editor for this task.