Hinzufügen einer Spalte mit einem Standardwert zu einer vorhandenen Tabelle in SQL Server

Question

Wie kann ich eine Spalte mit einem Standardwert zu einer bestehenden Tabelle in SQL-Server 2000 / SQL Server 2005 ?

Answer 1

Hier gibt es viele Antworten, aber ich habe das Bedürfnis, diese erweiterte Methode hinzuzufügen. Sie scheint viel länger zu sein, ist aber äußerst nützlich, wenn Sie ein NOT NULL-Feld zu einer Tabelle mit Millionen von Zeilen in einer aktiven Datenbank hinzufügen wollen.

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

Dadurch wird die Spalte als löschbares Feld und mit dem Standardwert hinzugefügt, alle Felder werden auf den Standardwert aktualisiert (oder Sie können aussagekräftigere Werte zuweisen), und schließlich wird die Spalte in NOT NULL geändert.

Der Grund dafür ist, wenn Sie eine große Tabelle aktualisieren und ein neues Nicht-Null-Feld hinzufügen, muss in jede einzelne Zeile geschrieben werden und dadurch wird die gesamte Tabelle gesperrt, da die Spalte hinzugefügt und dann alle Werte geschrieben werden.

Diese Methode fügt die löschbare Spalte hinzu, die von sich aus viel schneller arbeitet, und füllt dann die Daten, bevor der Nicht-Null-Status gesetzt wird.

Ich habe festgestellt, dass die Durchführung des gesamten Vorgangs in einer einzigen Anweisung eine unserer aktiveren Tabellen für 4-8 Minuten sperrt, und oft habe ich den Prozess abgebrochen. Bei dieser Methode dauert jeder Teil normalerweise nur ein paar Sekunden und verursacht nur minimale Sperren.

Wenn Sie eine Tabelle mit Milliarden von Zeilen haben, kann es sich außerdem lohnen, die Aktualisierung wie folgt zu stapeln:

WHILE 1=1
BEGIN
    UPDATE TOP (1000000) {schemaName}.{tableName}
        SET {columnName} = {DefaultValue}
        WHERE {columName} IS NULL

    IF @@ROWCOUNT < 1000000
        BREAK;
END

Answer 2

SQL Server + Tabelle ändern + Spalte hinzufügen + Standardwert uniqueidentifier

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

Answer 3

Versuchen Sie dies

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

Answer 4

--Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO

Answer 5

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