[RESOLVED] DB (Access or SQL Server) - Using pure SQL find first available Key in Table

[dee-u]

I am not sure if this will work in Access but should work with SQL Server.

SELECT
       CASE
              WHEN MAX(code) = COUNT(*)
              THEN CAST(NULL AS INTEGER)
              WHEN MIN(code) > 1
              THEN 1
              WHEN MAX(code) <> COUNT(*)
              THEN
                     (SELECT MIN(code) + 1
                     FROM    [TABLE]
                     WHERE
                             (
                                     code + 1
                             )
                             NOT IN
                                    (SELECT code
                                    FROM    [TABLE]
                                    )
                     )
              ELSE NULL
       END
FROM   [TABLE]