una - sql server alter table add column default value




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

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.

https://code.i-harness.com

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


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

Esempio:

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

Esempio:

ALTER TABLE tes 
ADD ssd  NUMBER   DEFAULT '0';

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.


Prova questo

ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO

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

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

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

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

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

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!


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)


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.


ALTER TABLE <table name> 
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name> 
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO

ALTER TABLE ADD ColumnName {Column_Type} Constraint

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


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.


ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)

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





sql-server-2000