Méritos de inserir na tabela temporária e renomear versus apenas adicionar/remover colunas

Eu recomendo que você leia as colunas da tabela do SQL Server sob o capô . Você verá que muitas operações DDL de coluna resultam em colunas 'fantasmas' na tabela, colunas físicas que existem na tabela, mas não são visíveis para o usuário. Uma reconstrução removerá todas essas colunas fantasmas.

Na maioria das vezes isso é benigno, mas existem algumas áreas escuras que podem levar aos problemas descritos em KB2504090 :

Esse problema ocorre porque o acessador que o SQL Server usa para inserir dados em diferentes partições reconhece as alterações de metadados incorretamente. Quando os dados são inseridos na nova partição criada após a eliminação de uma coluna, o número máximo de colunas anuláveis ​​na nova partição pode ser um a menos que o número máximo de colunas anuláveis ​​na partição antiga.

Eu, pelo menos, evito todas as ferramentas de comparação de esquema e implantação/atualizações baseadas em diferenças de comparação. Simplesmente não existe uma abordagem correta de tamanho único em relação à mudança de esquema. Assim como Henrik menciona, fui queimado pela 'cópia' da tabela de 500 GB, tyvm, sem mais diferenças para mim. Em vez disso, recomendo migrações, codificadas como scripts SQL e testadas em tamanho de dados relevantes antes de serem implantadas. Consulte Controle de versão e seu banco de dados . Rails ActiveRecord Migrations realmente entendem isso.

Se sua alteração for apenas uma alteração de metadados, isso não afetará todas as linhas da tabela e não ocorrerá nenhuma divisão de página. Como em

ALTER TABLE MyTable Add MyNewColumn varchar(50)

Se não for apenas uma alteração de metadados (como em

ALTER TABLE MyTable Add MyNewIntColumn NOT NULL DEFAULT (0)

), uma reconstrução de seu índice clusterizado fará uma "copiar e renomear" nos bastidores.

Algumas de nossas tabelas têm 15 bilhões de linhas e leva de 15 a 20 horas para reconstruir o índice clusterizado. Eu uso a abordagem "copiar e renomear", quando não posso aceitar as 20 horas de inatividade aguardando a reconstrução. Primeiro, copio 14,99 bilhões de linhas, depois desabilito o trabalho que insere linhas, levo os 10 milhões de linhas restantes para a nova estrutura e, finalmente, uma renomeação.

Sim, é um pouco caro (usando meu tempo), mas o sistema permanece online o maior tempo possível.

Você reconstrói um índice como este:

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON HumanResources.Employee REBUILD;
GO

Isso pode ser programado, por isso é muito mais fácil do que "copiar e renomear".