SqlServer 2012 序列号

从 SQLserver 2012 开始，微软数据库增加了新的编程对象——序列号。序列号的创建语法基本和oracle一样。

更多参考：序列号

创建序列号语法：CREATE SEQUENCE (Transact-SQL)

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

创建序列号：

CREATE SEQUENCE [dbo].[Sequence_Test] 
 AS [bigint]		--整数类型
 START WITH 1		--起始值
 INCREMENT BY 1		--增量值
 MINVALUE 1			--最小值
 MAXVALUE 9999999	--最大值
 CYCLE				--达到最值循环 [ CYCLE | NO CYCLE ]
 CACHE  5			--每次取出5个值缓存使用 [ CACHE [<常量>] | NO CACHE ]
GO

查看序列号信息：

SELECT * FROM sys.sequences

使用方法：

--	获取下一个值
SELECT NEXT VALUE FOR [dbo].[Sequence_Test];

--	重置为1
ALTER SEQUENCE [dbo].[Sequence_Test] RESTART WITH 1 ;

--	加编号
SELECT NEXT VALUE FOR [Sequence_Test] AS id, Name FROM sys.objects ;

--	【给表插入编号】

ALTER SEQUENCE [dbo].[Sequence_Test] RESTART WITH 1 ;

--	DROP TABLE #TEMP
CREATE TABLE #TEMP(ID INT,DTIME DATETIME)

INSERT INTO #TEMP VALUES (NEXT VALUE FOR [dbo].[Sequence_Test], GETDATE()) ;

INSERT INTO #TEMP SELECT NEXT VALUE FOR [dbo].[Sequence_Test], GETDATE();

SELECT * FROM #TEMP

--	【作为默认值使用】

--	先删除刚才的临时表
DROP TABLE #TEMP 

--	临时表不可这样使用(错误)
CREATE TABLE #TEMP(ID INT PRIMARY KEY CLUSTERED DEFAULT(NEXT VALUE FOR [dbo].[Sequence_Test]),DTIME DATETIME)

--	临时表不可用，换成实体表
CREATE TABLE TEMP(ID INT PRIMARY KEY CLUSTERED DEFAULT(NEXT VALUE FOR [dbo].[Sequence_Test]),DTIME DATETIME)

--	ALTER TABLE [dbo].[TEMP] ADD  DEFAULT (NEXT VALUE FOR [dbo].[Sequence_Test]) FOR [ID]

ALTER SEQUENCE [dbo].[Sequence_Test] RESTART WITH 1 ;

INSERT INTO TEMP(DTIME) SELECT GETDATE()
GO 10

SELECT * FROM TEMP

--	若保留其中 10 个号码，使用存储过程 sys.sp_sequence_get_range 跳过 10 个。
DECLARE  
  @FirstSeqNum sql_variant
, @LastSeqNum sql_variant
, @CycleCount int
, @SeqIncr sql_variant
, @SeqMinVal sql_variant
, @SeqMaxVal sql_variant ;

EXEC sys.sp_sequence_get_range
  @sequence_name = N'dbo.Sequence_Test'
, @range_size = 10
, @range_first_value = @FirstSeqNum OUTPUT 
, @range_last_value = @LastSeqNum OUTPUT 
, @range_cycle_count = @CycleCount OUTPUT
, @sequence_increment = @SeqIncr OUTPUT
, @sequence_min_value = @SeqMinVal OUTPUT
, @sequence_max_value = @SeqMaxVal OUTPUT ;

SELECT
  @FirstSeqNum AS FirstVal
, @LastSeqNum AS LastVal
, @CycleCount AS CycleCount
, @SeqIncr AS SeqIncrement
, @SeqMinVal AS MinSeq
, @SeqMaxVal AS MaxSeq ;

--	执行完成后，当前值增加了10，下次获取编号时，将接着继续。（未使用的的10个编号对表可显示插入数据）


SELECT current_value FROM sys.sequences --当前编号

INSERT INTO TEMP(DTIME) SELECT GETDATE() --此时插入id为21

--	显示插入数据
INSERT INTO TEMP(ID,DTIME) SELECT 13,GETDATE()--此处将未使用的13显示插入

SELECT * FROM TEMP

删除序列号：

--	删除序列,报错
DROP SEQUENCE [dbo].[Sequence_Test]

--	因为表中默认值定义了序列,先去掉
ALTER TABLE [dbo].[TEMP] DROP CONSTRAINT [DF__TEMP__ID__60C757A0]

--	删除序列
DROP SEQUENCE [dbo].[Sequence_Test]

对象权限管理：

--【只许某用户查询编号】

--	授予用户 [AA] 只要查询序列的权限
GRANT CONTROL ON [dbo].[Sequence_Test] TO [AA]
GRANT CONTROL ON OBJECT::dbo.Sequence_Test TO [AA]

--	用户 [AA] 登录后只可执行以下语句
SELECT NEXT VALUE FOR [dbo].[Sequence_Test];

参考：

序列号

CREATE SEQUENCE (Transact-SQL)