ALTER TABLE (Transact-SQL)

  • Artikel

Ändert eine Tabellendefinition durch Ändern, Hinzufügen oder Löschen von Spalten und Einschränkungen, Neuzuweisen von Partitionen oder Deaktivieren bzw. Aktivieren von Einschränkungen und Triggern.

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
{ 
    ALTER COLUMN column_name 
    { 
        [ type_schema_name. ] type_name [ ( { precision [ , scale ] 
            | max | xml_schema_collection } ) ] 
        [ COLLATE collation_name ] 
        [ NULL | NOT NULL ] [ SPARSE ]

    | {ADD | DROP } 
        { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
    } 
        | [ WITH { CHECK | NOCHECK } ]

    | ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
      | <column_set_definition> 
    } [ ,...n ]

    | DROP 
     {
         [ CONSTRAINT ] 
         { 
              constraint_name 
              [ WITH 
               ( <drop_clustered_constraint_option> [ ,...n ] ) 
              ] 
          } [ ,...n ]
          | COLUMN 
          {
              column_name 
          } [ ,...n ]
     } [ ,...n ]
    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT 
        { ALL | constraint_name [ ,...n ] } 

    | { ENABLE | DISABLE } TRIGGER 
        { ALL | trigger_name [ ,...n ] }

    | { ENABLE | DISABLE } CHANGE_TRACKING 
        [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]

    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table 
        [ PARTITION target_partition_number_expression ]

    | SET ( FILESTREAM_ON = { partition_scheme_name | filegroup | 
                "default" | "NULL" } )

    | REBUILD 
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ] 
      | [ PARTITION = partition_number 
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | <table_option>

    | <filetable_option>

}
[ ; ]

-- ALTER TABLE options


<column_set_definition> ::= 
    column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

<drop_clustered_constraint_option> ::=  
    { 
        MAXDOP = max_degree_of_parallelism 
      | ONLINE = {ON | OFF }
      | MOVE TO { partition_scheme_name ( column_name ) | filegroup
          | "default" }
    }
<table_option> ::=
    {
        SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    }

<filetable_option> ::=
    {
       [ { ENABLE | DISABLE } FILETABLE_NAMESPACE ]
       [ SET ( FILETABLE_DIRECTORY = directory_name ) ]
    }
<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE} }
}

Argumente

  • database_name
    Name der Datenbank, in der die Tabelle erstellt wurde.

  • schema_name
    Der Name des Schemas, zu dem die Tabelle gehört.

  • table_name
    Der Name der Tabelle, die geändert werden soll. Wenn die Tabelle nicht in der aktuellen Datenbank oder nicht in dem Schema enthalten ist, das dem aktuellen Benutzer gehört, müssen die Datenbank und das Schema explizit angegeben werden.

  • ALTER COLUMN
    Gibt an, dass die benannte Spalte geändert werden soll.

    Für die geänderte Spalte gilt Folgendes:

    • Die Spalte darf nicht vom timestamp-Datentyp sein.

    • Die Spalte darf nicht die ROWGUIDCOL-Spalte der Tabelle sein.

    • Die Spalte darf keine berechnete Spalte sein und nicht in einer berechneten Spalte verwendet werden.

    • Die Spalte darf nicht in Statistiken verwendet werden, die durch die CREATE STATISTICS-Anweisung erstellt wurden, es sei denn, die Spalte ist vom Datentyp varchar, nvarchar oder varbinary, der Datentyp wird nicht geändert, die neue Größe ist größer oder gleich der alten oder die Spalte wird von NOT NULL in NULL geändert. Entfernen Sie die Statistiken zunächst mithilfe der DROP STATISTICS-Anweisung. Vom Abfrageoptimierer automatisch generierte Statistiken werden von ALTER COLUMN automatisch gelöscht.

    • Die Spalte darf nicht in einer PRIMARY KEY- oder [FOREIGN KEY] REFERENCES-Einschränkung verwendet werden.

    • Die Spalte darf nicht in einer CHECK- oder UNIQUE-Einschränkung verwendet werden. Das Ändern der Länge einer Spalte mit variabler Länge, die in einer CHECK- oder UNIQUE-Einschränkung verwendet wird, ist dagegen zulässig.

    • Der Spalte darf keine Standarddefinition zugeordnet sein. Die Länge, die Genauigkeit oder die Dezimalstellen einer Spalte können jedoch geändert werden, sofern der Datentyp nicht geändert wird.

      Der Datentyp der text-, der ntext- und der image-Spalte kann nur in der folgenden Weise geändert werden:

      • text in varchar(max), nvarchar(max) oder xml

      • ntext in varchar(max), nvarchar(max) oder xml

      • image in varbinary(max)

      Änderungen des Datentyps können Datenänderungen zur Folge haben. Beispielsweise kann die Änderung einer Spalte vom Datentyp nchar oder nvarchar in char oder varchar zur Konvertierung erweiterter Zeichen führen. Weitere Informationen finden Sie unter CAST und CONVERT (Transact-SQL). Das Reduzieren der Genauigkeit und der Dezimalstellen einer Spalte kann zum Abschneiden von Daten führen.

      Der Datentyp einer Spalte einer partitionierten Tabelle kann nicht geändert werden.

    Der Datentyp von Spalten in einem Index darf nur geändert werden, wenn die Spalte den Datentyp varchar, nvarchar oder varbinary aufweist und die neue Größe größer oder gleich der alten Größe ist.

    Spalten in einer PRIMARY KEY-Einschränkung können nicht von NOT NULL in NULL geändert werden.

  • column_name
    Der Name der Spalte, die geändert, hinzugefügt oder gelöscht werden soll. column_name kann maximal 128 Zeichen umfassen. Bei neuen Spalten kann column_name für mit einem timestamp-Datentyp erstellte Spalten entfallen. Der Name timestamp wird verwendet, wenn column_name nicht für eine Spalte vom Typ timestamp angegeben ist.

  • [ type_schema_name**.** ] type_name
    Der neue Datentyp für die geänderte Spalte oder der Datentyp für die hinzugefügte Spalte. type_name kann für vorhandene Spalten von partitionierten Tabellen nicht angegeben werden. type_name kann einen der folgenden Werte haben:

    • Ein SQL Server-Systemdatentyp.

    • Ein Aliasdatentyp, der auf einem SQL Server-Systemdatentyp basiert. Aliasdatentypen werden mit der CREATE TYPE-Anweisung erstellt, bevor sie in einer Tabellendefinition verwendet werden können.

    • Ein benutzerdefinierter .NET Framework-Datentyp und das Schema, zu dem er gehört. Benutzerdefinierte .NET Framework-Typen werden mit der CREATE TYPE-Anweisung erstellt, bevor sie in einer Tabellendefinition verwendet werden können.

    Es gelten folgende Kriterien für type_name in einer geänderten Spalte:

    • Der ursprüngliche Datentyp muss implizit in den neuen Datentyp konvertiert werden können.

    • type_name darf nicht timestamp sein.

    • ANSI NULL DEFAULT ist für ALTER COLUMN immer aktiviert. Fehlt die Angabe, so lässt die Spalte NULL-Werte zu.

    • Die ANSI-Auffüllung ist für ALTER COLUMN immer aktiviert.

    • Wenn die geänderte Spalte eine Identitätsspalte ist, muss new_data_type ein Datentyp sein, der die IDENTITY-Eigenschaft unterstützt.

    • Die aktuelle Einstellung für SET ARITHABORT wird ignoriert. ALTER TABLE wird ausgeführt, als sei ARITHABORT aktiviert.

    HinweisHinweis

    Falls die COLLATE-Klausel nicht angegeben wird, bewirkt das Ändern des Datentyps einer Spalte die Änderung der Sortierung in die Standardsortierung der Datenbank.

  • precision
    Die Genauigkeit für den angegebenen Datentyp. Weitere Informationen zu gültigen Genauigkeitswerten finden Sie unter Genauigkeit, Dezimalstellen und Länge (Transact-SQL).

  • scale
    Die Dezimalstellen für den angegebenen Datentyp. Weitere Informationen zu gültigen Dezimalstellenwerten finden Sie unter Genauigkeit, Dezimalstellen und Länge (Transact-SQL).

  • max
    Gilt nur für die Datentypen varchar, nvarchar und varbinary zum Speichern von 2^31-1 Byte an Zeichen, Binärdaten und Unicode-Daten.

  • xml_schema_collection
    Gilt nur für den xml-Datentyp zum Zuordnen eines XML-Schemas zu dem Typ. Bevor Sie eine xml-Spalte mit einer Schemaauflistung typisieren können, muss die Schemaauflistung mithilfe von CREATE XML SCHEMA COLLECTION in der Datenbank erstellt werden.

  • COLLATE < collation_name >
    Gibt die neue Sortierung für die geänderte Spalte an. Wenn keine Sortierung angegeben ist, wird der Spalte die Standardsortierung der Datenbank zugewiesen. Als Sortierungsname kann entweder der Name einer Windows-Sortierreihenfolge oder ein SQL-Sortierungsname verwendet werden. Eine Liste und weitere Informationen finden Sie unter Name der Windows-Sortierreihenfolge (Transact-SQL) und SQL Server-Sortierungsname (Transact-SQL).

    Mit der COLLATE-Klausel können Sie nur die Sortierungen von Spalten der Datentypen char, varchar, nchar und nvarchar ändern. Wenn Sie die Sortierung einer Spalte eines benutzerdefinierten Aliasdatentyps ändern möchten, müssen Sie zunächst mit separaten ALTER TABLE-Anweisungen die Spalte in einen SQL Server-Systemdatentyp ändern und ihre Sortierung ändern. Anschließend können Sie die Spalte zurück in einen Aliasdatentyp ändern.

    Mit ALTER COLUMN kann die Sortierung nicht geändert werden, wenn eine der folgenden Bedingungen zutrifft:

    • Wenn eine CHECK-Einschränkung, eine FOREIGN KEY-Einschränkung oder berechnete Spalten auf die geänderte Spalte verweisen.

    • Wenn ein Index, eine Statistik oder ein Volltextindex mit der Spalte erstellt werden. Statistiken, die automatisch für die geänderte Spalte erstellt wurden, werden gelöscht, wenn die Spaltensortierung geändert wird.

    • Wenn eine schemagebundene Sicht oder Funktion auf die Spalte verweist.

    Weitere Informationen finden Sie unter COLLATE (Transact-SQL).

  • NULL | NOT NULL
    Gibt an, ob die Spalte NULL-Werte akzeptiert. Spalten, die keine NULL-Werte zulassen, können mit ALTER TABLE nur hinzugefügt werden, wenn für sie ein Standardwert angegeben ist oder wenn die Tabelle leer ist. NOT NULL kann für berechnete Spalten nur dann angegeben werden, wenn PERSISTED ebenfalls angegeben wird. Wenn die neue Spalte NULL-Werte zulässt und kein Standardwert angegeben ist, enthält sie einen NULL-Wert für jede Zeile in der Tabelle. Wenn die neue Spalte NULL-Werte zulässt und eine Standarddefinition mit der neuen Spalte hinzugefügt wird, kann WITH VALUES verwendet werden, um den Standardwert in der neuen Spalte für jede vorhandene Zeile in der Tabelle zu speichern.

    Wenn die neue Spalte keine NULL-Werte zulässt und die Tabelle nicht leer ist, muss eine DEFAULT-Definition mit der neuen Spalte hinzugefügt werden. Die neue Spalte wird dann automatisch in jeder vorhandenen Zeile mit dem Standardwert geladen.

    Durch die Angabe von NULL in ALTER COLUMN kann erzwungen werden, dass eine NOT NULL-Spalte, mit Ausnahme von Spalten in PRIMARY KEY-Einschränkungen, NULL-Werte zulässt. NOT NULL kann nur in ALTER COLUMN angegeben werden, wenn die Spalte keine NULL-Werte enthält. Die NULL-Werte müssen auf einen beliebigen Wert aktualisiert werden, damit ALTER COLUMN NOT NULL zulässig ist. Beispiel:

    UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL;
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL;

    Wenn Sie eine Tabelle mit der CREATE TABLE- oder ALTER TABLE-Anweisung erstellen bzw. ändern, beeinflussen die Datenbank- und Sitzungseinstellungen die NULL-Zulässigkeit des in einer Spaltendefinition verwendeten Datentyps und überschreiben diese möglicherweise. Es wird empfohlen, eine nicht berechnete Spalte stets explizit als NULL oder NOT NULL zu definieren.

    Wenn Sie eine Spalte mit einem benutzerdefinierten Datentyp hinzufügen, wird empfohlen, dass Sie die Spalte mit der gleichen NULL-Zulässigkeit wie der des benutzerdefinierten Datentyps definieren und einen Standardwert für die Spalte angeben. Weitere Informationen finden Sie unter CREATE TABLE (Transact-SQL).

    HinweisHinweis

    Wenn NULL oder NOT NULL mit ALTER COLUMN angegeben ist, muss auch new_data_type [(precision [, scale ])] angegeben werden. Wenn Datentyp, Genauigkeit und Dezimalstellen nicht geändert werden, geben Sie die aktuellen Spaltenwerte an.

  • [ {ADD | DROP} ROWGUIDCOL ]
    Gibt an, dass die ROWGUIDCOL-Eigenschaft der angegebenen Spalte hinzugefügt oder aus ihr gelöscht wird. ROWGUIDCOL zeigt an, dass die Spalte eine GUID-Spalte für eine Zeile darstellt. Pro Tabelle kann nur eine uniqueidentifier-Spalte zur ROWGUIDCOL-Spalte erklärt werden, und die ROWGUIDCOL-Eigenschaft kann nur einer uniqueidentifier-Spalte zugewiesen werden. ROWGUIDCOL kann keiner Spalte eines benutzerdefinierten Datentyps zugewiesen werden.

    ROWGUIDCOL erzwingt keine Eindeutigkeit der in der Spalte gespeicherten Werte und generiert nicht automatisch Werte für neue Zeilen, die in die Tabelle eingefügt werden. Verwenden Sie entweder die NEWID-Funktion in INSERT-Anweisungen, oder geben Sie die NEWID-Funktion als Standard für die Spalte an, um eindeutige Werte für jede Spalte zu generieren.

  • [ {ADD | DROP} PERSISTED ]
    Gibt an, dass die PERSISTED-Eigenschaft der angegebenen Spalte hinzugefügt oder aus ihr gelöscht wird. Die Spalte muss eine berechnete Spalte sein, die durch einen deterministischen Ausdruck definiert ist. Für Spalten, die als PERSISTED angegeben werden, speichert Database Engine (Datenbankmodul) die berechneten Werte physisch in der Tabelle und aktualisiert die Werte, wenn andere Spalten, von denen die berechnete Spalte abhängt, aktualisiert werden. Durch Kennzeichnen einer berechneten Spalte als PERSISTED können Sie Indizes für berechnete Spalten erstellen, die durch deterministische, aber nicht genaue Ausdrücke definiert sind. Weitere Informationen finden Sie unter Indizes in berechneten Spalten.

    Jede berechnete Spalte, die als Partitionierungsspalte einer partitionierten Tabelle verwendet wird, muss explizit als PERSISTED gekennzeichnet sein.

  • DROP NOT FOR REPLICATION
    Gibt an, dass Werte in Identitätsspalten inkrementiert werden, wenn Replikations-Agents Einfügevorgänge ausführen. Diese Klausel kann nur angegeben werden, wenn column_name eine Identitätsspalte ist.

  • SPARSE
    Gibt an, dass die Spalte eine Spalte mit geringer Dichte ist. Der Speicher für Spalten mit geringer Dichte ist für NULL-Werte optimiert. Spalten mit geringer Dichte können nicht als NOT NULL festgelegt werden. Beim Umwandeln einer Spalte mit geringer Dichte in eine Spalte ohne geringe Dichte oder umgekehrt wird die Tabelle für die Dauer der Befehlsausführung gesperrt. Sie müssen möglicherweise die REBUILD-Klausel verwenden, um Speicherplatzeinsparungen freizugeben. Weitere Einschränkungen und Informationen zu Spalten mit geringer Dichte finden Sie unter Verwenden von Spalten mit geringer Dichte.

  • WITH CHECK | WITH NOCHECK
    Gibt an, ob die Daten in der Tabelle in Bezug auf eine neu hinzugefügte oder reaktivierte FOREIGN KEY- oder CHECK-Einschränkung überprüft werden. Fehlt die Angabe, so wird WITH CHECK für neue Einschränkungen und WITH NOCHECK für reaktivierte Einschränkungen angenommen.

    Verwenden Sie WITH NOCHECK, wenn neue CHECK- oder FOREIGN KEY-Einschränkungen nicht in Bezug auf vorhandene Daten überprüft werden sollen. Diese Vorgehensweise wird nur in seltenen Fällen empfohlen. Die neue Einschränkung wird bei allen späteren Datenupdates ausgewertet. Einschränkungsverletzungen, die beim Hinzufügen der Einschränkung durch WITH NOCHECK unterdrückt werden, können zu Fehlern bei zukünftigen Updates führen, wenn Zeilen mit Daten aktualisiert werden, die der Einschränkung nicht entsprechen.

    Der Abfrageoptimierer berücksichtigt mit WITH NOCHECK definierte Einschränkungen nicht. Diese Einschränkungen werden ignoriert, bis sie mithilfe von ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL erneut aktiviert werden.

  • ADD
    Gibt an, dass eine oder mehrere Spaltendefinitionen, Definitionen berechneter Spalten oder Tabelleneinschränkungen hinzugefügt werden.

  • DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
    Gibt an, dass constraint_name oder column_name aus der Tabelle entfernt wird. Es können mehrere Spalten und Einschränkungen aufgeführt sein.

    Der benutzerdefinierte oder vom System bereitgestellte Name der Einschränkung kann durch Abfragen der Katalogsichten sys.check_constraint, sys.default_constraints, sys.key_constraints und sys.foreign_keys ermittelt werden.

    Eine PRIMARY KEY-Einschränkung kann nicht gelöscht werden, wenn ein XML-Index für die Tabelle vorhanden ist.

    Unter folgenden Umständen kann eine Spalte nicht gelöscht werden:

    • Wenn sie in einem Index verwendet wird.

    • Wenn sie in einer CHECK-, FOREIGN KEY-, UNIQUE- oder PRIMARY KEY-Einschränkung verwendet wird.

    • Wenn ihr ein mit dem DEFAULT-Schlüsselwort definierter Standardwert zugeordnet ist oder sie an ein Standardobjekt gebunden ist.

    • Wenn sie an eine Regel gebunden ist.

    HinweisHinweis

    Durch Löschen einer Spalte wird nicht der Speicherplatz der Spalte freigegeben. Unter Umständen müssen Sie den Speicherplatz einer gelöschten Spalte freigeben, wenn das Limit der Zeilengröße einer Tabelle fast erreicht oder überschritten ist. Zum Freigeben des Speicherplatzes erstellen Sie einen gruppierten Index für die Tabelle oder erstellen einen vorhandenen gruppierten Index mithilfe von ALTER INDEX neu. Weitere Informationen zu den Auswirkungen gelöschter LOB-Datentypen finden Sie diesem CSS-Blogeintrag.

  • WITH <drop_clustered_constraint_option>
    Gibt an, dass mindestens eine Option zum Löschen einer gruppierten Einschränkung festgelegt wurde.

  • MAXDOP = max_degree_of_parallelism
    Überschreibt die Konfigurationsoption Max. Grad an Parallelität nur für die Dauer des Vorgangs. Weitere Informationen finden Sie unter Konfigurieren der Serverkonfigurationsoption Max. Grad an Parallelität.

    Verwenden Sie die MAXDOP-Option, um die Anzahl der Prozessoren zu beschränken, die für die Ausführung paralleler Pläne verwendet werden. Der Maximalwert sind 64 Prozessoren.

    Für max_degree_of_parallelism sind die folgenden Werte möglich:

    • 1
      Unterdrückt die Generierung paralleler Pläne.

    • >1
      Beschränkt die maximale Anzahl von Prozessoren, die bei einem parallelen Indexvorgang verwendet werden, auf die angegebene Anzahl.

    • 0 (Standard)
      Verwendet abhängig von der aktuellen Systemarbeitsauslastung die tatsächliche Anzahl von Prozessoren oder weniger Prozessoren.

    Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

    HinweisHinweis

    Parallele Indexvorgänge sind nicht in jeder Edition von SQL Server verfügbar. Weitere Informationen finden Sie unter Von den SQL Server 2012-Editionen unterstützte Funktionen.

  • ONLINE = { ON | OFF }
    Gibt an, ob zugrunde liegende Tabellen oder zugehörige Indizes für Abfragen und Datenänderungen während des Indexvorgangs zur Verfügung stehen. Die Standardeinstellung ist OFF. REBUILD kann als ONLINE-Vorgang ausgeführt werden.

    • ON
      Während des Indexvorgangs werden keine Langzeitsperren für Tabellen aufrechterhalten. Während der Hauptphase des Indexvorgangs wird nur eine beabsichtigte freigegebene Sperre (IS) für die Quelltabelle aufrechterhalten. So können Abfragen oder Updates der zugrunde liegenden Tabelle und Indizes fortgesetzt werden. Zu Beginn des Vorgangs wird das Quellobjekt für sehr kurze Zeit mit einer freigegebenen Sperre (S) belegt. Am Ende des Vorgangs wird für kurze Zeit eine freigegebene Sperre (S) für die Quelle eingerichtet, wenn ein nicht gruppierter Index erstellt wird. Eine Schemaänderungssperre (SCH-M) wird dagegen eingerichtet, wenn ein gruppierter Index online erstellt oder gelöscht wird und wenn ein gruppierter oder nicht gruppierter Index neu erstellt wird. ONLINE kann nicht auf ON festgelegt werden, wenn ein Index für eine lokale temporäre Tabelle erstellt wird. Nur ein Heap-Neuerstellungsvorgang mit einem einzelnen Thread ist zulässig.

    • OFF
      Die Tabellensperren werden für die Dauer des Indexvorgangs angewendet. Ein Offlineindexvorgang, bei dem ein gruppierter Index erstellt, neu erstellt oder gelöscht bzw. ein nicht gruppierter Index neu erstellt oder gelöscht wird, aktiviert eine Schemaänderungssperre (SCH-M) für die Tabelle. Dadurch wird verhindert, dass Benutzer für die Dauer des Vorgangs auf die zugrunde liegende Tabelle zugreifen können. Ein Offlineindexvorgang, bei dem ein nicht gruppierter Index erstellt wird, aktiviert eine freigegebene Sperre (S) für die Tabelle. Dadurch werden Updates der zugrunde liegenden Tabelle verhindert. Lesevorgänge, wie SELECT-Anweisungen, sind jedoch zulässig. Heap-Neuerstellungsvorgänge mit mehreren Threads sind zulässig.

    Weitere Informationen finden Sie unter Funktionsweise von Onlineindexvorgängen.

    HinweisHinweis

    Onlineindexvorgänge sind nicht in jeder Edition von SQL Server verfügbar. Weitere Informationen finden Sie unter Von den SQL Server 2012-Editionen unterstützte Funktionen.

  • MOVE TO { partition_scheme_name**(column_name [ 1,** ... n] ) | filegroup | "default" }
    Gibt einen Speicherort an, an den die Datenzeilen verschoben werden sollen, die sich aktuell auf der Blattebene des gruppierten Index befinden. Die Tabelle wird an den neuen Speicherort verschoben. Diese Option gilt nur für Einschränkungen, die einen gruppierten Index erstellen.

    HinweisHinweis

    In diesem Kontext stellt default kein Schlüsselwort dar. Es handelt sich um einen Bezeichner für die Standarddateigruppe, der begrenzt werden muss, wie in MOVE TO "default" oder MOVE TO [default]. Wenn "default" angegeben wird, muss die QUOTED_IDENTIFIER-Option für die aktuelle Sitzung auf ON festgelegt sein. Dies ist die Standardeinstellung. Weitere Informationen finden Sie unter SET QUOTED_IDENTIFIER (Transact-SQL).

  • { CHECK | NOCHECK} CONSTRAINT
    Gibt an, dass constraint_name aktiviert oder deaktiviert wird. Diese Option kann nur mit FOREIGN KEY- und CHECK-Einschränkungen verwendet werden. Wenn NOCHECK angegeben wird, wird die Einschränkung deaktiviert, und zukünftige Einfügungen oder Updates der Spalte werden nicht bezüglich der Einschränkungsbedingungen überprüft. DEFAULT-, PRIMARY KEY- und UNIQUE-Einschränkungen können nicht deaktiviert werden.

  • ALL
    Gibt an, dass alle Einschränkungen entweder mit der Option NOCHECK deaktiviert oder mit der Option CHECK aktiviert werden.

  • {ENABLE | DISABLE} TRIGGER
    Gibt an, dass trigger_name aktiviert oder deaktiviert wird. Ein Trigger bleibt auch dann für die Tabelle definiert, wenn er deaktiviert ist. Wenn jedoch INSERT-, UPDATE- oder DELETE-Anweisungen an der Tabelle ausgeführt werden, werden die Aktionen im Trigger erst durchgeführt, wenn der Trigger reaktiviert wird.

  • ALL
    Gibt an, dass alle Trigger in der Tabelle aktiviert oder deaktiviert werden.

  • trigger_name
    Gibt den Namen des Triggers an, der deaktiviert oder aktiviert werden soll.

  • { ENABLE | DISABLE } CHANGE_TRACKING
    Gibt an, ob die Änderungsnachverfolgung für die Tabelle deaktiviert bzw. aktiviert wurde. Standardmäßig ist die Änderungsnachverfolgung deaktiviert.

    Diese Option ist nur dann verfügbar, wenn die Änderungsnachverfolgung für die Datenbank aktiviert ist. Weitere Informationen finden Sie unter ALTER DATABASE SET-Optionen (Transact-SQL).

    Um die Änderungsnachverfolgung aktivieren zu können, muss die Tabelle über einen primären Schlüssel verfügen.

  • WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )
    Gibt an, ob Database Engine (Datenbankmodul) verfolgt, welche Spalten mit Änderungsnachverfolgung aktualisiert wurden. Der Standardwert ist OFF.

  • SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name**.** ] target_table [ PARTITION target_ partition_number_expression ]
    Verlagert einen Datenblock auf eine der folgenden Arten:

    • Weist alle Daten einer Tabelle neu als eine Partition einer bereits vorhandenen partitionierten Tabelle zu.

    • Wechselt eine Partition von einer partitionierten Tabelle zu einer anderen.

    • Weist alle Daten aus einer Partition einer partitionierten Tabelle neu einer bereits vorhandenen nicht partitionierten Tabelle zu.

    Wenn table eine partitionierte Tabelle ist, muss source_partition_number_expression angegeben werden. Wenn target_table partitioniert ist, muss target_partition_number_expression angegeben werden. Wenn die Daten einer Tabelle als Partition einer vorhandenen partitionierten Tabelle neu zugewiesen werden oder eine Partition von einer partitionierten Tabelle zu einer anderen gewechselt wird, muss die Zielpartition vorhanden und leer sein.

    Wenn die Daten einer Partition neu zugewiesen werden, sodass sie eine einzelne Tabelle bilden, muss die Zieltabelle bereits erstellt und leer sein. Die Quelltabelle oder -partition und die Zieltabelle oder -partition müssen sich in derselben Dateigruppe befinden. Die entsprechenden Indizes oder Indexpartitionen müssen sich ebenfalls in derselben Dateigruppe befinden. Darüber hinaus gelten weitere Einschränkungen für das Wechseln von Partitionen. table und target_table können nicht identisch sein. target_table kann ein mehrteiliger Bezeichner sein.

    source_partition_number_expression und target_partition_number_expression sind konstante Ausdrücke, die auf Variablen und Funktionen verweisen können. Diese enthalten benutzerdefinierte Typvariablen und benutzerdefinierte Funktionen. Sie können nicht auf Transact-SQL-Ausdrücke verweisen.

    Informationen zur SWITCH-Einschränkung beim Verwenden der Replikation finden Sie unter Replizieren partitionierter Tabellen und Indizes.

  • SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" })
    Gibt an, wo FILESTREAM-Daten gespeichert werden.

    ALTER TABLE mit der SET FILESTREAM_ON-Klausel ist nur erfolgreich, wenn die Tabelle keine FILESTREAM-Spalten enthält. Die FILESTREAM-Spalten können mit einer zweiten ALTER TABLE-Anweisung hinzugefügt werden.

    Wenn partition_scheme_name angegeben wird, gelten die Regeln für CREATE TABLE. Die Tabelle sollte bereits für Zeilendaten partitioniert sein, und das Partitionsschema muss die gleiche Partitionsfunktion und die gleichen Partitionsspalten wie das FILESTREAM-Partitionsschema verwenden.

    filestream_filegroup_name gibt den Namen einer FILESTREAM-Dateigruppe an. Für die Dateigruppe muss eine Datei mit einer CREATE DATABASE-Anweisung oder einer ALTER DATABASE-Anweisung definiert worden sein, andernfalls wird ein Fehler ausgelöst.

    "default" gibt die FILESTREAM-Dateigruppe mit dem DEFAULT-Eigenschaftensatz an. Wenn keine FILESTREAM-Dateigruppe vorhanden ist, wird ein Fehler ausgelöst.

    " NULL**"** gibt an, dass alle Verweise auf FILESTREAM-Dateigruppen für die Tabelle entfernt werden. Alle FILESTREAM-Spalten müssen zuerst gelöscht werden. Sie müssen SET FILESTREAM_ON**="NULL"** verwenden, um alle mit einer Tabelle verknüpften FILESTREAM-Daten zu löschen.

  • SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    Gibt die zulässigen Methoden der Sperrenausweitung für eine Tabelle an.

    • AUTO
      Mit dieser Option kann von SQL Server Database Engine (Datenbankmodul) die für das Tabellenschema geeignete Granularität der Sperrenausweitung ausgewählt werden.

      • Wenn die Tabelle partitioniert ist, wird die Sperrenausweitung bis zur Partition erlaubt. Nach der Ausweitung der Sperre auf die Partitionsebene wird die Sperrenausweitung nicht bis zur TABLE-Granularität fortgeführt.

      • Wenn die Tabelle nicht partitioniert ist, wird die Sperre bis zur TABLE-Granularität ausgeweitet.

    • TABLE
      Die Sperrenausweitung wird immer mit der Granularität der Tabellenebene ausgeführt, unabhängig davon, ob die Tabelle partitioniert ist. Dieses Verhalten entspricht dem Verhalten in SQL Server 2005. TABLE ist der Standardwert.

    • DISABLE
      Verhindert die Sperrenausweitung in den meisten Fällen. Sperren auf Tabellenebene sind jedoch nicht völlig ausgeschlossen. Wenn Sie z. B. eine Tabelle scannen, die unter der serialisierbaren Isolationsstufe keinen gruppierten Index aufweist, muss Database Engine (Datenbankmodul) eine Tabellensperre zulassen, damit die Datenintegrität gewahrt wird.

  • REBUILD
    Verwenden Sie die REBUILD WITH-Syntax, um eine gesamte Tabelle neu zu erstellen, einschließlich aller Partitionen in einer partitionierten Tabelle. Wenn die Tabelle einen gruppierten Index besitzt, erstellt die REBUILD-Option den gruppierten Index neu. REBUILD kann als ONLINE-Vorgang ausgeführt werden.

    Verwenden Sie die REBUILD PARTITION-Syntax, um eine einzelne Partition in einer partitionierten Tabelle neu zu erstellen.

  • PARTITION = ALL
    Erstellt alle Partitionen neu, wenn die Komprimierungseinstellungen für die Partition geändert werden.

  • REBUILD WITH ( <rebuild_option> )
    Alle Optionen gelten für eine Tabelle mit einem gruppierten Index. Wenn die Tabelle nicht über einen gruppierten Index verfügt, wird die Heapstruktur nur von einigen der Optionen beeinflusst.

    Wenn eine bestimmte Komprimierungseinstellung nicht mit dem REBUILD-Vorgang angegeben wird, wird die aktuelle Komprimierungseinstellung für die Partition verwendet. Um die aktuelle Einstellung zurückzugeben, fragen Sie die data_compression-Spalte in der sys.partitions-Katalogsicht ab.

    Vollständige Beschreibungen der Optionen für die Neuerstellung finden Sie unter index_option (Transact-SQL).

  • DATA_COMPRESSION
    Gibt die Datenkomprimierungsoption für die angegebene Tabelle, die Partitionsnummer oder den Bereich von Partitionen an. Folgende Optionen stehen zur Verfügung:

    • NONE
      Die Tabelle bzw. die angegebenen Partitionen werden nicht komprimiert.

    • ROW
      Die Tabelle oder die angegebenen Partitionen werden mit Zeilenkomprimierung komprimiert.

    • PAGE
      Die Tabelle oder die angegebenen Partitionen werden mit Seitenkomprimierung komprimiert.

    Informationen zur gleichzeitigen Neuerstellung mehrerer Partitionen finden Sie unter index_option (Transact-SQL). Wenn die Tabelle nicht über einen gruppierten Index verfügt, werden bei Änderungen an der Datenkomprimierung der Heap und die nicht gruppierten Indizes neu erstellt. Weitere Informationen zur Komprimierung finden Sie unter Datenkomprimierung.

  • column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
    Der Name des Spaltensatzes. Bei einem Spaltensatz handelt es sich um eine nicht typisierte XML-Darstellung, die alle Tabellenspalten mit geringer Dichte in einer strukturierten Ausgabe kombiniert. Sie können einer Tabelle, die Spalten mit geringer Dichte enthält, keinen Spaltensatz hinzufügen. Weitere Informationen zu Spaltensätzen finden Sie unter Verwenden von Spaltensätzen.

  • { ENABLE | DISABLE } FILETABLE_NAMESPACE
    Aktiviert oder deaktiviert die systemdefinierten Einschränkungen für eine FileTable. Kann nur mit einer FileTable verwendet werden.

  • SET ( FILETABLE_DIRECTORY = directory_name )
    Gibt den Windows-kompatiblen FileTable-Verzeichnisnamen an. Dieser Name sollte für alle FileTable-Verzeichnisnamen in der Datenbank eindeutig sein. Bei Eindeutigkeitsvergleichen wird die Groß-/Kleinschreibung nicht beachtet, unabhängig von den SQL-Sortiereinstellungen. Kann nur mit einer FileTable verwendet werden.

Hinweise

Verwenden Sie INSERT, um neue Datenzeilen hinzuzufügen. Verwenden Sie DELETE oder TRUNCATE TABLE, um Datenzeilen zu entfernen. Verwenden Sie UPDATE, um die Werte in vorhandenen Zeilen zu ändern.

Wenn der Prozedurcache Ausführungspläne enthält, die auf die Tabelle verweisen, kennzeichnet ALTER TABLE diese für die erneute Kompilierung bei der nächsten Ausführung.

Ändern der Größe einer Spalte

Sie können Länge, Präzision oder Dezimalstellen einer Spalte ändern, indem Sie die neue Größe für den Spaltendatentyp in der ALTER COLUMN-Klausel angeben. Wenn Daten in der Spalte vorhanden sind, kann die neue Größe nicht kleiner sein als die Höchstgröße der Daten. Außerdem darf die Spalte nicht in einem Index verwendet werden, es sei denn, die Spalte ist vom Datentyp varchar, nvarchar oder varbinary und der Index ist nicht das Ergebnis einer PRIMARY KEY-Beschränkung. Siehe Beispiel P.

Sperren und ALTER TABLE

Die in ALTER TABLE angegebenen Änderungen werden sofort implementiert. Wenn die Änderungen Änderungen der Zeilen in der Tabelle erfordern, aktualisiert ALTER TABLE die Zeilen. ALTER TABLE belegt die Tabelle mit einer Schemaänderungssperre (SCH-M), um sicherzustellen, dass andere Verbindungen während der Änderung noch nicht einmal auf die Metadaten der Tabelle verweisen können. Eine Ausnahme sind Onlineindexvorgänge, die nur am Ende eine sehr kurze Schemaänderungssperre erfordern. Bei einem ALTER TABLE…SWITCH-Vorgang werden sowohl die Quell- als auch die Zieltabelle mit der Sperre belegt. Die an der Tabelle vorgenommenen Änderungen werden protokolliert und sind vollständig wiederherstellbar. Änderungen, die sich auf sämtliche Zeilen einer sehr großen Tabelle auswirken (z. B. das Löschen einer Spalte oder in einigen Editionen von SQL Server das Hinzufügen einer NOT NULL-Spalte mit einem Standardwert) kann viel Zeit in Anspruch nehmen und dazu führen, dass viele Protokolldatensätze generiert werden. Diese ALTER TABLE-Anweisungen sollten ebenso vorsichtig ausgeführt werden wie jede INSERT-, UPDATE- oder DELETE-Anweisung, die sich auf viele Zeilen auswirkt.

Hinzufügen von NOT NULL-Spalten als Onlinevorgang

In SQL Server 2012 Enterprise Edition ist das Hinzufügen einer NOT NULL-Spalte mit einem Standardwert ein Onlinevorgang, wenn der Standardwert eine Laufzeitkonstante ist. Dies bedeutet, dass der Vorgang unabhängig von der Anzahl von Zeilen in der Tabelle nahezu sofort abgeschlossen wird. Dies liegt daran, dass 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 in Abfragen wird bei Bedarf nach dem Wert gesucht und auf diese Zeilen zugegriffen. Dieses Verhalten ist automatisch. Es ist keine zusätzliche Syntax erforderlich, um den Onlinevorgang außerhalb der ADD COLUMN-Syntax zu implementieren. Eine Laufzeitkonstante ist ein Ausdruck, der zur Laufzeit unabhängig vom Determinismus den gleichen Wert für jede Zeile in der Tabelle erzeugt. Der konstante Ausdruck "My temporary data" oder die GETUTCDATETIME()-Systemfunktion sind z. B. Laufzeitkonstanten. Im Gegensatz dazu sind die Funktionen NEWID() oder NEWSEQUENTIALID() keine Laufzeitkonstanten, da für jede Zeile in der Tabelle ein eindeutiger Wert erzeugt wird. Das Hinzufügen einer NOT NULL-Spalte mit einem Standardwert, der keine Laufzeitkonstante ist, wird immer offline ausgeführt, und dabei wird eine exklusive (SCH-M) Sperre für die Dauer des Vorgangs abgerufen.

