Sort Order has been change due to DB server movement
We are using SQL server 2012 (service pack 3) and we have recently moved from one server to another.
After that we have encountered issue SQL sort order problem. We have many report where the order by clause not present. After the movement, the sort order has been changed and the results in the report became different than the actual.
I know the solution by putting Order by clause in ALL stored procedure and View. Since the number of files are huge, it is there anything we can do in the SQL configuration to resolve the issue?
sql-server
asked Apr 19 '17 at 19:10
KamalKamal
71
add a comment |
We are using SQL server 2012 (service pack 3) and we have recently moved from one server to another.
After that we have encountered issue SQL sort order problem. We have many report where the order by clause not present. After the movement, the sort order has been changed and the results in the report became different than the actual.
I know the solution by putting Order by clause in ALL stored procedure and View. Since the number of files are huge, it is there anything we can do in the SQL configuration to resolve the issue?
sql-server
asked Apr 19 '17 at 19:10
KamalKamal
71
3
Did the default collation change between the two servers?
– John Eisbrener
Apr 19 '17 at 19:16
add a comment |
We are using SQL server 2012 (service pack 3) and we have recently moved from one server to another.
After that we have encountered issue SQL sort order problem. We have many report where the order by clause not present. After the movement, the sort order has been changed and the results in the report became different than the actual.
I know the solution by putting Order by clause in ALL stored procedure and View. Since the number of files are huge, it is there anything we can do in the SQL configuration to resolve the issue?
sql-server
asked Apr 19 '17 at 19:10
KamalKamal
71
We are using SQL server 2012 (service pack 3) and we have recently moved from one server to another.
After that we have encountered issue SQL sort order problem. We have many report where the order by clause not present. After the movement, the sort order has been changed and the results in the report became different than the actual.
I know the solution by putting Order by clause in ALL stored procedure and View. Since the number of files are huge, it is there anything we can do in the SQL configuration to resolve the issue?
sql-server
sql-server
asked Apr 19 '17 at 19:10
KamalKamal
71
asked Apr 19 '17 at 19:10
KamalKamal
71
asked Apr 19 '17 at 19:10
KamalKamal
71
asked Apr 19 '17 at 19:10
KamalKamal
71
asked Apr 19 '17 at 19:10
KamalKamal
71
71
3
Did the default collation change between the two servers?
– John Eisbrener
Apr 19 '17 at 19:16
add a comment |
3
Did the default collation change between the two servers?
– John Eisbrener
Apr 19 '17 at 19:16
3
3
Did the default collation change between the two servers?
– John Eisbrener
Apr 19 '17 at 19:16
Did the default collation change between the two servers?
– John Eisbrener
Apr 19 '17 at 19:16
add a comment |
1 Answer
1
active
oldest
votes
If you don't have an ORDER BY clause, the order of the result set is never guaranteed.
- Connor Cunninghan No Seatbelt Expecting Order Without ORDER BY
Aaron Bertrand showing another exmaple of this
Also, you mentioned "report". If you are using SSRS or any reporting suite really, they have sort functions within them as well which means you wouldn't have to change any of your procedures or views.
answered Apr 19 '17 at 19:12
scsimonscsimon
1,332414
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%2f171442%2fsort-order-has-been-change-due-to-db-server-movement%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
If you don't have an ORDER BY clause, the order of the result set is never guaranteed.
- Connor Cunninghan No Seatbelt Expecting Order Without ORDER BY
Aaron Bertrand showing another exmaple of this
Also, you mentioned "report". If you are using SSRS or any reporting suite really, they have sort functions within them as well which means you wouldn't have to change any of your procedures or views.
answered Apr 19 '17 at 19:12
scsimonscsimon
1,332414
add a comment |
If you don't have an ORDER BY clause, the order of the result set is never guaranteed.
- Connor Cunninghan No Seatbelt Expecting Order Without ORDER BY
Aaron Bertrand showing another exmaple of this
Also, you mentioned "report". If you are using SSRS or any reporting suite really, they have sort functions within them as well which means you wouldn't have to change any of your procedures or views.
answered Apr 19 '17 at 19:12
scsimonscsimon
1,332414
add a comment |
If you don't have an ORDER BY clause, the order of the result set is never guaranteed.
- Connor Cunninghan No Seatbelt Expecting Order Without ORDER BY
Aaron Bertrand showing another exmaple of this
Also, you mentioned "report". If you are using SSRS or any reporting suite really, they have sort functions within them as well which means you wouldn't have to change any of your procedures or views.
answered Apr 19 '17 at 19:12
scsimonscsimon
1,332414
If you don't have an ORDER BY clause, the order of the result set is never guaranteed.
- Connor Cunninghan No Seatbelt Expecting Order Without ORDER BY
Aaron Bertrand showing another exmaple of this
Also, you mentioned "report". If you are using SSRS or any reporting suite really, they have sort functions within them as well which means you wouldn't have to change any of your procedures or views.
answered Apr 19 '17 at 19:12
scsimonscsimon
1,332414
edited 10 mins ago
answered Apr 19 '17 at 19:12
scsimonscsimon
1,332414
answered Apr 19 '17 at 19:12
scsimonscsimon
1,332414
answered Apr 19 '17 at 19:12
scsimonscsimon
1,332414
1,332414
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%2f171442%2fsort-order-has-been-change-due-to-db-server-movement%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
Did the default collation change between the two servers?
– John Eisbrener
Apr 19 '17 at 19:16