Hinzufügen von Spalten zu Produktionstabellen
Was ist der beste Weg, um großen Produktionstabellen unter SQL Server 2008 R2 Spalten hinzuzufügen? Laut den Online-Büchern von Microsoft:
Die in ALTER TABLE angegebenen Änderungen werden sofort implementiert. Wenn die Änderungen Änderungen an den Zeilen in der Tabelle erfordern, aktualisiert ALTER TABLE die Zeilen. ALTER TABLE erhält eine Schemamodifikationssperre für die Tabelle, um sicherzustellen, dass während der Änderung keine anderen Verbindungen auf die Metadaten für die Tabelle verweisen, mit Ausnahme von Online-Indexoperationen, für die am Ende eine sehr kurze SCH-M-Sperre erforderlich ist.
(http://msdn.Microsoft.com/en-us/library/ms190273.aspx)
Bei einer großen Tabelle mit Millionen von Zeilen kann dies eine Weile dauern. Ist ein Ausfall die einzige Option? Was ist der beste Weg, um mit dieser Art von Situation umzugehen?
"Es hängt davon ab, ob"
Wenn Sie eine Spalte hinzufügen, für die keine Daten zu den Zeilen hinzugefügt werden müssen, kann dies recht schnell gehen.
Zum Hinzufügen eines int oder char sind beispielsweise physische Zeilenbewegungen erforderlich. Das Hinzufügen eines nullbaren Varchars ohne Standard sollte nicht (es sei denn, die NULL-Bitmap muss erweitert werden).
Sie müssen es mit einer wiederhergestellten Kopie der Produktion versuchen, um einen Kostenvoranschlag zu erhalten
Das Erstellen einer neuen Tabelle, das Kopieren und Umbenennen kann länger dauern, wenn Sie Indizes und Schlüssel für eine Milliardenzeilentabelle erneut hinzufügen müssen.
Ich habe Milliarden Zeilentabellen geändert, die einige Sekunden gebraucht haben, um eine nullfähige Spalte hinzuzufügen.
Habe ich gesagt, zuerst ein Backup zu machen?
Wenn die Spalte NULL ist, sollte die Auswirkung vernachlässigbar sein. Wenn die Spalte nicht NULL sein kann und der Wert festgelegt werden muss, kann dies ganz anders sein. Was ich in diesem Fall tun würde, ist, anstatt in einem Schuss eine Nicht-Null- und Standardeinschränkung hinzuzufügen, effektiv Daten zu jeder Zeile hinzuzufügen:
- fügen Sie die Spalte als NULLable hinzu - sollte in den meisten Fällen schnell sein
- aktualisieren Sie die Werte auf den Standardwert
- sie können dies bei Bedarf stapelweise tun
- sie können dies auch verwenden, um bedingte Logik anzuwenden, bei der einige Zeilen möglicherweise nicht die Standardeinstellung erhalten
- fügen Sie die Nicht-Null-/Standardeinschränkungen hinzu
- dies ist schneller, wenn keine der Daten NULL ist, sollte aber dennoch messbar sein
Stimmen Sie mit @gbn überein, dass Sie dies testen können, indem Sie eine Kopie der Produktion wiederherstellen und dort versuchen ... Sie erhalten eine gute Vorstellung vom Timing (vorausgesetzt, die Hardware ist etwas ähnlich) und Sie können auch die Auswirkungen auf das Transaktionsprotokoll sehen.
Haben Sie überlegt:
- Erstellen einer neuen Tabelle, die die Änderungen an der Tabellendefinition enthält.
- Einfügen in die neue Tabellendefinition Auswahl aus der ursprünglichen Tabelle.
- Umbenennen der ursprünglichen Tabelle in _orig und Umbenennen der neuen Tabelle in den ursprünglichen Tabellennamen.
Der Nachteil hierbei ist, dass Sie genügend Speicherplatz in der Datenbank haben müssen, um diese Änderung vorzunehmen. Möglicherweise benötigen Sie noch eine Lesesperre für die Tabelle, um fehlerhafte Lesevorgänge zu vermeiden.
Sie minimieren jedoch die Auswirkungen auf die Endbenutzer, wenn die Möglichkeit besteht oder gleichzeitig auf die ursprüngliche Tabelle zugegriffen werden muss. Es sollte auch die Sperrdauer minimieren.
Ich habe eine besondere Ausnahme, die meiner Meinung nach erwähnt werden sollte.
Ab SQL Server 2012 Enterprise ist das Hinzufügen einer neuen NOT NULL-Spalte mit einer Laufzeitkonstante eine Online-Operation, die sofort abgeschlossen wird und nicht von der abhängt Anzahl der Zeilen in der Tabelle.
Weitere Informationen hierzu finden Sie in MSDN
Ich werde den wichtigen Abschnitt reproduzieren
Ab SQL Server 2012 (11.x) Enterprise Edition ist das Hinzufügen einer NOT NULL-Spalte mit einem Standardwert eine Online-Operation, wenn der Standardwert eine Laufzeitkonstante ist. Dies bedeutet, dass der Vorgang trotz der Anzahl der Zeilen in der Tabelle fast sofort abgeschlossen ist. Weil die vorhandenen Zeilen in der Tabelle während des Vorgangs nicht aktualisiert werden. Stattdessen wird der Standardwert nur in den Metadaten der Tabelle gespeichert und der Wert wird bei Bedarf in Abfragen nachgeschlagen, die auf diese Zeilen zugreifen.