SQL Server中表列的标识功能

1. 什么是表列的标识功能？

在SQL Server中，每个表的列都有一个称为标识列（Identity Column）的功能。标识列是指当向表中插入新行时，系统自动为该列分配递增的唯一值。

可以通过设置标识列的种子值（Seed）、增量值（Increment）、数据类型和其他属性来定义标识列。

-- 创建一个包含标识列的表
CREATE TABLE myTable (
   id INT PRIMARY KEY IDENTITY(1,1),
   name VARCHAR(50)
);
-- 向表中插入数据，系统将自动分配唯一值给id列
INSERT INTO myTable (name) VALUES ('James');
INSERT INTO myTable (name) VALUES ('John');

2. 标识列的优点

2.1 自动编号

标识列允许在插入新数据时，自动为其分配一个唯一的编号，避免手动输入编号可能产生的重复错误。

2.2 索引优化

标识列通常被用作主键，可以方便地为其创建一个主键索引，提高检索效率。

2.3 简化代码

使用标识列可以使插入数据的代码变得更加简洁，无需手动指定编号。

3. 如何创建标识列

3.1 使用IDENITY属性

IDENTITY属性用于定义标识列的种子值和增量值。

CREATE TABLE myTable (
   id INT PRIMARY KEY IDENTITY(1,1), -- 第一个1表示种子值，第二个1表示增量值
   name VARCHAR(50)
);

3.2 使用SEQUENCE对象

SEQUENCE是SQL Server 2012引入的一种新对象，用于生成递增的序列号。与标识列类似，也可以用来为表列分配唯一值。

-- 定义一个序列
CREATE SEQUENCE mySeq
   START WITH 1 --起始值
   INCREMENT BY 1 --增量值
   NO CYCLE; -- 不循环
-- 创建一个表，并指定id列使用mySeq作为默认值
CREATE TABLE myTable (
   id INT PRIMARY KEY DEFAULT (NEXT VALUE FOR mySeq),
   name VARCHAR(50)
);
-- 向表中插入数据，系统将自动分配唯一值给id列
INSERT INTO myTable (name) VALUES ('James');

4. 修改标识列

可以使用ALTER TABLE语句修改标识列的属性。

4.1 修改标识列的种子值和增量值

ALTER TABLE myTable
   ALTER COLUMN id INT IDENTITY(100,1); -- 将种子值改为100，增量值不变

4.2 禁用标识列

可以使用IDENTITY_INSERT选项禁用标识列，并允许手动插入值。

-- 尝试手动插入值
SET IDENTITY_INSERT myTable ON; -- 开启IDENTITY_INSERT选项
INSERT INTO myTable (id, name) VALUES (100, 'Tom');
SET IDENTITY_INSERT myTable OFF; -- 关闭IDENTITY_INSERT选项

5. 小结

标识列是SQL Server中的一项重要功能，可以自动为表列分配唯一值，提高数据插入的效率和准确性。

在设计数据库时，应该合理使用标识列，并根据具体需求选择恰当的属性和类型。