SQL Server中新增序列技术实现主键自动增长
1. 序言
在数据库设计中，主键的设置经常使用自增长的方式。通常我们使用IDENTITY属性实现自增长，但是IDENTITY属性有局限性。比如说，如果要对某个已有的表新加入一个自增列，这是IDENTITY属性就没法完成了。这时候就需要使用另外的方式了——序列。
2. 序列的介绍与使用
序列是一组生成唯一数字的对象，它可以用来创建自增列。序列是数据库对象，可以在数据库中创建，并且可以用在各种数据类型的列中。
序列的创建语法为：
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ CACHE ]
[ ; ]
下面是各参数的介绍：
- schema_name. 如果要创建的序列不在dbo架构下，可以在作为架构名称的模式名称之前指定它。
- sequence_name. 序列的名称
- built_in_integer_type. 可以是tinyint、smallint、int、bigint中的一个。
- user-defined_integer_type. 用户定义的别名、基本数据类型或CLR类型。
- START WITH. 序列的起始值。如果没有明确指定此参数，则默认为1。
- INCREMENT BY. 定义序列中的值的增量。如果没有明确指定此参数，则默认为1。
- MINVALUE. 序列中允许的最小值。如果没有指定此参数，默认最小值为tinyint、smallint、int和bigint的最小值。
- NO MINVALUE. 意味着序列中没有最小值。
- MAXVALUE. 序列中允许的最大值。如果没有指定此参数，默认最大值为tinyint、smallint、int和bigint的最大值。
- NO MAXVALUE. 意味着序列中没有最大值。
- CYCLE. 序列循环(达到最大值后，重新从最小值重新开始)。
- NO CYCLE. 序列不会循环。
- CACHE. 优化访问序列时的性能。
下面我们来看一个创建序列的实例代码：
CREATE SEQUENCE dbo.TestSequence
START WITH 1
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
CACHE 1;
3. 序列的使用
创建好了序列，我们接下来就可以把序列值赋值给表的某个列了。使用序列的语法为:
NEXT VALUE FOR sequence_name
或
sequence_name.NEXT VALUE
下面是一个应用序列的例子：
我们创建一个测试表来测试下，如下代码：
CREATE TABLE dbo.TestTable
(
Id INT PRIMARY KEY,
Name VARCHAR(50)
);
主键ID需要自动增长，而创建表时ID列没有设置IDENTITY属性，同时也没有数据。这时候我们可以使用序列来赋值。
我们的目标是将表中第一行的ID设置成1，然后其它行的ID逐次递增1。代码如下：
DECLARE @SeqValue INT;
SET @SeqValue = NEXT VALUE FOR dbo.TestSequence;
INSERT INTO dbo.TestTable
(
Id,
Name
)
VALUES
(
@SeqValue,
'TestName1'
);
SET @SeqValue = NEXT VALUE FOR dbo.TestSequence;
INSERT INTO dbo.TestTable
(
Id,
Name
)
VALUES
(
@SeqValue,
'TestName2'
);
SET @SeqValue = NEXT VALUE FOR dbo.TestSequence;
INSERT INTO dbo.TestTable
(
Id,
Name
)
VALUES
(
@SeqValue,
'TestName3'
);
执行完上述代码后，我们可以查询TestTable表的数据，验证执行结果：
SELECT *
FROM dbo.TestTable;
结果如下：
```
Id Name
1 TestName1
2 TestName2
3 TestName3
```
我们可以看到，通过使用序列来赋值，我们实现了主键的自增长。
4. 序列的属性
我们可以通过查询以下SQL语句来获取序列的详细信息：
SELECT *
FROM sys.sequences
WHERE [name] = 'TestSequence'
下面是几个常用的属性：
- start_value：序列的起始值。
- increment_value：序列每次增加的值。
- minimum_value：序列下限值。
- maximum_value：序列上限值。
- is_cycling：序列是否循环。
- cache_size：序列缓存大小，指定将 PROJECT 的项作为多个序列值的块存储在内存中，以提供更快的访问。
5. 序列的删除与修改
删除一个序列：
DROP SEQUENCE [schema_name.] sequence_name [CASCADE];
修改一个序列：
ALTER SEQUENCE [schema_name.] sequence_name
[ RESTART [ WITH ] ]
[ INCREMENT BY ]
[ MINVALUE [ ] | NO MINVALUE ]
[ MAXVALUE [ ] | NO MAXVALUE ]
[ ; ]
我们修改上面的TestSequence序列，
把序列起始值改成2，每次增加值改成2，同时序列的上下限值由默认的null设定为50。
ALTER SEQUENCE dbo.TestSequence
RESTART WITH 2
INCREMENT BY 2
MINVALUE 2
MAXVALUE 50;
总结
序列是一种实现主键自动增长效果的方法，它对IDENTITY属性有所补充，不需要在表上设置IDENTITY属性就能实现主键自动增长。使用序列方法更加灵活，同时也非常适用于复杂的表结构。