Why does new T-SQL of SQL Server 2008 work on database in compatability mode 80 ?

Question

Experimenting with new features of T-SQL, I've run into a puzzle. Here is some new syntax supported by SQL 2008 and I'd expect it to work on databases set for compatibility mode 100 (i.e. 2008) and not work for compat mode 80 (i.e. 2000). Yet this works for a database set for SQL SERVER 2000 compatibility mode on a SQL 2008 instance of Standard edition:

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', '[email protected]', 50000.00),
  ('Jisun', '[email protected]', 225000.00),
  ('Alice', '[email protected]', 75000.00),
  ('Sam', '[email protected]', 45000.00)

SELECT * FROM dbo.Employees  
drop table dbo.Employees

Answer 1

I don't see how this could work in compat mode 80. Try adding this line before you create your table:

sp_dbcmptlevel 'MDS'

Does it return 80?

Answer 2

I noticed this too, and found this statement on MSDN:

Sets certain database behaviors to be compatible with the specified version of SQL Server.

The compatibility mode affects "certain" behaviors, and not ALL behaviors. See the ALTER DATABASE documentation for details.

Answer 3

The compatibility mode setting is used to control certain relatively obscure (imho) aspects of databae engine behavior. It does not block or prevent the use of extensions to the T-SQL language from being used on databases migrated from prior versions--for example, a database backed up from SQL 2000 and restored on SQL 2008 will support CTEs and the new multi-value INSERT statements.

The actual details of what is controlled by the database compatibility mode are buried in several articles in Books Online; this is a good starting place for researching this.