May 20, 2012 at 7:34 pm
Hi, after upgrading from SQL2000 to SQL2008R2 on different servers, I have this snippet from a stored procedure that is used to update cv.service_id , giving me a different 'order' of rows returned, between the 2 databases.
The actual data is exactly the same in the 2 version od the table, but I need the result as per sql2000.
here is the query:
select cs.service_id, cv.01, cv.02, cv.03,cv.04,cv.05,cv.06
FROM table1 cs,
table2 cv
WHERE cs.01 = cv.06
AND cv.06 = '86223'
and cv.invoice = '20560593'
AND ISNULL(cs.program,cs.service_id) = cv.volume_group
AND cs.facility_id IS NULL
here's my results:
SQL2000
S-RH1 1915578 22 1676044 1676044 86223 5/08/2001 12:00:00.000 AM
S-RH2 1915578 22 1676044 1676044 86223 5/08/2001 12:00:00.000 AM
S-RH3 1915578 22 1676044 1676044 86223 5/08/2001 12:00:00.000 AM
S-RH1 1915578 22 1676044 1676044 86223 19/08/2001 12:00:00.000 AM
S-RH2 1915578 22 1676044 1676044 86223 19/08/2001 12:00:00.000 AM
S-RH3 1915578 22 1676044 1676044 86223 19/08/2001 12:00:00.000 AM
SQL2008R2:
S-RH1 1915578 22 1676044 1676044 86223 5/08/2001 12:00:00.000 AM
S-RH1 1915578 22 1676044 1676044 86223 19/08/2001 12:00:00.000 AM
S-RH2 1915578 22 1676044 1676044 86223 5/08/2001 12:00:00.000 AM
S-RH2 1915578 22 1676044 1676044 86223 19/08/2001 12:00:00.000 AM
S-RH3 1915578 22 1676044 1676044 86223 5/08/2001 12:00:00.000 AM
S-RH3 1915578 22 1676044 1676044 86223 19/08/2001 12:00:00.000 AM
The issue is the first column order, but I assume its to do with the dates somwehow, but how?
Any help would be greatly appreciated
May 20, 2012 at 8:21 pm
With no ORDER BY clause in your SQL, you're not guaranteed any particular order; SQL is set based. If the data is the same between both servers, then you're fine. The difference is probably in the clustered index on the table.
HTH,
Rob
May 20, 2012 at 8:34 pm
Hi,
there is no indices on table in either version. Would it pay to create a clustered index?
What I really wonder, is to why they results should be different, asd the data is extacly the same - I even exported data from sql2000 and imported inot SQl2008R2 on ensure this was so.
May 20, 2012 at 9:07 pm
Rachel Lee-244397 (5/20/2012)
What I really wonder, is to why they results should be different, asd the data is extacly the same -
Robert already explained this to you. Did you read his reply?
In case you did not let me restate what he said:
[highlight]If you want your rows in a specific order then you MUST specify an ORDER BY clause. If you do NOT do that, then you are telling SQL Server that you do not care about the order of the rows returned and that it can return them in any order that if finds convenient. And what it finds convenient can (and will) change without warning and when you least expect it.[/highlight]
In particular to your case, the SQL Server Data Engine was completely rewritten between version 2000 and version 2005. So what it finds convenient now, may well be entirely different from what it used to find convenient.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 20, 2012 at 9:10 pm
Thanks for your pleasant reply.
The fact that the database engine was completly rewritten does give me a more relevant reason as to the differences betwen versions.
May 20, 2012 at 10:49 pm
Rachel Lee-244397 (5/20/2012)
Thanks for your pleasant reply.
If you think that was 'pleasant', you're lucky Joe Celko didn't see your post :laugh:
The fact that the database engine was completly rewritten does give me a more relevant reason as to the differences betwen versions.
The relevant point is the one Robert made: SQL (the language, not the product) returns a multi-set, which has no concept of ordering. The SQL language supports presentation ordering only in the outermost scope of the query, specified using the ORDER BY clause. Without that, records may be returned in any order; the results are logically identical. Changes to the database engine are irrelevant (2005 was certainly a major upgrade, but not a rewrite).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 23, 2012 at 12:32 pm
Besides the WTF'ery about the ORDER BY... the lack of indices is more troublesome.
A clustered index should really be considered mandatory, and then supporting indexes as necessary by the needs of the queries.
/shudder
May 23, 2012 at 1:10 pm
DiverKas (5/23/2012)
Besides the WTF'ery about the ORDER BY... the lack of indices is more troublesome.A clustered index should really be considered mandatory, and then supporting indexes as necessary by the needs of the queries.
/shudder
A clustered index should be mandatory? Why? The question is not about indexes, so this is kind of irrelevant, unless you believe that adding a clustered index will guarantee order (Hint: it doesn't).
Jared
CE - Microsoft
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply