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











up vote
2
down vote

favorite












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.











share|improve this question






















  • Though your question is upvoted,it is not clear,what output you are expecting.what data you are looking for ?
    – KumarHarsh
    Dec 1 at 12:10















up vote
2
down vote

favorite












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.











share|improve this question






















  • Though your question is upvoted,it is not clear,what output you are expecting.what data you are looking for ?
    – KumarHarsh
    Dec 1 at 12:10













up vote
2
down vote

favorite









up vote
2
down vote

favorite











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.











share|improve this question













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.








sql-server






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Dec 1 at 8:09









Saroj Shrestha

304




304












  • Though your question is upvoted,it is not clear,what output you are expecting.what data you are looking for ?
    – KumarHarsh
    Dec 1 at 12:10


















  • Though your question is upvoted,it is not clear,what output you are expecting.what data you are looking for ?
    – KumarHarsh
    Dec 1 at 12:10
















Though your question is upvoted,it is not clear,what output you are expecting.what data you are looking for ?
– KumarHarsh
Dec 1 at 12:10




Though your question is upvoted,it is not clear,what output you are expecting.what data you are looking for ?
– KumarHarsh
Dec 1 at 12:10










1 Answer
1






active

oldest

votes

















up vote
2
down vote



accepted










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.






share|improve this answer























  • Although, I have marked this as an accepted answer. I feel like still it will be difficult to deal with numeric pagination. Do you have any idea how to deal with numeric pagination? For example, At first, I query from top 50 where id > 1. in case if 2 data were deleted in between 1-50 it will fetch data from 1-52. Now, for page no 2 I could fetch top 50 from id > 52. But how to find, if user decides to skip one page.
    – Saroj Shrestha
    Dec 2 at 4:12











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223895%2fhow-to-deal-with-id-gap-when-your-query-is-like-from-id-xx-to-id-xxx%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
2
down vote



accepted










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.






share|improve this answer























  • Although, I have marked this as an accepted answer. I feel like still it will be difficult to deal with numeric pagination. Do you have any idea how to deal with numeric pagination? For example, At first, I query from top 50 where id > 1. in case if 2 data were deleted in between 1-50 it will fetch data from 1-52. Now, for page no 2 I could fetch top 50 from id > 52. But how to find, if user decides to skip one page.
    – Saroj Shrestha
    Dec 2 at 4:12















up vote
2
down vote



accepted










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.






share|improve this answer























  • Although, I have marked this as an accepted answer. I feel like still it will be difficult to deal with numeric pagination. Do you have any idea how to deal with numeric pagination? For example, At first, I query from top 50 where id > 1. in case if 2 data were deleted in between 1-50 it will fetch data from 1-52. Now, for page no 2 I could fetch top 50 from id > 52. But how to find, if user decides to skip one page.
    – Saroj Shrestha
    Dec 2 at 4:12













up vote
2
down vote



accepted







up vote
2
down vote



accepted






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.






share|improve this answer














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.







share|improve this answer














share|improve this answer



share|improve this answer








edited Dec 1 at 11:17

























answered Dec 1 at 11:09









ypercubeᵀᴹ

73.8k11124203




73.8k11124203












  • Although, I have marked this as an accepted answer. I feel like still it will be difficult to deal with numeric pagination. Do you have any idea how to deal with numeric pagination? For example, At first, I query from top 50 where id > 1. in case if 2 data were deleted in between 1-50 it will fetch data from 1-52. Now, for page no 2 I could fetch top 50 from id > 52. But how to find, if user decides to skip one page.
    – Saroj Shrestha
    Dec 2 at 4:12


















  • Although, I have marked this as an accepted answer. I feel like still it will be difficult to deal with numeric pagination. Do you have any idea how to deal with numeric pagination? For example, At first, I query from top 50 where id > 1. in case if 2 data were deleted in between 1-50 it will fetch data from 1-52. Now, for page no 2 I could fetch top 50 from id > 52. But how to find, if user decides to skip one page.
    – Saroj Shrestha
    Dec 2 at 4:12
















Although, I have marked this as an accepted answer. I feel like still it will be difficult to deal with numeric pagination. Do you have any idea how to deal with numeric pagination? For example, At first, I query from top 50 where id > 1. in case if 2 data were deleted in between 1-50 it will fetch data from 1-52. Now, for page no 2 I could fetch top 50 from id > 52. But how to find, if user decides to skip one page.
– Saroj Shrestha
Dec 2 at 4:12




Although, I have marked this as an accepted answer. I feel like still it will be difficult to deal with numeric pagination. Do you have any idea how to deal with numeric pagination? For example, At first, I query from top 50 where id > 1. in case if 2 data were deleted in between 1-50 it will fetch data from 1-52. Now, for page no 2 I could fetch top 50 from id > 52. But how to find, if user decides to skip one page.
– Saroj Shrestha
Dec 2 at 4:12


















draft saved

draft discarded




















































Thanks for contributing an answer to Database Administrators Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223895%2fhow-to-deal-with-id-gap-when-your-query-is-like-from-id-xx-to-id-xxx%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Ellipse (mathématiques)

Quarter-circle Tiles

Mont Emei