sql 2012 sequence 速记

CodeSELECT  id
INTO    #t1
FROM    sys_id
WHERE   id < 3
SELECT next value for
        sequencebase
FROM    #t1
DROP TABLE #t1
go 

WITH    a AS ( SELECT   id
               FROM     sys_id
               WHERE    id < 3
             )
    SELECT next value for
            sequencebase id
    INTO    #t2
    FROM    a 
SELECT  *
FROM    #t2
DROP TABLE #t2 

go 

WITH    a AS ( SELECT   id
               FROM     sys_id
               WHERE    id < 3
             )
    SELECT next value for
            sequencebase id
    FROM    ( SELECT    id
              FROM      a
            ) b 
  go 

WITH    a AS ( SELECT   id
               FROM     sys_id
               WHERE    id < 3
             )
    SELECT next value for
            sequencebase id
    FROM    a 

GO



WITH    a AS ( SELECT   1 id
               UNION ALL
               SELECT   2
               UNION ALL
               SELECT   3
             )
    SELECT next value for
            sequencebase ,
            id
    FROM    a 
go 



--UNION 有不确定性，以下语句报错

WITH    a AS ( SELECT   1 id
               UNION
               SELECT   2
               UNION
               SELECT   3
             )
    SELECT next value for
            sequencebase ,
            id
    FROM    a

消息 11721，级别 15，状态 1，第 13 行
不能在使用 DISTINCT、UNION、UNION ALL、EXCEPT 或 INTERSECT 运算符的语句中直接使用 NEXT VALUE FOR 函数。

转载于:https://www.cnblogs.com/QinQouShui/p/3740707.html