Während die vorhandenen Zeilen auf den in Metadaten gespeicherten Wert verweisen, wird der Standardwert für alle neu eingefügten Zeilen in der Zeile gespeichert, ohne einen anderen Wert für die Spalte anzugeben. Der in Metadaten gespeicherte Standardwert wird in eine vorhandene Zeile verschoben, wenn die Zeile aktualisiert wird (auch wenn die tatsächliche Spalte nicht in der UPDATE-Anweisung angegeben wird) oder wenn die Tabelle oder der gruppierte Index neu erstellt wird.

Spalten des Typs varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography oder CLR UDTS können nicht in einem Onlinevorgang hinzugefügt werden. Eine Spalte kann nicht online hinzugefügt werden, wenn dies dazu führt, dass die maximal mögliche Zeilengröße den Grenzwert von 8.060 Byte überschreitet. Die Spalte wird in diesem Fall als Offlinevorgang hinzugefügt.

Ausführung paralleler Pläne

In Microsoft SQL Server 2012 Enterprise wird die Anzahl von Prozessoren, die zur Ausführung einer einzelnen ALTER TABLE ADD CONSTRAINT-Anweisung (indexbasiert) oder einer ALTER TABLE DROP CONSTRAINT-Anweisung (gruppierter Index) verwendet werden, durch die Konfigurationsoption Max. Grad an Parallelität und durch die aktuelle Arbeitsauslastung bestimmt. Wenn Database Engine (Datenbankmodul) erkennt, dass das System ausgelastet ist, wird der Grad an Parallelität für den Vorgang automatisch reduziert, bevor mit der Ausführung der Anweisung begonnen wird. Sie können die Anzahl der Prozessoren, die zur Ausführung der Anweisung verwendet werden, durch Angeben der MAXDOP-Option manuell konfigurieren. Weitere Informationen finden Sie unter Konfigurieren der Serverkonfigurationsoption Max. Grad an Parallelität.

