venoym (3/28/2011)
I just had an interesting occurance.Server Version:10.0, 64-bit
Database Compatibility: 80
the snippet in the question copied verbatim into SSMS and worked. The result was 255.
I would have thought database compatibility mode would have prevented this...
No Compatibility mode is not intended to disable new features. It's main goal is to keep existing code running. So the two spear points are:
1. If a new feature uses syntax that was not a reserved word in earlier versions, the feature is disabled in compat mode, so that old code that happens to use that word as an identifier continues to run.
2. If a feature has been deprecated in an earlier version already, it usually runs in compat mode only for one or more versions before being removed completely. This happened, for instance, to the ancient infixed outer join syntax (using =* and *=). Since SQL Server 2005, they are only available if you set the compatibility level to 80 or lower. And the implied sorting by a GROUP BY only works in SQL Server 2005 if you set compatibility to 60 or 65 - and since these levels are not supported in SQL Server 2008 and up, this "feature" is now gone for good.
There is no need (other than as described above) to disable new functionality when running in a lower compatibility level - unless you seriously excpect that people have written "SELECT @i+=1" in a SQL Server 2005 stored proc as a way to force an error, and will now become angry because their code suddenly no longer results in an error... 😉