Collate e Ordenação - Firebird SQL

O que é Collate?

Muitos desenvolvedores confundem Charset com Collate. De forma simples:

Charset: Define quais caracteres você pode salvar no banco de dados (ISO8859_1, WIN1252, UTF8, etc.)
Collate: Define como esses caracteres são comparados e ordenados (ex: se 'A' é igual a 'a', se 'José' é igual a 'Jose', etc.)

O Collate vive na camada de comparação. Se você precisa que uma busca por "JOAO" retorne "João", você precisa de um collation Case Insensitive (CI) e Accent Insensitive (AI).

Relacionamento com Charset: O collate está sempre atrelado a um charset. Sem o charset, o banco não teria a regionalidade de ordenação, ou quais sinais diacríticos são iguais a suas versões sem esses sinais. Para entender melhor sobre charsets, consulte o artigo Qual Charset usar?.

Por que Collate é importante?

Imagine que você tem uma tabela de clientes e precisa fazer uma busca. Sem o collate adequado, você teria que fazer conversões custosas via software (como UPPER() ou LOWER() em todas as queries), o que impede que o banco utilize os índices de forma eficiente.

O collate define regras como:

Será que 'A' vem antes de 'á' na ordenação?
Será que 'Pharmacia' e 'Farmacia' são a mesma coisa?
Será que 'José' e 'Jose' também são iguais?
Será que 'A' é igual a 'a' em comparações?

Quem cria os collates define isso. No Brasil não temos essa coisa de 'Ph' de pharmacia dos anos 30, mas em outros países podem haver agrupamentos de caracteres (collate) que devam ser tratados conjuntamente e não de forma individual.

Tipos de Collate: Case e Accent Sensitivity

Os collates geralmente são classificados por duas características principais:

Case Sensitivity (CS/CI): Se diferencia maiúsculas de minúsculas
- CI (Case Insensitive): 'A' = 'a' (não diferencia maiúsculas/minúsculas)
- CS (Case Sensitive): 'A' ≠ 'a' (diferencia maiúsculas/minúsculas)
Accent Sensitivity (AS/AI): Se diferencia caracteres acentuados
- AI (Accent Insensitive): 'José' = 'Jose' (não diferencia acentos)
- AS (Accent Sensitive): 'José' ≠ 'Jose' (diferencia acentos)

Collates disponíveis no Firebird

Collates por Charset no FirebirdSQL:

Para UTF8:
- UCS_BASIC - Ordenação binária (posição na tabela Unicode)
- UNICODE - Ordenação alfabética usando UCA (Unicode Collation Algorithm)
- UTF8 - Padrão binário, idêntico ao UCS_BASIC
- UNICODE_CI - Case-insensitive (não diferencia maiúsculas/minúsculas)
- UNICODE_CI_AI - Case-insensitive e accent-insensitive (não diferencia maiúsculas/minúsculas nem acentos)
Para ISO8859_1:
- PT_BR - Português do Brasil (case-sensitive, accent-sensitive)
- PT_PT - Português de Portugal (case-sensitive, accent-sensitive)
- ISO8859_1 - Padrão binário
Para WIN1252:
- WIN_PTBR - Português do Brasil para Windows (case-sensitive, accent-sensitive)
- WIN1252 - Padrão binário

Exemplo prático: Criando tabelas com diferentes collates

IBExpert – Criar banco de dados:

C:\TEMP\TEST_COLLATE.FDB
CHARSET: UTF8
COLLATE: UNICODE_CI_AI
ALIAS: TEST_COLLATE.FDB
PORTA: 3040

Criar tabela com diferentes collates:

CREATE TABLE CLIENTES (
    ID INTEGER NOT NULL PRIMARY KEY,
    NOME_CS_AS VARCHAR(50) CHARACTER SET UTF8 COLLATE UNICODE,  -- Case e Accent Sensitive
    NOME_CI_AS VARCHAR(50) CHARACTER SET UTF8 COLLATE UNICODE_CI,  -- Case Insensitive, Accent Sensitive
    NOME_CI_AI VARCHAR(50) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,  -- Case e Accent Insensitive
    LOGIN VARCHAR(20) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI  -- Para buscas sem diferenciação
);

