1

I have a primary key on a table in SQL Server (I'm using version 9.00.4060.00 SP3 Standard Edition).

Let's say it has values 1,2,3,5,17 in it.

I could cycle through each row in order until I found a missing value (4 in this case) which is fine if the number of rows is small, but if there are millions of rows and the first missing number is in the middle this will take ages.

Is there some SQL Server built-in functionality for doing this quickly?

Graham
  • 7,807
  • 20
  • 69
  • 114
  • Fastest way most likely is to compare row_number to the key and when it differs you have found the missing number – James Z Jul 22 '15 at 08:36
  • @JamesZ Not necessarily, that will work if the ID starts from `1`. If your range starts from anything other than that then you'll have to adjust the `ROW_NUMBER()` with an addition for the "offset". – Radu Gheorghiu Jul 22 '15 at 08:38
  • The bigger question is why? Who cares if there is a gap in your primary key? – Sean Lange Jul 22 '15 at 13:27

3 Answers3

4

Does this really takes ages? 

SELECT MIN(ID) + 1 
FROM TableName t1
WHERE NOT EXISTS
(
    SELECT 1 FROM TableName t2
    WHERE ID = t1.ID + 1
)

Edit: I have tested it on a table with ~100 million records and it really took some time, more than i thought. There was a missing primary key at 12522150 and the query took 31 seconds.

You could also use ROW_NUMBER, it works from SQL-Server 2005 on:

WITH CTE AS
(
    SELECT ID, RN = ROW_NUMBER() OVER (ORDER BY ID)
    FROM TableName
)
SELECT MIN(ID) + 1
FROM CTE
WHERE ID <> (RN - 1) + (SELECT MIN(ID) FROM TableName)

But this query needs even 41 seconds.

Both queries presume following rule:

  • If the first value is 1000, it's not the first gap
  • The first gap is 1002 if there are 1000,1001,1003
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • In the `with cte...` example, I found that the `+ 1` needs to be `- 1` and if there is a gap of more than 1 then it retrieves the higher number in the gap. – Graham Jul 22 '15 at 09:06
  • @Graham: you're right. The second approach is not quite correct. Let me lhave a look. I guess that's also the reason why it's so fast. – Tim Schmelter Jul 22 '15 at 09:18
  • @Graham: Yes, the second was incorrect. Have a look at my edited version. As you can see, it needs even 10 seconds more. – Tim Schmelter Jul 22 '15 at 09:21
  • seems I'm going to have to live with it being slow - thanks for your efforts – Graham Jul 22 '15 at 09:29
  • @Graham: if you would use at least sql-server 2012 you could use [`LEAD`](https://msdn.microsoft.com/en-us/library/hh213125.aspx) to access the next row. That avoids a subquery or self-join. – Tim Schmelter Jul 22 '15 at 09:33
2

There is a similar question on stackoverflow. Look here: Find the smallest unused number in SQL Server 

SELECT TOP 1 t1.Id+1 
FROM table t1
WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1)
ORDER BY t1.Id
Community
  • 1
  • 1
Dennis
  • 398
  • 2
  • 10
0

There is no in-built functionality to do this. You can try something like this

SELECT Min(ID) AS MIN_MISSING_ID
FROM   (SELECT *,
               RN= Row_number()OVER (ORDER BY ID)
        FROM   Yourtable) dt
WHERE  RN < ID
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172