[Troubleshooting] Diferenças de comportamento da opção 'QUOTE_IDENTIFIER'

Este problema ocorreu na verdade há alguns anos, logo que comecei a trabalhar como PFE, acho que por isso mesmo lembro detalhadamente dessas informações. Com novas versões sendo lançadas no mínimo a cada dois anos, quero procurar manter os posts focados em novas versões e funcionalidades, mas como se trata de um post sobre troubleshooting, não acho errado falar um pouquinho sobre uma versão ainda muito comum na maioria dos clientes que atuo (SQL Server 2008 R2).
Quero deixar este post como referência caso alguém passe pela mesma situação algum dia. Se você encontrou este post porque está passando por este problema: Lucky you, enjoy! J

Cenário:

Cliente X desejava executar uma procedure que manipulava informações em formato XML. A instância SQL Server havia sido migrada para SQL Server 2008 R2 há pouco menos de uma semana.

Problema:

Foi reportado o seguinte erro, ao tentar executar uma procedure com processamento de informações XML:

SELECT failed because the following SET options have incorrect settings:
'QUOTE_IDENTIFIER'. Verify set options are correct for use indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data types methods and/or spatial index operations.

Informações adicionais:

• Tal erro persistia mesmo com a opção SET QUOTED_IDENTIFIER ON sendo definida no nível da sessão;
• A mensagem ocorre apenas quando o banco de dados está em Compatibility Level 100. Quando o banco de dados é reconfigurado para Compatibility Level 90 (SQL 2005) a procedure era executada com sucesso.

Explicação:

Após alguns testes concluímos que a procedure havia sido criada com a opção SET QUOTED_IDENTIFIER OFF. Tal conclusão se dá pelo fato de que o SQL Server 2008 utiliza a opção QUOTED_IDENTIFIER utilizada no momento da criação da stored procedure, ou seja, mesmo forçando o SET QUOTED_IDENTIFIER ON na sessão que está executando a procedure, o SQL Server ignora a opção da sessão e passa a utilizar apenas o que foi definido no momento da criação.
Já o SQL Server 2005, utiliza a opção da sessão que está executando a procedure, independente do que foi definido na criação do objeto. Como todas as sessões abertas contra o banco de dados, por padrão, são QUOTED_IDENTIFIER ON, forçando ou não a utilização desta opção, a procedure é executada com sucesso em bancos de dados com o Compatibility Level 90.
 

Recomendação:

Para resolução deste problema recomendamos o cliente a manter o banco de dados em Compatibility Level 100 e alterar a procedure incluindo a opção SET QUOTED_IDENTIFIER ON.
Dessa forma, o código de alteração da procedure ficaria da seguinte forma:

SET QUOTED_IDENTIFIER ON;

GO

ALTER PROCEDURE …

Referências:

SET QUOTED_IDENTIFIER (Transact-SQL)
https://msdn.microsoft.com/pt-br/library/ms174393.aspx

Differences Between Lower Compatibility Levels and Level 100
https://msdn.microsoft.com/en-us/library/bb510680.aspx