Inserindo dados de teste:

INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (1, 'José', 'José', 'José', 'jose.silva');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (2, 'JOSE', 'JOSE', 'JOSE', 'JOSE.SILVA');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (3, 'Jose', 'Jose', 'Jose', 'jose');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (4, 'Maria', 'Maria', 'Maria', 'maria');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (5, 'MARIA', 'MARIA', 'MARIA', 'MARIA');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (6, 'Farmacia', 'Farmacia', 'Farmacia', 'farmacia');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (7, 'Farmácia', 'Farmácia', 'Farmácia', 'farmacia2');

COMMIT WORK;

Testando comparações com diferentes collates

Teste 1: Busca Case Sensitive vs Case Insensitive

Vamos testar se 'Jose' encontra 'JOSE' dependendo do collate:

-- Busca na coluna Case Sensitive (UNICODE)
SELECT * FROM CLIENTES 
WHERE NOME_CS_AS = 'Jose';
-- Resultado: Apenas o registro com 'Jose' (minúsculo)

-- Busca na coluna Case Insensitive (UNICODE_CI)
SELECT * FROM CLIENTES 
WHERE NOME_CI_AS = 'Jose';
-- Resultado: 'Jose', 'JOSE' e 'José' (encontra variações de maiúsculas/minúsculas)

-- Busca na coluna Case e Accent Insensitive (UNICODE_CI_AI)
SELECT * FROM CLIENTES 
WHERE NOME_CI_AI = 'Jose';
-- Resultado: 'Jose', 'JOSE', 'José' (encontra todas as variações)

Teste 2: Busca Accent Sensitive vs Accent Insensitive

Vamos testar se 'Farmacia' encontra 'Farmácia':

-- Busca na coluna Accent Sensitive
SELECT * FROM CLIENTES 
WHERE NOME_CS_AS = 'Farmacia';
-- Resultado: Apenas 'Farmacia' (sem acento)

-- Busca na coluna Accent Insensitive (UNICODE_CI_AI)
SELECT * FROM CLIENTES 
WHERE NOME_CI_AI = 'Farmacia';
-- Resultado: 'Farmacia' e 'Farmácia' (encontra ambas as variações)

Teste 3: Ordenação com diferentes collates

Vamos ver como a ordenação muda dependendo do collate:

-- Ordenação Case Sensitive (UNICODE)
SELECT NOME_CS_AS FROM CLIENTES 
ORDER BY NOME_CS_AS;
-- Resultado: Ordena primeiro maiúsculas, depois minúsculas
-- Exemplo: 'FARMACIA', 'JOSE', 'MARIA', 'Farmacia', 'Jose', 'Maria', 'Farmácia', 'José'

-- Ordenação Case Insensitive (UNICODE_CI)
SELECT NOME_CI_AS FROM CLIENTES 
ORDER BY NOME_CI_AS;
-- Resultado: Ignora maiúsculas/minúsculas na ordenação
-- Exemplo: 'Farmacia', 'Farmácia', 'Jose', 'JOSE', 'José', 'Maria', 'MARIA'

-- Ordenação Case e Accent Insensitive (UNICODE_CI_AI)
SELECT NOME_CI_AI FROM CLIENTES 
ORDER BY NOME_CI_AI;
-- Resultado: Ignora maiúsculas/minúsculas e acentos
-- Exemplo: 'Farmacia', 'Farmácia', 'Jose', 'JOSE', 'José', 'Maria', 'MARIA'

Exemplo prático: Collate em índices

O collate é especialmente importante quando você cria índices. Um índice criado com um collate específico só será usado eficientemente em queries que usam o mesmo collate:

-- Criar índice com collate Case e Accent Insensitive
CREATE INDEX IDX_CLIENTES_LOGIN ON CLIENTES (LOGIN);

-- Esta query usará o índice eficientemente
SELECT * FROM CLIENTES WHERE LOGIN = 'jose.silva';
-- Funciona porque LOGIN usa UNICODE_CI_AI

