Oi, pessoal. Hoje, apresento o caso em que as colunas que terão suas definições alteradas estão associadas a índices. E, mais importante de tudo, como recriar os índices associados a tais colunas usando as tabelas de sistema do SQL Server.
No artigo anterior (veja link), apresentei um exemplo criando um script para geração de declarações DDL para alterar as definições de colunas que atendessem a determinadas condições.
Scripts SQL e Tabelas de Sistema
Entenda o Caso de Uso
Antes de mais nada, é preciso lembrar que não se pode alterar colunas que estejam vinculadas a outros objetos, como índices, defaults ou qualquer tipo de constraints SQL.
Veja no Quadro 1 a seguir o resultado obtido quando tento alterar uma coluna associada a um índice:
A solução do problema é apagar estes índices, alterar as colunas e depois recriar seus índices. Na teoria, o procedimento é bem simples, mas é preciso considerar que são necessárias três operações para cada coluna que se vai alterar. Sendo assim, o script final pode facilmente conter centenas de declarações SQL.
Automatizar a criação desse script não apenas diminui o tempo de desenvolvimento do script, mas também reduz drasticamente o risco de erro no momento da implantação.
Então o script desejado terá 3 blocos principais:
Bloco 1 – apagar índices associados às colunas que serão alteradas
Bloco 2 – alterar as colunas desejadas (como vimos no artigo anterior)
Bloco 3 – reconstruir os índices apagados
A automação dos scripts para cada um destes blocos é apresentada a seguir.
Scripts SQL – Definindo Escopo
Antes de criar os blocos 1 e 3, é necessário identificar quais índices a serem impactados.
No Quadro 2 apresento a expressão de tabela comum (CTE) que identifica as tabelas e índices afetados pela alteração de tipos de dados das colunas, seguindo o exemplo mostrado no artigo anterior. Em seguida, identifico os metadados desses índices, para que possam ser recriados posteriormente.
LEIA TAMBÉM: O Caso das Consultas Lentas no Synapse
Observe que criei uma tabela temporária “#tempIndices”, porque estas as informações serão usadas duas vezes, na construção dos scripts dos blocos 1 e 3.
Outro ponto importante é que a declaração acima traz informação para geração de scripts que envolvam apenas índices não-clusterizados.
O caso dos índices clusterizados requer muito mais cuidado, porque eles ordenam os dados diretamente nas páginas de dados. Em outras palavras, fisicamente, o índice clusterizado é a própria tabela. Portanto, não dá para simplesmente excluí-los sem que haja perda de dados.
Bloco 1 – Script para Excluir Índices
Neste primeiro bloco, eu monto uma declaração que gera o script de exclusão de índices. Tal script precisa conter declarações com a sintaxe apresentada a seguir (veja Quadro 3).
Agora, no Quadro 4, uso uma nova CTE para ler da tabela temporária “#tempIndices” apenas as informações necessárias para apagar estes índices. Depois defino uma cadeia de caracteres que escreva a declaração DDL desejada. A adição do caractere CHAR(10) é exatamente para inserção de linhas extras no script.
LEIA TAMBÉM: Análise de Grafos no Azure SQL DB
Scripts SQL – Bloco 3 – Script para Recriar Índices
A criação de índices no SQL on-premises e Azure SQL oferece múltiplas opções para o desenvolvedor. (Clique aqui para saber mais).
Mesmo considerando apenas índices comuns (algumas vezes chamados de “B-tree” ou “rowstore”, para diferenciar dos índices “columnstore”) e não–clusteridados, existem diversas maneiras de customizá-los às suas necessidades.
Para simplificar a construção do gerador de scripts de criação de índices, vou considerar aqui apenas 03 cenários:
Índices rowstore não clusterizados com ordenação ascendente ou descendente
Índices rowstore não clusterizados com valores únicos
Índices rowstore não clusterizados incluindo colunas adicionais
As informações necessárias para identificar os detalhes de criação de qualquer índice do banco de dados estão disponíveis na tabela de sistema “sys.index_columns”. Caso você não tenha observado, esta tabela já foi utilizada para criação da tabela temporária “#tempIndices”.
As colunas principais desta última tabela são object_id, index_id e index_column_id. Eles identificam o ID da tabela, o ID do índice daquela tabela e o ID das colunas que compõem este índice. Com elas é possível fazer buscas combinando tabela temporária com outras tabelas de sistema, caso isso seja necessário.
Índices podem ser criados sobre múltiplas colunas e, neste caso, nossa tabela temporária apresentará mais de uma linha por índice. Porém, nosso gerador de scripts precisa combinar estas colunas numa única lista para escrever uma declaração SQL válida.
Para resolver esta questão, eu uso aqui a função de agregação STRING_AGG, disponível em qualquer banco de dados que tenha nível de compatibilidade 14.X ou superior (SQL Server 2017).
A sintaxe dessa função é bem simples, basta informar a coluna que se quer agregar e qual o separador que será usado. No presente caso, o separador desejado é o caractere vírgula (‘,’).
O mesmo problema acontece caso o índice tenha colunas incluídas (cláusula INCLUDE). A função STRING_AGG novamente resolve o problema. A distinção entre as colunas indexadas e as colunas incluídas no índice é feita com a informação da coluna is_included_column. Se o valor for 0 (falso), a coluna é indexada, se for 1 (verdadeiro), a coluna é incluída.
Então eu começo a criação do meu gerador de scripts usando expressões de tabela comum (CTE) separadas para colunas indexadas e incluídas.
O Quadro 5 mostra um exemplo com estas duas CTEs e o resultado que elas retornariam. Observe que o SELECT final neste quadro ainda não lida com geração das declarações SQL.
Três informações interessantes sobre os índices do quadro acima:
O índice da linha 2 é composto de cinco colunas
O índice da linha 4 é que usa a cláusula INCLUDE
Nenhum dos índices usa ordenação descendente de campos
Antes de gerar o script desejado, é necessário identificar as propriedades dos índices e eliminar duplicidades. O Quadro 6 é, na realidade uma continuação do quadro anterior, por isso mostro apenas a citação das CTEs descritas anteriormente. (Caso queira ver o script completo, clique nesse link).
Para fazer o download do script completo, acesse este link.
Como Executar os Scripts DDL
Dependendo da maneira como você deseja usar os scripts gerados, é preciso atenção a alguns detalhes. Caso deseje exibir o resultado dos SELECT`s acima e copiar os scripts gerados, tenha certeza de alterar o modo de exibição da janela de resultados da sua ferramenta para TEXTO.
Caso sua intenção seja executar de imediato o script gerado, é preciso guardar o texto gerado numa variável (que chamei de @sql) e, ao final, imprimir o conteúdo dessa variável e executá-la em seguida. Para o caso do bloco 1, veja no quadro abaixo como ficaria o gerador de scripts.
Comentários Finais
Neste artigo, apresentei um exemplo mais complexo de uso das tabelas de sistemas.
Vários pequenos truques foram adicionados no gerador de scripts, incluindo:
inserção de caracteres especiais no script para torná-lo mais legível
como contornar o problema da criação de listas de colunas
novas formas de executar o gerador.
Eu considero que é muito importante que desenvolvedores e administradores saibam utilizar os metadados da base SQL. E, insisto em repetir, este conhecimento pode ser útil na execução de inúmeras operações repetitivas.
Até aqui, apresentei exemplos para lidar com operações dentro de um único banco de dados. Nos próximos artigos, vou falar da geração de scripts que tenham escopo de execução para toda instância SQL, ou seja, scripts que atuam sobre múltiplas bases de dados.
De 0 a 10, o quanto você recomendaria este artigo para um amigo?