CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100))
INSERT INTO TestTab (st) values ('a')
INSERT INTO TestTab (st) values ('b')
INSERT INTO TestTab (st) values ('c')
INSERT INTO TestTab (st) values ('d')
INSERT INTO TestTab (st) values ('e')
INSERT INTO TestTab (st) SELECT TOP 10000 st from testtab
GO 30
ALTER TABLE TestTab ADD newcol nvarchar(10) DEFAULT 'newcol'
UPDATE TestTab SET newcol = 'newcol' --6 sec
ALTER TABLE TestTab ADD newcol1 nvarchar(10) DEFAULT 'newcol1' NOT NULL
DROP TABLE TestTab
Quando executo este script de teste, o ALTERcom UPDATEleva 6 segundos, o que é compreensível.
No entanto, o ALTERwith DEFAULT NOT NULLé executado instantaneamente mesmo em uma tabela muito maior. Existe alguma explicação sobre por que isso é instantâneo? No disco físico, os dados ainda precisam ser gravados em todas as linhas, certo?
Eu tentei olhar SET STATISTICS IO ONe o plano de consulta, no entanto, eles não parecem estar disponíveis para operações DDL.
Sim, adicionar uma coluna com NOT NULL e um padrão não grava os valores em todas as linhas no momento da alteração, portanto, não é mais uma operação de tamanho de dados. Quando você seleciona na tabela, as colunas são realmente materializadas de sys.system_internals_partition_columns , o que evita que todos os valores tenham que ser gravados (até que sejam alterados). Observe que isso não funciona para todos os tipos de dados e requer Enterprise Edition.
Remus Rusanu explica isso com mais detalhes aqui:
Além disso,
ALTERpelo menos, ainda não podemos mostrar um plano porque o SQL Server não produz um, mas para ver a E/S, você pode usar o SQL Sentry Plan Explorer .* Esta captura de tela mostra a adição de uma coluna, c5 , "online" conforme descrito acima e, em seguida, outra coluna, c6, "offline" porque os tipos de LOB não são suportados. Você pode ver que a E/S é expressa principalmente como leituras em vez de gravações, mas o que é mais revelador é o (inválido!)UPDATEassociado à alteração offline.Se você não tiver o Enterprise Edition, ambas as instruções terão o secundário
UPDATEanexado (e as leituras associadas). (E se você usar a versão gratuita do Plan Explorer, que não obtém a pilha de chamadas de consulta completa, você não verá o acima - você verá apenas uma árvore de extrato vazia. É necessária uma versão paga para ver a consulta completa pilha de chamada.)Observe que o SQL Server produzirá um plano estimado , mas não é muito útil. De forma alguma. E o plano estimado para uma alteração online é idêntico ao plano estimado para uma alteração offline.
* Isenção de responsabilidade: eu trabalho para o SQL Sentry.