Partitionierte Tabellen

Neben dem Ausführen von SWITCH-Vorgängen mit partitionierten Tabellen können mit ALTER TABLE der Status der Spalten, Einschränkungen und Trigger einer partitionierten Tabelle genau wie bei nicht partitionierten Tabellen geändert werden. Die Partitionierung der Tabelle selbst kann jedoch mit der Anweisung nicht geändert werden. Zum Neupartitionieren einer partitionierten Tabelle verwenden Sie ALTER PARTITION SCHEME und ALTER PARTITION FUNCTION. Zudem können Sie den Datentyp einer Spalte einer partitionierten Tabelle nicht ändern.

Einschränkungen für Tabellen mit schemagebundenen Sichten

Die Einschränkungen, die für ALTER TABLE-Anweisungen für Tabellen mit schemagebundenen Sichten gelten, sind dieselben, die derzeit beim Ändern von Tabellen mit einem einfachen Index angewendet werden. Das Hinzufügen einer Spalte ist zulässig. Das Entfernen oder Ändern einer Spalte, die Bestandteil einer schemagebundenen Sicht ist, ist dagegen nicht zulässig. Wenn für die ALTER TABLE-Anweisung das Ändern einer in einer schemagebundenen Sicht verwendeten Spalte erforderlich ist, schlägt ALTER TABLE fehl, und Database Engine (Datenbankmodul) löst eine Fehlermeldung aus. Weitere Informationen zu Schemabindung und indizierten Sichten finden Sie unter CREATE VIEW (Transact-SQL).

Das Hinzufügen oder Entfernen von Triggern für Basistabellen wird durch das Erstellen einer schemagebundenen Sicht, die auf die Tabellen verweist, nicht beeinflusst.

Indizes und ALTER TABLE

Als Teil einer Einschränkung erstellte Indizes werden gelöscht, wenn die Einschränkung gelöscht wird. Mit CREATE INDEX erstellte Indizes müssen mit DROP INDEX gelöscht werden. Die ALTER INDEX-Anweisung kann verwendet werden, um einen Index neu zu erstellen, der Teil einer Einschränkungsdefinition ist. Die Einschränkung muss nicht mit ALTER TABLE gelöscht und erneut hinzugefügt werden.

Alle auf einer Spalte basierenden Indizes und Einschränkungen müssen entfernt werden, bevor die Spalte entfernt werden kann.

Wenn eine Einschränkung, für die ein gruppierter Index erstellt wurde, gelöscht wird, werden die Datenzeilen, die auf der Blattebene des gruppierten Index gespeichert waren, in einer nicht gruppierten Tabelle gespeichert. Sie können den gruppierten Index löschen und die daraus resultierende Tabelle in einer einzigen Transaktion in eine andere Dateigruppe oder in ein anderes Partitionsschema verschieben, indem Sie die Option MOVE TO angeben. Die MOVE TO-Option weist die folgenden Einschränkungen auf:

  • MOVE TO ist für indizierte Sichten oder nicht gruppierte Indizes nicht gültig.

  • Das Partitionsschema oder die Dateigruppe muss bereits vorhanden sein.

  • Wird MOVE TO nicht angegeben, wird die Tabelle in demselben Partitionsschema oder derselben Dateigruppe platziert, das bzw. die für den gruppierten Index definiert war.

Beim Löschen eines gruppierten Index können Sie die Option ONLINE = ON angeben, sodass die DROP INDEX-Transaktion Abfragen und Änderungen der zugrunde liegenden Daten und der zugeordneten nicht gruppierten Indizes nicht blockiert.

Für ONLINE = ON gelten folgende Einschränkungen:

  • ONLINE = ON ist nicht gültig für gruppierte Indizes, die auch deaktiviert sind. Deaktivierte Indizes müssen mit ONLINE = OFF gelöscht werden.

  • Es können nicht mehrere Indizes gleichzeitig gelöscht werden.

  • ONLINE = ON ist nicht gültig für indizierte Sichten, nicht gruppierte Indizes oder Indizes für lokale temporäre Tabellen.

Zum Löschen eines gruppierten Index ist temporärer Speicherplatz im Umfang des vorhandenen gruppierten Index erforderlich. Dieser zusätzliche Speicherplatz wird nach Abschluss des Vorgangs freigegeben.

HinweisHinweis

Die unter <drop_clustered_constraint_option> aufgeführten Optionen gelten für gruppierte Indizes für Tabellen und können nicht auf gruppierte Indizes für Sichten oder nicht gruppierte Indizes angewendet werden.

Replizieren von Schemaänderungen

Wenn Sie ALTER TABLE für eine veröffentlichte Tabelle auf einem SQL Server-Verleger ausführen, wird diese Änderung standardmäßig an alle SQL Server-Abonnenten weitergegeben. Für diese Funktionalität bestehen einige Einschränkungen, und sie kann deaktiviert werden. Weitere Informationen finden Sie unter Vornehmen von Schemaänderungen in Veröffentlichungsdatenbanken.

Datenkomprimierung

Für Systemtabellen ist die Komprimierung nicht verfügbar. . Wenn die Tabelle ein Heap ist, erfolgt der Neuerstellungsvorgang für den ONLINE-Modus mit einem einzelnen Thread. Verwenden Sie den OFFLINE-Modus für einen Multithreaded-Neuerstellungsvorgang von Heaps. Weitere Informationen zur Datenkomprimierung finden Sie unter Datenkomprimierung.

Mithilfe der gespeicherten Prozedur sp_estimate_data_compression_savings können Sie einschätzen, wie sich eine Änderung des Komprimierungsstatus auf eine Tabelle, einen Index oder eine Partition auswirkt.

Für partitionierte Tabellen gelten die folgenden Einschränkungen:

  • Sie können die Komprimierungseinstellung einer einzelnen Partition nicht ändern, wenn die Tabelle blockfreie Indizes aufweist.

  • Durch die Syntax ALTER TABLE <table> REBUILD PARTITION ... wird die angegebene Partition neu erstellt.

  • Durch die Syntax ALTER TABLE <Tabelle> REBUILD WITH ... werden alle Partitionen neu erstellt.

Löschen von NTEXT-Spalten

Beim Löschen von NTEXT-Spalten wird das Cleanup der gelöschten Daten als serialisierter Vorgang für alle Zeilen ausgeführt. Dieser Vorgang kann viel Zeit in Anspruch nehmen. Wenn eine NTEXT-Spalte in einer Tabelle mit einer großen Anzahl Zeilen gelöscht wird, aktualisieren Sie die NTEXT-Spalte zuerst auf den NULL-Wert, und löschen Sie dann die Spalte. Dieser Vorgang kann parallel mit anderen Vorgängen ausgeführt werden und kostet deutlich weniger Zeit.

Kompatibilitätsunterstützung

Die ALTER TABLE-Anweisung lässt nur zweiteilige Tabellennamen (schema.object) zu. In SQL Server 2012 schlägt die Angabe eines Tabellennamens mit den folgenden Formaten zur Kompilierzeit mit Fehler 117 fehl.

  • server.database.schema.table

  • .database.schema.table

  • ..schema.table

Bei früheren Versionen wurde durch die Angabe des Formats "server.database.schema.table" der Fehler 4902 zurückgegeben. Die Angabe des Formats ".database.schema.table" oder ".schema.table" war erfolgreich.

