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
  solution a
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

 

solution b

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;

solution c

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:-

solution b 1

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

guest
0 Comments
Inline Feedbacks
View all comments

About The Author

Search Articles

Categories

Follow Fard Solutions