Hinzufügen von Spalten mit default-Wert zu große Tabelle

Ich habe eine Tabelle mit 40mil records. Ich muss hinzufügen eines neuen INT not NULL-Spalte der Tabelle, mit Standardwert = 0

Beim hinzufügen dieser Spalte, und verwenden Sie die folgenden:

ALTER TABLE myTable ADD NewColumnID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 0

Er stellt die NewColumnID 0 für alle Datensätze. Beim ausführen dieser Abfrage auf unserer prod Tabelle, die 40mil records, wird das lange dauern? Weil ich weiß, tun die folgende SEHR LANGE dauert:

UPDATE myTable SET NewColumnID = 0
  • Und Ihre Frage ist ?
  • Es ist lang und es scheint normal.
  • Sie könnte... das hinzufügen der Spalte 'NULL', und aktualisieren Sie dann die Tabelle noch ein wenig in einer Zeit
  • Wie kann Sie nicht wissen, ob das hinzufügen der Feld dauert eine lange Zeit, wenn Sie wissen aktualisieren es funktioniert?
InformationsquelleAutor FaNIX | 2013-11-19
  1. 4

    Das große problem ist, dass dieses schreiben muss um jede einzelne Zeile, die stark protokolliert und als eine einzige Transaktion. Ein Weg, um zu minimieren die Auswirkungen auf das Protokoll (und das funktioniert am besten, wenn Sie nicht dumm 10% vergrößert-Einstellungen auf Ihre log-Datei) zu brechen, bis die arbeiten soweit wie möglich:

    1. fügen Sie eine null-Spalte:

      ALTER TABLE dbo.myTable 
 ADD NewColumnID INT CONSTRAINT DF_Constraint DEFAULT 0;

    2. Update Zeilen in einer batch, sagen wir 10K Zeilen gleichzeitig (dies minimiert log-Auswirkungen - siehe dieser blog-post für hintergrund):

      BEGIN TRANSACTION;
SELECT 1;
WHILE @@ROWCOUNT > 0
BEGIN
  COMMIT TRANSACTION;
  BEGIN TRANSACTION;

  UPDATE TOP (10000) dbo.myTable SET NewColumnID = 0;
END
COMMIT TRANSACTION;

    3. Hinzufügen einer check-Einschränkung (sehen Sie diese Antworten für mehr Details):

      ALTER TABLE dbo.myTable WITH CHECK
  ADD CONSTRAINT NewCol_Not_Null
  CHECK (NewColumnID IS NOT NULL);

      Können Sie Zeit sparen, indem NOCHECK hier, aber wie Martin erklärte in seiner Antwort, dass ist ein einmalige Einsparungen, die Sie Kosten könnte viel von Kopfschmerzen über längere Sicht.

    Diese gerichtet war, in diese Vorherige Frage, aber die akzeptierte Antwort gibt, nutzt NOCHECK ohne Haftungsausschluss darüber, wie eine nicht Vertrauenswürdige Einschränkung kann Auswirkungen auf die Ausführung der Pläne.

    InformationsquelleAutor Aaron Bertrand
  2. 1

    Die Zeit, die für eine update-Abfrage ausführen, ist völlig abhängig von der zugrunde liegenden hardware und keiner hier kann Ihnen eine klare Antwort. Aktualisieren von 40 Millionen Datensätzen könnte Sekunden dauern oder Tage, abhängig von der hardware.. speziell, Menge an RAM in der Maschine und die Geschwindigkeit der Festplatten.

    Sagte, dass, wenn ein einfaches update, wie Sie gezeigt, nimmt eine "SEHR LANGE ZEIT" dann eine alter-Anweisung wird wahrscheinlich so lange dauern oder länger.

    • Danke für deinen Kommentar, aber Sie wurden völlig falsch. Ich habe es getestet. Die ALTER-Anweisung ausgeführt, werden fast augenblicklich. Update Tabelle dauert über eine Stunde. Sorry, aber -1 für, die.
    InformationsquelleAutor NotMe
  3. 1

    Nicht, wie der SQL Server 2012, dieser wird sofort funktionieren:

    Vor SQL Server 2012, wenn Sie ein neues nicht-null-Spalte mit
    Standard-Werten zu einer vorhandenen Tabelle eine Größe-Daten-operation Auftritt:
    jede Zeile in der Tabelle wird aktualisiert und fügen Sie den Standardwert des neuen
    Spalte. Bei kleinen Tabellen ist dies unbedeutend, aber für große Tabellen
    dies kann so problematisch sein, wie vollständig verbieten den Betrieb.
    Aber beginnend mit SQL Server 2012-der Betrieb ist, in den meisten Fällen
    plötzlich: nur die Metadaten der Tabelle geändert wird, werden keine Zeilen werden
    aktualisiert.

    http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/

    InformationsquelleAutor Loren Paulsen
  4. 0

    Wenn Sie behandeln können NULL als 0 man könnte hinzufügen, dass es da eine Spalte mit geringer Dichte - würde es nicht brauchen, um hinzuzufügen, jede Lagerung, bis Sie tatsächlich Hinzugefügt, Wert:

    ALTER TABLE myTable 
ADD NewColumnID int SPARSE NULL 
CONSTRAINT DF_Constraint

    Sonst Aarons Ansatz ist wahrscheinlich die am wenigsten aufdringlich - es wird noch eine lange Zeit dauern, aber es kann getan werden "online"

    InformationsquelleAutor D Stanley
  5. -1

    Es besser für drop die Spalten, und wieder Hinzugefügt. das wird minimieren Sie alle overhead wie ein trigger oder constraints über den Tisch.

    InformationsquelleAutor Ajay Kumar
  6. -2

    Danke Aaron für deine ausführliche Ansatz, aber ich habe einen schnellen test und die einfache Methode wäre die folgende:

    Einige Hintergrundinformationen. Ich bin das hinzufügen einer CompanyID, um eine vorhandene Tabelle. Die ID bezieht sich auf die Firma der Datensatz gehört. Default-Wert 0 sein. Da dieser aber geht in einen bestehenden Kunden prod-Datenbank, Ihre Unternehmens-ID ist 1. Wir haben eine generische upgrade-Skript für alle unsere Kunden, stellt sich heraus, eine leichte Modifikation dieses Skript für diesen spezifischen Kunden-Gewinnung erhebliche performance-Verbesserungen.

    STATT:

    ALTER TABLE myTable ADD CompanyID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 0 (takes about 1min to complete)
UPDATE myTable SET CompanyID = 1 (will take over an hour)

    ICH NUR DAZU:

    ALTER TABLE myTable ADD CompanyID int NOT NULL CONSTRAINT DF_Constraint DEFAULT 1 (takes about 1min to complete)

    Dann einfach den default-Wert auf 0 zurück. Nun die Tabelle CompanyID = 1 für alle Datensätze. BOOM!

    • Es dauerte eine minute, um eine Spalte hinzuzufügen, und setzen Sie alle 40 Millionen Zeilen 1? Können Sie erklären, warum Sie denken, dass die Standard-Einstellung auf 1 war so viel schneller als die Standard-Einstellung auf 0? Ich bin sehr verwirrt. Die Antwort spricht über eine default-Einstellung und Aktualisierung aller Daten auf einen anderen Wert, während die Frage erwähnt nie den Wert 1 an alle.
    • href="http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/" >ich nehme an, Sie sind auf 2012. Die Antwort scheint-Adresse ein Szenario nicht erwähnt, der in der obwohl Frage.
    • Dies ist nicht eine Antwort auf die Frage
    InformationsquelleAutor FaNIX
Schreibe einen Kommentar