Pages

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