【SQL Server】sequence
似乎是从SQL Server 2012开始有sequence的吧。具体未仔细研究过。
本测试环境为SQL Server 2014
在Oracle环境中,sequence是经常用到的一个对象,常用于逻辑主键、流水号等。博主在一个项目中遇到了从Oracle迁移到SQL Server的动作,基于Oracle开发时用到了sequence,为了便于继续开发,整体逻辑并没有变化,恰巧SQL Server 2014也有sequence,序列的迁移就变得简单的多。
SQL Server中sequence的语法:
1 2 3 4 5 6 7 8 9 |
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 } ] [ ; ] |
做一个简单的测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
## 创建sequence 1> create sequence dbo.seq1 as int start with 1 increment by 1 2> go (0 rows affected) ## sequence字典信息 1> select name,object_id,type,start_value,increment from sys.sequences where name='seq1' 2> go name object_id type start_value increment ------ ----------- ------ ------------- ----------- seq1 654625375 SO 1 1 (1 rows affected) ## 使用sequence 1> select next value for seq1 2> go --- 1 (1 rows affected) 1> ## 删除sequence 2> drop sequence dbo.seq1 3> go (0 rows affected) 1> |