Sort order different after DB restore
-
I have a database that return data from a select statement that joins several tables but not in the order I expected. But if I restore the DB and rerun the query, the data return in the correct order. Does SQL make some database changes to the database during the restore that I'm not aware of? Thanks for any input...
-
Scorpvo,
Could you tell us a couple of things?
- What version of SQL Server; and
- Does your join query have an ORDER BY clause?
Thanks,
Chris
-
Chris:
1. SQL Server 2000 with SP3a
2. Here's the query:
select ...
from tbl_lob_contracttype a
left outer join tbl_rates b on a.contractid = b.contractid
and a.lob_contracttypeid = b.rtype
left outer join tbl_lookup_contracttype c on a.Contracttypeid = c.contracttypeid
left outer join tbl_rates annrev on a.contractid = annrev.contractid and annrev.runit = 8
and annrev.rtype is null
left outer join tbl_rates mtons on a.contractid = mtons.contractid and mtons.runit = 9
and mtons.rtype is null
left outer join tbl_lookup_lob d on a.lobid = d.lobid
where inactive = 0 and a.contractid = 4142
order by contracttypetext, a.ContractTypeID + a.LOBID,lobtext,b.contractid asc
From the restore DB, the sort order for one of the field return data in order. However, the original database, this field data is out of order. Thanks for looking into this...
-
So you mean to say that your result set violates the ORDER BY clause in the "original" database - for one of the fields. Hmm. Which one? Do you have any sample output that shows all of the expressions referenced by the ORDER BY in the wrong order?
I'm surprised, by the way, to see something called lobtext in the ORDER BY clause - you can't ORDER by on a TEXT / NTEXT field.
-Chris
-
Here's the sample output from the Original DB:
(the data on RUnit column should be in order from 1-15 but for some reason it start at 10)
RUnitContrct TypeContractText
176521Collection
276521Collection
376521Collection
476521Collection
576521Collection
676521Collection
776521Collection
1076521Collection
1176521Collection
1276521Collection
1376521Collection
1476521Collection
1576521Collection
10151271Collection <----- out of order
11151271Collection
12151271Collection
13151271Collection
14151271Collection
15151271Collection
1151271Collection
2151271Collection
3151271Collection
4151271Collection
5151271Collection
6151271Collection
7151271Collection
Here's the sample output from the restored DB:
RunitContrctTypeContractType
176521Collection
276521Collection
376521Collection
476521Collection
576521Collection
676521Collection
776521Collection
1076521Collection
1176521Collection
1276521Collection
1376521Collection
1476521Collection
1576521Collection
1151271Collection
2151271Collection
3151271Collection
4151271Collection
5151271Collection
6151271Collection
7151271Collection
10151271Collection
11151271Collection
12151271Collection
13151271Collection
14151271Collection
15151271Collection
Thanks...
-
10 15127 1 Collection <----- out of order
I assume that in this row, 10 is contracttypetext, 15127 is a.ContractTypeID, 1 is a.LOBID, and Collection is lobtext ... correct?
Chris
PS. With this unambitious post, I have crossed the 200-post barrier. And they said it couldn't be done!
-
10 15127 1 Collection <----- out of order
10 is Runit and it's in an Integer type.
This column is not in the sort order criteria. But from the different backup and restore of the database (I restored different backup to different servers) and it seem this field "Runit" is sort in order. However, the original DB is out of order w/ these records.
PS. Good Luck posting. What do you win for 200+ posts?
-
My prize for 200 posts, apparently, is the honor of posting again. That, plus the chance to get lots of question marks posted next to my name.
Here's the scoop:
<soapbox>
You can never, NEVER, never, NEVER rely on a sort order you do not specify explicitly in an appropriate ORDER BY clause. Over the last decade, we've seen lots of people who thought that since SQL Server *did* sort something a certain way, they could rely on that sort order without coding an appropriate ORDER BY clause. Sometimes they even (naively) believed that they were eliminating a sort from their query processing by not specifying an ORDER BY clause. One instance that comes to mind is when SQL Server began doing parallelized execution plans. Groupings that used to be done serially in the execution plan were now parallelized, and the first worker thread back "won" the race, which meant it got to populate the result set of that part of the execution plan first.
Anyway, this soapbox is not meant to be historical - sorry about that. In your case, you have specified an ORDER BY clause, but runit is not part of the ORDER BY clause. Therefore, with respect to runit, if the ORDER BY clause produces ties, runit will come out sorted in whatever order the execution plan happens to produce each time it is run.
In short, no guarantees without specifying runit as part of the ORDER BY.
</soapbox>
I hate soapboxing, but I'm just the messenger. The reasons WHY you can't rely on a non-specified sort order being produced reliably (in other words, repeatedly) and the different FACTORS that can cause results to return in different sort orders at different times, even for identical queries, are fairly involved. But there is no guaranteed sort order other than what is specified in the ORDER BY clause.
Hope this helps (and I hope you don't have a lot of code to re-write),
Chris
-
Well, thanks for the "soapBox." It answers my question. 😐
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply