Compartilhar via

Trocando o Collate de um Banco de Dados SQL Server

  • Artigo

Introdução

Este artigo tem sua origem em uma pergunta postada no Fórum TechNet Brasil do SQL Server - Troca de Collate de um determinado banco de dados e também influenciada pela enorme necessidade de esclarecimentos em posts dos Fórum TechNet Internacional. Este é um problema muito comum sobre à manipulação de dados através do T-SQL e serve para outros produtos que utilizam os recursos do SQL Server, tais como: SharePoint e System Center.

Esta é uma das possíveis soluções relacionadas para este problema, se você conhece outras opções em T-SQL que atendam à necessidade do problema proposto, fique à vontade para acrescentar seu conteúdo neste artigo.

Problema

Durante minha leitura nos Threads do Fórum de SQL, encontrei à seguinte pergunta que estava em discussão até então.

A pergunta era: "Gostaria de alterar a collate de um determinado banco de dados, porém gostaria de saber se para alterar um collate de um banco preciso também aplicar nas colunas das tabelas existente? Tem algum procedimento que faça em todas às colunas ou tenho que aplicar manualmente coluna à coluna ?"

É preciso estar claro que o inquiridor da pergunta, já havia alterado o Collation do banco de dados para SQL_Latin1_General_CP1_CI_AI, e passou a receber diversos erros em consultas, principalmente onde os campos do tipo varchar faziam parte das condições em um comando JOIN.

Quando tentou alterar o Collation dos campos em uma tabela, surgiu o seguinte erro:

ALTER TABLE BibliografiaTipos ALTER COLUMN Descricao varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AI

--RESULTADO

Msg 4902, Level 16, State 1, Line 4
Cannot find the object "BibliografiaTipos" because it does not exist or you do not have permissions.

Um segundo erro surgiu, quando o inquiridor tentou alterar tabelas com campo texto que possuíam relacionamento entre tabelas do Banco de Dados, além de campos texto com chave prímária.

Msg 5074, Level 16, State 1, Line 5
The object ‘FK_FrequenciasNova_Alunos’ is dependent on column ‘Aluno_RA’.
Msg 4922, Level 16, State 9, Line 5
ALTER TABLE ALTER COLUMN Aluno_RA failed because one or more objects access this column.

Ter campos texto como chave primária fez toda diferença para a solução do problema. Vamos ver como foi executada esta alteração.

Causas

Quando alteramos o Collation de um banco de dados, todos os campos dos tipos: char, nchar, varchar, nvarchar permanecem no Collation em que foram criados. É necessário analisar com cuidado qual é a melhor solução para reduzir ao máximo o impacto nos sistemas cliente e nas consultas que os usuários já realizavam.

Para ajudar à compreender 

Isto porque, poderá ocorrer alterações no resultado de suas consultas. Em um dia, o seu relatório traz todas às informações e logo após a troca do Collation pode não retornar mais nada.

Alterar o Collation apenas em uma instrução ( SELECT, INSERT, UPDATE ou DELETE ) também não é recomendado, porque existe à possibilidade de gerar lentidão durante à conversão.

Então, de fato, a melhor solução no contexto deste post do Fórum é alterar o Collation de cada campo nas tabelas existentes.

Apenas esclarecendo, novos campos do tipo texto, como citados acima, criados após a mudança do Collation no Banco de Dados passarão à adotar o mesmo Collation indicado na configuração do Banco.

Etapas de Diagnóstico

Depois de diagnosticarmos à causa do problema, vamos para à sua resolução. Podem existir outras soluções como alternativa, mas esta indicada no final do artigo compreende exatamente à pergunta proposta no Fórum, da forma mais simples e prática possível.

Construíndo o Cenário do Problema

Para que possamos simular com precisão o problema e propor à sua solução, vamos construir uma tabela semelhante à situação indicada no Thread do Fórum (Figura 1):

SELECT name, collation_name FROM sys.databases WHERE name = 'WI_Infra'
GO

