Adicione uma coluna com um valor padrão a uma tabela existente no SQL Server

Como uma coluna com um valor padrão pode ser adicionada a uma tabela existente no SQL Server 2000 / SQL Server 2005 ?

Sintaxe:

ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} WITH VALUES 

Exemplo:

 ALTER TABLE SomeTable ADD SomeCol Bit NULL --Or NOT NULL. CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated. DEFAULT (0)--Optional Default-Constraint. WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records. 

Notas:

Nome de restrição opcional:
Se você deixar CONSTRAINT D_SomeTable_SomeCol , o SQL Server será CONSTRAINT D_SomeTable_SomeCol automaticamente
uma Default-Contraint com um nome engraçado como: DF__SomeTa__SomeC__4FB7FEF6

Declaração Opcional Com Valores:
O WITH VALUES só é necessário quando sua coluna é anulável
e você deseja o valor padrão usado para registros existentes.
Se sua coluna NOT NULL for NOT NULL , ela usará automaticamente o valor padrão
para todos os registros existentes, se você especificar WITH VALUES ou não.

Como as inserções funcionam com uma restrição padrão:
Se você inserir um registro em SomeTable e não especificar o valor de SomeCol , o padrão será 0 .
Se você inserir um registro e especificar o valor de SomeCol como NULL (e sua coluna permitir valores nulos),
então a restrição padrão não será usada e NULL será inserido como o valor.

As notas foram baseadas no ótimo feedback de todos abaixo.
Agradecimentos especiais para:
@Yatrix, @WalterStabosz, @YahooSerious e @StackMan para seus comentários.

 ALTER TABLE Protocols ADD ProtocolTypeID int NOT NULL DEFAULT(1) GO 

A inclusão do DEFAULT preenche a coluna nas linhas existentes com o valor padrão, portanto, a restrição NOT NULL não é violada.

Ao adicionar uma coluna anulável , WITH VALUES garantirá que o valor DEFAULT específico seja aplicado às linhas existentes:

 ALTER TABLE table ADD column BIT -- Demonstration with NULL-able column added CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES 
 ALTER TABLE  ADD   NOT NULL GO ALTER TABLE 
ADD CONSTRAINT DEFAULT FOR GO
 ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES' 

Tenha cuidado quando a coluna que você está adicionando tiver uma restrição NOT NULL , mas ainda não tiver uma restrição DEFAULT (valor). A instrução ALTER TABLE falhará nesse caso se a tabela tiver alguma linha nela. A solução é remover a restrição NOT NULL da nova coluna ou fornecer uma restrição DEFAULT para ela.

A versão mais básica com apenas duas linhas

 ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL DEFAULT 0 

Usar:

 -- Add a column with a default DateTime -- to capture when each record is added. ALTER TABLE myTableName ADD RecordAddedDate smalldatetime NULL DEFAULT(GetDate()) GO 

Se você quiser adicionar várias colunas, você pode fazer desta forma, por exemplo:

 ALTER TABLE YourTable ADD Column1 INT NOT NULL DEFAULT 0, Column2 INT NOT NULL DEFAULT 1, Column3 VARCHAR(50) DEFAULT 'Hello' GO 

Usar:

 ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} 

Referência: ALTER TABLE (Transact-SQL) (MSDN)

Você pode fazer a coisa com o T-SQL da seguinte maneira.

  ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} 

Assim como você pode usar o SQL Server Management Studio também clicando com o botão direito do mouse na tabela no menu Design, definindo o valor padrão como tabela.

E, além disso, se você quiser adicionar a mesma coluna (se ela não existir) a todas as tabelas no database, use:

  USE AdventureWorks; EXEC sp_msforeachtable 'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ; 

No SQL Server 2008 R2, eu vou para o modo de design – em um database de teste – e adiciono minhas duas colunas usando o designer e faço as configurações com a GUI, e então o infame Right-Click dá a opção ” Generate Change Script ” !

Bang up pops uma pequena janela com, você adivinhou, o roteiro de mudança garantido para o trabalho devidamente formatado. Aperte o botão fácil.

Como alternativa, você pode adicionar um padrão sem ter que nomear explicitamente a restrição:

 ALTER TABLE [schema].[tablename] ADD DEFAULT ((0)) FOR [columnname] 

Se você tiver um problema com as restrições padrão existentes ao criar essa restrição, elas poderão ser removidas por:

 alter table [schema].[tablename] drop constraint [constraintname] 

Para adicionar uma coluna a uma tabela de database existente com um valor padrão, podemos usar:

 ALTER TABLE [dbo.table_name] ADD [Column_Name] BIT NOT NULL Default ( 0 ) 

Aqui está outra maneira de adicionar uma coluna a uma tabela de database existente com um valor padrão.

Um script SQL muito mais completo para adicionar uma coluna com um valor padrão está abaixo, incluindo a verificação se a coluna existe antes de adicioná-la, também checando a restrição e soltando-a se houver uma. Este script também nomeia a restrição para que possamos ter uma boa convenção de nomenclatura (eu gosto de DF_) e, se não for, SQL nos dará uma restrição com um nome que tenha um número gerado aleatoriamente; então é legal poder nomear a restrição também.

 ------------------------------------------------------------------------- -- Drop COLUMN -- Name of Column: Column_EmployeeName -- Name of Table: table_Emplyee -------------------------------------------------------------------------- IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table_Emplyee' AND COLUMN_NAME = 'Column_EmployeeName' ) BEGIN IF EXISTS ( SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]') AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]') ) BEGIN ------ DROP Contraint ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName] PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped' END -- ----- DROP Column ----------------------------------------------------------------- ALTER TABLE [dbo].table_Emplyee DROP COLUMN Column_EmployeeName PRINT 'Column Column_EmployeeName in images table was dropped' END -------------------------------------------------------------------------- -- ADD COLUMN Column_EmployeeName IN table_Emplyee table -------------------------------------------------------------------------- IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table_Emplyee' AND COLUMN_NAME = 'Column_EmployeeName' ) BEGIN ----- ADD Column & Contraint ALTER TABLE dbo.table_Emplyee ADD Column_EmployeeName BIT NOT NULL CONSTRAINT [DF_table_Emplyee_Column_EmployeeName] DEFAULT (0) PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added' PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added' END GO 

Essas são duas maneiras de adicionar uma coluna a uma tabela de database existente com um valor padrão.

 ALTER TABLE ADD ColumnName {Column_Type} Constraint 

O artigo do MSDN ALTER TABLE (Transact-SQL) tem toda a syntax da tabela alter.

Exemplo:

 ALTER TABLE [Employees] ADD Seniority int not null default 0 GO 

Isso também pode ser feito na GUI do SSMS. Eu mostro uma data padrão abaixo, mas o valor padrão pode ser o que for, claro.

  1. Coloque sua tabela no modo de design (clique com o botão direito na tabela em object explorer-> Design)
  2. Adicione uma coluna à tabela (ou clique na coluna que você deseja atualizar se já existir)
  3. Em Propriedades da Coluna abaixo, insira (getdate()) ou abc ou 0 ou qualquer outro valor que você queira no campo Valor Padrão ou Vinculação como ilustrado abaixo:

insira a descrição da imagem aqui

Exemplo:

 ALTER TABLE tes ADD ssd NUMBER DEFAULT '0'; 

SQL Server + Alterar Tabela + Adicionar Coluna + Valor Padrão uniqueidentifier

 ALTER TABLE Product ADD ReferenceID uniqueidentifier not null default (cast(cast(0 as binary) as uniqueidentifier)) 

Primeiro crie uma tabela com nome aluno:

 CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL) 

Adicione uma coluna a ela:

 ALTER TABLE STUDENT ADD STUDENT_NAME INT NOT NULL DEFAULT(0) SELECT * FROM STUDENT 

A tabela é criada e uma coluna é incluída em uma tabela existente com um valor padrão.

Imagem 1

Tente isso

 ALTER TABLE Product ADD ProductID INT NOT NULL DEFAULT(1) GO 
 IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='TABLENAME' AND COLUMN_NAME = 'COLUMNNAME' ) BEGIN ALTER TABLE TABLENAME ADD COLUMNNAME Nvarchar(MAX) Not Null default END 

Isso tem muitas respostas, mas sinto a necessidade de adicionar esse método estendido. Isso parece muito mais longo, mas é extremamente útil se você estiver adicionando um campo NOT NULL a uma tabela com milhões de linhas em um database ativo.

 ALTER TABLE {schemaName}.{tableName} ADD {columnName} {datatype} NULL CONSTRAINT {constraintName} DEFAULT {DefaultValue} UPDATE {schemaName}.{tableName} SET {columnName} = {DefaultValue} WHERE {columName} IS NULL ALTER TABLE {schemaName}.{tableName} ALTER COLUMN {columnName} {datatype} NOT NULL 

O que isso fará é adicionar a coluna como um campo anulável e com o valor padrão, atualizar todos os campos para o valor padrão (ou você pode atribuir valores mais significativos) e, finalmente, ele alterará a coluna para NOT NOT NULL.

A razão para isso é se você atualizar uma tabela de grande escala e adicionar um novo campo não nulo, ele terá que gravar em cada linha e, assim, bloqueará a tabela inteira à medida que ela adiciona a coluna e grava todos os valores.

Esse método adicionará a coluna anulável, que opera muito mais rapidamente sozinha, e preenche os dados antes de definir o status não nulo.

Descobri que fazer a coisa toda em uma declaração bloqueia uma de nossas tabelas mais ativas por 4 a 8 minutos e, com bastante frequência, eu matei o processo. Este método cada parte geralmente leva apenas alguns segundos e causa um bloqueio mínimo.

Além disso, se você tiver uma tabela na área de bilhões de linhas, pode valer a atualização em lote da seguinte forma:

 WHILE 1=1 BEGIN UPDATE TOP (1000000) {schemaName}.{tableName} SET {columnName} = {DefaultValue} WHERE {columName} IS NULL IF @@ROWCOUNT < 1000000 BREAK; END 

Adicione uma nova coluna a uma tabela:

 ALTER TABLE [table] ADD Column1 Datatype 

Por exemplo,

 ALTER TABLE [test] ADD ID Int 

Se o usuário quiser aumentar automaticamente, então:

 ALTER TABLE [test] ADD ID Int IDENTITY(1,1) NOT NULL 
 --Adding Value with Default Value ALTER TABLE TestTable ADD ThirdCol INT NOT NULL DEFAULT(0) GO 

Isso pode ser feito pelo código abaixo.

 CREATE TABLE TestTable (FirstCol INT NOT NULL) GO ------------------------------ -- Option 1 ------------------------------ -- Adding New Column ALTER TABLE TestTable ADD SecondCol INT GO -- Updating it with Default UPDATE TestTable SET SecondCol = 0 GO -- Alter ALTER TABLE TestTable ALTER COLUMN SecondCol INT NOT NULL GO 
 ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0) 

A partir dessa consulta, você pode adicionar uma coluna de tipo de dados inteiro com o valor padrão 0.

Bem, agora tenho algumas modificações na minha resposta anterior. Eu notei que nenhuma das respostas mencionadas IF NOT EXISTS . Então, vou fornecer uma nova solução, já que enfrentei alguns problemas ao alterar a tabela.

 IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'TaskSheet' AND column_name = 'IsBilledToClient') BEGIN ALTER TABLE dbo.TaskSheet ADD IsBilledToClient bit NOT NULL DEFAULT ((1)) END GO 

Aqui TaskSheet é o nome da tabela específica e IsBilledToClient é a nova coluna que você irá inserir e 1 o valor padrão. Isso significa que na nova coluna qual será o valor das linhas existentes, portanto, um será definido automaticamente lá. No entanto, você pode alterar como quiser com o respeito do tipo de coluna como eu usei o BIT , então eu coloquei no valor padrão 1.

Eu sugiro o sistema acima, porque enfrentei um problema. Então qual é o problema? O problema é que, se a coluna IsBilledToClient existir na tabela table, se você executar apenas a parte do código fornecida abaixo, verá um erro no construtor SQL Query server. Mas se não existir, pela primeira vez não haverá erro durante a execução.

 ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} [WITH VALUES] 

Se o padrão for Nulo, então:

  1. No SQL Server, abra a tree da tabela de destino
  2. Clique com o botão direito em “Colunas” ==> New Column
  3. Digite o nome da coluna, Select Type e marque a checkbox de seleção Permitir nulos
  4. Na Barra de Menus, clique em Save

Feito!

SQL Server + Alterar Tabela + Adicionar Coluna + Valor Padrão uniqueidentifier …

 ALTER TABLE [TABLENAME] ADD MyNewColumn INT not null default 0 GO