-- Mas se você fizer uma busca com collate diferente, o índice pode não ser usado
SELECT * FROM CLIENTES 
WHERE LOGIN COLLATE UNICODE = 'jose.silva';
-- Pode não usar o índice porque o collate é diferente

Importante: Quando você especifica um collate diferente na query (usando COLLATE na cláusula WHERE), o banco pode não conseguir usar o índice de forma eficiente. Sempre que possível, use o mesmo collate da coluna nas suas queries.

Exemplo: Mesmo charset, collates diferentes

Vamos ver como o mesmo charset pode ter comportamentos diferentes com collates distintos:

CREATE TABLE TESTE_COLLATE (
    TEXTO_PT_BR VARCHAR(50) CHARACTER SET UTF8 COLLATE PT_BR,
    TEXTO_PT_PT VARCHAR(50) CHARACTER SET UTF8 COLLATE PT_PT,
    TEXTO_UNICODE VARCHAR(50) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI
);

INSERT INTO TESTE_COLLATE VALUES ('Farmacia', 'Farmacia', 'Farmacia');
INSERT INTO TESTE_COLLATE VALUES ('Farmácia', 'Farmácia', 'Farmácia');
INSERT INTO TESTE_COLLATE VALUES ('José', 'José', 'José');
INSERT INTO TESTE_COLLATE VALUES ('Jose', 'Jose', 'Jose');

-- Ordenação com PT_BR
SELECT TEXTO_PT_BR FROM TESTE_COLLATE ORDER BY TEXTO_PT_BR;
-- Ordenação com PT_PT
SELECT TEXTO_PT_PT FROM TESTE_COLLATE ORDER BY TEXTO_PT_PT;
-- Ordenação com UNICODE_CI_AI
SELECT TEXTO_UNICODE FROM TESTE_COLLATE ORDER BY TEXTO_UNICODE;

Você notará que a ordenação pode ser diferente entre PT_BR e PT_PT, mesmo usando o mesmo charset UTF8, porque cada collate segue regras linguísticas específicas de sua região.

Exemplo prático: Comparando PT_BR e PT_PT

Vamos criar uma tabela para demonstrar as diferenças entre collates brasileiro e português:

CREATE TABLE T2 (
    NOME  VARCHAR(30) NOT NULL COLLATE PT_BR
);

INSERT INTO T2 (NOME) VALUES ('FARMACIA');
INSERT INTO T2 (NOME) VALUES ('FARMÁCIA');
INSERT INTO T2 (NOME) VALUES ('Jose');
INSERT INTO T2 (NOME) VALUES ('José');
INSERT INTO T2 (NOME) VALUES ('JOSÉ');

Note agora a ordenação de dados entre dois collates diferentes usando o mesmo charset:

SELECT * FROM T2 a ORDER BY a.nome COLLATE PT_PT;

Agora o outro:

SELECT * FROM T2 a ORDER BY a.nome COLLATE PT_BR;

Os collates estão atrelados ao charset porque sem eles, o banco não teria a regionalidade de ordenação, ou quais sinais diacríticos são iguais a suas versões sem esses sinais e assim por diante.

Testando ordenação por charset

Mesmo que um banco de dados tenha uma tabela usando 3 charsets diferentes, contendo os mesmos dados, o collate poderá fazer com que os dados se comportem usando a mesma regra linguística. Por exemplo, para o Brasil, case/accent insensitive significa que o collate não fará distinção entre maiúsculos e minúsculos e que a ordenação seguirá um mesmo padrão.

Vamos testar se a ordenação foi influenciada pelo charset executando esta query (assumindo que você já criou a tabela T1 do exemplo de charset):

EXECUTE BLOCK
RETURNS(
  iso8859_1 VARCHAR(10),
  win1252 VARCHAR(10),
  unicode VARCHAR(10))
