Determine If Table Has Consecutive Primary ID Auto Increment Values

Is there a way to determine whether a table, a very large table possibly, has contiguous / consecutive auto increment primary key IDs? Is there开发者_StackOverflow社区 a SQL query way to determine this? Suppose someone deletes some rows randomly from a very large table. I need to know that this has happened.

e.g. 
table XYZ
id
1
2
3
4

table abc
1
2
4 <--- non contiguous, skipped 3
5

Curious about data integrity. I want a SQL query methodology way in order to just keep things simple and not have to write a PHP script to run against the database.

You could compare these two values:

SELECT (MAX(ID) - MIN(ID)) + 1, -- e.g. ID 2 - ID 1 = 1 (+1) = 2 rows
       COUNT(ID)
FROM Table

If the table is still contiguous they will be the same.

How about this: count the number of rows, subtract the lowest ID from the highest, and if the two numbers match then the IDs are contiguous.

Use a self join

SELECT *
FROM t t1
LEFT JOIN t t2 ON t2.ID = t1.ID + 1
WHERE t2.ID IS NULL

If there are no gaps, this will return only one row, for the last element in the table. You could even eliminate that row from the results set if you want to be clever, but this should give you the idea.

This technique is particularly useful if you want to do more work with the gaps. If not, the counting techniques others suggested are simpler.