I understood that setting a database to some COMPATIBILITY_LEVEL just before your native one avoided features from getting used. However this does not appear to be. Witness the next SQL script:
CREATE DATABASE Foo GO USE Foo GO ALTER DATABASE Foo SET COMPATIBILITY_LEVEL = 80 GO CREATE TABLE Bar ( Id UNIQUEIDENTIFIER NOT NULL, TestNvcMax NVARCHAR (MAX) NOT NULL, -- Arrived in SQL 2005 TestDateTime2 DATETIME2 (7) NOT NULL -- Arrived in SQL 2008 ) GO
But this table produces perfectly - any ideas? I'd have believed some type of a mistake message or warning could have been appropriate
Here read concerning the variations between compatibility level 80, 90 and 100. ALTER DATABASE Compatibility Level
Apparently new data types isn't affected. I believe that compatibility level can there be to create SQL Server "behave" such as the older version, not stop you from doing new fancy stuff.
Compatibility level provides only partial backward compatibility with earlier versions of SQL Server.
New functionality might work under older compatibility levels, but SET options may need changes.
In my opinion that's your situation.