Modificando as propriedades de uma coluna indexada
Introdução
Este artigo apresenta como alguns índices relacionados à uma coluna pode bloquear alterações de algumas propriedades, como "Data Types" ou "Collations" no SQL Server, retornando erros referentes ao relacionanto com algum(ns) outro(s) objeto(s) de um banco de dados e por essa razão, as propriedades desta coluna não podem ser modificadas.
Construindo um Ambiente para Teste
Para demonstrar como este problema ocorre vamos criar uma tabela simples com duas colunas: uma tipo númérico (datatype "int") e outra tipo texto (datatype "varchar").
Vamos adicionar também alguns dados e mais dois índices "não clusterizados" para mostrar como esta ação pode afetar alterações na estrutura de uma tabela, gerando erro.
Segue abaixo o script T-SQL para criação deste ambiente de teste:
--Criando um cenário
CREATE TABLE TB_PESSOAS (
ID int NOT NULL PRIMARY KEY,
Nome varchar(20) NOT NULL
)
GO
INSERT INTO TB_PESSOAS VALUES (1, 'TESTE 1');
INSERT INTO TB_PESSOAS VALUES (2, 'TESTE 2');
INSERT INTO TB_PESSOAS VALUES (3, 'TESTE 3');
GO
CREATE NONCLUSTERED INDEX ix_nome ON TB_PESSOAS (Nome ASC);
CREATE NONCLUSTERED INDEX ix_teste ON TB_PESSOAS (ID ASC, Nome ASC);
GO
sp_help TB_PESSOAS
GO
Compreendendo o Problema
Quando você está modificando o Collation de uma coluna que possui um texto (char, varchar, nchar, nvarchar) você está modificando o armazenamento da informação, "semelhante" à uma alteração de um tipo de dados e este é um dos motivos para ocorrer erros.
Veja na imagem abaixo:
Quando um índice é criado, uma referência para a estrutura das colunas indexadas também é criada de modo à otimizar sua ordenação de dados e, neste caso, o tipo de dados da coluna é fundamental para o SQL Server definir este critério.
Veja o script abaixo:
-- Mudando o Collation da Coluna "texto". Neste caso, datatype "varchar"
ALTER TABLE TB_PESSOAS
ALTER COLUMN Nome varchar(60) COLLATE SQL_Latin1_General_CP437_CI_AS
GO
-- Mudando o Tamanho da Coluna
ALTER TABLE TB_PESSOAS
ALTER COLUMN Nome varchar(20)
GO
Se você estivesse apenas alterando o tamanho do armazenamento(para um tamanho maior) isto não ocorreria, porque o SQL Server entende que não haverá incompatibilidade e perda de dados para os índices que utilizam esta coluna. O contrário, quando diminuímos o tamanho de armazenamento de um tipo de dados então o erro é apresentado.
Segue alguns tipos de alterações que modificam a estrutura e o armazenamento dos dados:
- Colocar uma coluna no meio de uma Tabela;
- Alterar um tipo de dados para outro não compatível;
- Mudar as colunas ou a ordenação de uma Chave Primária;
- Entre outros.
Não há problema em alterar a estrutura de uma tabela, mas é importante compreender que sempre estas modificações estarão reconstruíndo a tabela e isso pode levar um tempo excessivo, além de provocar indisponibilidadee possível perda de dados.
Por essa razão que o SQL Server Management Studio(SSMS) o adverte por padrão e impede a alteração da estrutura de suas tabelas.
Alterando as Propriedades da Coluna
Neste caso não adianta recriar ou desabilitar o índice onde esta coluna esta presente. Quando for necessário alterar o Collation de uma coluna, será preciso "excluir" os índices que utilizam esta coluna e "criar" novamente.
Tenha apenas cuidado caso o Collation desta coluna for diferente do seu banco de dados, o que poderá gerar em alguns casos, a necessidade de converter os valores durante algumas manipulações de dados, principalmente quando usado em critérios de filtragem das cláusulas WHERE e ON.
Veja abaixo a alteração do Collation e do tamanho do data type desta coluna:
Segue o script T-SQL executado para efetivar esta alteração:
-- Excluindo os índices (por questão da mudança do Collation)
DROP INDEX ix_nome ON TB_PESSOAS;
DROP INDEX ix_teste ON TB_PESSOAS;
GO
-- Mudando o Agrupamento da Coluna
ALTER TABLE TB_PESSOAS
ALTER COLUMN Nome varchar(60) COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
CREATE NONCLUSTERED INDEX ix_nome ON TB_PESSOAS (Nome ASC);
CREATE NONCLUSTERED INDEX ix_teste ON TB_PESSOAS (ID ASC, Nome ASC);
GO
Após a alteração os índices são "criados" novamente para habilitar as mesmas condições de uso dos dados armazenados.
Veja a mudança do Collation na coluna "NOME" ao executar a stored procedure "sp_help":
Como esperado, as alterações foram efetivadas com sucesso.
É recomendado que estas modificações sejam executadas antes em um servidor de homologação. Após a validação de suas alterações, procure reservar um horário alternativo, para evitar maiores impactos em seu ambiente de Produção.
Você pode adicionar outros scripts T-SQL como alternativas para obter estes resultados.
Referências
Leia alguns documentos relacionados ao assunto no BOL:
- Guia de criação de índice do SQL Server
- Definir ou alterar o agrupamento de banco de dados
- CREATE INDEX (Transact-SQL)
- ALTER TABLE (Transact-SQL)
Veja Também
Leia outros artigos no TNWiki: