How to find "holes" in SQL Server tables?

Question

I have a table with a column id (primary key, auto increment value 1).

My application nay allows lines to be excluded, hence the expected query

SELECT id FROM tbl ORDER BY id ASC

that would be it:

id
---
1
2
3
4
5
6
7
...

However, some rows were deleted for some reason, either because my user manipulated the table out of my application or the database is corrupted. Why those lines are missing doesn’t interest me at the moment.

Running the same query from the beginning, my result is not what I hope:

id
---
1
2
3
6
10
12
8870
...

How can I write a query to find these spaces? I need to find where these gaps start, in the case of the above result, I need to extract something like:

id
---
3

For it was from the id 3 where spaces begin.

Answer 1

Finding the intervals

Follow two efficient solutions, obtained in the article Solving Gaps and Islands with Enhanced Window Functions.

The first solution caters to various versions of SQL Server.

-- código #1
SELECT col1 + 1 AS rangestart, 
       (SELECT MIN(B.col1)   
          FROM dbo.T1 AS B
          WHERE B.col1 > A.col1) - 1 AS rangeend 
  FROM dbo.T1 AS A
  WHERE NOT EXISTS (SELECT * 
                      FROM dbo.T1 AS B
                      WHERE B.col1 = A.col1 + 1)
        AND col1 < (SELECT MAX(col1) FROM dbo.T1);

In code #1, replace col1 the name of the column containing the numbering and T1 by table name.

---

There is another suggestion, even more efficient, that works from the 2012 version (inclusive) of SQL Server. Uses the window Function LEAD().

-- código #2
WITH C AS (
SELECT col1 AS cur, LEAD(col1) OVER(ORDER BY col1) AS nxt
  FROM dbo.T1
)
SELECT cur + 1 AS rangestart, nxt - 1 AS rangeend
  FROM C
  WHERE nxt - cur > 1;

In code #2, replace col1 the name of the column containing the numbering and T1 by table name.

---

What is the cause of the intervals?

There could be multiple motives. For small intervals it is necessary to look for the cause in the application and in the accesses made directly to the table by the users. For larger ranges (usually multiples of 1000), one possibility is that the cause is directly linked to how the IDENTITY is implemented in SQL Server. The documentation itself reads "SQL Server Might cache Identity values for performance reasons and some of the Assigned values can be Lost During a database Failure or server Restart. This can result in gaps in the Identity value upon Insert”.
Attention to the excerpt "This can result in gaps"!

As a solution, still in the same documentation is quoted that "If gaps are not acceptable then the application should use its Own Mechanism to generate key values". That is, it is a fact that IDENTITY is unreliable to generate consecutive numerical sequences without intervals.

---

Deepening on the theme gaps and Islands

For those interested in knowing more about the classic problem of gaps and Islands (ranges and islands), here are some selected articles:

• The SQL of Gaps and Islands in Sequences
• Gaps and Islands (Chapter 5 of the book SQL Server MVP Deep Dives)
• Gaps and Islands... With a Twist!

Answer 2

Another method, which works on any BD:

SELECT
  min(id)
FROM tabela
WHERE id+1 NOT IN
  (SELECT id FROM tabela
  )
;

If you need to know all idIf you don’t have the next one, just do it like this:

SELECT
  id
FROM tabela
WHERE id+1 NOT IN
  (SELECT id FROM tabela
  )
ORDER BY id;

Note that these queries only take from the smallest number of id up. If for example the id’s are 5, 6 and 8 in the table, it will show the value 6, not 0, since it does not have id 1.

Answer 3

You can use the clause NOT EXISTS:

SELECT t1.*
  FROM tabela t1
 WHERE NOT EXISTS(SELECT 1
                    FROM tabela t2
                   WHERE t2.id -1 = t1.id)