Um das Problem zu beheben, vermeiden Sie die Verwendung eines vierteiligen Präfixes.

Berechtigungen

Erfordert die ALTER-Berechtigung für die Tabelle.

ALTER TABLE-Berechtigungen gelten für beide an einer ALTER TABLE SWITCH-Anweisung beteiligten Tabellen. Alle verschobenen Daten erben die Sicherheitseinstellungen der Zieltabelle.

Falls Spalten in der ALTER TABLE-Anweisung mit einem benutzerdefinierten CLR-Typ (Common Language Runtime) oder Aliasdatentyp definiert sind, ist die REFERENCES-Berechtigung für den Typ erforderlich.

Für das Hinzufügen einer Spalte, die die Zeilen der Tabelle aktualisiert, ist nur die UPDATE-Berechtigung für die Tabelle erforderlich. Dies gilt beispielsweise für das Hinzufügen einer NOT NULL-Spalte mit einem Standardwert oder für das Hinzufügen einer Identitätsspalte zu einer nicht leeren Tabelle.

Beispiele

Kategorie

Funktionssyntaxelemente

Hinzufügen von Spalten und Einschränkungen

ADD • PRIMARY KEY-Einschränkung mit Indexoptionen • Spalten und Spaltensätze mit geringer Dichte •

Löschen von Spalten und Einschränkungen

DROP

Ändern einer Spaltendefinition

Ändern des Datentyps • Ändern der Spaltengröße • Sortierung

Ändern einer Tabellendefinition

DATA_COMPRESSION • SWITCH PARTITION • LOCK ESCALATION • Änderungsnachverfolgung

Deaktivieren und Aktivieren von Einschränkungen und Triggern

CHECK • NO CHECK • ENABLE TRIGGER • DISABLE TRIGGER

Hinzufügen von Spalten und Einschränkungen

Die Beispiele in diesem Abschnitt veranschaulichen das Hinzufügen von Spalten und Einschränkungen zu einer Tabelle.

A.Hinzufügen einer neuen Spalte

Im folgenden Beispiel wird eine Spalte hinzugefügt, die NULL-Werte zulässt und für die keine Werte durch eine DEFAULT-Definition bereitgestellt werden. Jede Zeile in der neuen Spalte erhält einen NULL-Wert.

CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO

B.Hinzufügen einer Spalte mit einer Einschränkung

Im folgenden Beispiel wird eine neue Spalte mit einer UNIQUE-Einschränkung hinzugefügt.

CREATE TABLE dbo.doc_exc (column_a INT) ;
GO
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL 
    CONSTRAINT exb_unique UNIQUE ;
GO
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO

C.Hinzufügen einer nicht überprüften CHECK-Einschränkung zu einer vorhandenen Spalte

Im folgenden Beispiel wird einer vorhandenen Spalte in der Tabelle eine Einschränkung hinzugefügt. Die Spalte hat einen Wert, der die Einschränkung verletzt. Deshalb wird WITH NOCHECK verwendet, um zu verhindern, dass die Einschränkung in Bezug auf vorhandene Zeilen überprüft wird, und um das Hinzufügen der Einschränkung zu ermöglichen.

CREATE TABLE dbo.doc_exd ( column_a INT) ;
GO
INSERT INTO dbo.doc_exd VALUES (-1) ;
GO
ALTER TABLE dbo.doc_exd WITH NOCHECK 
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO

D.Hinzufügen einer DEFAULT-Einschränkung zu einer vorhandenen Spalte

Im folgenden Beispiel wird eine Tabelle mit zwei Spalten erstellt und ein Wert in die erste Spalte eingefügt, während die andere Spalte NULL bleibt. Anschließend wird der zweiten Spalte eine DEFAULT-Einschränkung hinzugefügt. Um zu überprüfen, ob der Standardwert angewendet wird, wird ein weiterer Wert in die erste Spalte eingefügt und die Tabelle abgefragt.

CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO

E.Hinzufügen mehrerer Spalten mit Einschränkungen

Im folgenden Beispiel werden mehrere Spalten mit Einschränkungen hinzugefügt, die mit der neuen Spalte definiert werden. Die erste neue Spalte weist eine IDENTITY-Eigenschaft auf. Jede Zeile in der Tabelle besitzt neue inkrementelle Werte in der Identitätsspalte.

CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE dbo.doc_exe ADD 

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY, 

-- Add a column that references another column in the same table.
column_c INT NULL  
CONSTRAINT column_c_fk 
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that 
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL 
CONSTRAINT column_d_chk
CHECK 
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO

F.Hinzufügen einer Spalte, die NULL-Werte zulässt, mit Standardwerten

Im folgenden Beispiel wird eine Spalte, die NULL-Werte zulässt, mit einer DEFAULT-Definition hinzugefügt und WITH VALUES verwendet, um Werte für jede vorhandene Zeile in der Tabelle bereitzustellen. Ohne WITH VALUES hat jede Zeile den Wert NULL in der neuen Spalte.

USE AdventureWorks2012 ; 
GO
CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf 
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

G.Erstellen einer PRIMARY KEY-Einschränkung mit Indexoptionen

Im folgenden Beispiel wird die PRIMARY KEY-Einschränkung PK_TransactionHistoryArchive_TransactionID erstellt und werden die Optionen FILLFACTOR, ONLINE und PAD_INDEX festgelegt. Der entstehende gruppierte Index hat denselben Namen wie die Einschränkung.

USE AdventureWorks2012;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);
GO

H.Hinzufügen einer Spalte mit geringer Dichte

In den folgenden Beispielen wird gezeigt, wie Spalten mit geringer Dichte der Tabelle T1 hinzugefügt und geändert werden. Der Code zum Erstellen der Tabelle T1 lautet wie folgt.

CREATE TABLE T1
(C1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL,
C3 int SPARSE NULL,
C4 int ) ;
GO

Um eine zusätzliche Spalte C5 mit geringer Dichte hinzuzufügen, führen Sie die folgende Anweisung aus.

ALTER TABLE T1
ADD C5 char(100) SPARSE NULL ;
GO

Um die Spalte C4 ohne geringe Dichte in eine Spalte mit geringer Dichte umzuwandeln, führen Sie die folgende Anweisung aus.

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

Um die Spalte C4 mit geringer Dichte in eine Spalte ohne geringe Dichte umzuwandeln, führen Sie die folgende Anweisung aus.

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE;
GO

I.Hinzufügen eines Spaltensatzes

In den folgenden Beispielen wird veranschaulicht, wie eine Spalte der Tabelle T2 hinzugefügt wird. Sie können einer Tabelle, die bereits Spalten mit geringer Dichte enthält, keinen Spaltensatz hinzufügen. Der Code zum Erstellen der Tabelle T2 lautet wie folgt.

CREATE TABLE T2
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

Die folgenden drei Anweisungen fügen den Spaltensatz CS hinzu und ändern dann die Spalten C2 und C3 in SPARSE.

ALTER TABLE T2
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO

ALTER TABLE T2
ALTER COLUMN C2 ADD SPARSE ; 
GO

ALTER TABLE T2
ALTER COLUMN C3 ADD SPARSE ;
GO

Pfeilsymbol, dass mit dem Link "Zurück zum Anfang" verwendet wird[Nach oben]

Löschen von Spalten und Einschränkungen

In den Beispielen in diesem Abschnitt wird das Löschen von Spalten und Einschränkungen veranschaulicht.

A.Löschen einer oder mehrerer Spalten

Im ersten Beispiel wird eine Tabelle durch Entfernen einer Spalte geändert. Im zweiten Beispiel werden mehrere Spalten entfernt.

CREATE TABLE dbo.doc_exb 
    (column_a INT
     ,column_b VARCHAR(20) NULL
     ,column_c datetime
     ,column_d int) ;
GO
-- Remove a single column.
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
-- Remove multiple columns.
ALTER TABLE dbo.doc_exb DROP COLUMN column_c, column_d;

B.Löschen von Einschränkungen und Spalten

