Como você adiciona uma coluna NOT NULL a uma tabela grande no SQL Server?

Para adicionar uma coluna NOT NULL a uma tabela com muitos registros, é necessário aplicar uma restrição DEFAULT. Essa restrição faz com que todo o comando ALTER TABLE demore muito tempo para ser executado se a tabela for muito grande. Isto é porque:

Premissas:

  1. A restrição DEFAULT modifica os registros existentes. Isso significa que o database precisa aumentar o tamanho de cada registro, o que faz com que ele mude registros em páginas de dados completas para outras páginas de dados e isso leva tempo.
  2. A atualização DEFAULT é executada como uma transação atômica. Isso significa que o log de transactions precisará ser aumentado para que um retrocesso possa ser executado, se necessário.
  3. O log de transactions mantém o controle de todo o registro. Portanto, mesmo que apenas um único campo seja modificado, o espaço necessário para o log será baseado no tamanho do registro inteiro multiplicado pelo número de registros existentes. Isso significa que adicionar uma coluna a uma tabela com registros pequenos será mais rápido do que adicionar uma coluna a uma tabela com registros grandes, mesmo que o número total de registros seja o mesmo para as duas tabelas.

Soluções possíveis:

  1. Chupe e espere o processo terminar. Apenas certifique-se de definir o período de tempo limite para ser muito longo. O problema com isso é que pode levar horas ou dias dependendo do número de registros.
  2. Adicione a coluna, mas permita NULL. Depois, execute uma consulta UPDATE para definir o valor DEFAULT para as linhas existentes. Não faça UPDATE *. Atualize lotes de registros por vez ou você terá o mesmo problema da solução 1. O problema com esta abordagem é que você acaba com uma coluna que permite NULL quando você sabe que esta é uma opção desnecessária. Acredito que há alguns documentos de práticas recomendadas que dizem que você não deve ter colunas que permitam NULL, a menos que seja necessário.
  3. Crie uma nova tabela com o mesmo esquema. Adicione a coluna a esse esquema. Transferir os dados da tabela original. Solte a tabela original e renomeie a nova tabela. Não tenho certeza de como isso é melhor que o número 1.

Questões:

  1. As minhas suposições estão corretas?
  2. Essas são minhas únicas soluções? Se sim, qual é o melhor? Eu não, o que mais eu poderia fazer?

Eu me deparei com esse problema para o meu trabalho também. E minha solução está ao longo de # 2.

Aqui estão os meus passos (estou usando o SQL Server 2005):

1) Adicione a coluna à tabela com um valor padrão:

ALTER TABLE MyTable ADD MyColumn varchar(40) DEFAULT('') 

2) Adicione uma restrição NOT NULL com a opção NOCHECK . O NOCHECK não é NOCHECK em valores existentes:

 ALTER TABLE MyTable WITH NOCHECK ADD CONSTRAINT MyColumn_NOTNULL CHECK (MyColumn IS NOT NULL) 

3) Atualize os valores incrementalmente na tabela:

 GO UPDATE TOP(3000) MyTable SET MyColumn = '' WHERE MyColumn IS NULL GO 1000 
  • A instrução de atualização só atualizará o máximo de 3.000 registros. Isso permite salvar um bloco de dados no momento. Eu tenho que usar “MyColumn IS NULL” porque minha tabela não tem uma chave primária de seqüência.

  • GO 1000 executará a declaração anterior 1000 vezes. Isso atualizará 3 milhões de registros, se você precisar de mais, basta aumentar esse número. Ele continuará sendo executado até que o SQL Server retorne 0 registros para a instrução UPDATE.

Aqui está o que eu tentaria:

  • Faça um backup completo do database.
  • Adicione a nova coluna, permitindo nulos – não defina um padrão.
  • Defina a recuperação SIMPLE, que trunca o log de transação assim que cada lote é confirmado.
  • O SQL é: ALTER DATABASE XXX SET RECUPERAÇÃO SIMPLES
  • Execute a atualização em lotes, como você discutiu acima, comprometendo após cada um.
  • Redefina a nova coluna para não permitir mais nulos.
  • Volte para a recuperação COMPLETA normal.
  • O SQL é: ALTER DATABASE XXX SET RECOVERY FULL
  • Faça o backup do database novamente.

O uso do modelo de recuperação SIMPLE não interrompe o registro, mas reduz significativamente o impacto. Isso ocorre porque o servidor descarta as informações de recuperação após cada confirmação.

Você poderia:

  1. Comece uma transação.
  2. Pegue uma trava de escrita na sua mesa original para que ninguém escreva nela.
  3. Crie uma tabela de sombra com o novo esquema.
  4. Transfira todos os dados da tabela original.
  5. execute sp_rename para renomear a tabela antiga.
  6. execute sp_rename para renomear a nova tabela em.
  7. Finalmente, você confirma a transação.

A vantagem dessa abordagem é que seus leitores poderão acessar a tabela durante o longo processo e que você pode executar qualquer tipo de alteração de esquema em segundo plano.

Apenas para atualizar isso com as informações mais recentes.

No SQL Server 2012, isso agora pode ser executado como uma operação on-line nas seguintes circunstâncias

  1. Apenas Enterprise Edition
  2. O padrão deve ser uma constante de tempo de execução

Para o segundo requisito, os exemplos podem ser uma constante literal ou uma function como GETDATE() que avalia o mesmo valor para todas as linhas. Um padrão de NEWID() não se qualificaria e ainda acabaria atualizando todas as linhas lá e depois.

Para os padrões que qualificam o SQL Server, ele os avalia e armazena o resultado como o valor padrão nos metadados da coluna, portanto, isso é independente da restrição padrão que é criada (que pode até ser descartada se não for mais necessária). Isso é visível em sys.system_internals_partition_columns . O valor não é gravado nas linhas até a próxima vez que elas forem atualizadas.

Mais detalhes sobre isso aqui: online não-nulo com coluna de valores add no sql server 2012

Eu acho que isso depende do sabor de SQL que você está usando, mas e se você tomou a opção 2, mas no final alterar tabela de tabela para não nulo com o valor padrão?

Seria rápido, pois vê todos os valores não são nulos?

Se você quiser a coluna na mesma tabela, terá que fazê-lo. Agora, a opção 3 é potencialmente a melhor para isso, porque você ainda pode ter o database “ativo” enquanto esta operação está ocorrendo. Se você usar a opção 1, a tabela é bloqueada enquanto a operação acontece e você fica realmente preso.

Se você realmente não se importa se a coluna está na tabela, então suponho que uma abordagem segmentada é a próxima melhor. No entanto, eu realmente tento evitar isso (a ponto de não fazê-lo) porque, como Charles Bretana diz, você terá que certificar-se e encontrar todos os lugares que atualizar / inserir essa tabela e modificá-los. Ugh!

Eu tive um problema semelhante, e fui para a sua opção # 2. Demora 20 minutos desta forma, ao contrário de 32 horas para o outro lado !!! Enorme diferença, obrigado pela dica. Eu escrevi uma input de blog completa sobre isso, mas aqui está o sql importante:

 Alter table MyTable Add MyNewColumn char(10) null default '?'; go update MyTable set MyNewColumn='?' where MyPrimaryKey between 0 and 1000000 go update MyTable set MyNewColumn='?' where MyPrimaryKey between 1000000 and 2000000 go update MyTable set MyNewColumn='?' where MyPrimaryKey between 2000000 and 3000000 go ..etc.. Alter table MyTable Alter column MyNewColumn char(10) not null; 

E a input do blog se você estiver interessado: http://splinter.com.au/adding-a-column-to-a-massive-sql-server-table

Eu tive um problema semelhante e fui com a abordagem # 3 modificada. No meu caso, o database estava no modo de recuperação SIMPLE e a tabela na qual a coluna deveria ser adicionada não era referenciada por nenhuma restrição de FK.

Em vez de criar uma nova tabela com o mesmo esquema e copiar o conteúdo da tabela original, usei a syntax SELECT… INTO .

De acordo com a Microsoft ( http://technet.microsoft.com/en-us/library/ms188029(v=sql.105).aspx )

A quantidade de registro para SELECT … INTO depende do modelo de recuperação em vigor para o database. Sob o modelo de recuperação simples ou o modelo de recuperação bulk-logged, as operações em massa são minimamente registradas. Com registro mínimo, o uso da instrução SELECT… INTO pode ser mais eficiente do que criar uma tabela e, em seguida, preencher a tabela com uma instrução INSERT. Para obter mais informações, consulte Operações que podem ser registradas minimamente.

A seqüência de etapas:

1.Move dados da tabela antiga para nova ao adicionar nova coluna com padrão

  SELECT table.*, cast ('default' as nvarchar(256)) new_column INTO table_copy FROM table 

Tabela velha 2.Drop

  DROP TABLE table 

3.Rename recém-criado tabela

  EXEC sp_rename 'table_copy', 'table' 

4.Criar restrições e índices necessários na nova tabela

No meu caso, a tabela tinha mais de 100 milhões de linhas e essa abordagem foi concluída mais rapidamente do que a abordagem # 2 e o crescimento do espaço de log foi mínimo.

Admitiu que esta é uma questão antiga. Recentemente, meu colega me disse que era capaz de fazer isso em uma única instrução alter table em uma tabela com 13,6 milhões de linhas. Ele terminou em um segundo no SQL Server 2012. Consegui confirmar o mesmo em uma tabela com 8 milhões de linhas. Algo mudou na versão posterior do SQL Server?

 Alter table mytable add mycolumn char(1) not null default('N'); 

1) Adicione a coluna à tabela com um valor padrão:

 ALTER TABLE MyTable ADD MyColumn int default 0 

2) Atualize os valores incrementalmente na tabela (mesmo efeito da resposta aceita). Ajuste o número de registros que estão sendo atualizados para o seu ambiente, para evitar o bloqueio de outros usuários / processos.

 declare @rowcount int = 1 while (@rowcount > 0) begin UPDATE TOP(10000) MyTable SET MyColumn = 0 WHERE MyColumn IS NULL set @rowcount = @@ROWCOUNT end 

3) Altere a definição da coluna para exigir não nulo. Execute o seguinte no momento em que a tabela não estiver em uso (ou programe alguns minutos de inatividade). Eu usei com sucesso isso para tabelas com milhões de registros.

 ALTER TABLE MyTable ALTER COLUMN MyColumn int NOT NULL 

Eu usaria CURSOR em vez de UPDATE. O cursor atualizará todos os registros correspondentes em lote, registro por registro – leva tempo, mas não bloqueia a tabela.

Se você quiser evitar bloqueios, use WAIT.

Também não tenho certeza, que a restrição DEFAULT altera as linhas existentes. Provavelmente, o uso da restrição NOT NULL junto com o DEFAULT causa casos descritos pelo autor.

Se mudar, adicione-o no final Assim, o pseudocódigo se parecerá com:

 -- without NOT NULL constrain -- we will add it in the end ALTER TABLE table ADD new_column INT DEFAULT 0 DECLARE fillNullColumn CURSOR LOCAL FAST_FORWARD SELECT key FROM table WITH (NOLOCK) WHERE new_column IS NULL OPEN fillNullColumn DECLARE @key INT FETCH NEXT FROM fillNullColumn INTO @key WHILE @@FETCH_STATUS = 0 BEGIN UPDATE table WITH (ROWLOCK) SET new_column = 0 -- default value WHERE key = @key WAIT 00:00:05 --wait 5 seconds, keep in mind it causes updating only 12 rows per minute FETCH NEXT FROM fillNullColumn INTO @key END CLOSE fillNullColumn DEALLOCATE fillNullColumn ALTER TABLE table ALTER COLUMN new_column ADD CONSTRAIN xxx 

Tenho certeza de que existem alguns erros de syntax, mas espero que isso ajude a resolver seu problema.

Boa sorte!

Segue verticalmente a tabela. Isso significa que você terá duas tabelas, com a mesma chave primária e exatamente o mesmo número de registros … Uma será a que você já tem, a outra terá apenas a chave e a nova coluna Não-Nula (com valor padrão) . Modifique todos os Insert, Update e delete code para que eles mantenham as duas tabelas em sincronia … Se você quiser, pode criar uma view que “une” as duas tabelas juntas para criar uma única combinação lógica das duas que aparece como uma única table for client Selecionar instruções …