SQL Server 2000 to 2008 Migration - ORDER BY Issue when using DISTINCT

We're currently testing our app from SQL Server 2000 to 2008R2.

The following statement works in 2000, and not in 2008.

select distinct left(tz.Zipcode,5) as zipCode
from TerritoryZip tz 
order by tz.Zipcode

The error message is:

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is sp开发者_高级运维ecified.

The fix is simple:

select distinct left(tz.Zipcode,5) as zipCode
from TerritoryZip tz 
order by left(tz.Zipcode,5)

However there is a risk that we may not find all the instances of this type of SQL.

So one solution might be to set the compatibility level to 2000 - what are the cons of doing that (e.g. performance of not updating the SQL to use this more strict approach)?

And are there other options/settings, e.g. is there a 'strictness' setting that is enforcing better practices, etc...?

Thanks!!

---

You could change the semantics slightly by doing this:

SELECT ZipCode FROM 
(
  SELECT DISTINCT ZipCode = LEFT(tz.Zipcode, 5)
    FROM dbo.TerritoryZip
) AS x
ORDER BY ZipCode;

Doesn't solve the problem really since the query is more verbose and you still can't avoid touching it. The fix you've already suggested is better in my mind because it is more explicit about what is going on.

Not to be harsh, but if you don't think you'll be able to "find all the instances of this type of SQL" then how are you trusting your testing at all?

I would suggest that keeping 2000 mode is not the optimal answer. The reason is this can cause other syntax to break (e.g. the way you might call dynamic management functions - see this Paul Randal blog post and my comment to it), you also run the risk of perpetuating code that should be fixed (e.g. old-style *= / =* joins that are valid in 2000 compat mode, but won't be valid when you go from 2008 R2 -> Denali, which doesn't support 2000 compat).

There is no "strictness" setting but you can vote for Erland Sommarskog's SET STRICT_CHECKS ON; suggestion.

---

So one solution might be to set the compatibility level to 2000 - what are the cons of doing that (e.g. performance of not updating the SQL to use this more strict approach)?

I suggest you look at the documentation on ALTER DATABASE Compatibility Level (Transact-SQL) which lists dozens of differences between the compatibility levels along with the Possibility of impacts of low medium and high.

Also you should probably run the Upgrade Advisor which looks through your components for potential problems that you would need to fix

---

MSDN has a really good article showing the differences between different compatibility levels in SQL Server 2008 (including performance notes and best practices): http://msdn.microsoft.com/en-us/library/bb510680.aspx. Even in the example you gave, the SQL in the 2008 version is more intuitive and is enforcing a better best practice.