Find the smallest unused number in SQL Server

by

Find the first row where there does not exist a row with Id + 1

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

Edit:

To handle the special case where the lowest existing id is not 1, here is a ugly solution:

SELECT TOP 1 * FROM (
    SELECT t1.Id+1 AS Id
    FROM table t1
    WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1 )
    UNION 
    SELECT 1 AS Id
    WHERE NOT EXISTS (SELECT * FROM table t3 WHERE t3.Id = 1)) ot
ORDER BY 1

More Related Contents:

  1. Detect consecutive dates ranges using SQL
  2. How do I group on continuous ranges
  3. sql group by only rows which are in sequence
  4. SQL count consecutive days
  5. Case statement for multiple columns
  6. SQL where clause odd behavior [closed]
  7. SQL Server Exec issue [closed]
  8. Count(*) vs Count(1) – SQL Server
  9. How do I escape a single quote in SQL Server?
  10. Selecting data from two different servers in SQL Server
  11. Is a view faster than a simple query?
  12. How to convert DateTime to VarChar
  13. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  14. SQL : BETWEEN vs =
  15. SQL Server: Howto get foreign key reference from information_schema?
  16. SCOPE_IDENTITY() for GUIDs?
  17. Postgres Case Sensitivity
  18. Splitting the string in sql server
  19. How do I query for all dates greater than a certain date in SQL Server?
  20. Check If the string contains accented characters in SQL?
  21. Need to list all triggers in SQL Server database with table name and table’s schema
  22. Count based on condition in SQL Server
  23. Does Sql JOIN order affect performance?
  24. Best equivalent for IsInteger in SQL Server
  25. Syntax of for-loop in SQL Server
  26. Using tuples in SQL “IN” clause
  27. Why does ISNUMERIC(‘.’) return 1?
  28. Cumulative Total in MS Sql server [duplicate]
  29. What does ON [PRIMARY] mean?
  30. Getting warning: Null value is eliminated by an aggregate or other SET operation

Leave a Comment