sql - Find gaps in auto incremented values -

Imagine having a table as table below:

  Create a table test ( Id int auto_increment, some int, column int)  

And then in this table a lot is used, the rows are inserted and the rows are removed and the number interval over time Which may have been automatically increased once. As an example, if I do the following question at some point:

  select the top 10 ID from the test  

I can get something like this

  3 4 6 7 9 10 13 14 18 19  

How can I create a query that is unavailable value 1,2,5,8 Gives etc.

The easiest way to get class of the missing values ​​is:

Select

  as the first missing (IDE + 1), select (TID) as the lastmissing (nextid-1), more than CD (ID) next T in the form of T) T where the nextid is not zero and the next id & lt; & Gt; ID + 1;  

Note that this uses the lead () function, which is available in SQL Server 2012+. You can do something with a subquery in the earlier versions of Apply Here is an example:

  as the first missing (id + 1) Select, select last (from the nextid - 1) (TIID, TT test 2 to top 1 ID, where T.ID> TDI order order ID) TTT, where the next identity is not empty and the next & Lt; & Gt; ID + 1;