April 20, 2018 at 5:13 am
Hi,
I have a weird situation that as from yesterday one of our queries started changing the order in which results are returned.
We make use of SQL server 2008 r2 sp3.
The specified query never had a order by clause as part of the query. I assumed it used the clustered index (PK).
It is an old Delphi application making use of ADO. If I run the application against our DEV SQL server the order remains correct. If I run the same application against our production server the order changed. The table has a composite PK consisting of 3 integer fields. Up until yesterday It ordered by field 1, 2, 3 automatically.
I have tried all I can think of. Any ideas of what could cause this?.
Kind Regards
April 20, 2018 at 5:23 am
jeandrep - Friday, April 20, 2018 5:13 AMHi,I have a weird situation that as from yesterday one of our queries started changing the order in which results are returned.
We make use of SQL server 2008 r2 sp3.
The specified query never had a order by clause as part of the query. I assumed it used the clustered index (PK).It is an old Delphi application making use of ADO. If I run the application against our DEV SQL server the order remains correct. If I run the same application against our production server the order changed. The table has a composite PK consisting of 3 integer fields. Up until yesterday It ordered by field 1, 2, 3 automatically.
I have tried all I can think of. Any ideas of what could cause this?.
Kind Regards
Unless there is an order by clause in the query, the server will return the results in any order it sees fit.
😎
Changes in the execution plan may and will change the order of the results, i.e. the query goes parallel etc.
April 20, 2018 at 7:25 am
This is an excellent explanation of what you witnessed. http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply