Mixing
Renumbering a column in SQL Server
I have a big problem in SQL Server. In one of my tables, I have an auto number column. When some row is deleted, numbering in this column gets messy. I want to renumber it using a stored procedure. How can I do this?
sql-server edited Jan 2 at 6:36
marc_s
581k13011221268
asked Jan 1 at 23:50
AhmadAhmad
12
add a comment |
I have a big problem in SQL Server. In one of my tables, I have an auto number column. When some row is deleted, numbering in this column gets messy. I want to renumber it using a stored procedure. How can I do this?
sql-server edited Jan 2 at 6:36
marc_s
581k13011221268
asked Jan 1 at 23:50
AhmadAhmad
12
2
That's not really the way Identity columns are mean to be used, I hope you aren't using that key as a link to other tables. But you might be able to use DBCC CHECKIDENT.
– Jon Wilson
Jan 2 at 0:04
add a comment |
I have a big problem in SQL Server. In one of my tables, I have an auto number column. When some row is deleted, numbering in this column gets messy. I want to renumber it using a stored procedure. How can I do this?
sql-server edited Jan 2 at 6:36
marc_s
581k13011221268
asked Jan 1 at 23:50
AhmadAhmad
12
I have a big problem in SQL Server. In one of my tables, I have an auto number column. When some row is deleted, numbering in this column gets messy. I want to renumber it using a stored procedure. How can I do this?
sql-server sql-server edited Jan 2 at 6:36
marc_s
581k13011221268
asked Jan 1 at 23:50
AhmadAhmad
12
edited Jan 2 at 6:36
marc_s
581k13011221268
asked Jan 1 at 23:50
AhmadAhmad
12
edited Jan 2 at 6:36
marc_s
581k13011221268
edited Jan 2 at 6:36
marc_s
581k13011221268
edited Jan 2 at 6:36
marc_s
581k13011221268
581k13011221268
asked Jan 1 at 23:50
AhmadAhmad
12
asked Jan 1 at 23:50
AhmadAhmad
12
asked Jan 1 at 23:50
AhmadAhmad
12
12
2
That's not really the way Identity columns are mean to be used, I hope you aren't using that key as a link to other tables. But you might be able to use DBCC CHECKIDENT.
– Jon Wilson
Jan 2 at 0:04
add a comment |
2
That's not really the way Identity columns are mean to be used, I hope you aren't using that key as a link to other tables. But you might be able to use DBCC CHECKIDENT.
– Jon Wilson
Jan 2 at 0:04
2
2
That's not really the way Identity columns are mean to be used, I hope you aren't using that key as a link to other tables. But you might be able to use DBCC CHECKIDENT.
– Jon Wilson
Jan 2 at 0:04
That's not really the way Identity columns are mean to be used, I hope you aren't using that key as a link to other tables. But you might be able to use DBCC CHECKIDENT.
– Jon Wilson
Jan 2 at 0:04
add a comment |
1 Answer
1
active
oldest
votes
This is too long for a comment.
An identity column uniquely identifies each row in a table. That is its purpose. Generally these are also primary keys in the table concerned. They are used for foreign key references.
You do not want to change the values after they have been created. In other words, you want an identifier for each row that is consistent across the database and consistent across time. You don't want "27" to mean one thing today and a different thing next month.
If you want some sort of sequential number with no gaps, then use row_number() when you query the table. This will "fill in" the gaps, although the particular number assigned to a given row can vary over time.
answered Jan 2 at 0:09
Gordon LinoffGordon Linoff
787k35311416
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53999842%2frenumbering-a-column-in-sql-server%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
This is too long for a comment.
An identity column uniquely identifies each row in a table. That is its purpose. Generally these are also primary keys in the table concerned. They are used for foreign key references.
You do not want to change the values after they have been created. In other words, you want an identifier for each row that is consistent across the database and consistent across time. You don't want "27" to mean one thing today and a different thing next month.
If you want some sort of sequential number with no gaps, then use row_number() when you query the table. This will "fill in" the gaps, although the particular number assigned to a given row can vary over time.
answered Jan 2 at 0:09
Gordon LinoffGordon Linoff
787k35311416
add a comment |
This is too long for a comment.
An identity column uniquely identifies each row in a table. That is its purpose. Generally these are also primary keys in the table concerned. They are used for foreign key references.
You do not want to change the values after they have been created. In other words, you want an identifier for each row that is consistent across the database and consistent across time. You don't want "27" to mean one thing today and a different thing next month.
If you want some sort of sequential number with no gaps, then use row_number() when you query the table. This will "fill in" the gaps, although the particular number assigned to a given row can vary over time.
answered Jan 2 at 0:09
Gordon LinoffGordon Linoff
787k35311416
add a comment |
This is too long for a comment.
An identity column uniquely identifies each row in a table. That is its purpose. Generally these are also primary keys in the table concerned. They are used for foreign key references.
You do not want to change the values after they have been created. In other words, you want an identifier for each row that is consistent across the database and consistent across time. You don't want "27" to mean one thing today and a different thing next month.
If you want some sort of sequential number with no gaps, then use row_number() when you query the table. This will "fill in" the gaps, although the particular number assigned to a given row can vary over time.
answered Jan 2 at 0:09
Gordon LinoffGordon Linoff
787k35311416
This is too long for a comment.
An identity column uniquely identifies each row in a table. That is its purpose. Generally these are also primary keys in the table concerned. They are used for foreign key references.
You do not want to change the values after they have been created. In other words, you want an identifier for each row that is consistent across the database and consistent across time. You don't want "27" to mean one thing today and a different thing next month.
If you want some sort of sequential number with no gaps, then use row_number() when you query the table. This will "fill in" the gaps, although the particular number assigned to a given row can vary over time.
answered Jan 2 at 0:09
Gordon LinoffGordon Linoff
787k35311416
answered Jan 2 at 0:09
Gordon LinoffGordon Linoff
787k35311416
answered Jan 2 at 0:09
Gordon LinoffGordon Linoff
787k35311416
answered Jan 2 at 0:09
Gordon LinoffGordon Linoff
787k35311416
787k35311416
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53999842%2frenumbering-a-column-in-sql-server%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
2
That's not really the way Identity columns are mean to be used, I hope you aren't using that key as a link to other tables. But you might be able to use DBCC CHECKIDENT.
– Jon Wilson
Jan 2 at 0:04