Qual é o comportamento real do nível de compatibilidade 80?

Dos documentos :

Define determinados comportamentos de banco de dados para serem compatíveis com a versão especificada do SQL Server.
...
O nível de compatibilidade fornece apenas compatibilidade parcial com versões anteriores do SQL Server. Use o nível de compatibilidade como um auxílio de migração provisório para contornar diferenças de versão nos comportamentos controlados pela configuração de nível de compatibilidade relevante.

Na minha interpretação, o modo de compatibilidade é sobre comportamento e análise de sintaxe, não para coisas como o analisador dizendo: "Ei, você não pode usar ROW_NUMBER()!" Às vezes, o nível de compatibilidade mais baixo permite que você continue fugindo com a sintaxe não mais suportada e, às vezes, impede que você use novas construções de sintaxe. A documentação lista vários exemplos explícitos, mas aqui estão algumas demonstrações:

Passando funções internas como argumentos de função

Este código funciona no nível de compatibilidade 90+:

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL);

Mas em 80 rende:

Msg 102, Level 15, State 1
Sintaxe incorreta perto de '('.

O problema específico aqui é que em 80 você não tem permissão para passar uma função interna para uma função. Se você quiser permanecer no modo de compatibilidade 80, poderá contornar isso dizendo:

DECLARE @db_id INT = DB_ID();

SELECT * 
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, NULL);

Passando um tipo de tabela para uma função com valor de tabela

Semelhante ao acima, você pode obter um erro de sintaxe ao usar um TVP e tentar passá-lo para uma função com valor de tabela. Isso funciona em níveis de compatibilidade modernos:

CREATE TYPE dbo.foo AS TABLE(bar INT);
GO
CREATE FUNCTION dbo.whatever
(
  @foo dbo.foo READONLY
)
RETURNS TABLE
AS 
  RETURN (SELECT bar FROM @foo);
GO

DECLARE @foo dbo.foo;
INSERT @foo(bar) SELECT 1;
SELECT * FROM dbo.whatever(@foo);

No entanto, altere o nível de compatibilidade para 80 e execute as três últimas linhas novamente; você recebe esta mensagem de erro:

Msg 137, Level 16, State 1, Line 19
Deve declarar a variável escalar "@foo".

Não é realmente nenhuma boa solução em cima da minha cabeça, além de atualizar o nível de compatibilidade ou obter os resultados de uma maneira diferente.

Usando nomes de coluna qualificados em APPLY

No modo de compatibilidade 90 ou superior, você pode fazer isso sem problemas:

SELECT * FROM sys.dm_exec_cached_plans AS p
  CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t;

No entanto, no modo de compatibilidade 80, a coluna qualificada entregue à função gera um erro de sintaxe genérico:

Msg 102, Level 15, State 1
Sintaxe incorreta perto de '.'.

ORDER BY um alias que corresponde a um nome de coluna

Considere esta consulta:

SELECT name = REVERSE(name), realname = name 
FROM sys.all_objects AS o
ORDER BY o.name;

No modo de compatibilidade 80, os resultados são os seguintes:

001_ofni_epytatad_ps   sp_datatype_info_100
001_scitsitats_ps      sp_statistics_100
001_snmuloc_corps_ps   sp_sproc_columns_100
...

No modo de compatibilidade 90, os resultados são bem diferentes:

snmuloc_lla      all_columns
stcejbo_lla      all_objects
sretemarap_lla   all_parameters
...

A razão? No modo de compatibilidade 80, o prefixo da tabela é totalmente ignorado, portanto, é ordenado pela expressão definida pelo alias na SELECTlista. Nos níveis de compatibilidade mais recentes, o prefixo da tabela é considerado, de modo que o SQL Server realmente usará essa coluna na tabela (se for encontrada). Se o ORDER BYalias não for encontrado na tabela, os níveis de compatibilidade mais recentes não perdoam a ambiguidade. Considere este exemplo:

