How to optimize to find in SQL a number that satisfies a condition?

Question

SQL

I have an id range in which I need to create 1-10 entries.
2 0 10 000 00 0
-
2 0 10 999 99 0
I want to create 5 records, find range:
2 010 123 450 - 2 010 123 459 and create id:
2 010 123 450
2 010 123 451
2 010 123 452
2 010 123 453
2 010 123 454
I need to find an empty range as quickly as possible, if there is none, print an error.

SELECT TOP 20 (users.id+1) as empty_id
FROM sw.dbo.users
WHERE (
    SELECT 1 FROM users as st WHERE st.id = (users.id + 1)
) IS NULL
ORDER BY users.id;

I found a code that finds a range of generally empty numbers. But maybe there is a better way.

Answer 1

The very idea of ​​obtaining an ID in this way is far from "optimal" and "performance" - for each such operation, you will have to lock the table for insertion and deletion.