How to find gaps in sequential key

Is it possible to write an SQL query to find gaps in a sequential key field?
Key Field
7
8
10
11
14
I would like the query to return the gaps
9
12
13
Or better yet, the range of the gaps
9,9
12,13
Any suggestions?Yes, use a numbers table.
http://www.aspfaq.com/2516
"D Babin" <DBabin@.discussions.microsoft.com> wrote in message
news:485C3250-0E8C-4217-9984-92D86B7C19DA@.microsoft.com...
> Is it possible to write an SQL query to find gaps in a sequential key
> field?
> Key Field
> 7
> 8
> 10
> 11
> 14
> I would like the query to return the gaps
> 9
> 12
> 13
> Or better yet, the range of the gaps
> 9,9
> 12,13
> Any suggestions?|||Here's one method:
SELECT T1.keycol+1, MIN(T2.keycol)-1
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.keycol < T2.keycol
GROUP BY T1.keycol
HAVING T1.keycol+1 < MIN(T2.keycol)
David Portas
SQL Server MVP
--|||Do:
SELECT t1.col + 1 AS "start",
MIN( t2.col ) - 1 AS "end"
FROM tbl t1
INNER JOIN tbl t2
ON t1.col < t2.col
GROUP BY t1.col
HAVING MIN( t2.col ) - t1.col > 1 ;
Anith|||Equivalently in SQL-92 syntax
select t1.field + 1 as "begin", t2.field - 1 as "end"
from testa t1
inner join testa t2 on t1.field < t2.field
and t2.field - t1.field > 1
and
not exists (select a.field from testa a
where a.field > t1.field and a.field < t2.field)
"D Babin" wrote:

> Is it possible to write an SQL query to find gaps in a sequential key fiel
d?
> Key Field
> 7
> 8
> 10
> 11
> 14
> I would like the query to return the gaps
> 9
> 12
> 13
> Or better yet, the range of the gaps
> 9,9
> 12,13
> Any suggestions?|||Hi D Babin,
create table YourTable(keycol int)
insert into YourTable values(7)
--insert into YourTable values(8 )
--insert into YourTable values(9 )
insert into YourTable values(11 )
insert into YourTable values(14 )
-- The following query give wrong results when 8 and 9 values are not
there
SELECT T1.keycol+1, MIN(T2.keycol)-1
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.keycol < T2.keycol
GROUP BY T1.keycol
HAVING T1.keycol+1 < MIN(T2.keycol)
-- If you are looking for a single column Then I go with Aaron
Bertrand's Solution
-- Here sequence is an auxiallry table having numbers from 1-99999
select seq from sequence S1,(select min(keycol) M1,max (keycol) M2
from YourTable) S2
where S1.seq between S2.M1 and S2.M2
and S1.SEQ NOT IN (SELECT keycol from YourTable)
drop table yourtable
With warm regards
Jatinder Singh
D Babin wrote:
> Is it possible to write an SQL query to find gaps in a sequential key fiel
d?
> Key Field
> 7
> 8
> 10
> 11
> 14
> I would like the query to return the gaps
> 9
> 12
> 13
> Or better yet, the range of the gaps
> 9,9
> 12,13
> Any suggestions?|||Jatinder Singh, I read your previous post and i have some questions for you
about your post

> -- The following query give wrong results when 8 and 9 values are not
> there
> SELECT T1.keycol+1, MIN(T2.keycol)-1
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.keycol < T2.keycol
> GROUP BY T1.keycol
> HAVING T1.keycol+1 < MIN(T2.keycol)
>
I tested this query by David Portas and it works fine even when the 8 and 9
values are not there.

> -- Here sequence is an auxiallry table having numbers from 1-99999
> select seq from sequence S1,(select min(keycol) M1,max (keycol) M2
> from YourTable) S2
> where S1.seq between S2.M1 and S2.M2
> and S1.SEQ NOT IN (SELECT keycol from YourTable)
Your query result set does not show that the beginning and end of the range
gaps.
Also, why are you creating an auxiliary sequence table from 1 to 99999? It
just uses more memory in SQL Server cache that could be used by some more
important table. Thank you.
"jsfromynr" wrote:

