确定 IDENTITY 列中的空洞

目的

本文档基本上描述了一种确定包含 IDENTITY() 列的表中空洞或缺失值的有效方法。 例如，假设您有一个如下定义的表

CREATE TABLE Main
(
    id int IDENTITIY(1,1),
    someOtherValue varchar(20) NULL
)

INSERT INTO Main VALUES (NULL)
INSERT INTO Main VALUES (NULL)
INSERT INTO Main VALUES (NULL)
INSERT INTO Main VALUES (NULL)
INSERT INTO Main VALUES (NULL)

这基本上会产生以下表结构

id	someOtherValue
1	NULL
2	NULL
3	NULL
4	NULL
5	NULL

现在假设您删除了第 2 行和第 3 行。 您将剩下 ID 1、4 和 5。 此代码基本上会告诉您 2 和 3 缺失了。

引言

最初，我在 Google 上做了一些快速研究，遇到了像这个页面描述的方法：http://www.sql-server-helper.com/tips/determine-missing-identity-values.aspx。 基本上，他们想要创建一个临时透视表，其中包含所有可能的身份值，从 1 到 MAX()，并对透视表和目标表执行 LEFT JOIN。 主表中的任何 NULL 值都等同于缺失的身份。

对于涉及超过 10,000 行的表来说，这很好。

代码

SET NOCOUNT ON 

IF OBJECT_ID('tempdb..#Main') IS NOT NULL
    DROP TABLE #Main -- Oops, something went wrong

CREATE TABLE #Main
(
    id int IDENTITY(1,1), 
    someOtherValue varchar(10) NULL
)

INSERT INTO #Main 
SELECT NULL 
UNION ALL SELECT NULL 
UNION ALL SELECT NULL 
UNION ALL SELECT NULL 
UNION ALL SELECT NULL 
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL

SELECT * FROM #Main

DELETE FROM #Main WHERE id IN (1, 2, 4, 5, 7, 9, 10)

SELECT * FROM #Main

DECLARE @temp TABLE (firstMiss int NOT NULL, nextValue int NOT NULL)
DECLARE @maxId INT 
DECLARE @lastId INT
DECLARE @increment INT
DECLARE @lowerBound INT

SELECT @maxId = MAX(id) FROM #Main
SELECT @lastId = IDENT_CURRENT('tempdb..#Main')
SELECT @increment = IDENT_INCR('tempdb..#Main')
SELECT @lowerBound = IDENT_SEED('tempdb..#Main')

-- Get the lower-bound misses
INSERT INTO @temp
SELECT
    @lowerBound, -- lower-bound
    (SELECT TOP 1 id FROM #Main WHERE id > @lowerBound ORDER BY id ASC)

-- Get the middle misses
INSERT INTO @temp
SELECT 
    id + @increment,
    COALESCE(
        (
            SELECT TOP 1 
                id 
            FROM 
                #Main 
            WHERE 
                id > (A.id + @increment) 
            ORDER BY 
                id ASC
        ), 
        @maxId)
FROM 
    #Main A
WHERE 
    id + @increment NOT IN (SELECT id FROM #Main) 
    AND (id + @increment) <= @maxId 

-- Get the upper-bound misses
INSERT INTO @temp
SELECT
    (SELECT TOP 1 id + @increment FROM #Main WHERE id < @lastId ORDER BY id DESC),
    @lastId + @increment -- The next value will be this

SELECT * FROM @temp

DECLARE @missingId TABLE (id int)
DECLARE @maxFirstMiss INT
DECLARE @firstMiss INT
DECLARE @nextValue INT

SELECT @firstMiss = MIN(firstMiss) FROM @temp
SELECT @maxFirstMiss = MAX(firstMiss) FROM @temp

WHILE @firstMiss <= @maxFirstMiss
BEGIN
    SELECT @nextValue = nextValue FROM @temp WHERE firstMiss = @firstMiss

    WHILE @firstMiss < @nextValue
    BEGIN
        INSERT INTO @missingId VALUES (@firstMiss)
        SET @firstMiss = @firstMiss + @increment
    END
        
    SELECT @firstMiss = MIN(firstMiss) FROM @temp WHERE firstMiss > @firstMiss
END

SELECT * FROM @missingId

DROP TABLE #Main

假设

首先，我只使用 IDENTITY(1,1) 场景进行了测试。 我真的不处理非 1 的增量或种子值。 它被编码为适用于您的表允许的任何内容，但逻辑可能无法实现。

其次，您的性能可能会因您拥有的间隙数量和表的大小而异。 我的测试是在大约 300,000 行的表上进行的，它的速度比透视表方法快得多。 因此，请注意。