SELECT myname = REVERSE(name), realname = name 
FROM sys.all_objects AS o
ORDER BY o.myname;

O resultado é ordenado pela mynameexpressão em 80, pois novamente o prefixo da tabela é ignorado, mas em 90 gera esta mensagem de erro:

Msg 207, Level 16, State 1, Line 3
Nome de coluna inválido 'myname'.

Tudo isso também é explicado na documentação :

Ao associar as referências de coluna na ORDER BYlista às colunas definidas na SELECTlista, as ambiguidades de coluna são ignoradas e os prefixos de coluna às vezes são ignorados. Isso pode fazer com que o conjunto de resultados retorne em uma ordem inesperada.

Por exemplo, uma ORDER BYcláusula com uma única coluna de duas partes ( <table_alias>.<column>) que é usada como referência a uma coluna em uma lista SELECT é aceita, mas o alias da tabela é ignorado. Considere a seguinte consulta.

SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1

Quando executado, o prefixo da coluna é ignorado no arquivo ORDER BY. A operação de classificação não ocorre na coluna de origem especificada ( x.c1) conforme o esperado; em vez disso, ocorre na derivadac1coluna que é definida na consulta. O plano de execução para esta consulta mostra que os valores da coluna derivada são calculados primeiro e, em seguida, os valores calculados são classificados.

ORDER BY algo que não está na lista SELECT

No modo de compatibilidade 90 você não pode fazer isso:

SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
UNION ALL
SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
ORDER BY a.name;

Resultado:

Msg 104, Level 16, State 1 Os
itens ORDER BY devem aparecer na lista de seleção se a instrução contiver um operador UNION, INTERSECT ou EXCEPT.

Em 80, porém, você ainda pode usar essa sintaxe.

Junções externas antigas e nojentas

O modo 80 também permite que você use a antiga e obsoleta sintaxe de junção externa ( *=/=*):

SELECT o.name, c.name
FROM sys.objects AS o, sys.columns AS c
WHERE o.[object_id] *= c.[object_id];

No SQL Server 2008 / 2008 R2, se você estiver em 90 ou superior, receberá esta mensagem detalhada:

Msg 4147, Level 15, State 1
A consulta usa operadores de junção externa não ANSI (" *=" ou " =*"). Para executar esta consulta sem modificação, defina o nível de compatibilidade do banco de dados atual para 80, usando a opção SET COMPATIBILITY_LEVEL de ALTER DATABASE. É altamente recomendável reescrever a consulta usando operadores de junção externa ANSI (LEFT OUTER JOIN, RIGHT OUTER JOIN). Nas versões futuras do SQL Server, os operadores de junção não ANSI não terão suporte, mesmo em modos de compatibilidade com versões anteriores.

No SQL Server 2012, isso não é mais uma sintaxe válida e produz o seguinte:

Msg 102, Level 15, State 1, Line 3
Sintaxe incorreta perto de '*='.

Claro que no SQL Server 2012 você não pode mais contornar esse problema usando o nível de compatibilidade, já que 80 não é mais suportado. Se você atualizar um banco de dados no modo de compatibilidade 80 (por atualização in-loco, desanexar/anexar, backup/restaurar, envio de log, espelhamento, etc.), ele será atualizado automaticamente para 90 para você.

Dicas de mesa sem COM

No modo de compatibilidade 80, você pode usar o seguinte e a dica da tabela será observada:

SELECT * FROM dbo.whatever NOLOCK; 

Em 90+, isso NOLOCKnão é mais uma dica de tabela, é um alias. Caso contrário, isso funcionaria:

SELECT * FROM dbo.whatever AS w NOLOCK;

Mas não:

Msg 1018, Level 15, State 1
Sintaxe incorreta perto de 'NOLOCK'. Se isso for parte de uma dica de tabela, a palavra-chave A WITH e os parênteses agora serão necessários. Consulte os Manuais Online do SQL Server para obter a sintaxe adequada.

