Do primary keys change when indexes are rebuilt or reorganized?
I have a database containing tables that each have auto-increment int columns serving as the primary key column.
When I do a rebuild or reorganize operation on my indexes, does either operation potentially change the values of this column?
Does it matter if the indexes are 'clustered'?
I'm using various versions of MS T-SQL from 2008 onward.
sql-server index primary-key
asked Dec 3 '18 at 15:30
DragonsdoomDragonsdoom
1194
add a comment |
I have a database containing tables that each have auto-increment int columns serving as the primary key column.
When I do a rebuild or reorganize operation on my indexes, does either operation potentially change the values of this column?
Does it matter if the indexes are 'clustered'?
I'm using various versions of MS T-SQL from 2008 onward.
sql-server index primary-key
asked Dec 3 '18 at 15:30
DragonsdoomDragonsdoom
1194
3
Index reorganizations or rebuilds will not alter data, only make sure it is laid out on disk in the proper order. The only thing that can change an auto-increment value (other than an insert/update/delete) is a truncate (resets the increment) or an RESEED command.
– Jonathan Fite
Dec 3 '18 at 15:36
3
@JonathanFite perhaps you should add this as an answer rather than a comment?
– George.Palacios
Dec 3 '18 at 15:46
@JonathanFite thank you for the information.
– Dragonsdoom
Dec 3 '18 at 16:40
add a comment |
I have a database containing tables that each have auto-increment int columns serving as the primary key column.
When I do a rebuild or reorganize operation on my indexes, does either operation potentially change the values of this column?
Does it matter if the indexes are 'clustered'?
I'm using various versions of MS T-SQL from 2008 onward.
sql-server index primary-key
asked Dec 3 '18 at 15:30
DragonsdoomDragonsdoom
1194
I have a database containing tables that each have auto-increment int columns serving as the primary key column.
When I do a rebuild or reorganize operation on my indexes, does either operation potentially change the values of this column?
Does it matter if the indexes are 'clustered'?
I'm using various versions of MS T-SQL from 2008 onward.
sql-server index primary-key
sql-server index primary-key
asked Dec 3 '18 at 15:30
DragonsdoomDragonsdoom
1194
asked Dec 3 '18 at 15:30
DragonsdoomDragonsdoom
1194
asked Dec 3 '18 at 15:30
DragonsdoomDragonsdoom
1194
asked Dec 3 '18 at 15:30
DragonsdoomDragonsdoom
1194
asked Dec 3 '18 at 15:30
DragonsdoomDragonsdoom
1194
1194
3
Index reorganizations or rebuilds will not alter data, only make sure it is laid out on disk in the proper order. The only thing that can change an auto-increment value (other than an insert/update/delete) is a truncate (resets the increment) or an RESEED command.
– Jonathan Fite
Dec 3 '18 at 15:36
3
@JonathanFite perhaps you should add this as an answer rather than a comment?
– George.Palacios
Dec 3 '18 at 15:46
@JonathanFite thank you for the information.
– Dragonsdoom
Dec 3 '18 at 16:40
add a comment |
3
Index reorganizations or rebuilds will not alter data, only make sure it is laid out on disk in the proper order. The only thing that can change an auto-increment value (other than an insert/update/delete) is a truncate (resets the increment) or an RESEED command.
– Jonathan Fite
Dec 3 '18 at 15:36
3
@JonathanFite perhaps you should add this as an answer rather than a comment?
– George.Palacios
Dec 3 '18 at 15:46
@JonathanFite thank you for the information.
– Dragonsdoom
Dec 3 '18 at 16:40
3
3
Index reorganizations or rebuilds will not alter data, only make sure it is laid out on disk in the proper order. The only thing that can change an auto-increment value (other than an insert/update/delete) is a truncate (resets the increment) or an RESEED command.
– Jonathan Fite
Dec 3 '18 at 15:36
Index reorganizations or rebuilds will not alter data, only make sure it is laid out on disk in the proper order. The only thing that can change an auto-increment value (other than an insert/update/delete) is a truncate (resets the increment) or an RESEED command.
– Jonathan Fite
Dec 3 '18 at 15:36
3
3
@JonathanFite perhaps you should add this as an answer rather than a comment?
– George.Palacios
Dec 3 '18 at 15:46
@JonathanFite perhaps you should add this as an answer rather than a comment?
– George.Palacios
Dec 3 '18 at 15:46
@JonathanFite thank you for the information.
– Dragonsdoom
Dec 3 '18 at 16:40
@JonathanFite thank you for the information.
– Dragonsdoom
Dec 3 '18 at 16:40
add a comment |
1 Answer
1
active
oldest
votes
No, they will not.
Rebuilding or reorganizing an index should never alter the underlying data itself, only its placement within database pages / extents.
However, as an aside have a read through this post - you might find that you don't need to rebuild these indexes in the first place.
answered Dec 3 '18 at 15:42
George.PalaciosGeorge.Palacios
2,203824
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%2f224017%2fdo-primary-keys-change-when-indexes-are-rebuilt-or-reorganized%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
No, they will not.
Rebuilding or reorganizing an index should never alter the underlying data itself, only its placement within database pages / extents.
However, as an aside have a read through this post - you might find that you don't need to rebuild these indexes in the first place.
answered Dec 3 '18 at 15:42
George.PalaciosGeorge.Palacios
2,203824
add a comment |
No, they will not.
Rebuilding or reorganizing an index should never alter the underlying data itself, only its placement within database pages / extents.
However, as an aside have a read through this post - you might find that you don't need to rebuild these indexes in the first place.
answered Dec 3 '18 at 15:42
George.PalaciosGeorge.Palacios
2,203824
add a comment |
No, they will not.
Rebuilding or reorganizing an index should never alter the underlying data itself, only its placement within database pages / extents.
However, as an aside have a read through this post - you might find that you don't need to rebuild these indexes in the first place.
answered Dec 3 '18 at 15:42
George.PalaciosGeorge.Palacios
2,203824
No, they will not.
Rebuilding or reorganizing an index should never alter the underlying data itself, only its placement within database pages / extents.
However, as an aside have a read through this post - you might find that you don't need to rebuild these indexes in the first place.
answered Dec 3 '18 at 15:42
George.PalaciosGeorge.Palacios
2,203824
answered Dec 3 '18 at 15:42
George.PalaciosGeorge.Palacios
2,203824
answered Dec 3 '18 at 15:42
George.PalaciosGeorge.Palacios
2,203824
answered Dec 3 '18 at 15:42
George.PalaciosGeorge.Palacios
2,203824
2,203824
add a comment |
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.
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%2f224017%2fdo-primary-keys-change-when-indexes-are-rebuilt-or-reorganized%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
3
Index reorganizations or rebuilds will not alter data, only make sure it is laid out on disk in the proper order. The only thing that can change an auto-increment value (other than an insert/update/delete) is a truncate (resets the increment) or an RESEED command.
– Jonathan Fite
Dec 3 '18 at 15:36
3
@JonathanFite perhaps you should add this as an answer rather than a comment?
– George.Palacios
Dec 3 '18 at 15:46
@JonathanFite thank you for the information.
– Dragonsdoom
Dec 3 '18 at 16:40