Search code examples
sqlsql-servert-sqlsql-server-2008compatibility-mode

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


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

Solution

  • 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.