CREATE TABLE BibliografiaTipos (
ID                  int PRIMARY KEY NOT NULL,
NM_USER             varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AI  NOT NULL,
Descricao           varchar(250) NOT NULL
)
GO 

sp_help BibliografiaTipos
GO

Figura 1 - Criando a tabela para demonstração da solução e indicando os Collations diferentes entre Banco e Tabela

Verificando o Cenário do Problema

Para que possamos entender o porque houve erro ao alterar o Collation da tabela é importante verificar qual é o "schema" na qual os campos da tabela pertencem e incluir na alteração da coluna à opção do campo em receber valores nulos ou não:

sp_help BibliografiaTipos
GO

ALTER TABLE BibliografiaTipos ALTER COLUMN Descricao varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
GO

Ainda assim, temos o último Problema do Inquiridor: campos texto com chave e/ou índice.

Solução

Parte da solução já foi realizada, orientando o inquiridor a corrigir o ALTER TABLE para alteração do Collation da coluna. É importante estar claro que a indicação das pequenas correções no cenário já atenderiam boa parte das tabelas no banco de dados, por não possuírem campos texto como chave primária ou estrangeira.
       
Para estruturar à segunda parte da solução do problema, de forma simples e eficiente, o inquiridor foi orientado à seguir alguns passos:

  1. Criar os scripts de todas às tabelas onde o campo texto é a chave primária
  2. Incluir no nome das tabelas deste script o termo "_NOVO"
  3. Efetuar um backup FULL do Banco de Dados
  4. Executar o script de carga de dados com INSERT / SELECT para tabelas onde a chave primária é um campo texto
  5. Executar o script de carga de dados com INSERT / SELECT com para tabelas onde a chave estrangeira é um campo texto e a chave primária é um identity

Vamos executar à carga de dados da tabela antiga para a nova, onde a chave primária é um campo texto

INSERT INTO FrequenciasNova_Alunos_NOVO
(ID_ALUNO, NM_ALUNO)
SELECT
ID_ALUNO,
NM_ALUNO COLLATE SQL_Latin1_General_CP1_CI_AI
FROM FrequenciasNova_Alunos;

Para executar à carga de dados da tabela antiga para a nova, onde a chave estrangeira é um campo texto e a chave primária é um identity é necessário declarar todos os campos da tabela. Incluindo a configuração "SET IDENTITY_INSERT" no início e no fim da instrução desabilitando e habilitando novamente a carga de dados em campos Identity.
        

SET IDENTITY_INSERT FrequenciasNova_Alunos_NOVO ON

INSERT INTO FrequenciasNova_Alunos_NOVO
(ID_ALUNO, NM_ALUNO)
SELECT
ID_ALUNO,
NM_ALUNO COLLATE SQL_Latin1_General_CP1_CI_AI
FROM FrequenciasNova_Alunos;

SET IDENTITY_INSERT FrequenciasNova_Alunos_NOVO OFF

 
Conclusão, para executar a troca de um Collation de um banco de dados é necessário à realização de uma análise prévia para identificar quais tabelas serão afetadas e como esta alteração deve ser efetuada. Em alguns casos, é necessário executar à recriação da tabela com outro nome, seguida da importação dos dados da tabela com o Collation anterior.

Veja Também

Leia alguns artigos relacionados ao assunto:

Para fortalecer o seu conhecimento sobre COLLATE e IDENTITY recomendo à leitura dos artigos:

Biblioteca TechNet

Leia alguns tópicos relacionados ao assunto:

Referências

Leia alguns posts relacionados ao assunto:

Créditos

Este artigo foi escrito inspirado nos artigos:

Meu agradecimento especial à Richard Mueller por me orientar na premiação do TechNet Guru de dezembro de 2013, explicando como organizar meu conteúdo em seções de forma adequada.

Outros Idiomas

Este artigo também pode ser encontrado em outros idiomas​​: