Microsoft SQL Server – Entendendo o Compatibility Level

[mr-renato]

O Compatibility Level é uma propriedade dos Bancos de Dados do SQL Server que permite, por exemplo, que um Database criado no SQL Server 2008 seja migrado para uma instalação do SQL Server 2019 sem a necessidade de ajustes na aplicação. Isso acontece porque o Compatibility Level indica quais comandos e chamadas são compatíveis com determinado database.

Neste exemplo vamos analisar um Database residente em um servidor com o SQL Server 2008 R2 instalado, porem a base está com o Compatibility Level = 80 (equivalente ao SQL Server 2000).

SELECT @@Version

Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Embora alguns comandos, objetos e datatypes não existissem no SQL 2000 e 2005 (foram introduzidos somente a partir do SQL 2008), o simples fato da base de dados residir em uma instalação do SQL 2008 faz com que esses comandos e objetos funcionem, mesmo quando a base tem um compatibility level inferior.
Lembrando que essa base está com o compatibility level = SQL 2000

-- Atribuição de valor na declaração só existe a partir do SQL 2008 DECLARE @Variavel int = 123456 Print @Variavel GO

--Datatypes Date, XML, etc… foram introduzidos do SQL 2005 e 2008

CREATE TABLE DataVenda 
( dia Date,
  tags XML )
GO

INSERT DataVenda
Values( '20191028',  
'<Venda> 
<CodProd> 123 </CodProd>
</Venda>  ')
GO

SELECT * FROM DataVenda
GO

-- Cria tabela de produto
Create table Produto
(    codprod int,
     nome varchar(20))
GO

-- Cria tabela de vendas
Create table Vendas
(    codpedido int,
     codprod int,
     qtd int)
GO

-- Carrega dados em produto (inserção múltipla  não existia no 
-- SQL Server 2000/2005)
insert Produto
Values( 10, 'Leite'), 
      ( 20, 'Arroz'),
      ( 30, 'Ovos')
GO

-- Carrega dados em Vendas
insert Vendas
Values( 10050, 30, 12), 
       ( 10051, 20, 1 ),
       ( 10052, 20, 5 ),
       ( 10053, 30, 6 )
GO

-- O comando INNER JOIN retorna dados de relacionamento entre tabelas
-- INNER JOIN ANSI (funciona em qualquer versão)
SELECT * 
FROM Produto    
          INNER JOIN
     Vendas ON Vendas.codprod = Produto.codprod
GO

-- O comando OUTER JOIN retorna todos de uma das tabelas
-- OUTER JOIN ANSI (funciona em qualquer versão)
SELECT *
FROM Produto    
          LEFT JOIN
     Vendas ON Vendas.codprod = Produto.codprod
GO

-- OUTER JOIN non-ANSI (igual ao anterior, só funciona no SQL 2000)
SELECT *
FROM Produto, Vendas 
WHERE Vendas.codprod =* Produto.codprod

-- O comando PIVOT, por outro lado não funciona em bases com  
--  compatibility level inferior a 90
SELECT *
FROM Produto
Pivot ( sum(codProd) FOR Nome IN ([Leite], [Arroz], [Ovos])) as PVT
go

-- Alterando o Compatibility Level
ALTER DATABASE Renato
SET COMPATIBILITY_LEVEL = 100
GO

Com essa mudança o comando PIVOT passa a funcionar, por outro lado o OUTER JOIN non-ANSI e consequentemente a proc apresentarão erro.

-- O comando PIVOT 
SELECT * FROM Produto
Pivot ( sum(codProd) FOR Nome IN ([Leite], [Arroz], [Ovos])) as PVT
go

-- OUTER JOIN non-ANSI
SELECT * 
FROM Produto, Vendas 
WHERE Vendas.codprod =* Produto.codprod
GO

Msg 4147, Level 15, State 1, Line 52
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this 
query without modification, please set the compatibility level for current
 database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER 
DATABASE. It is strongly recommended to rewrite the query using ANSI outer 
join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions
 of SQL Server, non-ANSI join operators will not be supported even in 
backward-compatibility modes.

A configuração de Compatibility Level é reversível, ou seja, se eu alterar novamente a configuração o OUTER JOIN non-ANSI volta a funcionar.

[mr-renato]

SQL Server