OlĂ¡ pessoal,
Boa tarde!
Neste post vou demonstrar a vocĂªs como resolver de forma simples e rĂ¡pida um problema que apesar de ser simples e a mensagem ser bem clara, jĂ¡ vi muitos analistas nĂ£o sabendo como resolver.
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1_CS_AS” in the equal to operation.
O que Ă© uma COLLATION?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).
ReferĂªncias:
https://docs.microsoft.com/pt-br/sql/t-sql/statements/collations
https://docs.microsoft.com/pt-br/sql/relational-databases/collations/collation-and-unicode-support
Simulando o erro
Uma forma fĂ¡cil de simular esse erro Ă© criando uma tabela onde duas ou mais colunas possuem collations diferentes e tentar fazer uma comparaĂ§Ă£o no WHERE entre essas colunas, uma concatenaĂ§Ă£o ou criar duas tabelas com colunas de collations diferentes e tentar realizar um join entre as 2 colunas.
Vale lembrar que esse erro sĂ³ acontece ao comparar ou manipular duas colunas de TEXTO (VARCHAR, NVARCHAR, CHAR, etc) de collations diferentes.
1 2 3 4 5 6 7 8 9 10 11 12 |
IF (OBJECT_ID('tempdb..#Teste1') IS NOT NULL) DROP TABLE #Teste1 CREATE TABLE #Teste1 ( Id INT IDENTITY(1,1), Nome1 VARCHAR(100), Nome2 VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS ) INSERT INTO #Teste1 SELECT 'SQL Server', 'Oracle' SELECT A.Nome2 + ' Ă© melhor que ' + A.Nome1 FROM #Teste1 A |
Realizando a consulta sem alterar estrutura
Uma forma simples, rĂ¡pida e prĂ¡tica para conseguir contornar esse problema de forma provisĂ³ria ou quando vocĂª nĂ£o tem acesso para alterar a estrutura da tabela/database, Ă© utilizar o operador COLLATE no prĂ³prio select, que irĂ¡ converter todos os dados da coluna para um determinado collation e depois poderĂ¡ comparar e trabalhar com os dados normalmente.
1 2 |
SELECT A.Nome1 COLLATE SQL_Latin1_General_CP1_CS_AS + ' Ă© melhor que ' + A.Nome2 FROM #Teste1 A |
VocĂª ainda pode utilizar o collation DATABASE_DEFAULT nas duas colunas envolvidas, para garantir a compatibilidade entre elas, convertendo as duas colunas para a mesma collation do banco:
1 2 3 |
SELECT * FROM #Teste1 A LEFT JOIN #Teste1 B ON A.Nome1 COLLATE DATABASE_DEFAULT = B.Nome2 COLLATE DATABASE_DEFAULT |
Essa Ă© uma soluĂ§Ă£o provisĂ³ria e de contorno, que nĂ£o deve ser aplicada em grandes volumes de dados, uma vez que a performance nĂ£o Ă© ideal, jĂ¡ que toda a coluna precisa ser lida e convertida para depois trabalhar com o dado.
Como identificar o COLLATION do database
O primeiro passo ao identificar um problema de conflito de collation entre databases, Ă© identificar primeiro qual o collation dos databases envolvidos para tentar entender se o problema estĂ¡ neste posto. Para se identificar o collation de um database, podemos utilizar o comando sp_helpdb:
TambĂ©m podemos utilizar a view de catĂ¡logo sys.databases:
1 2 3 |
SELECT name, collation_name, compatibility_level FROM sys.databases WHERE name IN ('master', 'msdb', 'model', 'tempdb') |
E tambĂ©m utilizar a funĂ§Ă£o DATABASEPROPERTYEX:
1 |
SELECT DATABASEPROPERTYEX('master', 'Collation') |
Como identificar o COLLATION de uma coluna
ApĂ³s analisar o collation dos databases envolvidos e constatar que jĂ¡ estĂ£o utilizando a mesma codificaĂ§Ă£o, vamos analisar agora as colunas envolvidas.
Existem vĂ¡rias formas de realizar essa verificaĂ§Ă£o, como a sys.columns:
1 2 3 4 |
SELECT A.name, A.column_id, B.name, A.max_length, A.[precision], A.scale, A.collation_name FROM msdb.sys.columns A JOIN msdb.sys.types B ON A.user_type_id = B.user_type_id WHERE [object_id] = OBJECT_ID('msdb.dbo.sysjobs') |
TambĂ©m podemos obter essa informaĂ§Ă£o analisando as views do database INFORMATION_SCHEMA:
1 2 3 |
SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE, COLLATION_NAME FROM msdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'sysjobs' |
Como alterar o COLLATION da coluna
Uma soluĂ§Ă£o definitiva para resolver o problema do collation reportado Ă© alterar a coluna que possui a codificaĂ§Ă£o de caracteres diferente do restante, de forma a padronizar a tabela para que todas as colunas utilizem a mesma codificaĂ§Ă£o.
Para fazer isso, basta utilizar o comando abaixo:
1 2 |
ALTER TABLE #Teste1 ALTER COLUMN Nome2 VARCHAR(100) COLLATE DATABASE_DEFAULT |
Com esse comando, estamos alterando a coluna Nome2 do nosso exemplo para a mesma collation do database. Caso vocĂª necessite, pode alterar a collation para uma de sua escolha, ignorando o collation padrĂ£o do database:
1 2 |
ALTER TABLE #Teste1 ALTER COLUMN Nome2 VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI |
Como alterar o COLLATION do Database
Uma outra soluĂ§Ă£o definitiva para esse problema de conflito entre collations, Ă© alterando o collation default do database.
Isso Ă© especialmente Ăºtil quando as colunas nĂ£o possuem definiĂ§Ă£o de collation (utilizando a padrĂ£o do database) e vocĂª estĂ¡ realizando joins e/ou manipulaĂ§Ă£o com strings entre colunas de databases diferentes, com collations diferentes e estĂ¡ enfrentando esse problema.
Exemplo de comando para alterar o collation do database:
1 |
ALTER DATABASE MeuBanco COLLATE SQL_Latin1_General_CP1_CS_AS |
E Ă© isso aĂ, pessoal!
Espero que tenham gostado do post!
Qualquer dĂºvida, Ă© sĂ³ deixar seu comentĂ¡rio
Abraço e atĂ© a prĂ³xima!