INSERT INTO dbo.T1(keycol, datacol) VALUES(4, 'b');
INSERT INTO dbo.T1(keycol, datacol) VALUES(6, 'c');
INSERT INTO dbo.T1(keycol, datacol) VALUES(7, 'd');
--looking for the minimum missing integer for dbo.T1 keycol column
SELECT
CASE
WHEN NOT EXISTS( SELECT * FROM dbo.T1 WITH(NOLOCK) WHERE keycol=1) THEN 1
ELSE (SELECT min(1+A.keycol)
FROM dbo.T1 AS A WITH(NOLOCK)
WHERE NOT EXISTS
( SELECT *
FROM dbo.T1 AS B WITH(NOLOCK)
WHERE A.keycol+1=B.keycol))
END;
SELECT MIN(A.keycol + 1)
FROM dbo.T1 AS A
LEFT OUTER JOIN dbo.T1 AS B
ON B.keycol = A.keycol + 1
WHERE B.keycol IS NULL;
DECLARE @min INT, @max INT
SET @min = 1
SELECT @max =max(keycol)+1 FROM dbo.T1 (NOLOCK)
print @min
print @max
while( @min < @max )
BEGIN;
IF NOT EXISTS(SELECT * FROM dbo.T1 (NOLOCK) WHERE keycol=@min)
BEGIN;
print @min;
break;
END;
ELSE
SET @min = @min+1
END;
