Geek Answers Handbook

Why is the new T-SQL SQL Server 2008 working with the database in compatibility mode 80?

While experimenting with new T-SQL features, I came across a mystery. Here are some of the new syntaxes supported by SQL 2008, and I expect it to work with the databases set for compatibility mode 100 (i.e. 2008), and not work for compatibility mode 80 (i.e. 2000). However, this works for the database set for SQL SERVER 2000 compatibility mode on an instance of SQL 2008 standard version:

use MDS -- this db is compat mode 80 go CREATE TABLE dbo.Employees ( Name VARCHAR(50) NULL, Email VARCHAR(50) NULL, Salary money NULL ) INSERT INTO dbo.Employees(Name, Email, Salary) VALUES('Scott', ' scott@example.com ', 50000.00), ('Jisun', ' jisun@example.com ', 225000.00), ('Alice', ' al@example.com ', 75000.00), ('Sam', ' sam@example.com ', 45000.00) SELECT * FROM dbo.Employees drop table dbo.Employees
+4
source share
3 answers

The compatibility mode parameter is used to control some relatively unclear (imho) aspects of the behavior of the databae engine. It does not block or prevent the use of T-SQL extensions in databases migrated from previous versions — for example, a database that has been backed up to SQL 2000 and restored to SQL 2008 will support CTE and the new INSERT multi-values.

Actual information about what is controlled by the database compatibility mode is stored in several articles in Books Online; this is a good starting point to explore this.

+1
source

I noticed this too and found this statement on MSDN :

Sets the behavior of certain databases compatible with the specified version of SQL Server.

Compatibility mode affects “specific” behavior, not ALL behavior. See the ALTER DATABASE documentation for more details.

+2
source

I do not see how this can work in compatibility mode 80. Try adding this line before creating the table:

 sp_dbcmptlevel 'MDS'

Does it return 80?

0
source

More articles:


All Articles