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.

[codesyntax lang=”tsql”]

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);

[/codesyntax]

 

Solutions:

As I mentioned I would like to find out the rows gap by the following solutions:-

Solution A:

[codesyntax lang=”tsql”]

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 
[/codesyntax]

 

solution a

[codesyntax lang="tsql"]
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.

[/codesyntax]

 

As you see, Solution A has 39 logical reads which for analyzing huge table is not suitable.

Solution B

[codesyntax lang="tsql"]

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

[/codesyntax]

 

solution b

[codesyntax lang="tsql"]

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.

[/codesyntax]

 

As statistics output shows, Solution B also has quiet high no. of logical reads, but lower than Solution A.

Solution C

[codesyntax lang="tsql"]

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;

[/codesyntax]

solution c

[codesyntax lang="tsql"]

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.

[/codesyntax]

 

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

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan