T-SQL 标识值

/**********************************
        标识符 (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开发、管理与应用实例》