IDENTITY属性的使用详解

最新推荐文章于 2021-06-29 17:09:28 发布
最新推荐文章于 2021-06-29 17:09:28 发布
阅读量1.3w 收藏 7
点赞数
分类专栏： SQL 文章标签： sql-server identity

IDENTITY属性的使用

1.创建查看IDENTITY
  • 创建
    IF OBJECT_ID('test','U') IS NOT NULL
        DROP TABLE test
    GO

    CREATE TABLE test
    (
        id int IDENTITY(1, 1),  --如果直接写IDENTITY则默认为（1， 1）
        c1 char(1)
    )
    INSERT test VALUES('a');
    INSERT test VALUES('b');

    SELECT * FROM test
  • 查看（通过函数或者系统视图）
    SELECT COLUMNPROPERTY(OBJECT_ID('test'),'id','IsIdentity') AS is_identity

    SELECT object_name(object_id) as table_name, is_identity, *
    FROM sys.columns
    WHERE object_id = object_id('test')
--and is_identity=1
  • 重置IDENTITY列的初始值(通常在数据删除/归档后进行)
DELETE test
DBCC CHECKIDENT('test', RESEED, 1)
DBCC CHECKIDENT('test', NORESEED)

--TRUNCATE表后会自动重置IDENTITY列
TRUNCATE TABLE test
DBCC CHECKIDENT('test', NORESEED)
2.获取IDENTITY列值

  1. 说明

    1. 插入了数据，有时还需要获取刚才生成的序列值另作他用，返回给前端也好，或者插入其他将来需要关联的表。

  2. 示例

    曾经有个面试题：假设当前表 IDENTITY 列最大值为 N，在存储过程中，对这个表插入 1 行数据，获取到的 IDENTITY 列值有时小于或者大于 N + 1，可能是什么原因？

    获取 IDENTITY 列值有三种方式：

    1. IDENT_CURRENT( 'table_name' )返回为任何会话和任何作用域中的特定表最后生成的标识值。
    2. @@IDENTITY返回为当前会话的所有作用域中的任何表最后生成的标识值。
    3. SCOPE_IDENTITY()返回为当前会话和当前作用域中的任何表最后生成的标识值。

    IDENT_CURRENT( 'table_name' )针对特定表，是全局的。
    @@IDENTITY和SCOPE_IDENTITY()针对所有表，区别在于作用域，也就是上下文：

    1. 如果当前 INSERT 语句上有函数，触发器等(不同作用域的)对象返回的 IDENTITY 值，那么@@IDENTITY会取所有表上的最后1个，而不是当前表上的；
    2. SCOPE\_IDENTITY()会取当前作用域所有表上最后1 个 IDENTITY 值，被调用的函数，触发器已经超出了作用域或上下文。所以在使用 INSERT 后，接着使用SCOPE_IDENTITY()获取 IDENTITY 列值，就不会有问题了：
        INSERT test VALUES('z');
    SELECT SCOPE_IDENTITY() AS curr_value

    一个 GO 语句/批处理，也是一个上下文的分界点，但是 SQL 语句是顺序执行的，所以一个会话里，只要在 INSERT 之后用SCOPE_IDENTITY()来获取 IDENTITY 值是没问题的。

3.修改IDENTITY列值/属性
  1. 对已存在的列增加/删除IDENTITY属性
IF OBJECT_ID('t_id') IS NOT NULL
DROP TABLE t_id
GO

CREATE TABLE t_id(id int,c1 char(1))

INSERT INTO t_id
SELECT 1,'a' UNION ALL
SELECT 2,'b'

ALTER TABLE t_id alter column id int identity(1,2)
/*
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'identity'.
*/
注意：直接修改列属性会报错，IDENTITY属性只能伴随着列增加/删除。

  • 利用中间表

    1. 在SSMS界面上设计表(SSMS/Tables/Design)，可以直接增加/删除列上的 IDENTITY 属性，如果生成脚本看看的话(右击编辑框/工具栏/菜单栏)，可以发现SSMS是利用了中间表，并非在原表直接修改属性。

    2. 表上有约束，索引等对象时，脚本会更加繁杂些。示例如下图：
      中间表

    3. 如果出现如下错误：
      Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.
      错误

    4. 是因为SSMS里有个选项没设置，SQL Server认为有删除/重建表的脚本不安全，所以默认关闭了，需要手动开启一下，去掉那个勾：
      错误解决

    5. 对表上已存在列添加 IDENTITY 属性，生成的脚本如下代码1：

    6. 对表上已存在列删除 IDENTITY 属性，生成的脚本如下代码2：

