/********************************** 标识符 (IDENTITY) 值是一种特殊的值,它依赖于标识列,由SQL SERVER 自动维护,是自增的,而且一般是不会重复的. 但是SQL SERVER并不维护标识(IDENTITY) 值唯一(要保证标识值唯一,应该在该列上使用主键或者唯一键约束) ----------------------------------------------------------------------------------------------------------- 1、标识值不连续 一般来说，出现下面的情况时，可能会导致 标识值不连续 A，事务回滚： 如果在插入数据时回滚事务，则插入记录时最后一个标识会被记录下来，下次插入的标识值就会不连续。 回滚事务包括手工事务的回滚和SQL SERVER 自动开启事务的回滚。 ************************************/ USE TEST GO IF OBJECT_ID('TEMPDB..#A') IS NOT NULL DROP TABLE #A GO CREATE TABLE #A( ID INT IDENTITY(0,1) , B INT UNIQUE ) INSERT INTO #A(B) VALUES(1); INSERT INTO #A(B) VALUES(2); -- a.手工事务回滚 BEGIN TRAN INSERT INTO #A(B) VALUES(3); ROLLBACK TRAN INSERT INTO #A(B) VALUES(3); SELECT * FROM #A /* ID B 0 1 1 2 3 3 */ --b.因操作失败自动回滚事务 INSERT INTO #A(B) VALUES(3); INSERT INTO #A(B) VALUES(4); SELECT * FROM #A /* ID B 0 1 1 2 3 3 5 4 上面查询结果不存在标识 2 是因为手工回滚事务导致的, 不存在标识 3 是因为插入数据违反了唯一约束，自动回滚事务导致的 */ /* B，删除记录：无论被删除记录的标识值是最新添加的，还是删除以前添加的记录，标识值均不会被回收再利用。 因此，删除记录会导致标识值不连续 */ IF OBJECT_ID('TEMPDB..#A') IS NOT NULL DROP TABLE #A GO CREATE TABLE #A( ID INT IDENTITY(0,1), B INT ) INSERT INTO #A(B) SELECT 1 UNION ALL SELECT 2 --删除一条记录 DELETE FROM #A WHERE B = 2 INSERT INTO #A(B) VALUES(2) SELECT * FROM #A /* ID B 0 1 2 2 从结果可以看到,标识值2并没有因为删除记录而回收,所以新增加记录的标识值仍然是3 */ DROP TABLE #A /* C，使用DBCC CHECKIDENT 重置标识值: 可以通过 DBCC CHECKIDENT 中的RESEED 选项重新设置指定表的当前标识值,重置标识值也可能会导致标识值不连续 */ IF OBJECT_ID('TEMPDB..#A') IS NOT NULL DROP TABLE #A GO CREATE TABLE #A( ID INT IDENTITY(0,1), B INT ) INSERT INTO #A(B) VALUES(1) --重置当前标识值 DBCC CHECKIDENT (#A , RESEED ,1 ) INSERT #A(B) VALUES (1) SELECT * FROM #A DROP TABLE #A --DBCC CHECKIDENT 的用法:http://technet.microsoft.com/zh-cn/library/ms176057%28zh-tw,SQL.90%29.aspx DBCC CHECKIDENT (#A ) DBCC CHECKIDENT (#A , NORESEED ) /* 在第一次插入后,当前标识值为0 ,然后通过 DBCC CHECKIDENT 将当前标识设置为1,帮再次插入记录时,新插入记录的标识值为2 */ /************************************ 2，标识值重复:由于SQL SERVER 不维护标识值的唯一,因些在没有主键,唯一键等手段来维持标识值不重复的表中, 标识值可能会产生重复,导致标识重复的一般有以下两个情况: A：强制插入标识值 ************************************/ IF OBJECT_ID('TEMPDB..#A') IS NOT NULL DROP TABLE #A GO CREATE TABLE #A( ID INT IDENTITY(1,1), B INT ) INSERT INTO #A(B) VALUES (1) --插入重复的标识值 SET IDENTITY_INSERT #A ON INSERT INTO #A(ID ,B) VALUES (SCOPE_IDENTITY() ,2) SET IDENTITY_INSERT #A OFF SELECT * FROM #A DROP TABLE #A /* 上述两条记录的标识值是一样的,因为第2条记录在插入时,强制指定了与第1条记录一样的标识值 -------------------------------------------------------------------------------------- B, 使用DBCC CHECKIDENT 重置标识值: 如果重置标识值为一个比表中现有标识值更小的标识值(如果标识值增量为负数,则是比现有标识值更大), 那么新插入列的标识值会重复 */ IF OBJECT_ID('TEMPDB..#A') IS NOT NULL DROP TABLE #A GO CREATE TABLE #A( ID INT IDENTITY(1,1), B INT ) INSERT INTO #A(B) VALUES (1) -- 重置标识值 DBCC CHECKIDENT(#A , RESEED , 0) INSERT #A(B) VALUES(2) SELECT * FROM #A DROP TABLE #A /* 第1次插入记录之后,当前的标识值为1,然后通过 DBCC CHECKIDENT 将当前标识值设置为 0 ,故再次插入记录时,新插入记录的标识值为1 */
参考：《SQL Server 2005开发、管理与应用实例》