Query From Hell – Como 100 é menor que 11???

Você sabia que 100 é maior que 10, mas 100 é menor que 11, e mesmo que 1000 seja maior que 100, 1000 ainda será menor que 99? Não entendeu? Acha que estou ficando louco, então veja bem isso:

Comparação de texto	Comparação de valores numéricos

Não se trata de um bug, pois o algoritmo de comparação de textos compara caracteres por caracteres iniciando pela esquerda, isso quer dizer que “ANA BEATRIZ” é menor que “ANA PAULA” visto que “B” é menor que “P” (a mesma comparação utilizada pelo ORDER BY).

O fato do texto “10” ser menor que “9” pode soar meio estranho, mas o primeiro caractere dos dois textos diz que “1” é menor que “9”, logo o texto “10” é menor que “9”, mesmo que o valor numérico 10 seja menor que o valor numérico 9.

Para demonstrar melhor este fato, segue um esboço de como funciona o algoritmo de comparação de texto:

Muitos problemas podem surgir quando trabalhamos com campos do tipo texto que deveriam ser tratado como numéricos, como no caso da utilização da funções MIN/MAX ou até mesmo no ORDER BY, que pode ter resultados bem diferentes da comparação feita com valores numéricos:

Ignorando letras maiúsculas, minúsculas, acentos e ç

No SQL Server, COLLATIONs são estruturas que definem regras para os algoritmos de pesquisa e ordenação das consultas, isso quer dizer que quando se compara textos no WHERE ou ordena as consultas no ORDER BY, o COLLATION do banco de dados definirá se o algoritmo da consulta diferenciará entre letras maiúsculo-minúsculas, acentos, ç-c e outras regras não muito úteis para idiomas latinos.

Existem três “hierarquias” básicas na utilização de COLLATIONs:

Banco de dados

Nível mais geral para especificar o COLLATE utilizado pelas consultas. Podemos identificar os COLLATEs de todos os banco de dados de uma instância por meio da seguinte query:

SELECT name, collation_name FROM sys.databases

E podemos definir na criação do banco de dados ou alterar com a DDL ALTER DATABASE, exemplo:

ALTER DATABASE BancoDeDados COLLATE Latin1_General_CS_AI

Coluna

Quando criamos uma coluna numa tabela e não definimos seu COLLATE, ela obedecerá ao COLLATE do banco de dados. Podemos identificar os COLLATEs das colunas por meio da seguinte query:

SELECT OBJECT_NAME(object_id), name, collation_name FROM sys.columns WHERE collation_name IS NOT NULL

E podemos definir o na criação ou alteração da tabela.

Consulta

Em nível de consulta, podemos utilizar COLLATEs específicos para comparação e ordenação, exemplos:

SELECT name FROM clients WHERE name LIKE 'A%' COLLATE Latin1_General_BIN
SELECT name FROM clients ORDER BY name COLLATE Latin1_General_CI_AI

Tanto nas comparações de textos quanto nas ordenações, podemos alterar estas regras de COLLATE, se for necessário. Mas se for necessário alterar estas regras de COLLATE muitas vezes, verifique se não é melhor mudar o COLLATE do banco de dados ou alterar o COLLATE das colunas.

Conhecendo um pouco da estrutura dos COLLATEs

Quando os textos precisam ser idênticos a nível binário, podemos utilizar os COLLATEs terminados com BIN, exemplo:

SELECT
CASE WHEN 'Produção' = 'Produção' COLLATE Latin1_General_BIN
THEN 1
ELSE 0 END

Quando os textos precisam ser idênticos em nível de minúsculas/maiúsculas, podemos utilizar os COLLATEs com CS:

SELECT
CASE WHEN 'Produção' = 'Produção' COLLATE SQL_Latin1_General_CP1_CS_AS
THEN 1
ELSE 0 END

Para ignorar a diferença entre as letras maiúsculas e minúsculas, utilizamos COLLATEs com CI:

SELECT
CASE WHEN 'Produção' = 'produção' COLLATE SQL_Latin1_General_CP1_CI_AS
THEN 1
ELSE 0 END

Quando precisamos ignorar acentos, COLLATES com AI (insensibilidade a acentos):

SELECT
CASE WHEN 'Produção' = 'produçao' COLLATE SQL_Latin1_General_CP1_CI_AI
THEN 1
ELSE 0 END

Quando precisamos ignorar a diferença até do ç e c, recomendo a utilização de COLLATEs com CI e AI do sistema operacional, exemplo:

SELECT
CASE WHEN 'Produção' = 'producao' COLLATE Latin1_General_CI_AI
THEN 1
ELSE 0 END