Friday, April 13, 2012

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. :

No comments:

Post a Comment