| Author |
Topic |
|
nwalter
Starting Member
39 Posts |
 Posted - 2009-03-23 : 15:47:23
|
| We just moved a database from SQL 2000 to SQL 2008 and we have a particular query that returns different result sets on the SQL 2008 server only when including one specific column in an order by clause. If that column is removed the query returns the correct results exactly the same as it did in SQL 2000.Any idea why an order by would produce a different result set?Below is the query if that might help, the particular culprite here is the e.Name field.SELECT g.GroupRosterPK, g.GroupFk, o.groupPk, g.Term, g.EmployeeId, g.Program, p.ProgramName, g.MonthNum, g.Subject, g.Courseno, g.RosterNumber, g.ModifiedBy, g.ModifiedWhen FROM dbo.vw_GroupRoster g LEFT JOIN dbo.Program p ON g.Program = p.Program LEFT JOIN dbo.[Group] o ON g.GroupFK = o.GroupPK LEFT JOIN dbo.Employee e on g.EmployeeID = e.EmployeeIDWHERE (g.Term = @Term) AND (g.Program = @Program) AND (g.MonthNum = @Monthnum)AND (g.EmployeeId = @EmployeeID) ORDER BY g.MonthNum, g.program, e.Name, o.GroupCode, g.RosterNumber |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-23 : 15:50:51
|
| I'd have to guess your collations are different between databases and or server.Mike"oh, that monkey is going to pay" |
 |
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-03-23 : 15:57:06
|
| Thanks, didn't look at that but unfortunately they are the same.The DB collation on both the old and new server are both SQL_Latin1_General_CP1_CI_AS, and the Server level collation on both old and new server are SQL_Latin1_General_CP1_CI_AS. :( |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-23 : 16:05:46
|
| just to rule it out completely check at the column level as well.Mike"oh, that monkey is going to pay" |
|
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-03-23 : 16:11:14
|
| No luck there either, same as all the rest. Atleast for the apparently offending table/column Employee.Name. |
|
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-03-23 : 16:24:42
|
| Something else to add, i just found out that all of these columns are user defined types. I vaguely remember something about migrating UTs from 2000 to 2005/2008. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-23 : 17:45:09
|
| Do you think it would be possible for you to explain exactly how the result sets are different?CODO ERGO SUM |
|
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-03-23 : 17:55:59
|
| The most obvious example is a particular set of parameters on the old server only returns a single row, on the 2008 server the same parameters passed to that query returns 55 rows of data unless of course I remove the e.Name part of the order by and then it returns the same single row as it did on the old 2000 server.Does that make sense? |
|
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-03-23 : 18:09:49
|
| One more thing to add, it appears to not be a problem with order by but more a problem with my e.Name field. If I include the e.Name field in the select list I get the same results as if I had included it in the order by field. |
|
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-03-23 : 19:21:42
|
| I think I found my problem here: http://support.microsoft.com/kb/957815/Specifically "The column order of the primary key constraints in the base table differs from the column order of the columns in the base table."Seems non-clustered primary key indexes have problems returning data properly.. Nice bug there.For now I just changed the clustered index but I may need to apply the CU/hotfix. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-23 : 21:50:53
|
| I don't see how this form the KB article applies to your situation:"For a Microsoft SQL Server 2008 query, you create a spatial index that joins or filters a range of values that are returned by a spatial method."CODO ERGO SUM |
|
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-03-24 : 01:07:02
|
| I think the problem extends much further than Microsoft realizes or admits to. CU2 resolves the issue on my dev and test system. I think the key is not in the title of the article but in the third part of their listed scenario, unfortunately they are not sharing the details of the bug fix or the exact changes to the query optimizer that they made to resolve the problem.Deleting/disabling the index also fixes it, as well as changing the clustered index to match the primary key. So that further points to a problem with the optimizer as the presence or lack of an index should never effect the result set returned from query. |
|
|
|
|
Different results when using order by