Collate (Collation) no SQLServer

Aqui vou passar alguns exemplos e explicações bem básicas e diretas do que é um Collate (Collation) no SQLServer.

O Collate é definição da cadeia de caracteres utilizados como padrão em um banco, colunas de uma tabela ou alguma consulta.

Vários alfabetos de outros paises utilizam caracteres bem diferentes do nosso, e isso é definido no Collate, por exemplo, o alfabeto utilizado no Ingles não usa acentuação, já o Latino (utilizado aqui no Brasil) sim, isso faz total diferença em uma pesquisa ou no armazenamento da informação no banco.

/*

Para saber quais são os Collates disponíveis no SQLServer do padrão Latin1_General:

*/

Select *
from ::fn_helpcollations()
Where Name like 'Latin1_General%'



/*
Notem que no final do nome do Collate tem algumas siglas, essas siglas fazem toda diferença na escolha, pois influenciam diretamente em como as querys e algumas funcionalidades do Banco tratam os dados.

CI = Case Insensitive
AI = Accents Insensitive
CS = Case Sensitive
AS = Accents Sensitive
WS = Width Sensitive (*1)
KS = Kanatype Sensitive (*2)

(*1) Kana-sensitive - Especifica que o SQL Server deve distinguir entre os dois tipos de caracteres kana japoneses: hiragana e katakana.
Se não for selecionada o SQL Server considera caracteres hiragana e katakana iguais.

(*2) Width-sensitive - Especifica que o SQL Server deve distinguir entre um caractere de byte único (meia largura) e o mesmo caractere quando representado como um caractere de byte duplo (largura total).
Se não for selecionada o SQL Server considera o byte único e byte duplo igualmente.

Ambos os casos acimas são praticamente não utilizados.

*/


/*

Para saber qual o Collation configurado no seu servidor

Alterar o Collation do Servidor é um pouco mais complicado. Como fazer isso está neste post: Alterar o Collation do SERVIDOR SQLServer.

*/

Select ServerProperty('CollationId') CollationId,
SERVERPROPERTY('Collation') Collation


/*

Para saber qual o Collation configurado no seu Banco de Dados

*/

Select DatabasePropertyEx('Nome_Banco','Collation')
--Ou na coluna Status da
sp_helpdb

--Para Alterar o Collate do Banco:
Alter database Nome_Banco collate Latin1_General_CI_AS


/*

Para saber qual o Collation configurado na Tabela

*/

Select name, collation
from syscolumns
Where [id]=object_id('Nome_Tabela')

--Se a coluna Collation estiver com Null significa que está assumindo o Collate do Banco



Agora vamos ao exemplo bem simples de busca para mostrar como isso funciona:
Estou partindo do principio que o Collation default do banco é o Latin1_General_CI_AI.
Em uma tabela temporária vou inserir o nome 'Conceição' exatamente por causa do Ç e do à para fazer as buscas utilizando acentuação e maiuscula/minuscula.

--Criar uma tabela temporária para fazer o exemplo com as buscas:

Create Table #tbTesteCollate (id Int, Nome VarChar(50))
GO
Insert Into #tbTesteCollate Values (1, 'Conceição')
Insert Into #tbTesteCollate Values (2, 'CONCEIÇÃO')
Insert Into #tbTesteCollate Values (3, 'Conceicao')
Insert Into #tbTesteCollate Values (4, 'CONCEICAO')


--Agora vamos executar uma busca utilizando os 4 Collates disponíveis com o critério like '%cao%' e ver o que retorna com cada um dos Collates

Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CS_AS like '%cao%' --Case Sensitive / Accents Sensitive
go
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CI_AS like '%cao%' --Case Insensitive / Accents Sensitive
GO
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CI_AI like '%cao%' --Case Insensitive / Accents Insensitive
go
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CS_AI like '%cao%' --Case Sensitive / Accents Insensitive




/*

Mais Exemplos de busca

*/


Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CS_AS like '%ÇÃO%'
go
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CI_AS like '%ÇÃO%'
GO
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CI_AI like '%ÇÃO%'
go
Select *
from #tbTesteCollate
Where Nome collate Latin1_General_CS_AI like '%ÇÃO%'