原文地址 ： http://www.simple-talk.com/sql/t-sql-programming/identity-columns/
Identity 列，有名字，基数，步长。当向表中插入一条记录，Identity列会以当前基数加上步长的结果插入到表中。
注意：Identity列，既不会保证不重复，也不会保证其数据的连续性。所以，如果你的系统需要其唯一，一定要在该Identity列加上唯一索引。
创建和使用Identity列
我们先创建一个表作为示例。
- CREATE TABLE #a(i INT IDENTITY(1,1), j INT)
下面是我们经常看到的创建Identity列的方法，同时也是默认的方法。它的执行结果和上面是一样的。
- CREATE TABLE #a(i INT IDENTITY, j INT)
插入数据……
- INSERT #a SELECT 1SELECT * FROM #a
- i j
- ----------- -----------
- 1 1
上面的语句将会执行，但却有点让人迷惑。而且版本数据库版本不同结果也可能不同。所以，在插入数据时，比较好的做法是，总是给出列名（如果有默认值可以不给出）。
- INSERT #a (j) SELECT 1SELECT * FROM #a
- i j
- ----------- -----------
- 1 1
- 2 1
提示：Identity列的用途之一就是区别两行一样的数据。另外也可以用Identity列来表示数据插入顺序。
我们可以通过DBCC checkident 命令来得到当前的Identity基数。
- DBCC checkident (#a)
- Checking identity information: current identity value '2', current column val
看，当前基数是2。记住，下一个Identity值是当前基数加上步长。
插入失败
- BEGIN TRANINSERT #a (j) SELECT 1ROLLBACK TRANSELECT * FROM #a
- i j
- ----------- -----------
- 1 1
- 2 1
- DBCC checkident (#a)
- Checking identity information: current identity value '3', current column value
表中数据并没有变化。不过通过命令 checkident 我们得知，当前基数已经发生变化。我们知道已经产生了下一个基数。
注意：新的Identity值是当前基数加上步长，并不是当前表中最大值加一或者最大值加上步长。
- INSERT #a (j) SELECT 1SELECT * FROM #a
- i j
- ----------- -----------
- 1 1
- 2 1
- 4 1
The situation above commonly happens when there is an index violation on j.
基数和步长的初始值
提示：基数和步长的初始值可以是任意整数。
- CREATE TABLE #b (i INT IDENTITY(-7,5), j INT)INSERT #b (j) SELECT 1INSERT #b (j) SELECT 1INSERT #b (j) SELECT 1SELECT * FROM #b
- i j
- ----------- -----------
- -7 1
- -2 1
- 3 1
- CREATE TABLE #c (i INT IDENTITY(1,-3), j INT)INSERT #c (j) SELECT 1INSERT #c (j) SELECT 1INSERT #c (j) SELECT 1SELECT * FROM #c
- i j
- ----------- -----------
- 1 1
- -2 1
- -5 1
在Identity 列中插入给定值
我们可以向Identity列中插入一个给定的值，而不是系统生成的值。要实现这种功能，执命令 SET IDENTITY_INSERT #a ON ，这样就可以插入想要的值了。
- SET IDENTITY_INSERT #a ONINSERT #a (i,j) SELECT 2,2SET IDENTITY_INSERT #a OFFSELECT * FROM #a
- i j
- ----------- -----------
- 1 1
- 2 1
- 4 1
- 2 2
还记得Identity列并不能保证数据的唯一性么？现在这个表中有两条记录Identity列的值都是2。
那么基数（seed）到底发生了什么变化？
- DBCC checkident (#a)
- Checking identity information: current identity value '4', current column value '4'.
注意，前面的插入操作并没有影响到基数。现在我们插入一个更大的数。
- SET IDENTITY_INSERT #a ONINSERT #a (i,j) SELECT 10,3SET IDENTITY_INSERT #a OFFSELECT * FROM #a
- i j
- ----------- -----------
- 1 1
- 2 1
- 4 1
- 2 2
- 10 3
- DBCC checkident (#a)
- Checking identity information: current identity value '10', current column value '10'.
现在基数更新了。这是因为我们插入了一个比当前基数更大的值。也就是说，基数之增加不减少。
但是如果步长是负数会是什么结果呢？
- SELECT * FROM #c
- i j
- ----------- -----------
- 1 1
- -2 1
- -5 1
- DBCC checkident (#c)
- Checking identity information: current identity value '-5', current column value '-5'.
- note: step is -3
- SET IDENTITY_INSERT #c ON
- INSERT #c (i,j) SELECT -8,2SET IDENTITY_INSERT #c OFF
- SELECT * FROM #c
- i j
- ----------- -----------
- 1 1
- -2 1
- -5 1
- -8 2
- DBCC checkident (#c)
- Checking identity information: current identity value '-8', current column value '-8'.
- SET IDENTITY_INSERT #c ONINSERT #c (i,j) SELECT 10,2SET IDENTITY_INSERT #c OFFSELECT * FROM #c
- i j
- ----------- -----------
- 1 1
- -2 1
- -5 1
- -8 2
- 10 2
- DBCC checkident (#c)
通过上面的实验得出，基数的增长是基于步长的正负的。
更改基数的当前值
我们已经知道插入数据可以更改基数的当前值，但是这种改变是基于步长（的正负）的。一个更好的办法是使用命令 DBCC checkident 。通过关键字 reseed 和具体值来改变基数的当前值。
- CREATE TABLE #d (i INT IDENTITY (5,2), j INT)INSERT #d (j) SELECT 1INSERT #d (j) SELECT 1SELECT * FROM #d
- i j
- ----------- -----------
- 5 1
- 7 1
- DBCC checkident(#d)
- Checking identity information: current identity value '7', current column value '7'.
- DBCC checkident(#d, reseed, 2)
- Checking identity information: current identity value '7', current column value '2'.
注意，这是第一次基数的当前值与系统分配的值不同！
- INSERT #d (j) SELECT 2SELECT * FROM #d
- i j
- ----------- -----------
- 5 1
- 7 1
- 4 2
我们也可以使用 truncate table命令来重置基数。
注意：delete 命令不行！
- TRUNCATE TABLE #d
- DBCC checkident(#d)
- Checking identity information: current identity value 'NULL', current column value 'NULL'.
- INSERT #d (j) SELECT 1SELECT * FROM #d
- i j
- ----------- -----------
- 5 1
- DBCC checkident(#d)
- Checking identity information: current identity value '5', current column value '5'.
得到Identity的值
一般我们都需要得到插入数据的Identity值。有几种方法可以实现：
- scope_identity
- ident_current
- @@identity
| scope_identity() |
returns the last identity inserted in the current scope and session. This is usualy the only one of these functions that is useful. It is not affected by other connections or tables nor by triggers. 得到当前scope或session的最后插入的Identity值。This is usualy the only one of these functions that is useful. |
| @@identity |
will return the last identity value inserted in any scope. This means that if a trigger inserts into a table with an identity then that is the value returned. This means that adding replication or auditing triggers to a database can alter the value of @@identity. In earlier versions of sql server this was the only means of returning the identity value and care had to be taken. 会得到在任一scope中插入的最后一个Identity值。这就意味着，如果一个触发器向一个有Identity列的表插入数据，这个Identity值会被返回。 |
| ident_current('table') | 此操作会返回任一链接向那个表插入数据后的最新的Identity值。记住，给出表名。 |
正如前面所说，scope_identity() 可能是这些函数中你唯一需要使用的函数。
使用 scope_identity()
scope_identity() 返回最后一个插入数据的Identity值。
- CREATE TABLE #t1 (i INT IDENTITY(5,1), j INT)INSERT #t1 (j) SELECT 1SELECT SCOPE_IDENTITY(), MAX(i) FROM #t1
- --------------------------------------- -----------
- 5 5
即使是事物回滚了，scop_identity依旧可以取得Identity值。
- BEGIN TRANINSERT #t1 (j) SELECT 1ROLLBACK TRANSELECT SCOPE_IDENTITY(), MAX(i) FROM #t1
- --------------------------------------- -----------
- 6 5
由于违反索引约束，致使插入失败，即使已经为Identity分配了新值，但是Identity值并没有更新。
- CREATE UNIQUE INDEX ix ON #t1 (j)INSERT #t1 (j) SELECT 1SELECT SCOPE_IDENTITY(), MAX(i) FROM #t1
- --------------------------------------- -----------
- 6 5
（译者注：此时插入失败，但用scope_identity取得的值还是6，并没有因为分配了新值而更新。但再插入数据的时候，会计算前面分配的所有Identity值）
- INSERT #t1 (j) SELECT 2SELECT SCOPE_IDENTITY(), MAX(i) FROM #t1
- --------------------------------------- -----------
- 8 8
向表中添加Identity列
我们可以使用修改表的语句来向表中添加一个Identity列。Identity的值也会根据基数和步长来设置。
下面的例子中 SCOPE_IDENTITY() 不会返回分配的值。
- CREATE TABLE #t2 (j INT)INSERT #t2 (j) SELECT 1INSERT #t2 (j) SELECT 1INSERT #t2 (j) SELECT 1INSERT #t2 (j) SELECT 1SELECT * FROM #t2
- j
- -----------
- 1
- 1
- 1
- 1
- ALTER TABLE #t2 ADD i INT IDENTITY (5,2)SELECT * FROM #t2
- j i
- ----------- -----------
- 1 5
- 1 7
- 1 9
- 1 11
这有助于处理表中含有重复行。
注意：表中已有的列不能转换成Identity列。在此例中你必须删除已有的列，再重新添加一列。
在此例中，已经存在的数据不能保留。如果想保留已经存在的数据，可以在创建一个表，然后在插入前使用identity_insert 命令。同事这样操作会更新表中所有的行，如果表很大的话，将耗时很长，日志文件也会增大。
Select Into
我们还可以通过select into 命令 和 Identity 函数来向一个表中添加Identity列。
- SELECT *, IDENTITY(INT,1,1) AS id INTO #tbl FROM sysobjects
如果我们是通过复制表结构来创建表的话，这招就非常有用。
Identity的数据类型
Identity列必须含有整数值，但是数据类型可以是任何数值类型。This can be useful when values greater than be contained in a bigint are required.
获得Identity列和属性
可以通过下面语句来检查一个表中是否含有Identity列，如果表中含有Identity列则返回 1
- SELECT OBJECTPROPERTY(OBJECT_ID('<tablename>'),'TableHasIdentity')
类似的方法：
- SELECT COLUMNPROPERTY(OBJECT_ID('<tablename>'),'<columnname>','IsIdentity')
这个方法可以判定某个表中某列是否是Identity列。
获得Identity列信息更好用的方法是使用使用系统视图 。它将返回数据库中所有使用了Identity列的信息。
- SELECT TableName = OBJECT_NAME(OBJECT_ID) , ColumnName = name ,
- OriginalSeed = seed_value ,
- Step = increment_value ,
- LastValue = last_value ,
- IsNotForReplication = is_not_for_replication
- FROM sys.identity_columns
在Identity列中使用字符
一个常见的需求是在Identity列中使用组合值，在字符后面跟着系统生成的一个连续的数值。
- a1
- a2
- a3
- b1
- b2
这不可能实现，甚至根本不可取。请注意这个列实际上包含了俩种值，那么它究竟应该是什么呢？
也许系统会试图按顺序生成字符那部分。这个需求的定义以及标识了一个错误——这个顺序的值分出来生产字符，那么它就应该分出来单独成一列。
现在，在访问一个表的时候我们可以使用Identity列来生产数字，也可以很容易的生成顺序的字符。
如果需要在表中保持连续的值，那么也许出于效率的原因，可以使用触发器来维护这列的数据。
批量插入数据
如果表中含有Identity列，在向该表中插入大批量数据时，如果Identity值没有在文本文件中提到，往往会失败。（If the table has an identity column then a bulk insert will often fail if the identity values are not held in the text file） 解决这个问题最简单的方式是为这个表创建一个试图，视图中要包含Identity列。然后向这个视图中批量插入数据。另一个方法是创建一个带有格式的文本文件，然后通过这个文件向表中批量插入数据。但是我会尽可能不用这个方法，因为这将使用额外的文件，而且维护起来也更加困难。
是否要使用Identity列
这个问题经常引起类似宗教般的热情讨论。如果你搜一下将找到大量关于这个话题的帖子。增加有人对我说，库中每一个表都应该有一个Identity列，而且在使用join联合查询的时候只是用它。另一些人说，在关系中Identity列就不应该存在，就不应该是用它。我不认同他们任何一方的观点，我会在需要的地方使用Identity列。如果在一个表中不使用Identity列将有可能出现重复列，那么就使用Identity列。同样情况，Identity列也可以对分别处理数据有益。字典表需要一个ID列，在脚本不能够提供一个有效值的时候，为什么不使用Identity列呢？分配ID是很有用的，比如给每个客户生产一个ID，但是要小心不同的系统生成了同一个ID。
要点总结
Identity列基本属性：名字，基数和步长。
Identity列不能保证数据唯一和数据连续。
我们可以通过使用 dbcc checkident 命令来得到和改变Identity列的当前基数值。
Identity列的下一个值是当前基数 加 步长
使用 identity_insert on 命令可以显式的插入数据到Identity列。
如果想Identity列显式的插入的数值大于当前基数值，则基数值变为这个值。注意这个值是有正负方向的。
truncate table 命令（注意不是delete）可以重置Identitylieder基数值。
Scope_identity() 可以得到最后插入的Identity列的值
我们可以向表中添加一个Identity列，但是我们不能把表中某一列修改为Identity列。
我们可以使用select into 和identity函数向表中添加一个Identity列。
我们可以在系统试图 sys.identity_columns 中得到Identity 列.
Bulk insert cannot be guaranteed to allocate the identity values in the order of rows in a text file.
有些时候通过视图向含有Identity列的表插入数据更容易.
