How to deal with id gap when your query is like from id > XX to id < XXX

I have a large table of around 50M data. I am trying to get data with something like

select id,name,address,city
from table_name
where id > 50000000 and id < 50000050 
order by id;

But, this strategy could work only when there is no deleted data. However, In my case, I am soft-deleting data so whenever a row is deleted, its column deleted_at will be filled with current timestamp i.e when deleted deleted_at is not null.

Now, how can I tackle this situation? Please, suggest me what would be my best bet for this.

I am ready to:

• create the index with an extra column(if possible) and reorder on every deletion

• create a temp table with extra table and reorder it on every deletion

• Or same trick with view

• or some other way.

I aaume you want to extend the range of your search so it returns 50 rows even if there are gaps. Then you can use this:

SELECT TOP (50)
    id, name, address city
FROM table_name
WHERE id > 5000000
  AND deleted_at IS NULL
ORDER BY id ;

A partial index on (id) WHERE (deleted_at IS NULL) would improve efficiency.