> Hi D Babin,
> create table YourTable(keycol int)
> insert into YourTable values(7)
> --insert into YourTable values(8 )
> --insert into YourTable values(9 )
> insert into YourTable values(11 )
> insert into YourTable values(14 )
> -- The following query give wrong results when 8 and 9 values are not
> there
> SELECT T1.keycol+1, MIN(T2.keycol)-1
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.keycol < T2.keycol
> GROUP BY T1.keycol
> HAVING T1.keycol+1 < MIN(T2.keycol)
> -- If you are looking for a single column Then I go with Aaron
> Bertrand's Solution
> -- Here sequence is an auxiallry table having numbers from 1-99999
> select seq from sequence S1,(select min(keycol) M1,max (keycol) M2
> from YourTable) S2
> where S1.seq between S2.M1 and S2.M2
> and S1.SEQ NOT IN (SELECT keycol from YourTable)
> drop table yourtable
> With warm regards
> Jatinder Singh|||> Also, why are you creating an auxiliary sequence table from 1 to 99999? It
> just uses more memory in SQL Server cache that could be used by some more
> important table.
A numbers table is probably more useful, and less of a burden
performance-wise, than you seem to think.
http://www.aspfaq.com/2516|||Aaron Bertrand, Thank you for your reply. I am trying to identify what the
benefits of the numbers table technique are using Jatinder Singh's select
statement
select seq from sequence S1,(select min(keycol) M1,max (keycol) M2
from YourTable) S2
where S1.seq between S2.M1 and S2.M2
and S1.SEQ NOT IN (SELECT keycol from YourTable)
if we use
CREATE TABLE SEQUENCE
(
SEQ INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
the Sql server execution plan show that a Clustered Index S is used for
"where S1.seq between S2.M1 and S2.M2".
if we use
CREATE TABLE SEQUENCE
(
SEQ INT IDENTITY(1,1)
)
the SQL Server Execution Plan shows that a table scan is used for
"where S1.seq between S2.M1 and S2.M2".
So, it appears that the primary clustered index on the number tables improve
s
the SQL Server execution plan. But how can I see the real benefits of the
numbered
table approach? Maybe, SQL Profiler can reveal them. Again, I don't have the
actual data set that you experimented with on your blog but I am curious how
many rows were in it. Could you please identifity the specific benefits of
the numbered table
approach in this type application(i.e. finding gaps)? Thank you.
"Aaron Bertrand [SQL Server MVP]" wrote:

> A numbers table is probably more useful, and less of a burden
> performance-wise, than you seem to think.
> http://www.aspfaq.com/2516
>
>|||Jatinder Singh, I modified your query (using Aaron Bertrand's suggestion) to
select the beginning and end of the range gaps (in sequential order):
select number from numberstest S1,
(select t1.keycol M1, min (t2.keycol) M2
from testing t1, testing t2
where t1.keycol + 1 < t2.keycol
group by t1.keycol
) S2 -- S2 is an derived table which avoids having to use temporary table
where S1.number between S2.M1 and S2.M2
and NOT EXISTS (SELECT keycol from testing where keycol = S1.number)
Please tell me this if this runs faster than my original query from yesterda
y:
select t1.keycol + 1 as "begin", t2.keycol - 1 as "end"
from testing t1
inner join testing t2 on t1.keycol < t2.keycol
and t2.keycol - t1.keycol > 1
and
not exists (select a.keycol from testing a
where a.keycol > t1.keycol and a.keycol < t2.keycol)
Thank you.
"jsfromynr" wrote:

> Hi D Babin,
> create table YourTable(keycol int)
> insert into YourTable values(7)
> --insert into YourTable values(8 )
> --insert into YourTable values(9 )
> insert into YourTable values(11 )
> insert into YourTable values(14 )
> -- The following query give wrong results when 8 and 9 values are not
> there
> SELECT T1.keycol+1, MIN(T2.keycol)-1
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.keycol < T2.keycol
> GROUP BY T1.keycol
> HAVING T1.keycol+1 < MIN(T2.keycol)
> -- If you are looking for a single column Then I go with Aaron
> Bertrand's Solution
> -- Here sequence is an auxiallry table having numbers from 1-99999
> select seq from sequence S1,(select min(keycol) M1,max (keycol) M2
> from YourTable) S2
> where S1.seq between S2.M1 and S2.M2
> and S1.SEQ NOT IN (SELECT keycol from YourTable)
> drop table yourtable
> With warm regards
> Jatinder Singh
> D Babin wrote:
>