CODEWITHSUNDEEP
sql-server-2005t-sqlsql-server-2008
Simon Molloy
Simon Molloy

Reputation: 1864

SQL Server 2008 T-SQL features do not respect compatibility levels

I have inadvertently used a couple of the SQL Server 2008 T-SQL features in a script containing a function which will be deployed to numerous customer databases, some of which will be SQL 2005.

An example of an offending statement:

create function dbo.fnThisWontWorkOnSQL2005
    ()
returns varchar(max)
as
begin
    declare @intCounter int = 2
    return @intCounter
end

select dbo.fnThisWontWorkOnSQL2005()

On a SQL2008 instance this will return 2.

On a SQL2005 instance this advises "Cannot assign a default value to a local variable."

However, on a SQL 2005 database set by compatibility level running on a SQL2008 R2 instance the code runs happily and returns 2.

My issue arises as I am developing on a SQL2008R2 instance and I had assumed that setting the compatibility level of the target database to SQL Server 2005 would flag up any invalid T-SQL though this does NOT happen, or at least I can find nothing to prevent it happening.

Is anyone aware of a way to prevent this happening? I have searched variously but cannot find a way and it feels like a bug to me or do I always need to develop on the lowest SQL version I will be deploying to?

Upvotes: 3

Views: 2199

Answers (3)

Tao
Tao

Reputation: 14006

OK, this question is already answered to death, but further kill it:

The Compatibility Level functionality functionality is designed to help ensure that a SQL Server 2005 application can work with minimal or no changes on a SQL Server 2008 instance (or between any 2 versions basically). Its only purpose is to deal with changes in syntax, where existing code would probably work incorrectly under 2008 (or whatever later version you're dealing with).

There's a huge list of things that are perfectly usable in 2005 and 2008 databases even when set to 80 (SQL Server 2000) compatibility level: - MERGE statements - CTEs - Default Values for declared parameters - New Data Types (eg NVarChar(Max)) - the list goes on and on.

I am embarrassed to say that the main system I work with is still running in SQL Server 2000 compatibility mode, even though we're now on SQL Server 2008 R2; We're using lots and lots of the newer features, we just never got around to fixing all our table hints. The compatibility level was pretty much created for lazy people like me :) (or rather, to reduce the friction on upgrades to help sell more units of newer versions and reduce the necessary support window on older versions of sql servers - good goals in their own right).

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

However, on a SQL 2005 database set by compatibility level running on a SQL2008 R2 instance the code runs happily and returns 2.

Changing to compatibility level 90 does not change your database to a SQL Server 2005 database, it is still a SQL Server 2008 R2 database.

Compatibility level is not there to prevent you from doing things in SQL Server 2008, it is there to make the functions that was in SQL Server 2005 behave the same way in SQL Server 2008 as they did before.

Upvotes: 2

gbn
gbn

Reputation: 432271

This is correct.

Compatibility level does not mean "run exactly like an older version"
It means "break less often while I fix the code".

Never assume: check. This is what ALTER DATABASE says on MSDN says (my bold):

Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting. If existing SQL Server applications are affected by behavioral differences in SQL Server 2008, convert the application to work properly

If you target SQL Server 2005, develop on SQL Server 2005. See developing SQL 2005 application using SQL 2008 server too

Upvotes: 6

Related Questions