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.
sql-server
asked Dec 1 at 8:09
Saroj Shrestha
304
add a comment |
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
asked Dec 1 at 8:09
Saroj Shrestha
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
add a comment |
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
asked Dec 1 at 8:09
Saroj Shrestha
304
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
sql-server
asked Dec 1 at 8:09
Saroj Shrestha
304
asked Dec 1 at 8:09
Saroj Shrestha
304
asked Dec 1 at 8:09
Saroj Shrestha
304
asked Dec 1 at 8:09
Saroj Shrestha
304
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
add a comment |
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
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Dec 1 at 11:09
yper-crazyhat-cubeᵀᴹ
74.3k11125206
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
add a comment |
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',
autoActivateHeartbeat: false,
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
answered Dec 1 at 11:09
yper-crazyhat-cubeᵀᴹ
74.3k11125206
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
add a comment |
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.
answered Dec 1 at 11:09
yper-crazyhat-cubeᵀᴹ
74.3k11125206
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
add a comment |
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.
answered Dec 1 at 11:09
yper-crazyhat-cubeᵀᴹ
74.3k11125206
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.
answered Dec 1 at 11:09
yper-crazyhat-cubeᵀᴹ
74.3k11125206
edited Dec 1 at 11:17
answered Dec 1 at 11:09
yper-crazyhat-cubeᵀᴹ
74.3k11125206
answered Dec 1 at 11:09
yper-crazyhat-cubeᵀᴹ
74.3k11125206
answered Dec 1 at 11:09
yper-crazyhat-cubeᵀᴹ
74.3k11125206
74.3k11125206
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
add a comment |
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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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