Casos do Dia a Dia – Erro de Collation em uma Consulta
Fala Pessoal,
O foco desse post não é explicar o que é uma collation, mas sim contar uma experiência de duas situações que já passei.
Quando instalamos o SQL Server ele possui uma collation default chamada SQL_Latin1_General_CP1_CI_AS. Eu particularmente, utilizo na maioria das vezes a collation SQL_Latin1_General_CP1_CI_AI.
Agora, imaginem que vocês por algum motivo possuam bases com diferentes collations em um servidor e precisem de dar um join entre duas tabelas dessas bases. O que você faria?
Para simular essa situação, criei duas databases com collations diferentes:
- DBTeste1 com a collate SQL_Latin1_General_CP1_CS_AS
- DBTeste2 com a collate SQL_Latin1_General_CP1_CI_AI
Em seguida, criei uma tabela em cada database para tentar fazer um join entre elas por um campo char.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE DBTeste1 USE DBTeste1 CREATE TABLE Cliente(Nr_CPF CHAR(11)) INSERT INTO Cliente VALUES('11111111111') GO USE DBTeste2 CREATE TABLE Funcionario(Nr_CPF CHAR(11)) INSERT INTO Funcionario VALUES('11111111111') |
Ao tentar executar esse join:
|
1 2 3 |
SELECT * FROM DBTeste1..Cliente A JOIN DBTeste2..Funcionario B ON A.Nr_CPF = B.Nr_CPF |
Recebi o erro abaixo:
Nessa mesma hora alguém vai te perguntar se é possível alterar a Collation de uma dessas databases (isso já aconteceu comigo). Entretanto, alterar a collation de uma database pode gerar problemas em outras queries.
Para sair dessa situação, você pode converter a collation de uma das tabelas antes de realizar a comparação no join:
|
1 2 3 |
SELECT * FROM DBTeste1..Cliente A JOIN DBTeste2..Funcionario B on A.Nr_CPF COLLATE SQL_Latin1_General_CP1_CI_AI = B.Nr_CPF |
Parece mágica mas agora a query rodou com sucesso. Nesse exemplo, o comando collate SQL_Latin1_General_CP1_CI_AI realiza algo parecido com um CAST, convertendo a coluna A.Nr_CPF para outra collation.
Outra situação em que essa conversão me ajudou foi a seguinte:
Uma pessoa estava precisando retirar todos os acentos de uma coluna para utilizar em uma comparação e também me falaram que alguém já tinha feito uma função massa que varria toda a string e fazia essa operação.
Contudo, bastava converter a collation conforme o exemplo abaixo para retirar toda acentuação dessas colunas:
|
1 2 3 |
Declare @String varchar(30) Set @String = 'aeiouáéíóúàèìòòâêîôûãõäëïöüç' Select @String collate sql_latin1_general_cp1251_ci_as |
Acho que esse código ficou um pouco mais rápido de se fazer do que uma função que varre toda a string. =)
OBS: Eu já tinha visto esse script em algum lugar na Internet.
Gostou dessa Dica?
Curta, comente, compartilhe…
Assine meu canal no Youtube e curta minha página no Facebook para receber Dicas de Leituras, Vídeos e Eventos sobre SQL Server.
Confira mais experiências do Dia a Dia de um DBA no meu Treinamento de Tarefas do Dia a Dia de um DBA.
Até a próxima.
Fabrício Lima
MCITP – Database Administrator
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
Boa Fabricio. Já passei por isso num servidor com SQL Server 2008.
Curiosamente, alguns dias após ter acontecido esse problema e ter resolvido o mesmo, fui fazer a prova de certificação e tinha uma questão falando justamente disso.
Continue com o blog. Muito bom mesmo.
Obrigado Marco.
Não sabia que isso também era questão de prova.
Cara, esse script de remoção de acentos é genial.
Parabéns!
Obrigado Felipe!!!
Achei na Internet… =)
Bom caso! Isso além de ser frequente também cai em prova de certificação (70-432). O segundo collate não conhecia, valeu por compartilhar!
Obrigado Renato.
vc poderia me ajudar, estou tendo este problema com uma tabela ja tentei de tudo e nao consigo resolver.
olha só o script
Use MuOnline;
SELECT MEMB_STAT.memb___id, MEMB_STAT.ConnectTM, MEMB_STAT.ServerName,
AccountCharacter.GameIDC, Character.Resets as Resets
FROM MuOnline.dbo.MEMB_STAT JOIN MuOnline.dbo.AccountCharacter
ON (MEMB_STAT.memb___id = AccountCharacter.Id) JOIN MuOnline.dbo.
Character ON (AccountCharacter.GameIDC = Character.Name) WHERE MEMB_STAT.ConnectStat = 1
no caso a coluna Memb_ID da Character
Cannot resolve the collation conflict between “Chinese_PRC_CI_AS” and “Latin1_General_CI_AS” in the equal to operation
nao consigo fazer pois tenho dados na bd e nao vai.
Muito boa a dica!
Obrigada.
Que bom que ajudou Priscila.
Obrigado.
Cara precisei comentar que vc salvou meu dia! Obrigado mesmo!!!
Massa!!! Que bom que ajudei!
Excelente, muito obrigado por compartlhar…
Obrigado Ricardo!