SQL SERVER 系统变量设置

SQL Server 会话级别设置选项

[TOC]

## 1. IDENTITY_INSERT

### 1.1. 定义

允许将显式值插入到表的标识列中，通俗的讲就是允许将指定的值插入到自增长的列中，在 SQL Server 中，IDENTITY_INSERT 不是系统变量，而是一个会话级别的设置选项，用于控制是否允许向标识列（IDENTITY 列）手动插入显式值。

**它的特点与使用方式：**

+ 以 SET 命令进行配置，格式为 SET IDENTITY_INSERT [表名] ON/OFF
+ 作用范围仅限于当前会话和指定表
+ 用于在需要时向标识列插入自定义值（默认情况下，标识列由系统自动生成值，不允许手动插入）

### 1.2. 语法
<mark>SET IDENTITY_INSERT [库名].[架构（一般是dbo）].[表名] ON | OFF</mark>

当 IDENTITY_INSERT 设置为 ON 时，关闭表中自增长字段的限制，关闭后就可以往有设置自增长的字段插入指定的值数据，当 IDENTITY_INSERT 设置为 OFF 时则重新启动自增长字段的限制

### 1.3. 示例

创建一张测试表test，表里有两个字段，【id：自增的主键】，【title：标题】，往表中插入3条记录

**数据源：**

|id|titel|
|--|--|
|1|三国演义|
|2|红楼梦|
|3|西游记|

**示例代码：**

```SQL
DELETE FROM dbo.test WHERE id = 3
SET IDENTITY_INSERT dbo.test ON
-- INSERT INTO dbo.test VALUES (5, '水浒传') -- 报错语句
INSERT INTO dbo.test (id, title) VALUES (5, '水浒传')
SET IDENTITY_INSERT dbo.test OFF
INSERT INTO test VALUES ('罗密欧与朱丽叶')
```

> Tips：关闭限制添加完数据后记得要重新开启限制

**执行结果：**

|id|titel|
|--|--|
|1|三国演义|
|2|红楼梦|
|5|水浒传|
|6|罗密欧与朱丽叶|

> Tips：新插入的标识（自增长）值如果大于表中已存在标识（自增长）的最大值，那么之后在往表中插入新记录时，自增长值将以新插入的最大值开始增长，例如示例中，新插入的值是5，那么下一条新记录则从6开始，直接跳过了4这个值

### 1.4. 常见错误

#### 1.4.1. 简写错误

+ **错误提示：**<mark>仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表test中的标识列指定显式值</mark>

+ **产生原因：**简写插入语句，没有指明标识（自增长）字段，往标识（自增长）字段插入值得语句 INSERT INTO 不可省略自增长字段

+ **示例：**上面示例中的 `INSERT INTO dbo.test VALUES (5, '水浒传')`

### 1.5. 补充说明

+ 一般情况下自增长的字段都是设置在主键上，因此一般情况下也可以说是给主键插入指定的值，当然在插入时要先确定主键中是否已经存在了你要设置的值

+ 使用 SET IDENTITY_INSERT 需要拥有表的修改权限

参考资料：[【微软官方文档】](https://learn.microsoft.com/zh-cn/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver16&f1url=%3FappId%3DDev15IDEF1%26l%3DZH-CN%26k%3Dk(IDENTITY_INSERT_TSQL)