Rows Gap Analysis

The estimated reading time for this post is 3 minutes

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 < (SELECT MAX(VALUE) FROM SeqVal))

 

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

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

2 Comments on "Rows Gap Analysis"

Notify of
avatar
1000
Sort by:   newest | oldest | most voted
Pawan Khowal
Guest

This solution scales well.. easy to understand as well…

SELECT * FROM
(
SELECT Value+1 v1 ,LEAD(Value) OVER(ORDER BY Value)-1 v2
FROM SeqVal
)t
WHERE v2>=v1

–Pawan Khowal

wpDiscuz