April 27, 2011 at 1:03 pm
HI All,
We are in process to upgrading sql server 2000 to sqlserver2008 as of now every thing is going fine but we've found that sqlserver 2008 return same result set but changed order seq for example
sql server 2000 return below reuslt
Column1 Column2
1 A
2 B
3 C
4 D
but sqlserver2008
Column1 Column2
2 B
1 A
4 D
3 C
I've checked execution plan in both sql2k as well as sql2k8 both are identical still producing different order client want same as in sql2k
thnx
April 27, 2011 at 1:17 pm
What is the query you are running?
Without an explicit ORDER BY clause, it is by design that queries are not guaranteed to return data in a set order.
Even (i think) with a clustered index unless ORDER BY is specified a guaranteed order is not to be expected.
HTH
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
April 27, 2011 at 1:19 pm
Add an ORDER BY to your query.
Rows in a table have no concept of sequence, and the only way to guarantee sequential order in a query result is by using ORDER BY.
April 27, 2011 at 1:23 pm
thnx for you reply
we are using order by date column but problem is that date value is same for some of record in this case sqlserver2k8 and sql2k producing diff. seq.
April 27, 2011 at 1:34 pm
Sounds possible. Output the ORDER BY columns and you'll see if that is correct.
April 27, 2011 at 1:36 pm
Can't see what you see. Sounds like you may be querying a view with an order by declared in the view. This works in SQL Server 2000 but changed in SQL Server 2005. You need to put your order by in the outer most query to ensure order of the result set.
April 27, 2011 at 2:04 pm
no we are querying from view is there any Collation settings for sorting data problem?
April 27, 2011 at 2:05 pm
no we aren't querying from view is there any Collation settings for sorting data problem?
April 27, 2011 at 2:12 pm
Post table definition and query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply