SQL Server自增列（Identity列）的所有操作

数据库开发
目录

一、定义

  • 在CREATE TABLE 中：
CREATE TABLE dbo.Identity_test
(
	ID INT IDENTITY(1,1),  --(seed = 1,increment = 1) 从1开始,每次递增1
	Content NVARCHAR(200)
)
  • 在SELECT INTO 中：
SELECT
	IDENTITY(INT,100,1) ID,
	ColName
INTO #temp 
FROM TableName

二、自增列属性函数

一个表最多只能有一个自增列。

下面的三个函数，可以很方便地查看表中identity列的情况

SELECT
    IDENT_SEED('dbo.Identity_test') [IDENT_SEED]        -- 表中自增列的种子值
   ,IDENT_INCR('dbo.Identity_test') [IDENT_INCR]        -- 表中自增列的的增量
   ,IDENT_CURRENT('dbo.Identity_test') [IDENT_CURRENT]  -- 表中自增列的当前值

表中的自增列一旦创建就不能更改种子值和增量值了，只能更改当前值。要修改只有先删除再重建了

使用上面三个函数的一例：

INSERT INTO Identity_test(Content) -- 不写出ID列，它自动增加
SELECT 'Str1' UNION ALL
SELECT 'Str2' UNION ALL
SELECT 'Str3' UNION ALL
SELECT 'Str4' UNION ALL
SELECT 'Str5'

SELECT
    IDENT_SEED('dbo.Identity_test') [IDENT_SEED]	-- 1
   ,IDENT_INCR('dbo.Identity_test') [IDENT_INCR]	-- 1
   ,IDENT_CURRENT('dbo.Identity_test') [IDENT_CURRENT]	-- 5

DELETE 不会让ID断流，下次的ID值是ID列的增量+上次的ID值。

Truncate 会让ID列重新开始。

三、全局变量@@Identity

@@Identity可以获取最近产生的一个ID值，一般是Insert 之后产生的。不区分具体表。

在编程中可以用这个变量，而没必要用select Max(ID) From TableName 这样的语句，如果对其不放心，可以用ident_current()函数。

四、显式插入ID列

这个需要先开启 SET IDENTITY_INSERT TableName ON，而且不能省略Insert后面括号里的列名列表。

例如：

SET IDENTITY_INSERT dbo.Identity_test ON

INSERT INTO Identity_test(ID,Content) -- 显式写出列表，至少ID列表不能少
SELECT 11,'Repeat'

SET IDENTITY_INSERT dbo.Identity_test OFF

注意：只要ID列不是主键，没有设唯一索引，它的值是可以重复的。

五、重置当前列表值

在必要的时候，可以用DBCC CHECKIDENT命令来重置表中自增列的当前ID值。

如下示例将表的重置为10：

DBCC CHECKIDENT('dbo.Identity_test',RESEED,10) --下次从11开始

六、将Identity(1,1)改为Identity(10000,1)

这个没有直接的办法，只能绕道而行：

ALTER TABLE dbo.Identity_test DROP COLUMN ID
GO
ALTER TABLE dbo.Identity_test ADD ID INT IDENTITY(10000,1)
Go

执行后，表中所有记录的ID会自动按新的种子和递增赋值。

查询数据库中所有含自增列的：表名，表中自增列列表，种子值，增量，当前ID值

七、查询数据库的所有自增列（Identity列）的情况

SELECT
	 b.name TableName
	,a.name ColumnName
	,a.seed_value
	,a.increment_value
	,a.last_value
FROM  sys.identity_columns a
INNER JOIN sys.tables b ON a.object_id = b.object_id