Eu encontrei várias fontes que indicam ALTER TABLE ... DROP COLUMN é uma operação apenas de metadados.
Como isso pode ser? Os dados durante uma DROP COLUMN não precisam ser removidos dos índices não clusterizados subjacentes e do índice/heap clusterizado?
Além disso, por que o Microsoft Docs sugere que é uma operação totalmente registrada?
As modificações feitas na tabela são registradas e totalmente recuperáveis. As alterações que afetam todas as linhas em tabelas grandes, como descartar uma coluna ou, em algumas edições do SQL Server, adicionar uma coluna NOT NULL com um valor padrão, podem levar muito tempo para serem concluídas e gerar muitos registros de log . Execute essas instruções ALTER TABLE com o mesmo cuidado que qualquer instrução INSERT, UPDATE ou DELETE que afete muitas linhas.
Como uma pergunta secundária: como o mecanismo acompanha as colunas descartadas se os dados não forem removidos das páginas subjacentes?
Há certas circunstâncias em que a eliminação de uma coluna pode ser uma operação somente de metadados. As definições de coluna para qualquer tabela não são incluídas em cada página onde as linhas são armazenadas, as definições de coluna são armazenadas apenas nos metadados do banco de dados, incluindo sys.sysrowsets, sys.sysrscols, etc.
Ao descartar uma coluna que não é referenciada por nenhum outro objeto, o mecanismo de armazenamento simplesmente marca a definição da coluna como não mais presente, excluindo os detalhes pertinentes de várias tabelas do sistema. A ação de excluir os metadados invalida o cache do procedimento, exigindo uma recompilação sempre que uma consulta fizer referência a essa tabela posteriormente. Como a recompilação retorna apenas as colunas que existem atualmente na tabela, os detalhes da coluna descartada nunca são solicitados; o mecanismo de armazenamento ignora os bytes armazenados em cada página dessa coluna, como se a coluna não existisse mais.
Quando uma operação DML subsequente ocorre na tabela, as páginas afetadas são regravadas sem os dados da coluna eliminada. Se você reconstruir um índice clusterizado ou um heap, todos os bytes da coluna descartada naturalmente não serão gravados de volta na página no disco. Isso distribui efetivamente a carga de queda da coluna ao longo do tempo, tornando-a menos perceptível.
Há circunstâncias em que você não pode descartar uma coluna, como quando a coluna é incluída em um índice ou quando você cria manualmente um objeto de estatísticas para a coluna. Eu escrevi uma postagem no blog mostrando o erro que é apresentado ao tentar alterar uma coluna com um objeto de estatísticas criado manualmente. A mesma semântica se aplica ao descartar uma coluna - se a coluna for referenciada por qualquer outro objeto, ela não poderá ser simplesmente descartada. O objeto de referência deve ser alterado primeiro, então a coluna pode ser descartada.
Isso é bastante fácil de mostrar observando o conteúdo do log de transações depois de descartar uma coluna. O código abaixo cria uma tabela com uma única coluna longa de 8.000 caracteres. Ele adiciona uma linha, depois a descarta e exibe o conteúdo do log de transações aplicável à operação de descarte. Os registros de log mostram modificações em várias tabelas do sistema onde as definições de tabela e coluna são armazenadas. Se os dados da coluna estivessem realmente sendo excluídos das páginas alocadas à tabela, você veria registros de log registrando os dados reais da página; não existem tais registros.
(A saída é muito grande para mostrar aqui, e dbfiddle.uk não me permite acessar fn_dblog)
O primeiro conjunto de saída mostra o log como resultado da instrução DDL eliminando a coluna. O segundo conjunto de saída mostra o log depois de executar a instrução DML em que atualizamos a
ridcoluna. No segundo conjunto de resultados, vemos registros de log indicando uma exclusão em dbo.DropColumnTest, seguida por uma inserção em dbo.DropColumnTest. Cada comprimento de registro de log é 8116, indicando que a página real foi atualizada.Como você pode ver na saída do
fn_dblogcomando no teste acima, toda a operação está totalmente registrada. Isso vale para a recuperação simples, bem como para a recuperação completa. A terminologia "totalmente registrada" pode ser mal interpretada, pois a modificação dos dados não é registrada. Isso não é o que acontece - a modificação é registrada e pode ser totalmente revertida. O log está simplesmente gravando apenas as páginas que foram tocadas e, como nenhuma das páginas de dados da tabela foi registrada pela operação DDL, tanto oDROP COLUMN, quanto qualquer rollback que possa ocorrer acontecerá extremamente rapidamente, independentemente do tamanho da tabela.Para science , o código a seguir despejará as páginas de dados da tabela incluída no código acima, usando
DBCC PAGE, estilo "3". O estilo "3" indica que queremos o cabeçalho da página mais a interpretação detalhada por linha . O código usa um cursor para exibir os detalhes de cada página na tabela, portanto, você pode ter certeza de não executar isso em uma tabela grande.Observando a saída da primeira página da minha demonstração (depois que a coluna é descartada, mas antes que a coluna seja atualizada), vejo isso:
Eu removi a maior parte do despejo de página bruta da saída mostrada acima para brevidade. No final da saída, você verá isso para a
ridcoluna:A última linha acima,
rid = 1, retorna o nome da coluna e o valor atual armazenado na coluna na página.A seguir, você verá isso:
A saída mostra que o Slot 0 contém uma coluna excluída, em virtude do
DELETEDtexto onde normalmente estaria o nome da coluna. O valor da coluna é retornado comoNULLdesde que a coluna foi excluída. No entanto, como você pode ver nos dados brutos, o valor de 8.000 caracteres,REPLICATE('Z', 8000), para essa coluna ainda existe na página. Esta é uma amostra dessa parte da saída DBCC PAGE: