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 &gt; 50000000 and id &lt; 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 &gt; 5000000
  AND deleted_at IS NULL
ORDER BY id ;


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