SQLite Forum

Suppose you want to keep ids small and always (re-)use the first unused id for a new row.  This is not complex:

        select 1 as newid
            where newid not in (select thingid from thing)
        union all select thingid+1 as newid from thing
            where newid not in (select thingid from thing)
        limit 1;

Now suppose that new things arrive in batches and you want to find the next N ids in one operation.  Efficiency requirements:

1) Scan all existing ids at most once.
2) Use temporary space proportional to N rather than the number of existing ids or the number of unused ids.

This does get complex:

        with recursive
        result (newid) as
            (select candidate from
                (select 1 as candidate
                    where candidate not in (select thingid from thing)
                union all select thingid+1 as candidate from thing
                    where candidate not in (select thingid from thing)
                limit ?1)
            union all select newid+1 as candidate from result
                where candidate not in (select thingid from thing)
            order by candidate
            limit ?1)
        select newid from result;

Can anybody suggest something simpler?