December 9, 2013 at 9:37 am
Hi,
We upgraded a SQL 2000 to SQL 2008 server this weekend (I just restored the backup in a SQL 2008 Std on a Windows Server 2008 R2 and it worked perfectly).
Now, some queries that used to work perfectly for years, return the data not in the same "format" as in the SQL 2000. I run the query on the 2000 and all the columns are ordered perfectly (even if there is no ORDER BY clause in the query). When I run the same query on the new 2008 server, the same data are returned but the columns are not ordered at all, so the report is all mixed up. I can't modify the query because I don't have the source code of the software.
Collation is the same on both server. Date format also. I don't know what else to check.
can anyone help me on this please?
thanks a lot for your time and help
December 9, 2013 at 9:44 am
Are you talking about column or row order?
Column order is defined by the SELECT list or the ordinal position for the table definition in case you have a SELECT *.
Row order is only guaranteed when ORDER BY is present.
December 9, 2013 at 9:44 am
If by order being wrong you mean row order, I came across something like this when upgrading from SQL2000 to SQL2005 about 5 years ago.
The ordinal column order should be the same as this is recorded in the db, I haven't seen an example of this being out of order.
In my case, old developers had implemented views with an order by which had since been "fixed" in SQL 2005. It was a fairly common workaround a SQL limitation at the time.
CREATE VIEW orderby AS SELECT TOP 100 PERCENT ..... ORDER BY column DESC
This no longer worked in SQL2005 as ORDER BY should really be in the query not the view.
Is this what you meant?
December 9, 2013 at 9:54 am
this is the query I got from Profiler:
SELECT TB_lotLot.lotLabelNo , TB_lotLot.lotCode , TB_prdProduct.prdCode, TB_prdProduct.prdDescription , TB_cntContenantType.cntCode,
TB_cntContenantType.cntPoid , TB_palPaletteType.palCode, TB_palPaletteType.palDescription, TB_palPaletteType.palPoid , TB_prdProduct.prdCode,
TB_prdProduct.prdDescription, TB_cntContenantType.cntCode, TB_cntContenantType.cntPoid, SUM(VW_lotCurrentQtyWeights.lotCurrentQty) AS lotCurrentQty,
SUM(VW_lotCurrentQtyWeights.lotNetWeight) AS lotNetWeight, TB_palPaletteType.palCode, TB_palPaletteType.palDescription, TB_palPaletteType.palPoid,
COUNT(TB_lotLot.lotCode) as NbPal
FROM TB_lotLot LEFT OUTER JOIN TB_recReceptionLot ON TB_lotLot.lotID = TB_recReceptionLot.lotID
LEFT OUTER JOIN TB_brdBrand ON TB_lotLot.brdID = TB_brdBrand.brdID LEFT OUTER JOIN TB_cntContenantType ON TB_lotLot.cntID = TB_cntContenantType.cntID
LEFT OUTER JOIN TB_prdProduct ON TB_lotLot.prdID = TB_prdProduct.prdID LEFT OUTER JOIN TB_wrhWareHouse ON TB_lotLot.wrhID = TB_wrhWareHouse.wrhID
LEFT OUTER JOIN TB_supSupplier ON TB_lotLot.supID = TB_supSupplier.supID LEFT OUTER JOIN TB_palPaletteType ON TB_lotLot.palID = TB_palPaletteType.palID
INNER JOIN VW_lotCurrentQtyWeights ON dbo.TB_lotLot.lotID = VW_lotCurrentQtyWeights.lotID
WHERE VW_lotCurrentQtyWeights.lotCurrentQty IS NOT NULL AND VW_lotCurrentQtyWeights.lotCurrentQty <> 0 AND (TB_wrhWarehouse.wrhCode >= '02'
AND TB_wrhWarehouse.wrhCode <= '02') AND (TB_recReceptionLot.recRecette IS NULL)
GROUP BY TB_lotLot.lotLabelNo , TB_lotLot.lotCode , TB_prdProduct.prdCode, TB_prdProduct.prdDescription, TB_palPaletteType.palCode,
TB_palPaletteType.palDescription, TB_palPaletteType.palPoid, TB_cntContenantType.cntCode, TB_cntContenantType.cntPoid, TB_lotLot.lotID
Columns are in the correct order in the resultset, but the data in those columns are not ordered in the same way as they are when the same query is run in the 2000 server. In the 2000, the columns "lotLabelNo" and "lotCode" are ordered. But not in the 2008 and I don't understand why, the tables have the same data.
December 9, 2013 at 10:19 am
This is taken directly from Books OnLine:
The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.
If you need the order, add the order by. There's no other way.
December 9, 2013 at 11:25 am
I can't add the ORDER BY, I don't have the source code of the application.
Why the data are returned perfectly in the SQL 2000 and the same query, on a restored backup of the same database, won't give the same result ?
December 9, 2013 at 11:35 am
The order in which data is returned by the database engine is not guaranteed absent an ORDER BY clause, as noted above. This is basic relational theory. My guess is that you have better hardware on the new server and the Query Optimizer is choosing to execute the code in parallel.
However, check to see if there is a clustered index on the table. If there is not, you can define one that orders the fields in the order you desire the query to return. That will still not guarantee order, though.
December 9, 2013 at 11:49 am
Dominic Gagné (12/9/2013)
I can't add the ORDER BY, I don't have the source code of the application.Why the data are returned perfectly in the SQL 2000 and the same query, on a restored backup of the same database, won't give the same result ?
Bottom line, luck. As everyone else has been saying, the only way to ensure order in the final result set is to have an ORDER BY clause on the outer most query.
December 9, 2013 at 12:36 pm
By chance, is the query being run a view? I know when we upgraded from SQL 2000 to 2005 there were problems because people included an ORDER BY in a view definition. It was SQL 2005 when the "no order by in view" rule was actually implemented. The solution there is to change the definition of the view.
December 9, 2013 at 12:56 pm
thanks a lot everyone for your replies.
I'll see if I can do something with the indexes on the tables
December 9, 2013 at 1:20 pm
Dominic Gagné (12/9/2013)
thanks a lot everyone for your replies.I'll see if I can do something with the indexes on the tables
That still will not guarantee the order of the final result set. You may find that it helps at first, but then it may stop again. Just going to say it once more with feeling:
The only way to ensure the proper order of the result set is to use an order by on the outer select statement.
December 9, 2013 at 1:48 pm
Lynn Pettis (12/9/2013)
Dominic Gagné (12/9/2013)
thanks a lot everyone for your replies.I'll see if I can do something with the indexes on the tables
That still will not guarantee the order of the final result set. You may find that it helps at first, but then it may stop again. Just going to say it once more with feeling:
The only way to ensure the proper order of the result set is to use an order by on the outer select statement.
I felt the feeling in your statement. 😀 That was good.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply