Aggiungere una colonna con un valore predefinito a una tabella esistente in SQL Server

Come può essere aggiunta una colonna con un valore predefinito a una tabella esistente in SQL Server 2000 / SQL Server 2005 ?

Sintassi:

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

Esempio:

 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. 

Gli appunti:

Nome vincolo facoltativo:
Se si CONSTRAINT D_SomeTable_SomeCol SQL Server si autogenererà
un DF__SomeTa__SomeC__4FB7FEF6 con un nome divertente come: DF__SomeTa__SomeC__4FB7FEF6

Dichiarazione facoltativa con valori:
WITH VALUES è necessario solo quando la tua colonna è Nullable
e si desidera il valore predefinito utilizzato per i record esistenti.
Se la tua colonna NOT NULL è NOT NULL , allora utilizzerà automaticamente il valore predefinito
per tutti i record esistenti, indipendentemente dal fatto che si specifichi WITH VALUES o meno.

In che modo gli inserti funzionano con un vincolo predefinito:
Se si inserisce un record in SomeTable e non si specifica il valore di SomeCol , verrà SomeCol su 0 .
Se si inserisce un record e si specifica il valore di SomeCol come NULL (e la colonna consente i valori null),
quindi il vincolo predefinito non verrà utilizzato e NULL verrà inserito come valore.

Le note erano basate sull’ottimo feedback di tutti di seguito.
Ringraziamenti speciali a:
@Yatrix, @WalterStabosz, @YahooSerious e @StackMan per i loro commenti.

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

L’inclusione di DEFAULT riempie la colonna nelle righe esistenti con il valore predefinito, quindi il vincolo NOT NULL non viene violato.

Quando si aggiunge una colonna nullable , WITH VALUES garantisce che il valore DEFAULT specifico venga applicato alle righe esistenti:

 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' 

Fai attenzione quando la colonna che stai aggiungendo ha un vincolo NOT NULL , ma non ha un vincolo DEFAULT (valore). L’istruzione ALTER TABLE fallirà in quel caso se la tabella contiene delle righe. La soluzione è rimuovere il vincolo NOT NULL dalla nuova colonna o fornire un vincolo DEFAULT per esso.

La versione più semplice con solo due linee

 ALTER TABLE MyTable ADD MyNewColumn INT NOT NULL DEFAULT 0 

Uso:

 -- 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 vuoi aggiungere più colonne puoi farlo in questo modo ad esempio:

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

Uso:

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

Riferimento: ALTER TABLE (Transact-SQL) (MSDN)

Puoi fare la cosa con T-SQL nel modo seguente.

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

Oltre a poter utilizzare SQL Server Management Studio, puoi anche fare clic con il pulsante destro del mouse sul menu Design, impostando il valore predefinito sulla tabella.

Inoltre, se si desidera aggiungere la stessa colonna (se non esiste) a tutte le tabelle nel database, utilizzare:

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

In SQL Server 2008-R2, vado nella modalità di progettazione – in un database di test – e aggiungo le mie due colonne utilizzando il designer e ho apportato le impostazioni con la GUI, quindi il famigerato clic destro offre l’opzione ” Genera script di modifica ” !

Bang up apre una piccola finestra con, avete indovinato, lo script di modifica garantita sul lavoro correttamente formattato. Premi il pulsante facile.

In alternativa, puoi aggiungere un valore predefinito senza dover esplicitamente denominare il vincolo:

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

Se si verifica un problema con i vincoli predefiniti esistenti durante la creazione di questo vincolo, possono essere rimossi da:

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

Per aggiungere una colonna a una tabella di database esistente con un valore predefinito, possiamo usare:

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

Ecco un altro modo per aggiungere una colonna a una tabella di database esistente con un valore predefinito.

Uno script SQL molto più approfondito per aggiungere una colonna con un valore predefinito è al di sotto che include il controllo se la colonna esiste prima di aggiungerla, anche se si verifica il vincolo e lo si elimina se ce n’è uno. Questo script nomina anche il vincolo in modo che possiamo avere una bella convenzione di denominazione (mi piace DF_) e se non SQL ci darà un vincolo con un nome che ha un numero generato casualmente; quindi è bello poter nominare anche il vincolo.

 ------------------------------------------------------------------------- -- 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 

Questi sono due modi per aggiungere una colonna a una tabella di database esistente con un valore predefinito.

 ALTER TABLE ADD ColumnName {Column_Type} Constraint 

L’articolo MSDN ALTER TABLE (Transact-SQL) presenta tutta la syntax di alter table.

Esempio:

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

Questo può essere fatto anche nella GUI SSMS. Io mostro una data di default sotto, ma il valore di default può essere qualunque cosa, ovviamente.

  1. Metti la tua tabella in visualizzazione disegno (fai clic con il tasto destro del mouse su object explorer-> Design)
  2. Aggiungi una colonna alla tabella (o fai clic sulla colonna che vuoi aggiornare se già esiste)
  3. In Proprietà colonna di seguito, immettere (getdate()) o abc o 0 o qualsiasi valore desiderato nel campo Valore predefinito o Binding , come illustrato di seguito:

inserisci la descrizione dell'immagine qui

Esempio:

 ALTER TABLE tes ADD ssd NUMBER DEFAULT '0'; 

SQL Server + Modifica tabella + Aggiungi colonna + Valore predefinito uniqueidentifier

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

Per prima cosa crea una tabella con nome studente:

 CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL) 

Aggiungi una colonna ad esso:

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

La tabella viene creata e una colonna viene aggiunta a una tabella esistente con un valore predefinito.

Immagine 1

Prova questo

 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 

Questo ha molte risposte, ma sento il bisogno di aggiungere questo metodo esteso. Questo sembra molto più lungo, ma è estremamente utile se si aggiunge un campo NOT NULL a una tabella con milioni di righe in un database attivo.

 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 

Ciò che questo farà è aggiungere la colonna come campo nullable e con il valore predefinito, aggiornare tutti i campi al valore predefinito (oppure assegnare valori più significativi) e alla fine cambierà la colonna in NOT NOT.

La ragione di ciò è che se si aggiorna una tabella di grandi dimensioni e si aggiunge un nuovo campo non nullo che deve scrivere su ogni singola riga, con la presente si bloccherà l’intera tabella man mano che si aggiunge la colonna e quindi si scrivono tutti i valori.

Questo metodo aggiungerà la colonna nullable che opera molto più velocemente da sola, quindi riempie i dati prima di impostare lo stato non nullo.

Ho scoperto che eseguire l’intera operazione in un’unica istruzione bloccherà uno dei nostri tavoli più attivi per 4-8 minuti e molto spesso ho ucciso il processo. Questo metodo richiede solitamente solo pochi secondi e causa un blocco minimo.

Inoltre, se si dispone di una tabella nell’area di miliardi di righe, potrebbe valere la pena di eseguire il batching dell’aggiornamento in questo modo:

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

Aggiungi una nuova colonna a una tabella:

 ALTER TABLE [table] ADD Column1 Datatype 

Per esempio,

 ALTER TABLE [test] ADD ID Int 

Se l’utente vuole farlo incrementare automaticamente, allora:

 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 

Questo può essere fatto con il codice seguente.

 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) 

Da questa query è ansible aggiungere una colonna di un intero tipo di dati con il valore predefinito 0.

Bene, ora ho qualche modifica alla mia risposta precedente. Ho notato che nessuna delle risposte citate IF NOT EXISTS . Quindi ne fornirò una nuova soluzione poiché ho riscontrato alcuni problemi nell’alterare la tabella.

 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 

Qui TaskSheet è il nome della tabella particolare e IsBilledToClient è la nuova colonna che stai per inserire e 1 il valore predefinito. Ciò significa che nella nuova colonna quale sarà il valore delle righe esistenti, quindi verrà impostato automaticamente lì. Tuttavia, puoi cambiare come desideri con il rispetto del tipo di colonna come se avessi usato BIT , quindi inserisco il valore predefinito 1.

Suggerisco il sistema di cui sopra, perché ho avuto un problema. Quindi qual’è il problema? Il problema è che se la colonna IsBilledToClient esiste nella tabella tabella, se si esegue solo la parte del codice indicata di seguito, verrà visualizzato un errore nel generatore Query del server SQL. Ma se non esiste allora per la prima volta non ci saranno errori durante l’esecuzione.

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

Se il valore predefinito è Null, quindi:

  1. In SQL Server, apri l’albero della tabella di destinazione
  2. Fai clic con il tasto destro del mouse su “Colonne” ==> New Column
  3. Digitare la colonna Nome, Select Type e selezionare la casella di controllo Consenti null
  4. Dalla barra dei menu, fare Save su Save

Fatto!

SQL Server + Modifica tabella + Aggiungi colonna + Valore predefinito uniqueidentifier …

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