AS
BEGIN
  --iso8859_1
  iso8859_1='Sim';
  win1252='-';
  unicode='-';
  SUSPEND;
  FOR SELECT
    a.fruta_iso8859, a.fruta_win1252, a.fruta_unicode
    FROM T1 a
    ORDER BY a.fruta_iso8859
    INTO iso8859_1, win1252, unicode
  DO BEGIN
    SUSPEND;
  END
  -- win1252
  iso8859_1='-';
  win1252='Sim';
  unicode='-';
  SUSPEND;
  FOR SELECT
    a.fruta_iso8859, a.fruta_win1252, a.fruta_unicode
    FROM T1 a
    ORDER BY a.fruta_win1252
    INTO iso8859_1, win1252, unicode
  DO BEGIN
    SUSPEND;
  END
  -- unicode
  iso8859_1='-';
  win1252='-';
  unicode='Sim';
  SUSPEND;
  FOR SELECT
    a.fruta_iso8859, a.fruta_win1252, a.fruta_unicode
    FROM T1 a
    ORDER BY a.fruta_unicode
    INTO iso8859_1, win1252, unicode
  DO BEGIN
    SUSPEND;
  END
END

Notamos na saída do comando EXECUTE BLOCK que a ordenação pelo charset ISO8859_1 (latin1), WIN1252 ou unicode não teve diferença! Isso demonstra que o collate padrão de cada charset está fazendo um trabalho similar de ordenação.

Recomendações práticas

Quando usar cada tipo de collate:

Case e Accent Insensitive (CI_AI) - UNICODE_CI_AI:
- Campos de login, email, códigos
- Quando você quer que 'JOAO' encontre 'João'
- Campos de busca onde o usuário pode digitar sem acentos
- Campos que precisam de buscas flexíveis
Case Insensitive, Accent Sensitive (CI_AS) - UNICODE_CI:
- Nomes próprios onde acentos são importantes
- Quando 'José' deve ser diferente de 'Jose', mas 'JOSE' = 'Jose'
- Campos onde a diferenciação de acentos é necessária
Case e Accent Sensitive (CS_AS) - UNICODE, PT_BR, WIN_PTBR:
- Senhas, tokens, códigos exatos
- Quando a diferenciação exata é crítica
- Campos onde maiúsculas/minúsculas e acentos importam
PT_BR ou WIN_PTBR:
- Para aplicações brasileiras que usam ISO8859_1 ou WIN1252
- Ordenação seguindo regras do português brasileiro
- Quando você precisa de regionalidade específica

Boas práticas:

Escolha o collate adequado na criação da tabela: Evite ter que fazer conversões em todas as queries
Use o mesmo collate no índice e na coluna: Para garantir uso eficiente dos índices
Considere o collate do banco: Se não especificar, a coluna herdará o collate padrão do banco
Para aplicações brasileiras: PT_BR ou UNICODE_CI_AI são geralmente boas escolhas
Teste as comparações: Sempre teste se suas queries encontram os dados esperados com o collate escolhido
Evite usar COLLATE na query: Se possível, defina o collate correto na coluna ao invés de forçar na query

Resumo: Definir o Collate correto evita que sua aplicação tenha que fazer conversões custosas via software (como UPPER() ou LOWER() em todas as queries), permitindo que o banco utilize os índices de forma eficiente. O collate é uma decisão importante que afeta tanto a performance quanto a funcionalidade das suas queries.

Collate e Performance

O collate escolhido pode impactar a performance das suas queries:

Collates mais simples (binários): Geralmente mais rápidos, mas menos flexíveis
Collates CI_AI: Podem ser um pouco mais lentos em comparações, mas permitem buscas mais flexíveis sem conversões
Uso de índices: Índices criados com um collate específico só serão usados eficientemente em queries que usam o mesmo collate
Conversões em runtime: Usar COLLATE na query força conversões em tempo de execução, o que pode ser custoso

Dica de Performance: Se você frequentemente precisa fazer buscas case-insensitive ou accent-insensitive, defina o collate adequado na criação da coluna ao invés de usar funções como UPPER() ou LOWER() nas queries. Isso permite que o Firebird use os índices de forma eficiente.

Referências oficiais:

FirebirdSQL - COLLATE Documentation
FirebirdSQL - Character Data Types
Qual Charset usar? - Entenda a relação entre Charset e Collate
Guia geral sobre Collate - Comparação entre diferentes SGBDs

← Voltar para o Guia Firebird