It happens sometimes that we are in need to find out the row number’s gap in a table to analyze the deleted/update/missing records range after an incident. There are different ways to find out the rows gap, I will explain 3 different ways in this post.
Problem:
There is a table with few rows which are not in sequence. The following statement creates the table and the rows.
USE tempdb;
CREATE TABLE SeqVal (VALUE INT not null CONSTRAINT PK_Val PRIMARY KEY);
go
INSERT INTO SeqVal VALUES(2);
INSERT INTO SeqVal VALUES(3);
INSERT INTO SeqVal VALUES(10);
INSERT INTO SeqVal VALUES(11);
INSERT INTO SeqVal VALUES(13);
INSERT INTO SeqVal VALUES(14);
INSERT INTO SeqVal VALUES(19);
INSERT INTO SeqVal VALUES(22);
INSERT INTO SeqVal VALUES(24);
INSERT INTO SeqVal VALUES(25);
INSERT INTO SeqVal VALUES(29);
Solutions:
As I mentioned I would like to find out the rows gap by the following solutions:-
Solution A:
SELECT A.VALUE + 1 AS Start_Val ,
(SELECT MIN(B.VALUE) FROM SeqVal B
WHERE B.VALUE > A.VALUE) -1 AS End_Value
FROM SeqVal A
WHERE not exists ( SELECT * FROM SeqVal C WHERE C.VALUE = A.VALUE +1 )
and (A.VALUE
TABLE 'SeqVal'. Scan COUNT 9, logical READS 39, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
As you see, Solution A has 39 logical reads which for analyzing huge table is not suitable.
Solution B
SELECT cur AS Start_V, nxt AS End_V , nxt - cur AS Gap
FROM (SELECT A.VALUE AS cur,
(SELECT MIN(B.VALUE) FROM SeqVal B
WHERE B.VALUE > A.VALUE ) AS nxt
FROM SeqVal A) AS D
WHERE nxt - cur > 1
TABLE 'SeqVal'. Scan COUNT 12, logical READS 24, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
As statistics output shows, Solution B also has quiet high no. of logical reads, but lower than Solution A.
Solution C
DECLARE cur CURSOR fast_forward FOR SELECT VALUE FROM seqval ORDER BY VALUE;
DECLARE @val INT , @preVal INT;
OPEN cur;
FETCH NEXT FROM cur INTO @preVal;
IF @@FETCH_STATUS = 0 FETCH NEXT FROM cur INTO @val;
PRINT 'Start End Gap';
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF(@val - @preVal) > 1
PRINT CAST(@preval AS NVARCHAR(2)) + ' ' +CAST(@val AS NVARCHAR(2)) + ' ' + CAST((@val - @preVal) AS NVARCHAR(3));
SET @preVal = @val;
FETCH NEXT FROM cur INTO @val;
END
CLOSE cur;
DEALLOCATE cur;
TABLE 'SeqVal'. Scan COUNT 1, logical READS 2, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
TABLE 'SeqVal'. Scan COUNT 1, logical READS 1, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
START END Gap
TABLE 'SeqVal'. Scan COUNT 1, logical READS 1, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
3 10 7
TABLE 'SeqVal'. Scan COUNT 1, logical READS 1, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
TABLE 'SeqVal'. Scan COUNT 1, logical READS 1, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
11 13 2
TABLE 'SeqVal'. Scan COUNT 1, logical READS 1, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
TABLE 'SeqVal'. Scan COUNT 1, logical READS 1, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
14 19 5
TABLE 'SeqVal'. Scan COUNT 1, logical READS 1, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
19 22 3
TABLE 'SeqVal'. Scan COUNT 1, logical READS 1, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
22 24 2
TABLE 'SeqVal'. Scan COUNT 1, logical READS 1, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
TABLE 'SeqVal'. Scan COUNT 1, logical READS 1, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
25 29 4
TABLE 'SeqVal'. Scan COUNT 1, logical READS 1, physical READS 0, read-ahead READS 0, lob logical READS 0, lob physical READS 0, lob read-ahead READS 0.
Solution C looks faster than the other solutions, but it is not recommended to use against huge table due to use of cursor and potential blocking and deadlocks.
Conclusion
Solution B can be used for medium to large tables, due to the produced execution plan as you can observe from the following figure:-