Agora, para provar que o comportamento não é observado no primeiro exemplo no modo de compatibilidade 90, use o AdventureWorks (certificando-se de que está em um nível de compatibilidade mais alto) e execute o seguinte:

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader UPDLOCK;
SELECT * FROM sys.dm_tran_locks 
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 0
COMMIT TRANSACTION;

BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader WITH (UPDLOCK);
SELECT * FROM sys.dm_tran_locks
  WHERE request_session_id = @@SPID
  AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 2
COMMIT TRANSACTION;

Este é particularmente problemático porque o comportamento muda sem uma mensagem de erro ou mesmo um erro. E também é algo que o consultor de atualização e outras ferramentas podem nem perceber, já que, pelo que sabe, é um alias de tabela.

Conversões envolvendo novos tipos de data/hora

Os novos tipos de data/hora introduzidos no SQL Server 2008 (por exemplo date, e datetime2) suportam um intervalo muito maior do que o original datetimee smalldatetime). As conversões explícitas de valores fora do intervalo suportado falharão, independentemente do nível de compatibilidade, por exemplo:

SELECT CONVERT(SMALLDATETIME, '00010101');

Rendimentos:

Msg 242, Level 16, State 3
A conversão de um tipo de dados varchar em um tipo de dados smalldatetime resultou em um valor fora do intervalo.

No entanto, as conversões implícitas funcionarão nos níveis de compatibilidade mais recentes. Por exemplo, isso funcionará em 100+:

SELECT DATEDIFF(DAY, CONVERT(SMALLDATETIME, SYSDATETIME()), '00010101');

Mas em 80 (e também em 90), gera um erro semelhante ao acima:

Msg 242, Level 16, State 3
A conversão de um tipo de dados varchar em um tipo de dados datetime resultou em um valor fora do intervalo.

Cláusulas FOR redundantes em gatilhos

Este é um cenário obscuro que surgiu aqui . No modo de compatibilidade 80, isso será bem-sucedido:

CREATE TABLE dbo.x(y INT);
GO
CREATE TRIGGER tx ON dbo.x
FOR UPDATE, UPDATE
------------^^^^^^ notice the redundant UPDATE
AS PRINT 1;

Na compatibilidade 90 e superior, isso não é mais analisado e, em vez disso, você recebe a seguinte mensagem de erro:

Msg 1034, Level 15, State 1, Procedure tx
Erro de sintaxe: Especificação duplicada da ação "UPDATE" na declaração do trigger.

PIVOT/UNPIVOT

Algumas formas de sintaxe não funcionarão abaixo de 80 (mas funcionam bem em 90+):

SELECT col1, col2
FROM dbo.t1
UNPIVOT (value FOR col3 IN ([x],[y])) AS p;

Isso rende:

Msg 156, Level 15, State 1
Sintaxe incorreta perto da palavra-chave 'for'.

Para algumas soluções alternativas, incluindo CROSS APPLY, consulte estas respostas .

Novas funções integradas

Tente usar novas funções como TRY_CONVERT()em um banco de dados com nível de compatibilidade < 110. Elas simplesmente não são reconhecidas lá.

SELECT TRY_CONVERT(INT, 1);

Resultado:

Msg 195, Level 15, State 10
'TRY_CONVERT' não é um nome de função interno reconhecido.

Recomendação

Only use 80 compatibility mode if you actually need it. Since it will no longer be available in the next version after 2008 R2, the last thing you want to do is write code in this compat level, rely on the behaviors you see, and then have a whole bunch of breakage when you can no longer use that compat level. Be forward thinking and don't try to paint yourself into a corner by buying time to continue using old, deprecated syntax.

Compatibility Levels are only present to allow a controlled migration from an earlier version of SQL Server. Compat Level 90 does not preclude using new features, it simply means certain aspects of the database are retained in a way that is compatible with how SQL Server 2005 worked.

See http://msdn.microsoft.com/en-us/library/bb510680.aspx for more info.