代码1：

    BEGIN TRANSACTION
    SET QUOTED_IDENTIFIER ON
    SET ARITHABORT ON
    SET NUMERIC_ROUNDABORT OFF
    SET CONCAT_NULL_YIELDS_NULL ON
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    COMMIT
    BEGIN TRANSACTION
    GO
    CREATE TABLE dbo.Tmp_t_id
        (
        id int NOT NULL IDENTITY (1, 1),
        c1 char(1) NULL
        )  ON [PRIMARY]
    GO
    ALTER TABLE dbo.Tmp_t_id SET (LOCK_ESCALATION = TABLE)
    GO
    SET IDENTITY_INSERT dbo.Tmp_t_id ON
    GO
    IF EXISTS(SELECT * FROM dbo.t_id)
         EXEC('INSERT INTO dbo.Tmp_t_id (id, c1)
            SELECT id, c1 FROM dbo.t_id WITH (HOLDLOCK TABLOCKX)')
    GO
    SET IDENTITY_INSERT dbo.Tmp_t_id OFF
    GO
    DROP TABLE dbo.t_id
    GO
    EXECUTE sp_rename N'dbo.Tmp_t_id', N't_id', 'OBJECT'
    GO
    COMMIT

代码2：

    BEGIN TRANSACTION
    SET QUOTED_IDENTIFIER ON
    SET ARITHABORT ON
    SET NUMERIC_ROUNDABORT OFF
    SET CONCAT_NULL_YIELDS_NULL ON
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    COMMIT
    BEGIN TRANSACTION
    GO
    CREATE TABLE dbo.Tmp_t_id
        (
        id int NOT NULL,
        c1 char(1) NULL
        )  ON [PRIMARY]
    GO
    ALTER TABLE dbo.Tmp_t_id SET (LOCK_ESCALATION = TABLE)
    GO
    IF EXISTS(SELECT * FROM dbo.t_id)
         EXEC('INSERT INTO dbo.Tmp_t_id (id, c1)
            SELECT id, c1 FROM dbo.t_id WITH (HOLDLOCK TABLOCKX)')
    GO
    DROP TABLE dbo.t_id
    GO
    EXECUTE sp_rename N'dbo.Tmp_t_id', N't_id', 'OBJECT'
    GO
    COMMIT

  • 利用中间列

    1. 对表上已存在列删除IDENTITY属性,如代码3：
    2. 对表上已存在列添加IDENTITY属性，用中间列的方式不太可行，因为IDENTITY列不接受UPDATE，新增的IDENTITY列无法直接复制原id的值，还得借助中间表，但如果不需要原来id的值，那么可以如代码4：

代码3：

    IF OBJECT_ID('t_id') IS NOT NULL
        DROP TABLE t_id
    GO
    CREATE TABLE t_id(id int identity(1,1),c1 char(1))

    INSERT INTO t_id
    SELECT 'a' UNION ALL
    SELECT 'b'
    SELECT * FROM t_id
    SELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity')

    --在表上新增一个列，把IDENTITY列值复制过去
    ALTER TABLE t_id add id_new int
    GO
    UPDATE t_id SET id_new = id

    --删除原来的列，并重命名新增列
    ALTER TABLE t_id drop column id
    exec sp_rename 't_id.id_new','id'
    SELECT * FROM t_id
    SELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity')

代码4：

    IF OBJECT_ID('t_id') IS NOT NULL
        DROP TABLE t_id
    GO
    CREATE TABLE t_id(id int,c1 char(1))

    INSERT INTO t_id
    SELECT 1,'a' UNION ALL
    SELECT 3,'b'
    SELECT * FROM t_id
    SELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity')

    --在表上新增一个IDENTITY列，不复制原来的ID值
    ALTER TABLE t_id add id_new int identity(1,1) NOT NULL

    --删除原来的列，并重命名新增列
    ALTER TABLE t_id drop column id
    exec sp_rename 't_id.id_new','id'
    SELECT * FROM t_id
    SELECT COLUMNPROPERTY(OBJECT_ID('t_id'),'id','IsIdentity')

  1. 在IDENTITY列上做增删改操作(DML)

    1. 删除操作没有问题，直接 DELETE 即可:
      DELETE test WHERE id = 2

    2. 如果要显式 INSERT 某个值，需要开启IDENTITY_INSERT这个SESSION级的选项

      SET IDENTITY_INSERT test on;
INSERT test(id,c1) VALUES(3,'c');
SET IDENTITY_INSERT test off;
SELECT * FROM test

    3. 如果要 UPDATE IDENTITY 列值，无论是否开启IDENTITY_INSERT这个选项都无法更新

      SET IDENTITY_INSERT test on;
UPDATE test SET id = 10 WHERE id = 1
SET IDENTITY_INSERT test off;
/*
Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'id'.
*/

      注: 非要修改的话，就得借助中间表，在不含IDENTITY属性的中间表里做完UPDATE，然后再把数据导回来。中间表可参考上面的脚本。

  1. IDENTITY列属性复制

    1. 直接从单表 SELECT INTO table_name，原表其他约束，索引等等都不会被复制，但是 IDENTITY 属性会被复制。

      SELECT * INTO test2 FROM test
SELECT * FROM test2
SELECT columnproperty(OBJECT_ID('test'),'id','IsIdentity')
SELECT columnproperty(OBJECT_ID('test2'),'id','IsIdentity')

    2. 如果有 IDENTITY 属性的表和其他表 JOIN，那么 IDENTITY 属性不会被复制。

      SELECT a.* INTO test3
FROM test a INNER JOIN sys.objects b
ON a.id = b.object_id

SELECT * FROM test3
SELECT columnproperty(OBJECT_ID('test3'),'id','IsIdentity')

      1. 假如复制表时，不想要 IDENTITY 属性，正好可以利用一下这个特点，如下：

        SELECT a.* INTO test4
FROM test a INNER JOIN sys.objects b
ON 1=2

    3. 如果用 SELECT INTO table_name 导数据时，FROM子句有多表关联，且想要保留 IDENTITY 属性，这时可以用 INSERT，并考虑使用 TABLOCK 提示

      IF OBJECT_ID('test5','U') is not null
DROP TABLE test5
GO

CREATE TABLE test5
(
    id int IDENTITY(1, 1),  --如果直接写IDENTITY则默认为（1， 1）
    c1 char(1)
)
SELECT * FROM test5
GO

SET IDENTITY_INSERT test5 ON;
INSERT INTO test5 WITH(TABLOCK) (id,c1)
SELECT a.* FROM test a INNER JOIN test2 b ON a.id = b.id
SET IDENTITY_INSERT test5 off;

SELECT * FROM test5
SELECT columnproperty(OBJECT_ID('test5'),'id','IsIdentity')

      这里使用了WITH(TABLOCK)选项，在 SIMPLE 或者BULK_LOGGED恢复模式下，SELECT…INTO table_name 和 INSERT INTO table_name WITH(TABLOCK) 都能最小化日志。

  1. 借助SWITCH来处理IDENTITY属性(推荐)

    1. 同样也是利用中间表，上面的几个列子都使用了INSERT，这里使用SWITCH，不再有数据倒来倒去的开销，需要SQL Server 2008及以上版本，能比较有效地同时解决上面的3个问题：

      1. 不能直接对表上现有列增加/删除 IDENTITY 属性；
      2. 不能直接更新 IDENTITY 列；
      3. 复制表时，有选择的复制 IDENTITY 列属性(多表关联，对关联后的表做 SWITCH 以实现)；
    --原表
    CREATE TABLE Temp1
    (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        X VARCHAR(10)
    )

    --插入测试数据
    INSERT INTO Temp1
    OUTPUT INSERTED.*
    SELECT 'Foo' UNION ALL
    SELECT 'Bar' UNION ALL
    SELECT 'Baz'

    --创建中间表，用作交换分区
    CREATE TABLE Temp2
    (
        ID INT PRIMARY KEY,
        X VARCHAR(10)
    )

    --交换分区
    ALTER TABLE Temp1 SWITCH TO Temp2;
    SELECT COLUMNPROPERTY(OBJECT_ID('Temp1'),'id','IsIdentity')
    SELECT COLUMNPROPERTY(OBJECT_ID('Temp2'),'id','IsIdentity')

    --插入测试数据
    INSERT INTO Temp2
    OUTPUT INSERTED.*
    SELECT 10,'Foo' UNION ALL
    SELECT 20,'Bar' UNION ALL
    SELECT 5, 'Baz'

    --更改IDENTITY初始值
    UPDATE Temp2 SET ID = ID + 1;

    --交换分区
    ALTER TABLE Temp2 SWITCH TO Temp1;
    SELECT * FROM Temp2
    SELECT * FROM Temp1
  • 另外，从SQL Server 2012开始，如果开发时使用了SEQUENCE，这些IDENTITY列的限制就都不会存在了。
4.参考
Shirsen
关注
  • 0
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
C# 6.0的属性(Property)的语法与初始值详解
01-20
昨晚有学点新知识，是有关C# 6.0的。 在数据库创建有一张表： CREATE TABLE [dbo].[ToolLocation] ( [ToolLocation_nbr] SMALLINT IDENTITY(1,1) NOT NULL PRIMARY KEY, [LocationName] NVARCHAR(20) NOT NULL, [Description] NVARCHAR(50) NULL, [IsActive] BIT NOT NULL DEFAULT(1) ) GO Source Code 看看前后对比与写法： using System; using Syste
SQL SERVER 中identity
停留一小会
01-16 732
SQL SERVER 中identity用法： 在数据库中, 常用的一个流水编号通常会使用 identity 栏位来进行设置, 这种编号的好处是一定不会重覆, 而且一定是唯一的, 这对table中的唯一值特性很重要, 通常用来做客户编号, 订单编号等功能, 以下介绍关于此种栏位常用方式及相关技术. CREATE TABLE products (id int IDENTITY P
Android 学习矩阵
FlyPig_Vip的博客
01-11 339
矩阵乘法规则 矩阵加减法: 矩阵乘以一个常数 Matrix 矩阵类 找到源码具体说明一下方法: matrix.invert() 判断该矩阵是否可以倒置 matrix.isAffine()判断该矩阵是否是仿射的 matrix.isIdentity() 判断该矩阵是否是单位矩阵 matrix.mapPoints(float[] dst, int dstIndex...
SqlSugar.NET之InitTables，Insert，Update，PageEach
极客神殿
06-29 2367
InitTables Code First 生成表-SqlSugar4-文档园 // 属性介绍 // 我们会根据字段的属性生成数据库字段 // IsNullable表示表字段是否可空 // IsIgnore 为true表示不会生成字段到数据库 // IsIdentity表示为自增列 // IsPrimaryKey表示为主键 // Length 表示字段长度 // DecimalDigits 表示字段的精度 4.4 // ColumnDataType 强制设置数据库字段的类型（考虑到切换数据库有些类型其
identity 1 1 mysql_ORACLE中实现类似SQLSERVER中IDENTITY(1,1)序号自增的功能
weixin_34285757的博客
01-25 416
欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入 最近在做一个把SQL SERVER中的数据库的表同步到ORACLE 中，排除解决了了一系列如类型不匹配等产生的错误后，终于将数据同步过来了，可是又发现了一个新问题：SQL SERVER中源表的ID是通过IDENTITY(1欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入最近在做一个把SQ...
mysql identity属性_Mysql中Identity 详细介绍
weixin_31819459的博客
01-27 3115
假如表中包含一列为auto_increment，如果是myisam类型的引擎，那么在删除了最新一笔数据，无论是否重启mysql，下一次插入之后仍然会使用上次删除的最大id+1.mysql> create table test_myisam (id int not null auto_increment primary key, name char(5)) engine=myisam;quer...
MySQL：常见约束和标识列
weixin_44389456的博客
02-28 185
常见约束 /* 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确可靠性 分类：六大约束 not null：非空，用于保证该字段的值不能为空（姓名，学号等）； default：默认约束，用于保证该字段有默认值（性别）； primary：主键，保证该字段值有唯一性且非空（学号，员工编号）； unioue：唯一，用于保证该字段的值具有唯一性，可空（座位号）； check：检查，MySQL不...
NodeJS使用mssql连接SQLServer出现"Incorrect syntax near the keyword \'user\'."
ccf19881030的专栏
03-06 2305
最近使用NodeJS的mssql模块连接SQLServer数据库出现了"Incorrect syntax near the keyword ‘user’."的错误，Google了一下发现原来我在SQLServer中使用了user作为表明，但是SQLServer中user是保留的关键字，不能被用于做表名或者变量名。所以解决方案很简单，直接重命名表名user为t_user或者其他的名称就OK了。 在S...
ASP.NET Forms身份认证详解
01-02
2. 如果是一个已登录用户，访问HttpContext.User.Identity.Name可获取登录名（都是实例属性）。 接下来，本文将会围绕上面二个问题展开，请继续阅读。 ASP.NET身份认证过程 在ASP.NET中，整个身份认证的过程其实可...
hibernate 框架详解
06-07
使用连接表的单向关联（Unidirectional associations with join tables） 8.3.1. 一对多(one to many) 8.3.2. 多对一（many to one） 8.3.3. 一对一（one to one） 8.3.4. 多对多（many to many） 8.4. 双向...
详解ASP.NET Core和ASP.NET Framework共享身份验证
01-20
.NET Core 已经热了好一阵子，1.1版本发布后其可用性也越来越高，开源、组件化、跨平台、性能优秀、社区...我们先来看看.net core中对identity的实现，在Startup.cs的Configure中配置Cookie认证的相关属性 public vo
后端开发是一个涉及广泛技术和工具的领域.docx
04-30
后端开发是一个涉及广泛技术和工具的领域，这些资源对于构建健壮、可扩展和高效的Web应用程序至关重要。以下是对后端开发资源的简要介绍： 首先，掌握一门或多门编程语言是后端开发的基础。Java、Python和Node.js是其中最受欢迎的几种。Java以其跨平台性和丰富的库而著名，Python则因其简洁的语法和广泛的应用领域而备受欢迎。Node.js则通过其基于JavaScript的单线程异步I/O模型，为Web开发提供了高性能的解决方案。 其次，数据库技术是后端开发中不可或缺的一部分。关系型数据库（如MySQL、PostgreSQL）和非关系型数据库（如MongoDB、Redis）各有其特点和应用场景。关系型数据库适合存储结构化数据，而非关系型数据库则更适合处理大量非结构化数据。 此外，Web开发框架也是后端开发的重要资源。例如，Express是一个基于Node.js的Web应用开发框架，它提供了丰富的API和中间件支持，使得开发人员能够快速地构建Web应用程序。Django则是一个用Python编写的Web应用框架，它采用了MVC的软件设计模式，使得代码结构更加清晰和易于维护。
华为数字化转型实践28个精华问答glkm.pptx
04-30
华为数字化转型实践28个精华问答glkm.pptx
新员工入职培训全流程资料包gl.zip
04-30
新员工入职培训全流程资料包（100+个文件） 1入职流程指引 万科新职员入职通知书 万科新职员入职引导手册 新进员工跟进管理表 新员工入职报到工作单(文职) 新员工入职报到流程 新员工入职流程表 新员工入职手续办理流程(工厂 新员工入职手续清单 新员工入职须知 新员工入职训流程 新员工入职引导表(导师用) 2 入职工具表格 3 培训方案计划 4培训管理流程 5培训教材课件 6 培训效果检测 7 员工管理制度 8 劳动合同协议 9 新员工培训PPT模板(28套)
三菱PLC通讯程序实例
04-30
FX5U PLC作为主、从站的通讯方式程序实例，以及包含详细说明文件...
技术需求报告-集行波测距与故障录波功能于一体的电网综合故障分析系统.docx
最新发布
04-30
技术需求报告-集行波测距与故障录波功能于一体的电网综合故障分析系统.docx
最新二开版本源码博客论坛源码,UI很漂亮,可切换皮肤界面.rar
04-30
最新二开版本源码博客论坛源码,UI很漂亮,可切换皮肤界面.rar最新二开版本源码博客论坛源码,UI很漂亮,可切换皮肤界面.rar
sql2008 去除identity属性语句
03-22
可以使用以下语句去除表中的identity属性： ALTER TABLE table_name ALTER COLUMN column_name int 其中，table_name是要修改的表名，column_name是要去除identity属性的列名，int是该列的数据类型。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值