Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Como padronizar o Collation de todas as colunas do database

Post Views 18,739 views
Reading time 15 minutes

Hey Guys!
Tudo tranquilo?

Neste artigo, gostaria de compartilhar com vocês um problema que tive recentemente, o qual várias colunas, de várias tabelas de um determinado database utilizavam uma collation diferente do padrão do DB, fazendo com que ao realizar joins e condições WHERE entre colunas VARCHAR/CHAR/NVARCHAR com collations diferentes, o banco nos retorne a seguinte mensagem de erro:

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

O que é uma COLLATION?

O Collation nada mais é do que a forma de codificação de caracteres que um banco de dados utiliza para interpretá-los.

Um Collation é um agrupamento desses caracteres em uma determinada ordem (cada Collation tem uma ordem diferente), onde o “A” é um caracter diferente do “a”, caso o collation seja case-sensitive (diferenciação de maiúsculos e minúsculos) e o “a” é diferente do “á”, caso o collation seja Accent-Sensitive (diferenciação de acentos).

O COLLATION possui três níveis de hierarquia:
– Servidor
– Database
– Coluna

Caso o database seja criado sem especificar qual a collation que será utilizada, ele será criado com a collation do servidor (idioma do sistema operacional). Quando uma tabela é criada sem especificar o collation das colunas de texto (VARCHAR, NVARCHAR, CHAR, etc), o collate do database será utilizado como o collation das tabelas.

No SQL Server, o nome da Collation segue o seguinte padrão de nomeclatura:
SQL_CollationDesignator_CaseSensitivity_AccentSensitivity_KanatypeSensitive_WidthSensitivity

Exemplo de Collation:
SQL_Latin1_General_CP1_CS_AS

Onde:

  • CollationDesignator: Especifica as regras de agrupamento básicas usadas pelo agrupamento do Windows, onde as regras de classificação são baseadas no alfabeto ou no idioma.
  • CaseSensitivity: CI especifica que não diferencia maiúsculas de minúsculas, CS especifica que diferencia maiúsculas de minúsculas.
  • AccentSensitivity: AI especifica que não diferencia acentos, AS especifica que diferencia acento.
  • KanatypeSensitive: Omitido especifica que não faz distinção de caracteres kana, KS especifica que faz distinção de caracteres kana.
  • WidthSensitivity: Omitido especifica que não distingue largura, WS especifica que distingue largura.

Se uma coluna estiver utilizando uma COLLATION case sensitive (CS), uma query como SELECT * FROM Tabela WHERE Coluna LIKE ‘%Oracle%’ irá retornar o registro “Oracle”, mas não irá retornar o registro “oracle”.

A mesma coisa acontece com uma coluna utilizando um COLLATION accent sensitive (AS). Uma query como SELECT * FROM Tabela WHERE Coluna LIKE ‘%JOÃO%’ irá retornar o registro “João”, mas não irá retornar o registro “Joao”.

Para verificar a lista completa de Collations por região e idioma, acesse este link, lembrando que o mais utilizado no idioma Português (Brasil) é o SQL_Latin1_General_CP1_CI_AI (ou SQL_Latin1_General_CP1_CS_AS).

Para saber mais sobre o que é Collation, dê uma olhada no artigo SQL Server – Cannot resolve the collation conflict between … in the equal to operation..

Como identificar a collation padrão?

Para identificar a collation padrão de um database, basta utilizar a query abaixo:

Result:

Para identificar a collation padrão da sua instância, basta utilizar o comando abaixo:

Result:

Geração da base para os testes

Para padronizar os exemplos e ajudar no entendimento prático desse artigo, vou utilizar um script simples para gerar dados de exemplo (testes):

Como padronizar a collation de todas as colunas?

Caso você queira padronizar a collation de todas as colunas de um database, de modo que elas utilizem uma collation específica ou a padrão do database, desenvolvi um script que irá identificar todas as colunas que estão fora do padrão e irá executar um comando de ALTER TABLE para aplicar a alteração.

Como existem dependências (constraints, índices e foreign keys), o script irá dropar os objetos dependentes identificados, aplicar as alterações e recriar esses objetos. Por conta disso, recomendo fortemente que você teste bem antes de executar esse script em produção. De preferência, faça um backup de estrutura antes.

Script para alterar a collation de todas as colunas (e dependências) que estejam com o Collation diferente do database
Visualizar código-fonte

Script para alterar o collation de todas as colunas (e dependências) de acordo com o Collation desejado:
Visualizar código-fonte

Result:

Caso o script identifique muitas colunas para alterar e a saída do PRINT esteja ficando cortada por causa do tamanho das strings geradas, você pode utilizar a função fncSplitTexto, que disponibilizei no post Como quebrar um string em uma tabela de substrings utilizando um delimitador no SQL Server e substituir o final do script por esse trecho aqui:

Result:

Espero que tenham gostado desse post e até a próxima!
PS: Lembre de fazer o backup e testar BASTANTE se você planeja aplicar esse script em Produção.

Em caso de dúvidas ou problemas, deixe aqui nos comentários.