Im ersten Beispiel wird eine UNIQUE-Einschränkung aus einer Tabelle entfernt. Im zweiten Beispiel werden zwei Einschränkungen und eine einzelne Spalte entfernt.

CREATE TABLE dbo.doc_exc ( column_a int NOT NULL CONSTRAINT my_constraint UNIQUE) ;
GO

-- Example 1. Remove a single constraint.
ALTER TABLE dbo.doc_exc DROP my_constraint ;
GO

DROP TABLE dbo.doc_exc;
GO

CREATE TABLE dbo.doc_exc ( column_a int  
                          NOT NULL CONSTRAINT my_constraint UNIQUE
                          ,column_b int 
                          NOT NULL CONSTRAINT my_pk_constraint PRIMARY KEY) ;
GO

-- Example 2. Remove two constraints and one column
-- The keyword CONSTRAINT is optional. The keyword COLUMN is required.
ALTER TABLE dbo.doc_exc 

    DROP CONSTRAINT CONSTRAINT my_constraint, my_pk_constraint, COLUMN column_b ;
GO

C.Löschen einer PRIMARY KEY-Einschränkung im ONLINE-Modus

Im folgenden Beispiel wird eine PRIMARY KEY-Einschränkung gelöscht, wobei die Option ONLINE auf ON festgelegt wird.

USE AdventureWorks2012;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

D.Hinzufügen und Löschen einer FOREIGN KEY-Einschränkung

Im folgenden Beispiel wird die ContactBackup-Tabelle erstellt und dann geändert, indem zuerst eine FOREIGN KEY-Einschränkung hinzugefügt wird, die auf die Person.Person-Tabelle verweist, und dann die FOREIGN KEY-Einschränkung gelöscht wird.

USE AdventureWorks2012 ;
GO
CREATE TABLE Person.ContactBackup
    (ContactID int) ;
GO

ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Person (BusinessEntityID) ;
GO

ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO

DROP TABLE Person.ContactBackup ;

Pfeilsymbol, dass mit dem Link "Zurück zum Anfang" verwendet wird[Nach oben]

Ändern einer Spaltendefinition

A.Ändern des Datentyps einer Spalte

Im folgenden Beispiel wird eine Spalte einer Tabelle von INT in DECIMAL geändert.

CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE dbo.doc_exy ;
GO

B.Ändern der Größe einer Spalte

Im folgenden Beispiel werden die Größe einer varchar-Spalte sowie die Genauigkeit und die Dezimalstellen einer decimal-Spalte geändert. Da die Spalten Daten enthalten, kann die Spaltengröße nur erhöht werden. Beachten Sie auch, dass col_a in einem eindeutigen Index definiert ist. Die Größe von col_a kann erhöht werden, da die Spalte vom Datentyp varchar ist und der Index nicht das Ergebnis einer PRIMARY KEY-Beschränkung ist.

IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL 
    DROP TABLE dbo.doc_exy;
GO
-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy ( col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2));
GO
INSERT INTO dbo.doc_exy VALUES ('Test', 99.99);
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
GO
-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25);
GO
-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4);
GO
-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ;
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');

C.Ändern von Spaltensortierungen

Im folgenden Beispiel wird gezeigt, wie die Sortierung einer Spalte geändert wird. Zuerst wird eine Tabelle mit der standardmäßigen Benutzersortierung erstellt.

CREATE TABLE T3
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

Anschließend wird die Sortierung der Spalte C2 in Latin1_General_BIN geändert. Beachten Sie, dass der Datentyp erforderlich ist, auch wenn er nicht geändert wird.

ALTER TABLE T3
ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN;
GO

Pfeilsymbol, dass mit dem Link "Zurück zum Anfang" verwendet wird[Nach oben]

Ändern einer Tabellendefinition

Die Beispiele in diesem Abschnitt veranschaulichen, wie die Definition einer Tabelle geändert wird.

A.Ändern einer Tabelle zur Änderung der Komprimierung

Im folgenden Beispiel wird die Komprimierung einer nicht partitionierten Tabelle geändert. Der Heap oder der gruppierte Index wird neu erstellt. Wenn die Tabelle ein Heap ist, werden alle nicht gruppierten Indizes neu erstellt.

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);

Im folgenden Beispiel wird die Komprimierung einer partitionierten Tabelle geändert. Die REBUILD PARTITION = 1-Syntax bewirkt, dass nur die Partition 1 neu erstellt wird.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

Mit der folgenden alternativen Syntax werden im gleichen Vorgang alle Partitionen in der Tabelle neu erstellt.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;

Weitere Beispiele zur Datenkomprimierung finden Sie unter Datenkomprimierung.

B.Wechseln von Partitionen zwischen Tabellen

Im folgenden Beispiel wird eine partitionierte Tabelle erstellt, wobei vorausgesetzt wird, dass das myRangePS1-Partitionsschema bereits in der Datenbank erstellt wurde. Anschließend wird eine nicht partitionierte Tabelle mit derselben Struktur wie die partitionierte Tabelle und in derselben Dateigruppe wie PARTITION 2 der PartitionTable-Tabelle erstellt. Die Daten von PARTITION 2 der PartitionTable-Tabelle werden dann in die NonPartitionTable-Tabelle verschoben.

CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

C.Zulassen der Sperrenausweitung in partitionierten Tabellen

Im folgenden Beispiel wird die Sperrenausweitung auf die Partitionsebene in einer partitionierten Tabelle aktiviert. Wenn die Tabelle nicht partitioniert ist, wird die Sperrenausweitung auf der TABLE-Ebene festgelegt.

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO);
GO

D.Konfigurieren der Änderungsnachverfolgung in einer Tabelle

Im folgenden Beispiel wird die Änderungsnachverfolgung für die Person.Person-Tabelle aktiviert.

USE AdventureWorks2012;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING;

Im folgenden Beispiel wird die Änderungsnachverfolgung aktiviert, und die Verfolgung der Spalten, die während einer Änderung aktualisiert werden, wird aktiviert.

USE AdventureWorks2012;
GO
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

Im folgenden Beispiel wird die Änderungsnachverfolgung für die Person.Person-Tabelle deaktiviert.

USE AdventureWorks2012;
Go
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING;

Pfeilsymbol, dass mit dem Link "Zurück zum Anfang" verwendet wird[Nach oben]

Deaktivieren und Aktivieren von Einschränkungen und Triggern

A.Deaktivieren und erneutes Aktivieren einer Einschränkung

Im folgenden Beispiel wird eine Einschränkung deaktiviert, die die in den Daten akzeptierten Gehälter begrenzt. NOCHECK CONSTRAINT wird mit ALTER TABLE verwendet, um die Einschränkung zu deaktivieren und eine Einfügung zuzulassen, die die Einschränkung andernfalls verletzen würde. CHECK CONSTRAINT aktiviert die Einschränkung wieder.

CREATE TABLE dbo.cnst_example 
(id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000)
);

-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1,'Joe Brown',65000);
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000);

-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;

B.Deaktivieren und erneutes Aktivieren eines Triggers

Im folgenden Beispiel wird die DISABLE TRIGGER-Option von ALTER TABLE verwendet, um den Trigger zu deaktivieren und eine Einfügung zuzulassen, die den Trigger normalerweise verletzen würde. Anschließend wird der Trigger mithilfe von ENABLE TRIGGER wieder aktiviert.

CREATE TABLE dbo.trig_example 
(id INT, 
name VARCHAR(12),
salary MONEY) ;
GO
-- Create the trigger.
CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT
AS
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
    print 'TRIG1 Error: you attempted to insert a salary > $100,000'
    ROLLBACK TRANSACTION
END ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ;
GO

Pfeilsymbol, dass mit dem Link "Zurück zum Anfang" verwendet wird[Nach oben]

Siehe auch

Verweis

sys.tables (Transact-SQL)

sp_rename (Transact-SQL)

CREATE TABLE (Transact-SQL)

DROP TABLE (Transact-SQL)

sp_help (Transact-SQL)

ALTER PARTITION SCHEME (Transact-SQL)

ALTER PARTITION FUNCTION (Transact-SQL)

EVENTDATA (Transact-SQL)