ALTER TABLE (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Warehouse in Microsoft FabricSQL-Datenbank in Microsoft Fabric

Ändert eine Tabellendefinition durch Ändern, Hinzufügen oder Löschen von Spalten und Einschränkungen. ALTER TABLE außerdem werden Partitionen neu zugewiesen und neu erstellt oder deaktiviert und aktiviert Einschränkungen und Trigger.

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_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 | HIDDEN }
      | { ADD | DROP } MASKED [ WITH ( FUNCTION = ' mask_function ') ]
    }
    [ WITH ( ONLINE = ON | OFF ) ]
    | [ WITH { CHECK | NOCHECK } ]

    | ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <column_set_definition>
    } [ ,...n ]
      | [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START
                [ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END
                   [ HIDDEN ] [ NOT NULL ][ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                start_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  start_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES]
        ]
       PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
    | DROP
     [ {
         [ CONSTRAINT ][ IF EXISTS ]
         {
              constraint_name
              [ WITH
               ( <drop_clustered_constraint_option> [ ,...n ] )
              ]
          } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...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 ]
        [ WITH ( <low_priority_lock_wait> ) ]

    | SET
        (
            [ FILESTREAM_ON =
                { partition_scheme_name | filegroup | "default" | "NULL" } ]
            | SYSTEM_VERSIONING =
                  {
                    OFF
                  | ON
                      [ ( HISTORY_TABLE = schema_name . history_table_name
                          [, DATA_CONSISTENCY_CHECK = { ON | OFF } ]
                          [, HISTORY_RETENTION_PERIOD =
                          {
                              INFINITE | number {DAY | DAYS | WEEK | WEEKS
                  | MONTH | MONTHS | YEAR | YEARS }
                          }
                          ]
                        )
                      ]
                  }
            | DATA_DELETION =
                {
                      OFF
                    | ON
                        [(  [ FILTER_COLUMN = column_name ]
                            [, RETENTION_PERIOD = { INFINITE | number { DAY | DAYS | WEEK | WEEKS
                                    | MONTH | MONTHS | YEAR | YEARS } } ]
                        )]
                    } )
    | REBUILD
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ]
      | [ PARTITION = partition_number
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | <table_option>
    | <filetable_option>
    | <stretch_configuration>
}
[ ; ]

-- 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 ) ]
    }

<stretch_configuration> ::=
    {
      SET (
        REMOTE_DATA_ARCHIVE
        {
            = ON (<table_stretch_options>)
          | = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )
          | ( <table_stretch_options> [, ...n] )
        }
            )
    }

<table_stretch_options> ::=
    {
     [ FILTER_PREDICATE = { null | table_predicate_function } , ]
       MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }
    }

<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE}
    | ONLINE = { ON [( <low_priority_lock_wait> ) ] | OFF }
}

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ],
        ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

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

    | ALTER INDEX index_name
    {
        [ type_schema_name. ] type_name
        REBUILD
        [ [ NONCLUSTERED ] WITH ( BUCKET_COUNT = bucket_count )
        ]
    }

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

    | DROP
     [ {
         CONSTRAINT [ IF EXISTS ]
         {
              constraint_name
          } [ ,...n ]
        | INDEX [ IF EXISTS ]
      {
         index_name
       } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...n ] ]
    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
        { ALL | constraint_name [ ,...n ] }

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

    | SWITCH [ [ PARTITION ] source_partition_number_expression ]
        TO target_table
        [ PARTITION target_partition_number_expression ]
        [ WITH ( <low_priority_lock_wait> ) ]

}
[ ; ]

-- ALTER TABLE options

< table_constraint > ::=
 [ CONSTRAINT constraint_name ]
{
   {PRIMARY KEY | UNIQUE }
     {
       NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])
       | NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )
     }
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
    | CHECK ( logical_expression )
}

<column_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] | [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count) }

<table_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
  | [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )
      [ ON filegroup_name | default ]
  | CLUSTERED COLUMNSTORE [ WITH ( COMPRESSION_DELAY = { 0 | delay [MINUTES] } ) ]
      [ ON filegroup_name | default ]
}

ALTER TABLE { database_name.schema_name.source_table_name | schema_name.source_table_name | source_table_name }
{
    ALTER COLUMN column_name
        {
            type_name [ ( precision [ , scale ] ) ]
            [ COLLATE Windows_collation_name ]
            [ NULL | NOT NULL ]
        }
    | ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]
    | DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
    | REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
      }
    | { SPLIT | MERGE } RANGE (boundary_value)
    | SWITCH [ PARTITION source_partition_number
        TO target_table_name [ PARTITION target_partition_number ] [ WITH ( TRUNCATE_TARGET = ON | OFF ) ] ]
}
[ ; ]

<column_definition>::=
{
    column_name
    type_name [ ( precision [ , scale ] ) ]
    [ <column_constraint> ]
    [ COLLATE Windows_collation_name ]
    [ NULL | NOT NULL ]
}

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
        DEFAULT constant_expression
        | PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
        | UNIQUE (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
    }
<rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
}

<single_partition_rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
}

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
  ADD  { column_name <data_type> [COLLATE collation_name] [ <column_options> ] } [ ,...n ]
| ADD { <column_constraint> FOR column_name} [ ,...n ]
| DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
}
[ ; ]

<column_options> ::=
[ NULL ] -- default is NULL

<data type> ::= type_name [ ( precision [ , scale ] ) ]

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
       PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
        | UNIQUE NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] NOT ENFORCED
    }

Arguments

Datenbankname

Der Name der Datenbank, in der Sie die Tabelle erstellt haben.

schema_name

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

Tabellenname

Der Name der zu ändernden Tabelle. Wenn sich die Tabelle nicht in der aktuellen Datenbank befindet oder das Schema der Tabelle nicht dem aktuellen Benutzer gehört, müssen Sie die Datenbank und das Schema explizit angeben.

ALTER-SÄULE

Gibt die zu ändernde benannte Spalte an.

Für die geänderte Spalte gilt Folgendes:

• Eine Spalte mit dem timestamp-Datentyp.

• Die ROWGUIDCOL für die Tabelle.

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

• Wird in Statistiken verwendet, die von der CREATE STATISTICS Anweisung generiert wurden. Führen Sie zum Ablegen dieser Statistiken eine Ausführung DROP STATISTICS aus, bevor ALTER COLUMN sie erfolgreich ausgeführt werden kann. Führen Sie diese Abfrage aus, um alle vom Benutzer erstellten Statistiken und Statistikspalten für eine Tabelle abzurufen.

  SELECT s.name AS statistics_name,
         c.name AS column_name,
         sc.stats_column_id
  FROM sys.stats AS s
       INNER JOIN sys.stats_columns AS sc
           ON s.object_id = sc.object_id
          AND s.stats_id = sc.stats_id
       INNER JOIN sys.columns AS c
           ON sc.object_id = c.object_id
          AND c.column_id = sc.column_id
  WHERE s.object_id = OBJECT_ID('<table_name>');
  

• Wird in einer PRIMARY KEY oder [FOREIGN KEY] REFERENCES einer Einschränkung verwendet.

• Wird in einer CHECK oder UNIQUE einer Einschränkung verwendet. Sie können jedoch die Länge einer Spalte mit variabler Länge ändern, die in einer CHECK oder UNIQUE einer Einschränkung verwendet wird.

• Der Spalte darf keine Standarddefinition zugeordnet sein. Sie können jedoch die Länge, Genauigkeit oder Skalierung einer Spalte ändern, wenn Sie den Datentyp nicht ändern.

ALTER COLUMN legt Statistiken ab, die der Abfrageoptimierer automatisch generiert.

Sie können den Datentyp von Text-, ntext- und Bildspalten nur auf folgende Weise ändern:

• text in varchar(max) , nvarchar(max) oder xml
• ntext in varchar(max) , nvarchar(max) oder xml
• image in varbinary(max)

Einige Datentypänderungen können zu einer Änderung der Daten führen. Beispielsweise kann das Ändern einer nchar - oder nvarchar-Spalte in Zeichen oder Varchar dazu führen, dass erweiterte Zeichen konvertiert werden. 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.
• Sie können den Datentyp einer Spalte in einer partitionierten Tabelle nicht ändern.
• Sie können den Datentyp von Spalten, die in einem Index enthalten sind, nicht ändern, es sei denn, die Spalte ist ein Varchar-, nvarchar- oder varbinary-Datentyp , und die neue Größe ist gleich oder größer als die alte Größe.
• Sie können eine Spalte, die in einer Primärschlüsseleinschränkung enthalten ist, nicht von "in NOT NULLNULL" ändern.

Wenn Sie Always Encrypted (ohne sichere Enklaven) verwenden, ENCRYPTED WITHkönnen Sie den Datentyp in einen kompatiblen Datentyp (z. B. int in bigint) ändern, aber keine Verschlüsselungseinstellungen ändern.

Wenn Sie Always Encrypted mit sicheren Enklaven verwenden, können Sie jede Verschlüsselungseinstellung ändern, wenn der Spaltenverschlüsselungsschlüssel, der die Spalte schützt (und der neue Spaltenverschlüsselungsschlüssel, wenn Sie den Schlüssel ändern) Enklavenberechnungen (verschlüsselt mit Enklaven-aktivierten Spaltenschlüsseln master ) unterstützt. Weitere Einzelheiten finden Sie unter Always Encrypted mit Secure Enclaves.

Wenn Sie eine Spalte ändern, verfolgt die Datenbank-Engine jede Änderung, indem eine Zeile in einer Systemtabelle hinzugefügt und die vorherige Spaltenänderung als verworfene Spalte markiert wird. In dem seltenen Fall, dass Sie eine Spalte zu oft ändern, erreicht die Datenbank-Engine möglicherweise das Limit für die Datensatzgröße. In diesem Fall erhalten Sie einen Fehler MSSQLSERVER_511 oder 1708. Um diese Fehler zu vermeiden, erstellen Sie entweder den gruppierten Index in der Tabelle regelmäßig neu, oder verringern Sie die Anzahl der Spaltenänderungen.

Spaltenname

Der Name der Spalte, die geändert, hinzugefügt oder abzulegen werden soll. column_name darf maximal 128 Zeichen lang sein.

Bei neuen Spalten, die mit einem timestamp-Datentyp erstellt wurden, ist column_name nicht erforderlich. Der Name timestamp wird verwendet, wenn Sie column_name nicht für eine Spalte vom Datentyp timestamp angeben.

[ 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 Typen aufweisen:

• Ein SQL Server Systemdatentyp.
• Ein Aliasdatentyp basierend auf einem SQL Server Systemdatentyp. Sie erstellen Alias-Datentypen mit der CREATE TYPE Anweisung, bevor sie in einer Tabellendefinition verwendet werden können.
• Ein benutzerdefinierter Framework-Typ .NET und das Schema, zu dem es gehört. Sie erstellen benutzerdefinierte Typen mit der CREATE TYPE Anweisung, bevor sie in einer Tabellendefinition verwendet werden können.

Die folgenden Kriterien gelten für type_name einer geänderten Spalte:

• Der vorherige Datentyp muss implizit in den neuen Datentyp konvertiert werden können.
• type_name darf nicht timestamp sein.
• ANSI_NULL Standardwerte sind immer für ALTER COLUMN; wenn nicht angegeben, ist die Spalte NULL-Werte zulässig.
• ANSI_PADDING Abstand ist immer ON für ALTER COLUMN.
• 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 wird SET ARITHABORT ignoriert. ALTER TABLE funktioniert so, als ob ARITHABORT festgelegt ONist.

Präzision

Die Genauigkeit für den angegebenen Datentyp. Weitere Informationen zu gültigen Genauigkeitswerten finden Sie unter Precision, Scale und Length (Transact-SQL).

scale

Die Dezimalstellen für den angegebenen Datentyp. Weitere Informationen zu gültigen Skalierungswerten finden Sie unter Precision, scale, and length (Transact-SQL).

max

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

xml_schema_collection

Gilt für: SQL Server und Azure SQL-Datenbank.

Gilt nur für den xml-Datentyp zum Zuordnen eines XML-Schemas zum Typ. Bevor Sie eine xmlSpalte in eine Schemaauflistung eingeben, erstellen Sie zuerst die Schemaauflistung in der Datenbank mithilfe von CREATE XML SCHEMA COLLECTION (Transact-SQL).

SORTIEREN <collation_name>

Gibt die neue Sortierung für die geänderte Spalte an. Wenn Sie keine Sortierung festlegen, wird der Spalte die Standardsortierung der Datenbank zugewiesen. Der Sortierungsname kann entweder ein Windows Sortierungsname oder ein SQL-Sortierungsname sein. Eine Liste und weitere Informationen finden Sie unter Windows Sortierungsname (Transact-SQL) und SQL Server Sortierungsname (Transact-SQL).

Die COLLATE Klausel ändert nur die Sortierungen der Datentypen Char, varchar, nchar und nvarchar . Wenn Sie die Sortierung einer benutzerdefinierten Aliasdatentypspalte ändern möchten, verwenden Sie separate ALTER TABLE-Anweisungen, um die Spalte in einen SQL Server Systemdatentyp zu ändern. Ändern Sie dann die Sortierung, und ändern Sie die Spalte zurück in einen Aliasdatentyp.

ALTER COLUMN Kann keine Sortierungsänderung haben, wenn eine oder mehrere der folgenden Bedingungen vorhanden sind:

• Eine CHECK Einschränkung, FOREIGN KEY Einschränkung oder berechnete Spalten verweisen auf die geänderte Spalte.
• Index, Statistiken oder Volltextindex werden in der Spalte erstellt. Statistiken, die automatisch für die geänderte Spalte erstellt wurden, werden gelöscht, wenn die Spaltensortierung geändert wird.
• Eine schemagebundene Ansicht oder Funktion verweist auf die Spalte.

Weitere Informationen zu unterstützten Sortierungen finden Sie unter COLLATE (Transact-SQL).

NULL | NICHT NULL

Gibt an, ob die Spalte NULL-Werte akzeptiert. Sie können Spalten hinzufügen, die keine NULL-Werte zulassen, indem Sie nur dann ALTER TABLE verwenden, wenn sie einen Standardwert angegeben haben oder wenn die Tabelle leer ist. Sie können nur für berechnete Spalten angeben NOT NULL , wenn Sie auch angeben PERSISTED. Wenn die neue Spalte NULL-Werte zulässt und kein Standardwert angegeben wird, enthält sie einen NULL-Wert für jede Zeile in der Tabelle. Wenn die neue Spalte Nullwerte zulässt und Sie eine Standarddefinition mit der neuen Spalte hinzufügen, können WITH VALUES Sie den Standardwert in der neuen Spalte für jede vorhandene Zeile in der Tabelle speichern.

Wenn die neue Spalte keine Nullwerte zulässt und die Tabelle nicht leer ist, müssen Sie eine DEFAULT Definition mit der neuen Spalte hinzufügen. Die neue Spalte wird automatisch mit dem Standardwert in den neuen Spalten in jeder vorhandenen Zeile geladen.

Sie können angeben NULLALTER COLUMN , dass eine NOT NULL Spalte erzwingt, dass Nullwerte zulässig sind, mit Ausnahme von Spalten in PRIMARY KEY Einschränkungen. Sie können nur angeben NOT NULLALTER COLUMN , wenn die Spalte keine Nullwerte enthält. Sie müssen die NULL-Werte auf einen Wert aktualisieren, bevor der ALTER COLUMNNOT NULL Wert zulässig ist, z. B.:

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 mithilfe der Anweisungen ALTER TABLE oder Anweisungen CREATE TABLE erstellen oder ändern, können Datenbank- und Sitzungseinstellungen die Nullierbarkeit des Datentyps, den Sie in einer Spaltendefinition angeben, beeinflussen und möglicherweise außer Kraft setzen. Definieren Sie immer explizit eine Spalte als NULL oder NOT NULL für nicht komputierte Spalten.

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

[ {ADD | DROP} ROWGUIDCOL ]

Gilt für: SQL Server und Azure SQL-Datenbank.

Gibt an, dass die ROWGUIDCOL Eigenschaft der angegebenen Spalte hinzugefügt oder gelöscht wird. ROWGUIDCOL gibt an, dass es sich bei der Spalte um eine Zeilen-GUID-Spalte handelt. Sie können pro Tabelle nur eine Eindeutigidentifiziererspalte als ROWGUIDCOL Spalte festlegen. Sie können die ROWGUIDCOL Eigenschaft nur einer eindeutigen Identifikationsspalte zuweisen. Sie können einer Spalte eines benutzerdefinierten Datentyps nicht zuweisen ROWGUIDCOL .

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 zum Generieren eindeutiger Werte für jede Spalte entweder die NEWID()- oder NEWSEQUENTIALID()-Funktion in INSERT Anweisungen. Oder geben Sie die NEWID()- oder NEWSEQUENTIALID()-Funktion als Standard für die Spalte an.

[ {ADD | DROP} BEIBEHALTEN ]

Gibt an, dass die PERSISTED Eigenschaft der angegebenen Spalte hinzugefügt oder gelöscht wird. Die Spalte muss eine berechnete Spalte sein, die durch einen deterministischen Ausdruck definiert ist. Bei Spalten, die als PERSISTED angegeben sind, speichert die Datenbank-Engine die berechneten Werte in der Tabelle physisch und aktualisiert die Werte, wenn alle anderen Spalten, von denen die berechnete Spalte abhängt, aktualisiert werden. Indem Sie eine berechnete Spalte als PERSISTEDkennzeichnen, können Sie Indizes für berechnete Spalten erstellen, die für Ausdrücke definiert sind, die deterministisch, aber nicht präzise sind. Weitere Informationen finden Sie unter "Indizes für berechnete Spalten".

SET QUOTED_IDENTIFIER muss sein ON , wenn Sie Indizes für berechnete Spalten oder indizierte Ansichten erstellen oder ändern. Weitere Informationen finden Sie unter SET QUOTED_IDENTIFIER (Transact-SQL).

Jede berechnete Spalte, die als Partitionierungsspalte einer partitionierten Tabelle verwendet wird, muss explizit markiert PERSISTEDwerden.

DROP NICHT FÜR REPLIKATION

Gilt für: SQL Server und Azure SQL-Datenbank.

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 Sparsespalte ist. Der Speicher für Sparsespalten ist für NULL-Werte optimiert. Sie können keine Spalten mit geringem Satzes festlegen als NOT NULL. Wenn Sie eine Sparsespalte in eine Nicht-Sparsespalte oder umgekehrt umwandeln, wird die Tabelle für die Dauer der Befehlsausführung durch diese Option gesperrt. Möglicherweise müssen Sie die REBUILD Klausel verwenden, um Speicherplatzeinsparungen zurückzufordern. Weitere Einschränkungen und weitere Informationen zu geringen Spalten finden Sie unter Verwenden von Spalten mit geringem Aufwand.

FÜGEN SIE MASKIERT MIT ( FUNCTION = 'mask_function') HINZU.

Gilt für: SQL Server 2016 (13.x) und höhere Versionen und Azure SQL-Datenbank.

Gibt eine dynamische Datenmaske an. mask_function ist der Name der Maskierungsfunktion mit den entsprechenden Parametern. Drei Optionen stehen zur Verfügung:

• default()
• email()
• partial()
• random()

Erfordert ALTER ANY MASK die Berechtigung.

Verwenden Sie DROP MASKED, um eine Maske zu löschen. Informationen zu Funktionsparametern finden Sie unter Dynamische Datenformatierung.

Zum Hinzufügen und Ablegen einer Maske ist die Berechtigung erforderlich ALTER ANY MASK .

WITH ( ONLINE = ON | OFF) <wie beim Ändern einer Spalte>

Gilt für: SQL Server 2016 (13.x) und höhere Versionen und Azure SQL-Datenbank.

Ermöglicht verschiedene Aktionen zum Ändern einer Spalte, während die Tabelle verfügbar bleibt. Der Standardwert ist OFF. ALTER COLUMN kann online für Spaltenänderungen in Bezug auf Datentyp, Spaltenlänge, Genauigkeit, NULL-Zulässigkeit, geringe Dichte und Sortierung ausgeführt werden.

Online ALTER COLUMN ermöglicht es benutzern, auf die geänderte Spalte für die Dauer des ALTER COLUMN Vorgangs zu verweisen, sodass Abfragen wie gewohnt ausgeführt werden können. Nach dem Beenden des Vorgangs werden automatisch erstellte Statistiken, die auf die Spalte verweisen, gelöscht, und vom Benutzer erstellte Statistiken werden ungültig. Der Benutzer muss benutzergenerierte Statistiken manuell aktualisieren, nachdem der Vorgang abgeschlossen wurde. Wenn die Spalte Teil eines Filterausdrucks für Statistiken oder Indizes ist, können Sie keinen Vorgang ausführen ALTER COLUMN .

• Während der Onlinevorgang ALTER COLUMN ausgeführt wird, wird jeder DDL-Vorgang, der von dieser Spalte abhängig sein kann (z. B. das Erstellen oder Ändern von Indizes oder Ansichten), blockiert oder schlägt mit einem geeigneten Fehler fehl. Dieses Verhalten garantiert, dass online ALTER COLUMN aufgrund von Abhängigkeiten, die während der Ausführung des Vorgangs eingeführt wurden, nicht fehlschlägt.

• Das Ändern einer Spalte von NOT NULL "in NULL " wird nicht als Onlinevorgang unterstützt, wenn auf die geänderte Spalte durch nicht gruppierte Indizes verwiesen wird.

• Online ALTER wird nicht unterstützt, wenn auf die Spalte durch eine Prüfeinschränkung verwiesen wird und der ALTER Vorgang die Genauigkeit der Spalte (numerisch oder datetime) einschränkt.

• Die WAIT_AT_LOW_PRIORITY Option kann nicht mit online ALTER COLUMNverwendet werden.

• ALTER COLUMN ... ADD/DROP PERSISTED wird für online ALTER COLUMNnicht unterstützt.

• ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION ist nicht von online ALTER COLUMNbetroffen.

• Online ALTER COLUMN unterstützt keine Änderung einer Tabelle, in der die Änderungsnachverfolgung aktiviert ist oder dies ein Herausgeber der Seriendruckreplikation ist.

• Online ALTER COLUMN unterstützt keine Änderung von oder zu CLR-Datentypen.

• Online ALTER COLUMN unterstützt keine Änderung an einem XML-Datentyp, der eine Schemaauflistung aufweist, die sich von der aktuellen Schemaauflistung unterscheidet.

• Online ALTER COLUMN reduziert nicht die Einschränkungen, wenn eine Spalte geändert werden kann. Verweise nach Index, Statistiken usw. können dazu führen, dass die Änderung fehlschlägt.

• Online ALTER COLUMN unterstützt nicht das gleichzeitige Ändern von mehr als einer Spalte.

• Online ALTER COLUMN hat keine Auswirkung auf eine systemversionierte zeitliche Tabelle. ALTER Spalte wird unabhängig davon, welcher Wert für ONLINE die Option angegeben wurde, nicht online ausgeführt.

Online ALTER COLUMN verfügt über ähnliche Anforderungen, Einschränkungen und Funktionen wie die Neuerstellung von Onlineindexen, einschließlich:

• Die Online-Indexneuerstellung wird nicht unterstützt, wenn die Tabelle ältere LOB- oder Filestream-Spalten enthält oder die Tabelle über einen Columnstore-Index verfügt. Die gleichen Einschränkungen gelten für online ALTER COLUMN.
• Eine vorhandene Spalte, die geändert wird, erfordert die doppelte Speicherplatzzuordnung: für die ursprüngliche Spalte und für die neu erstellte, ausgeblendete Spalte.
• Die Sperrstrategie während eines Onlinevorgangs zur Spaltenänderung folgt demselben Sperrmuster wie die Onlineindexerstellung.

MIT CHECK | MIT NOCHECK

Gibt an, ob die Daten in der Tabelle anhand einer neu hinzugefügten oder erneut aktivierten oder FOREIGN KEY einschränkung überprüft CHECK werden oder nicht überprüft werden. Wenn Sie dies nicht angeben, WITH CHECK wird für neue Einschränkungen angenommen und WITH NOCHECK für wieder aktivierte Einschränkungen angenommen.

Wenn Sie keine neuen CHECK oder FOREIGN KEY Einschränkungen für vorhandene Daten überprüfen möchten, verwenden Sie WITH NOCHECKdiese. Dies wird in der Regel nie empfohlen, kann aber unter bestimmten Umständen erforderlich sein. Die neue Einschränkung wird bei allen späteren Datenupdates ausgewertet. Alle Einschränkungsverletzungen, die beim WITH NOCHECK Hinzufügen der Einschränkung unterdrückt werden, können dazu führen, dass zukünftige Aktualisierungen fehlschlagen, wenn sie Zeilen mit Daten aktualisieren, die nicht der Einschränkung folgen. Der Abfrageoptimierer berücksichtigt keine Einschränkungen, die definiert WITH NOCHECKsind. Diese Einschränkungen werden ignoriert, bis sie mithilfe von ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL erneut aktiviert werden. Weitere Informationen finden Sie unter Deaktivieren von Fremdschlüsseleinschränkungen mit INSERT- und UPDATE-Anweisungen.

ALTER INDEX index_name

Gibt an, dass die Bucketanzahl von index_name geändert werden muss.

Die Syntax ALTER TABLE ... ADD/DROP/ALTER INDEX wird nur für speicheroptimierte Tabellen unterstützt.

ADD

Gibt an, dass eine oder mehrere Spaltendefinitionen, Definitionen berechneter Spalten oder Tabelleneinschränkungen hinzugefügt werden. Alternativ dazu werden die Spalten hinzugefügt, die das System zur Systemversionsverwaltung verwendet. Für speicheroptimierte Tabellen kann ein Index hinzugefügt werden.

ZEITRAUM FÜR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )

Gilt für: SQL Server 2017 (14.x) und höhere Versionen und Azure SQL-Datenbank.

Gibt die Namen der Spalten an, die das System zum Aufzeichnen des Zeitraums verwendet, für den ein Datensatz gültig ist. Sie können vorhandene Spalten angeben oder neue Spalten als Teil des ADD PERIOD FOR SYSTEM_TIME Arguments erstellen. Richten Sie die Spalten mit dem Datentyp "datetime2 " ein, und definieren Sie sie als NOT NULL. Wenn Sie eine Punktspalte als NULL"Punkt" definieren, wird ein Fehler zurückgegeben. Sie können eine column_constraint und/oder Standardwerte für Spalten für die system_start_time und system_end_time Spalten angeben. Sehen Sie sich Beispiel A in den folgenden Beispielen zur Systemversionsverwaltung an, in dem veranschaulicht wird, wie Sie Standardwerte für system_end_time-Spalten einsetzen können.

Verwenden Sie dieses Argument mit dem SET SYSTEM_VERSIONING Argument, um eine vorhandene Tabelle zu einer zeitlichen Tabelle zu machen. Weitere Informationen finden Sie unter "Zeitliche Tabellen " und "Erste Schritte mit zeitlichen Tabellen".

Ab SQL Server 2017 (14.x) können Benutzer eine oder beide Punktspalten mit HIDDEN Flag markieren, um diese Spalten implizit auszublenden, sodass SELECT * FROM <table_name> keinen Wert für die Spalten zurückgibt. Standardmäßig sind Zeitraumspalten nicht ausgeblendet. Damit sie verwendet werden können, müssen ausgeblendete Spalten explizit in allen Abfragen eingeschlossen werden, die direkt auf die temporale Tabelle verweisen.

DROP

Gibt an, dass eine oder mehrere Spaltendefinitionen, Definitionen berechneter Spalten oder Tabelleneinschränkungen oder die Spezifikation der Spalten, die das System zur Systemversionsverwaltung verwendet, gelöscht werden.

CONSTRAINT-constraint_name

Gibt an, dass constraint_name aus der Tabelle entfernt wird. Es können mehrere Einschränkungen aufgeführt werden.

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 in der Tabelle ein XML-Index vorhanden ist.

INDEX-index_name

Gibt an, dass index_name aus der Tabelle entfernt wird.

Die Syntax ALTER TABLE ... ADD/DROP/ALTER INDEX wird nur für speicheroptimierte Tabellen unterstützt.

SPALTE column_name

Gibt an, dass contraint_name oder column_name aus der Tabelle gelöscht wird. Es können mehrere Spalten aufgeführt werden.

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

• Wird in einem Index verwendet, ob als Schlüsselspalte oder als Schlüsselspalte INCLUDE
• Wird in einer CHECK, FOREIGN KEY, UNIQUEoder PRIMARY KEY Einschränkung verwendet.
• Einem Standard zugeordnet, der mit dem DEFAULT Schlüsselwort definiert oder an ein Standardobjekt gebunden ist.
• Wenn sie an eine Regel gebunden ist.

ZEITRAUM FÜR SYSTEM_TIME

Gilt für: SQL Server 2016 (13.x) und höhere Versionen und Azure SQL-Datenbank.

Legt die Spezifikation für die Spalten ab, die das System für die Systemversionsverwaltung verwendet.

MIT <drop_clustered_constraint_option>

Gibt an, dass mindestens eine Option zum Löschen einer gruppierten Einschränkung festgelegt wurde.

MAXDOP = max_degree_of_parallelism

Gilt für: SQL Server und Azure SQL-Datenbank.

Überschreibt die Konfigurationsoption Max. Grad an Parallelität nur für die Dauer des Vorgangs. Weitere Informationen finden Sie unter Serverkonfiguration: max. Grad der Parallelität.

Verwenden Sie die MAXDOP Option, um die Anzahl der Prozessoren zu begrenzen, die bei der parallelen Planausführung verwendet werden. Maximal sind 64 Prozessoren zulässig.

max_degree_of_parallelism kann einer der folgenden Werte sein:

• 1

  Unterdrückt das Generieren paralleler Pläne.

• >1

  Begrenzt die Höchstzahl von Prozessoren in einem parallelen Indexvorgang auf die angegebene Zahl

• 0 (Standardwert)

  Verwendet die tatsächliche Anzahl von Prozessoren oder weniger basierend auf der aktuellen Systemauslastung.

Weitere Informationen finden Sie unter Konfigurieren von parallelen Indexvorgängen.

ONLINE = { ON | OFF } <wie bei drop_clustered_constraint_option>

Gibt an, ob die zugrunde liegenden Tabellen und zugeordneten Indizes für Abfragen und Datenänderungen während des Indexvorgangs verfügbar sind. Der Standardwert lautet OFF. Sie können als REBUILD Vorgang ausführenONLINE.

• ON

  Lang andauernde Tabellensperren werden nicht für die Dauer des Indexvorgangs aufrechterhalten. Während der Hauptphase des Indexvorgangs wird nur eine Intent Share(IS)-Sperre in der Quelltabelle gespeichert. Durch dieses Verhalten können Abfragen oder Updates der zugrunde liegenden Tabelle und Indizes fortgesetzt werden. Zu Beginn des Vorgangs wird das Quellobjekt für kurze Zeit mit einer gemeinsamen Sperre (Shared, S) belegt. Am Ende des Vorgangs wird für kurze Zeit eine gemeinsame Sperre (S) für die Quelle aktiviert, wenn ein nicht gruppierter Index erstellt wird. Oder eine Sch-M -Sperre (Schemaänderung) wird abgerufen, wenn ein gruppierter Index online erstellt oder abgelegt wird und ein gruppierter oder nicht gruppierter Index neu erstellt wird. ONLINE kann nicht festgelegt ON werden, wenn ein Index in einer lokalen temporären Tabelle erstellt wird. Nur ein Heap-Neuerstellungsvorgang mit einem einzelnen Thread ist zulässig.

  Um die DDL für SWITCH oder die Onlineindexerstellung auszuführen, müssen alle aktiven Blockierungstransaktionen, die auf einer bestimmten Tabelle ausgeführt werden, abgeschlossen sein. Bei der Ausführung verhindert der SWITCH Vorgang oder die Neuerstellung, dass neue Transaktionen gestartet werden, und kann sich erheblich auf den Workloaddurchsatz auswirken und den Zugriff auf die zugrunde liegende Tabelle vorübergehend verzögern.

• 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. Durch diese Sperre 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. Diese Sperre verhindert Aktualisierungen der zugrunde liegenden Tabelle, ermöglicht jedoch Lesevorgänge, z SELECT . B. Anweisungen. Heap-Neuerstellungsvorgänge mit mehreren Threads sind zulässig.

  Weitere Informationen finden Sie unter Funktionsweise von Onlineindexvorgängen.

  Note

  Onlineindexvorgänge sind in jeder Edition von SQL Server nicht verfügbar. Weitere Informationen finden Sie unter Editions und unterstützten Features von SQL Server 2022.

IN { partition_scheme_name(column_name [ ,... n ] ) | filegroup | "default" }

Gilt für: SQL Server und Azure SQL-Datenbank.

Gibt einen Speicherort an, an den die Datenzeilen verschoben werden sollen, die sich aktuell auf der Blattebene des gruppierten Indexes befinden. Die Tabelle wird an den neuen Speicherort verschoben. Diese Option gilt nur für Einschränkungen, durch die ein gruppierter Index erstellt wird.

{ CHECK | NOCHECK } EINSCHRÄNKUNG

Gibt an, dass contraint_name aktiviert oder deaktiviert wird. Diese Option kann nur mit FOREIGN KEY und CHECK Einschränkungen verwendet werden. Wenn NOCHECK angegeben, wird die Einschränkung deaktiviert, und zukünftige Einfügungen oder Aktualisierungen an der Spalte werden nicht anhand der Einschränkungsbedingungen überprüft. DEFAULT, PRIMARY KEYund UNIQUE Einschränkungen können nicht deaktiviert werden.

• ALL

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

{ ENABLE | DEAKTIVIERT } TRIGGER

Gibt an, dass trigger_name aktiviert oder deaktiviert wird. Wenn ein Trigger deaktiviert wird, bleibt er dennoch weiterhin für die Tabelle definiert. Wenn INSERTjedoch , UPDATEoder DELETE Anweisungen für die Tabelle ausgeführt werden, werden die Aktionen im Trigger erst ausgeführt, wenn der Trigger erneut aktiviert ist.

• 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 | DEAKTIVIEREN } CHANGE_TRACKING

Gilt für: SQL Server und Azure SQL-Datenbank.

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 options (Transact-SQL).

Um die Änderungsnachverfolgung zu aktivieren, muss die Tabelle über einen Primärschlüssel verfügen.

MIT ( TRACK_COLUMNS_UPDATED = { ON | FALSCH } )

Gilt für: SQL Server und Azure SQL-Datenbank.

Gibt an, ob die Datenbank-Engine Spuren, welche Überarbeitungsspalten aktualisiert wurden. Standardwert: OFF.

SCHALTE [ PARTITION source_partition_number_expression ] AUF [ schema_name UM. ] target_table [ PARTITION target_partition_number_expression ]

Gilt für: SQL Server und Azure SQL-Datenbank.

Verlagert einen Datenblock auf eine der folgenden Arten:

• Weist alle Daten einer Tabelle als Partition einer bereits vorhandenen partitionierten Tabelle neu zu.
• Wechselt eine Partition von einer partitionierten Tabelle zu einer anderen.
• Weist alle Daten aus einer Partition einer partitionierten Tabelle einer bereits vorhandenen nicht partitionierten Tabelle neu 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 vorhanden 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 dürfen nicht gleich sein. target_table kann ein mehrteiliger Bezeichner sein.

Sowohl source_partition_number_expression als auch 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.

Eine partitionierte Tabelle mit einem gruppierten Columnstore-Index verhält sich wie ein partitionierter Heap:

• Der Primärschlüssel muss den Partitionsschlüssel beinhalten.
• Der eindeutige Index muss den Partitionsschlüssel beinhalten. Durch das Einbeziehen des Partitionsschlüssels in einen eindeutigen Bezeichner kann sich jedoch die Eindeutigkeit verändern.
• Alle nicht gruppierten Indizes müssen den Partitionsschlüssel enthalten, damit Partitionen gewechselt werden können.

Einschränkungen SWITCH bei der Verwendung der Replikation finden Sie unter Replizieren partitionierte Tabellen und Indizes.

Nicht gruppierte Spaltenspeicherindizes wurden in einem schreibgeschützten Format vor SQL Server 2016 (13.x) und für SQL-Datenbank vor Version V12 erstellt. Sie müssen nicht gruppierte Columnstore-Indizes in das aktuelle Format (aktualisierbar) neu erstellen, bevor Vorgänge PARTITION ausgeführt werden können.

Limitations

Wenn beide Tabellen identisch partitioniert werden, einschließlich nicht gruppierter Indizes und die Zieltabelle keine nicht gruppierten Indizes enthält, erhalten Sie möglicherweise einen Fehler von 4907.

Beispielausgabe:

Msg 4907, Level 16, State 1, Line 38
'ALTER TABLE SWITCH' statement failed. The table 'MyDB.dbo.PrtTable1' has 4 partitions while index 'MS1' has 6 partitions.

SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "Standard" | "NULL" })

Gilt für: SQL Server. Azure SQL-Datenbank unterstützt FILESTREAM nicht.

Gibt an, wo FILESTREAM-Daten gespeichert werden.

ALTER TABLE wenn die SET FILESTREAM_ON Klausel nur erfolgreich ist, wenn die Tabelle keine FILESTREAM-Spalten enthält. Sie können FILESTREAM-Spalten mithilfe einer zweiten ALTER TABLE Anweisung hinzufügen.

Wenn Sie partition_scheme_name angeben, gelten die Regeln für CREATE TABLE (Transact-SQL). Stellen Sie sicher, dass die Tabelle bereits für Zeilendaten partitioniert ist und dass das Partitionsschema die gleiche Partitionsfunktion und die gleichen Partitionsspalten wie das FILESTREAM-Partitionsschema verwendet.

filestream_filegroup_name gibt den Namen einer FILESTREAM-Dateigruppe an. Die Dateigruppe muss über eine Datei verfügen, die für die Dateigruppe mithilfe einer CREATE DATABASE oder ALTER DATABASE (Transact-SQL)-Anweisung definiert ist, oder Sie erhalten einen Fehler.

"default" Gibt die FILESTREAM-Dateigruppe mit dem DEFAULT Eigenschaftensatz an. Wenn keine FILESTREAM-Dateigruppe vorhanden ist, wird eine Fehlermeldung angezeigt.

"NULL" Gibt an, dass alle Verweise auf FILESTREAM-Dateigruppen für die Tabelle entfernt werden. Alle FILESTREAM-Spalten müssen zuerst gelöscht werden. Dient SET FILESTREAM_ON = "NULL" zum Löschen aller FILESTREAM-Daten, die einer Tabelle zugeordnet sind.

SET ( SYSTEM_VERSIONING = { OFF | ON [ ( HISTORY_TABLE = schema_name . history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) } )

Gilt für: SQL Server 2016 (13.x) und höhere Versionen und Azure SQL-Datenbank.

Deaktiviert oder aktiviert die Systemversionsverwaltung einer Tabelle. Um die Systemversionsverwaltung einer Tabelle zu aktivieren, überprüft das System, ob der Datentyp, die Nullbarkeitseinschränkung und die Primärschlüsseleinschränkungsanforderungen für die Systemversionsverwaltung erfüllt sind. Das System zeichnet den Verlauf jedes Datensatzes in der Systemversionstabelle in einer separaten Verlaufstabelle auf. Wenn das HISTORY_TABLE Argument nicht verwendet wird, lautet MSSQL_TemporalHistoryFor<primary_table_object_id>der Name dieser Verlaufstabelle . Wenn die Verlaufstabelle nicht vorhanden ist, generiert das System eine neue Verlaufstabelle, die dem Schema der aktuellen Tabelle entspricht, erstellt eine Verknüpfung zwischen den beiden Tabellen und ermöglicht dem System, den Verlauf jedes Datensatzes der aktuellen Tabelle in der Verlaufstabelle aufzuzeichnen. Wenn Sie das Argument HISTORY_TABLE verwenden, um eine Verknüpfung mit einer vorhandenen Verlaufstabelle zu erstellen und diese zu verwenden, erstellt das System eine Verknüpfung zwischen der aktuellen Tabelle und der angegebenen Tabelle. Wenn Sie eine Verknüpfung mit einer vorhandenen Verlaufstabelle erstellen, können Sie eine Datenkonsistenzprüfung durchführen. Diese Datenkonsistenzprüfung stellt sicher, dass vorhandene Datensätze nicht überlappen. Die Datenkonsistenzprüfung ist standardmäßig aktiviert. Verwenden Sie das Argument SYSTEM_VERSIONING = ON für eine Tabelle, die mit der Klausel PERIOD FOR SYSTEM_TIME definiert ist, um die vorhandene Tabelle als temporale Tabelle festzulegen. Weitere Informationen finden Sie in zeitlichen Tabellen.

HISTORY_RETENTION_PERIOD = { INFINITE | Zahl { TAG | TAGE | WOCHE | WOCHEN | MONAT | MONATE | JAHR | YEARS } }

Gilt für: SQL Server 2017 (14.x) und Azure SQL-Datenbank.

Gibt die endliche oder unendliche Aufbewahrungsdauer für Verlaufsdaten in temporalen Tabellen an. Wenn sie weggelassen wird, wird von einer unendlichen Vermerkdauer ausgegangen.

DATA_DELETION

Gilt für: Azure SQL Edge only

Aktiviert die auf Aufbewahrungsrichtlinien basierende Bereinigung von alten oder veralteten Daten aus Tabellen innerhalb einer Datenbank. Weitere Informationen finden Sie unter Aktivieren und Deaktivieren der Datenaufbewahrung. Die folgenden Parameter müssen angegeben werden, damit Datenaufbewahrung aktiviert wird.

• FILTER_COLUMN = { spaltenname }

  Gibt die Spalte an, die verwendet werden soll, um zu bestimmen, ob die Zeilen in der Tabelle veraltet sind oder nicht. Die folgenden Datentypen sind für die Filterspalte zulässig.

  • date
  • datetime
  • datetime2
  • smalldatetime
  • datetimeoffset

• RETENTION_PERIOD = { INFINITE | Zahl { TAG | TAGE | WOCHE | WOCHEN | MONAT | MONATE | JAHR | YEARS } }

  Gibt die Richtlinie für den Aufbewahrungszeitraum an. Der Aufbewahrungszeitraum wird als eine Kombination aus einem positiven Integerwert und der Datumsteileinheit angegeben.

SET ( LOCK_ESCALATION = { AUTO | TABELLE | DISABLE } )

Gilt für: SQL Server und Azure SQL-Datenbank.

Gibt die zulässigen Methoden der Sperrenausweitung für eine Tabelle an.

• AUTO

  Mit dieser Option können SQL Server-Datenbank-Engine die Granularität der Sperreskalation auswählen, die für das Tabellenschema geeignet ist.

  • Wenn die Tabelle partitioniert ist, ist die Sperreskalation für die Heap- oder B-Tree-Granularität (HoBT) zulässig. Mit anderen Worten, die Eskalation ist auf die Partitionsebene zulässig. Nachdem die Sperre zur HoBT-Ebene eskaliert wurde, wird die Sperre später nicht zu TABLE Granularität eskaliert.

  • Wenn die Tabelle nicht partitioniert ist, erfolgt die Sperreskalation auf die TABLE Granularität.

• TABLE

  Die Sperrenausweitung wird immer mit der Granularität der Tabellenebene ausgeführt, unabhängig davon, ob die Tabelle partitioniert ist. TABLE ist der Standardwert.

• DISABLE

  Verhindert die Sperrenausweitung in den meisten Fällen. Sperren auf Tabellenebene sind jedoch nicht völlig ausgeschlossen. Wenn Sie beispielsweise eine Tabelle scannen, die keinen gruppierten Index unter der serialisierbaren Isolationsebene aufweist, muss Datenbank-Engine eine Tabellensperre verwenden, um die Datenintegrität zu schützen.

REBUILD

Verwenden Sie die REBUILD WITH Syntax, um eine gesamte Tabelle einschließlich aller Partitionen in einer partitionierten Tabelle neu zu erstellen. Wenn die Tabelle über einen gruppierten Index verfügt, 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 = ALLE

Gilt für: SQL Server und Azure SQL-Datenbank.

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

NEUERSTELLEN MIT ( <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 Neuerstellungsoptionen finden Sie unter ALTER TABLE index_option (Transact-SQL).

DATA_COMPRESSION

Gilt für: SQL Server und Azure SQL-Datenbank.

Gibt die Datenkomprimierungsoption für die angegebene Tabelle, die Partitionsnummer oder den Bereich von Partitionen an. Die folgenden Optionen sind verfügbar:

• NONE

  Tabelle oder angegebene Partitionen werden nicht komprimiert. Diese Option gilt nicht für Columnstore-Tabellen.

• RUDERN

  Tabellen- oder angegebene Partitionen werden mithilfe der Zeilenkomprimierung komprimiert. Diese Option gilt nicht für Columnstore-Tabellen.

• SEITE

  Tabellen- oder angegebene Partitionen werden mithilfe der Seitenkomprimierung komprimiert. Diese Option gilt nicht für Columnstore-Tabellen.

• COLUMNSTORE

  Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank.

  Gilt nur für columnstore-Tabellen. COLUMNSTORE Gibt an, eine Partition zu dekomprimieren, die mit der COLUMNSTORE_ARCHIVE Option komprimiert wurde. Nachdem die Daten wiederhergestellt wurden, sind sie weiterhin mit der Columnstore-Komprimierung komprimiert, die für alle Columnstore-Tabellen verwendet wird.

• COLUMNSTORE_ARCHIVE

  Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank.

  Gilt nur für columnstore-Tabellen. Dies sind Tabellen, die mit einem gruppierten columnstore-Index gespeichert wurden. COLUMNSTORE_ARCHIVE komprimiert die angegebene Partition weiter auf eine kleinere Größe. Verwenden Sie diese Option bei der Archivierung und in anderen Situationen, in denen es auf eine geringere Speicherbelegung und nicht auf den zusätzlichen Zeitaufwand für das Speichern und Abrufen ankommt.

  Informationen zur gleichzeitigen Neuerstellung mehrerer Partitionen finden Sie unter index_option. 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".

  ALTER TABLE REBUILD PARTITION WITH DATA COMPRESSION = ROW oder PAGE ist in der SQL-Datenbank in Microsoft Fabric nicht zulässig.

XML_COMPRESSION

Gilt für: SQL Server 2022 (16.x) und höhere Versionen, Azure SQL-Datenbank und Azure SQL Managed Instance.

Gibt die XML-Komprimierungsoption für Spalten mit dem Datentyp xml in der Tabelle an. Die folgenden Optionen sind verfügbar:

• ON

  Spalten mit dem Datentyp xml werden komprimiert.

• OFF

  Spalten mit dem Datentyp xml werden nicht komprimiert.

ONLINE = { ON | OFF } <wie bei single_partition_rebuild_option>

Gibt an, ob eine einzelne Partition der zugrunde liegenden Tabellen und der zugeordneten Indizes für Abfragen und Datenänderungen während des Indexvorgangs verfügbar ist. Der Standardwert lautet OFF. Sie können als REBUILD Vorgang ausführenONLINE.

• ON

  Lang andauernde Tabellensperren werden nicht für die Dauer des Indexvorgangs aufrechterhalten. Erfordert eine S-Sperre für die Tabelle am Anfang der Indexneuerstellung und eine Sch-M-Sperre für die Tabelle am Ende der Onlineneuerstellung des Indexes. Obwohl beide Sperren kurze Metadatensperren sind, muss die Sch-M-Sperre auf den Abschluss aller blockierenden Transaktionen warten. Während der Wartezeit sperrt die Sch-M-Sperre alle anderen Transaktionen, die an dieser Sperre warten, wenn sie auf die gleiche Tabelle zugreifen.

  Note

  Durch Neuerstellung von Onlineindizes können die low_priority_lock_wait-Optionen festgelegt werden, die weiter unten in diesem Abschnitt beschrieben werden.

• OFF

  Die Tabellensperren werden für die Dauer des Indexvorgangs angewendet. Dadurch wird verhindert, dass Benutzer für die Dauer des Vorgangs auf die zugrunde liegende Tabelle zugreifen können.

column_set_name XML-COLUMN_SET FOR ALL_SPARSE_COLUMNS

Gilt für: SQL Server und Azure SQL-Datenbank.

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

{ ENABLE | DEAKTIVIEREN } FILETABLE_NAMESPACE

Gilt für: SQL Server.

Aktiviert oder deaktiviert die systemdefinierten Einschränkungen für eine FileTable. Kann nur mit einer FileTable verwendet werden.

SET ( FILETABLE_DIRECTORY = directory_name )

Gilt für: SQL Server. Azure SQL-Datenbank unterstützt FileTable nicht.

Gibt den namen des Windows kompatiblen FileTable-Verzeichnisses an. Dieser Name sollte für alle FileTable-Verzeichnisnamen in der Datenbank eindeutig sein. Bei Eindeutigkeitsvergleichen wird die Groß-/Kleinschreibung unabhängig von den SQL-Sortiereinstellungen nicht beachtet. Kann nur mit einer FileTable verwendet werden.

REMOTE_DATA_ARCHIVE

Gilt für: SQL Server 2017 (14.x) und höhere Versionen.

Aktiviert bzw. deaktiviert Stretch Database für die Datenbank. Weitere Informationen finden Sie unter Stretch Database.

Aktivieren der Stretchdatenbank für eine Tabelle

Wenn Sie durch Angeben von ON Stretch für eine Tabelle aktivieren, können Sie optional MIGRATION_STATE = OUTBOUND festlegen, um sofort mit dem Migrieren von Daten zu beginnen, oder Sie können MIGRATION_STATE = PAUSED festlegen, um die Datenmigration zu verzögern. Standardwert: MIGRATION_STATE = OUTBOUND. Weitere Informationen über das Aktivieren von Stretch für eine Tabelle finden Sie unter Aktivieren von Stretch Database für eine Tabelle.

Prerequisites. Sie müssen Stretch auf dem Server und auf der Datenbank aktivieren, bevor Sie Stretch für eine Tabelle aktivieren können. Weitere Informationen finden Sie unter Aktivieren von Stretch Database für eine Datenbank.

Permissions. Zum Aktivieren von Stretch für eine Datenbank oder eine Tabelle benötigen Sie db_owner-Berechtigungen. Das Aktivieren von Stretch für eine Tabelle erfordert ALTER auch Berechtigungen für die Tabelle.

Dehnungsdatenbank für eine Tabelle deaktivieren

Wenn Sie "Stretch" für eine Tabelle deaktivieren, haben Sie zwei Optionen für die Remotedaten, die bereits zu Azure migriert wurden. Weitere Informationen finden Sie unter Deaktivieren von Stretch Database und Zurückholen von Remotedaten.

• Um Stretch für eine Tabelle zu deaktivieren und die Remotedaten für die Tabelle von Azure zurück in SQL Server zu kopieren, führen Sie den folgenden Befehl aus. Dieser Befehl kann nicht abgebrochen werden.

  ALTER TABLE <table_name>
     SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;
  

Dieser Vorgang verursacht Datenübertragungskosten und kann nicht abgebrochen werden. Weitere Informationen finden Sie unter Datenübertragungen – Preisdetails.

Nachdem alle Remotedaten von Azure zurück in SQL Server kopiert wurden, ist Stretch für die Tabelle deaktiviert.

• Führen Sie den folgenden Befehl aus, um Stretch für eine Tabelle zu deaktivieren und die Remotedaten zu verwerfen.

  ALTER TABLE <table_name>
     SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;
  

Nach dem Deaktivieren von Stretch Database für eine Tabelle wird die Datenmigration beendet und die Abfrageergebnisse enthalten nicht mehr die Ergebnisse aus der Remotetabelle.

Auch wenn Stretch deaktiviert wird, wird die Remotetabelle nicht entfernt. Wenn Sie die Remotetabelle löschen möchten, legen Sie sie mithilfe des Azure Portals ab.

[ FILTER_PREDICATE = { null | Prädikat } ]

Gilt für: SQL Server 2017 (14.x) und höhere Versionen.

Gibt optional ein Filterprädikat zum Auswählen der Zeilen an, die aus einer Tabelle migriert werden sollen, die sowohl Verlaufsdaten als auch aktuelle Daten enthält. Das Prädikat muss eine deterministische Inline-Tabellenwertfunktion aufrufen. Weitere Informationen finden Sie unter Aktivieren von Stretch Database für eine Tabelle und Auswählen zu migrierender Zeilen mithilfe einer Filterfunktion – Stretch Database.

Wenn Sie kein Filterprädikat angeben, wird die gesamte Tabelle migriert.

Wenn Sie ein Filter-Prädikat angeben, müssen Sie auch angeben MIGRATION_STATE.

MIGRATION_STATE = { AUSGEHEND | EINGEHEND | PAUSED }

Gilt für: SQL Server 2017 (14.x) und höhere Versionen.

• Geben Sie OUTBOUND an, um Daten von SQL Server zu Azure zu migrieren.

• Geben Sie INBOUND an, um die Remotedaten für die Tabelle von Azure zurück in SQL Server zu kopieren und "Stretch" für die Tabelle zu deaktivieren. Weitere Informationen finden Sie unter Deaktivieren von Stretch Database und Zurückholen von Remotedaten.

  Dieser Vorgang verursacht Datenübertragungskosten und kann nicht abgebrochen werden.

• Geben Sie PAUSED an, um die Datenmigration zu pausieren oder nach hinten zu verschieben. Weitere Informationen finden Sie unter Anhalten und Fortsetzen der Datenmigration – Stretch Database.

WAIT_AT_LOW_PRIORITY

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank.

Bei der Onlineindexneuerstellung muss auf blockierende Vorgänge für diese Tabelle gewartet werden. WAIT_AT_LOW_PRIORITY gibt an, dass der Onlineindex-Neuerstellungsvorgang auf Sperrungen mit niedriger Priorität wartet, sodass andere Vorgänge weiter ausgeführt werden können, während der Onlineindexbuildvorgang wartet. Das Auslassen der WAIT AT LOW PRIORITY Option ist identisch mit WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = Zeit [ MINUTEN ]

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank.

Die Wartezeit, bei der es sich um einen ganzzahligen Wert handelt, der in Minuten angegeben ist, dass die SWITCH Sperrungen des Onlineindexes bei ausführung des DDL-Befehls mit niedriger Priorität warten. Wenn der Vorgang für die MAX_DURATION Zeit blockiert wird, wird eine der ABORT_AFTER_WAIT Aktionen ausgeführt. MAX_DURATION Die Zeit ist immer in Minuten, und Sie können das Wort MINUTESweglassen.

ABORT_AFTER_WAIT = { NONE | SELF | BLOCKER }

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank.

• NONE

  Es wird weiterhin mit normaler (regulärer) Priorität auf die Sperre gewartet.

• SELF

  Beenden Sie den DDL-Vorgang, der gerade ausgeführt wird, ohne eine Aktion auszuführen, oder beenden Sie den SWITCH DDL-Vorgang neu.

• BLOCKERS

  Beenden Sie alle Benutzertransaktionen, die den SWITCH DDL-Vorgang derzeit blockieren oder online neu erstellen, damit der Vorgang fortgesetzt werden kann.

  Erfordert ALTER ANY CONNECTION die Berechtigung.

WENN VORHANDEN

Gilt für: SQL Server 2016 (13.x) und höhere Versionen und Azure SQL-Datenbank.

Löscht die Spalte oder Einschränkung nur, wenn diese bereits vorhanden ist.

RESUMABLE = { ON | OFF}

Gilt für: SQL Server 2022 (16.x) und höhere Versionen.

Gibt an, ob ein ALTER TABLE ADD CONSTRAINT-Vorgang fortsetzbar ist. Das Hinzufügen eines Tabellenconstraints kann bei ON fortgesetzt werden. Das Hinzufügen eines Tabellenconstraints kann bei OFF nicht fortgesetzt werden. Der Standardwert ist OFF. Die Option RESUMABLE kann als Teil der ALTER TABLE index_option (Transact-SQL) im ALTER TABLE table_constraint (Transact-SQL) verwendet werden.

MAX_DURATION bei Verwendung mit RESUMABLE = ON (erforderlich ONLINE = ON) gibt zeit (einen ganzzahligen Wert in Minuten) an, dass ein reaktivierbarer Online-Add-Einschränkungsvorgang ausgeführt wird, bevor er angehalten wird. Wenn nicht angegeben, wird der Vorgang bis zum Abschluss fortgesetzt.

Weitere Informationen zum Aktivieren und Verwenden von reaktivierbaren ALTER TABLE ADD CONSTRAINT Vorgängen finden Sie unter "Resumable add table constraints".

Remarks

Um neue Datenzeilen hinzuzufügen, verwenden Sie INSERT (Transact-SQL). Zum Entfernen von Datenzeilen verwenden Sie DELETE (Transact-SQL) oder TRUNCATE TABLE (Transact-SQL). Um die Werte in vorhandenen Zeilen zu ändern, verwenden Sie UPDATE (Transact-SQL).

Wenn im Prozedurcache Ausführungspläne vorhanden sind, die auf die Tabelle verweisen, ALTER TABLE werden sie für die nächste Ausführung neu kompiliert.

Größe einer Spalte ändern

Sie können Länge, Genauigkeit oder Dezimalstellen einer Spalte ändern, indem Sie die neue Größe für den Spaltendatentyp angeben. Verwenden Sie die ALTER COLUMN Klausel. Wenn die Spalte Daten enthält, darf die neue Größe nicht unter der maximalen Datenmenge liegen. Außerdem können Sie die Spalte in einem Index nicht definieren, es sei denn, die Spalte ist ein Varchar-, nvarchar- oder varbinary-Datentyp , und der Index ist nicht das Ergebnis einer PRIMARY KEY Einschränkung. Sehen Sie sich hierzu das Beispiel in dem kurzen Abschnitt mit dem Titel Ändern einer Spaltendefinition an.

Sperren und ALTER TABLE

Änderungen, die Sie angeben, ALTER TABLE werden sofort wirksam. Wenn die Änderungen Änderungen an den Zeilen in der Tabelle erfordern, ALTER TABLE werden die Zeilen aktualisiert. ALTER TABLE erwirbt eine Schemaänderungssperre (Sch-M) in der Tabelle, um sicherzustellen, dass keine anderen Verbindungen auch auf die Metadaten für die Tabelle während der Änderung verweisen, mit Ausnahme von Onlineindexvorgängen, die eine kurze Sch-M Sperre am Ende 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 alle Zeilen in großen Tabellen auswirken, z. B. das Ablegen einer Spalte oder, bei einigen Editionen von SQL Server, das Hinzufügen einer NOT NULL Spalte mit einem Standardwert kann eine lange Zeit in Anspruch nehmen, um viele Protokolldatensätze zu generieren. Führen Sie diese ALTER TABLE Anweisungen mit der gleichen Sorgfalt wie jede oder jede INSERTAnweisung aus, UPDATEDELETE die sich auf viele Zeilen auswirkt.

Permissions

Erfordert ALTER die Berechtigung für die Tabelle.

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

Wenn Sie Spalten in der ALTER TABLE Anweisung als benutzerdefinierten Typ oder Aliasdatentyp (Common Language Runtime, CLR) definieren, REFERENCES ist die Berechtigung für den Typ erforderlich.

Zum Hinzufügen oder Ändern einer Spalte, die die Zeilen der Tabelle aktualisiert, ist die Berechtigung für die Tabelle erforderlich UPDATE . Fügen Sie beispielsweise eine NOT NULL Spalte mit einem Standardwert hinzu, oder fügen Sie eine Identitätsspalte hinzu, wenn die Tabelle nicht leer ist.

Examples

Die Codebeispiele in diesem Artikel verwenden die AdventureWorks2025 oder AdventureWorksDW2025 Beispieldatenbank, die Sie von der Startseite Microsoft SQL Server Samples and Community Projects herunterladen können.

Spalten und Einschränkungen hinzufügen

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 Nullwerte zulässt und keine Definition enthält DEFAULT . In der neuen Spalte verfügt NULLjede Zeile über .

CREATE TABLE dbo.doc_exa (column_a INT);
GO

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

B. Spalte mit einer Einschränkung hinzufügen

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

EXECUTE sp_help doc_exc;
GO

DROP TABLE dbo.doc_exc;
GO

C. Nicht überprüfte CHECK-Einschränkung zu einer vorhandenen Spalte hinzufügen

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. Daher wird das Beispiel verwendet WITH NOCHECK , um zu verhindern, dass die Einschränkung für vorhandene Zeilen überprüft wird und die Einschränkung hinzugefügt werden kann.

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

EXECUTE sp_help doc_exd;
GO

DROP TABLE dbo.doc_exd;
GO

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

Im folgenden Beispiel wird eine Tabelle mit zwei Spalten erstellt und ein Wert in die erste Spalte eingefügt, während die andere Spalte verbleibt NULL. Anschließend wird der zweiten Spalte eine DEFAULT Einschränkung hinzugefügt. Um zu überprüfen, ob der Standardwert angewendet wird, fügt das Beispiel einen anderen Wert in die erste Spalte ein und fragt die Tabelle ab.

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. Mehrere Spalten mit Einschränkungen hinzufügen

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 a PRIMARY KEY identity column.
    ADD 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 FOREIGN KEY 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

EXECUTE sp_help doc_exe;
GO

DROP TABLE dbo.doc_exe;
GO

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

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. Wenn Sie nicht verwenden WITH VALUES, weist jede Zeile den Wert NULL in der neuen Spalte auf.

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
        CONSTRAINT AddDateDflt DEFAULT GETDATE() WITH VALUES NULL;
GO

DROP TABLE dbo.doc_exf;
GO

G. PRIMARY KEY-Einschränkung mit Index- oder Datenkomprimierungsoptionen erstellen

Im folgenden Beispiel wird die PRIMARY KEY Einschränkung PK_TransactionHistoryArchive_TransactionID erstellt und die Optionen FILLFACTOR, ONLINEund .PAD_INDEX Der resultierende gruppierte Index hat denselben Namen wie die Einschränkung.

Gilt für: SQL Server und Azure SQL-Datenbank.

USE AdventureWorks2022;
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

In diesem ähnlichen Beispiel werden Seitenkomprimierung und der gruppierte Primärschlüssel angewandt.

USE AdventureWorks2022;
GO

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
        PRIMARY KEY CLUSTERED (TransactionID) WITH (DATA_COMPRESSION = PAGE);
GO

H. Sparsespalte hinzufügen

In den folgenden Beispielen wird gezeigt, wie Sparsespalten 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 Sparsespalte C5 hinzuzufügen, führen Sie die folgende Anweisung aus.

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

Um die Nicht-Sparsespalte C4 in eine Sparsespalte umzuwandeln, führen Sie die folgende Anweisung aus.

ALTER TABLE T1
    ALTER COLUMN C4 ADD SPARSE;
GO

Um die C4-Sparsespalte in eine Nicht-Sparsespalte umzuwandeln, führen Sie die folgende Anweisung aus.

ALTER TABLE T1
    ALTER COLUMN C4 DROP SPARSE;
GO

I. Spaltensatz hinzufügen

Die folgenden Beispiele zeigen, wie Sie einer Tabelle T2eine Spalte hinzufügen. Sie können einer Tabelle, die bereits sparse Spalten enthält, keinen Spaltensatz hinzufügen. Mit dem folgenden Code wird eine Tabelle T2erstellt.

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

J. Verschlüsselte Spalte hinzufügen

Die folgende Anweisung fügt eine verschlüsselte Spalte mit dem Namen PromotionCode hinzu.

ALTER TABLE Customers
    ADD PromotionCode NVARCHAR (100)
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = MyCEK,
            ENCRYPTION_TYPE = RANDOMIZED,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        );

K. Primärschlüssel mit einem fortsetzbaren Vorgang hinzufügen

Fortsetzbarer ALTER TABLE-Vorgang zum Hinzufügen eines Primärschlüssels, gruppiert für Spalte (a) mit MAX_DURATION von 240 Minuten.

ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

Spalten und Einschränkungen löschen

Die Beispiele in diesem Abschnitt veranschaulichen, wie Spalten und Einschränkungen abzulegen sind.

A. Eine oder mehrere Spalten löschen

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. Einschränkungen und Spalten löschen

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 my_constraint, my_pk_constraint, COLUMN column_b;
GO

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

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

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

D. Hinzufügen und Ablegen einer FREMDSCHLÜSSELeinschränkung

Im folgenden Beispiel wird die Tabelle ContactBackuperstellt und anschließend die Tabelle geändert. Zunächst wird eine FOREIGN KEY Einschränkung hinzugefügt, die auf die Tabelle Person.Personverweist. Anschließend wird die FOREIGN KEY Einschränkung fallen.

CREATE TABLE Person.ContactBackup (ContactID INT);
GO

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

ALTER TABLE Person.ContactBackup
    DROP CONSTRAINT FK_ContactBackup_Contact;
GO

DROP TABLE Person.ContactBackup;

Spaltendefinition ändern

A. Datentyp einer Spalte ändern

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. Größe einer Spalte ändern

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, können Sie die Spaltengröße nur vergrößern. Beachten Sie auch, dass col_a in einem eindeutigen Index definiert ist. Sie können die Größe col_a trotzdem erhöhen, da der Datentyp eine Varchar ist und der Index nicht das Ergebnis einer PRIMARY KEY Einschränkung ist.

-- 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 der Spaltensortierung

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

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

Ändern Sie als Nächstes die Sortierung der Spalte C2 in Latin1_General_BIN. Sie müssen den Datentyp angeben, auch wenn er nicht geändert wird.

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

D. Spalte verschlüsseln

Das folgende Beispiel zeigt, wie Sie eine Spalte mithilfe von Always Encrypted mit sicheren Enklaven verschlüsseln.

Zuerst erstellen Sie eine Tabelle ohne verschlüsselte Spalten.

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

Als Nächstes verschlüsseln Sie die Spalte C2 mit einem Spaltenverschlüsselungsschlüssel, benannten CEK1und zufälligen Verschlüsselung. Für die erfolgreiche Ausführung der Anweisung ist Folgendes erforderlich:

• Der Spaltenverschlüsselungsschlüssel muss Enclave-fähig sein. Diese Anforderung bedeutet, dass sie mithilfe eines Spaltenschlüssels master (CMK) verschlüsselt werden muss, der Enklavenberechnungen zulässt.
• Das Ziel SQL Server Instanz muss Always Encrypted mit sicheren Enklaven unterstützen.
• Die Anweisung muss über eine Verbindung übermittelt werden, die für Always Encrypted mit Secure Enclaves eingerichtet ist, und unter Verwendung eines unterstützten Clienttreibers.
• Die aufrufende Anwendung muss Zugriff auf das CMK haben und schützen CEK1.

ALTER TABLE T3 ALTER COLUMN C2 VARCHAR (50)  ENCRYPTED WITH (
     COLUMN_ENCRYPTION_KEY = [CEK1],
     ENCRYPTION_TYPE = RANDOMIZED,
     ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NULL;
GO

Tabellendefinition ändern

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

A. Tabelle ändern, um die Komprimierung zu ändern

Im folgenden Beispiel wird die Komprimierung einer nicht partitionierten Tabelle geändert. Der Heap- oder Clusterindex 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.

Gilt für: SQL Server.

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.

Gilt für: SQL Server.

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

Weitere Datenkomprimierungsbeispiele finden Sie unter "Datenkomprimierung".

B. Columnstore-Tabelle ändern, um die Archivierungskomprimierung zu ändern

Im folgenden Beispiel wird eine columnstore-Tabellenpartition weiter komprimiert, indem ein zusätzlicher Komprimierungsalgorithmus angewendet wird. Durch diese Komprimierung wird zwar die Tabellengröße reduziert, aber die zum Speichern und Abrufen benötigte Zeit wird verlängert. Diese Komprimierung ist nützlich für die Archivierung oder für Situationen, die weniger Speicherplatz erfordern und mehr Zeit für Speicher und Abruf leisten können.

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank.

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

Im folgenden Beispiel wird eine Spaltenspeichertabellenpartition dekomprimiert, die mit COLUMNSTORE_ARCHIVE der Option komprimiert wurde. Nachdem die Daten wiederhergestellt wurden, sind sie weiterhin mit der Columnstore-Komprimierung komprimiert, die für alle Columnstore-Tabellen verwendet wird.

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank.

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

C. Partitionen zwischen Tabellen wechseln

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

D. Sperrenausweitung in partitionierten Tabellen zulassen

Im folgenden Beispiel wird die Sperrenausweitung auf die Partitionsebene einer partitionierten Tabelle ermöglicht. Wenn die Tabelle nicht partitioniert ist, wird die Sperreskalation auf der TABLE Ebene festgelegt.

Gilt für: SQL Server und Azure SQL-Datenbank.

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

E. Änderungsnachverfolgung in einer Tabelle konfigurieren

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

Gilt für: SQL Server und Azure SQL-Datenbank.

USE AdventureWorks2022;

ALTER TABLE Person.Person ENABLE CHANGE_TRACKING;

Im folgenden Beispiel werden die Änderungsnachverfolgung sowie die Verfolgung von Spalten aktiviert, die während einer Änderung aktualisiert werden.

Gilt für: SQL Server.

USE AdventureWorks2022;
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.

Gilt für: SQL Server und Azure SQL-Datenbank.

USE AdventureWorks2022;
GO

ALTER TABLE Person.Person DISABLE CHANGE_TRACKING;

Einschränkungen und Trigger deaktivieren und aktivieren

A. Einschränkung deaktivieren und erneut aktivieren

Im folgenden Beispiel wird eine Einschränkung deaktiviert, die die in den Daten akzeptierten Gehälter begrenzt. ALTER TABLE Verwenden Sie diese OptionNOCHECK CONSTRAINT, um die Einschränkung zu deaktivieren und eine Einfügung zuzulassen, die in der Regel gegen die Einschränkung verstößt. Wird verwendet CHECK CONSTRAINT , um die Einschränkung erneut zu aktivieren.

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 fails.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4, 'Eric James', 110000);

B. Trigger deaktivieren und erneut aktivieren

Im folgenden Beispiel wird die DISABLE TRIGGER Option ALTER TABLE zum Deaktivieren des Triggers verwendet und ein Einfügen zugelassen, das in der Regel gegen den Trigger verstößt. Wird verwendet ENABLE TRIGGER , um den Trigger erneut zu aktivieren.

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;
           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

Onlinevorgänge

A. Onlineindexneuerstellung mit LOW_PRIORITY_WAIT-Optionen

Im folgenden Beispiel wird gezeigt, wie eine Onlineindexneuerstellung mithilfe der LOW_PRIORITY_WAIT-Optionen ausgeführt wird.

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank.

ALTER TABLE T1 REBUILD WITH (
    PAD_INDEX = ON,
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)
    )
);

B. Onlinespaltenänderung

Das folgende Beispiel zeigt, wie Sie einen Änderungsspaltenvorgang mit der ONLINE Option ausführen.

Gilt für: SQL Server 2016 (13.x) und höhere Versionen und Azure SQL-Datenbank.

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) WITH (ONLINE = ON);
GO

EXECUTE sp_help doc_exy;
DROP TABLE dbo.doc_exy;
GO

Systemversionsverwaltung

Die folgenden vier Beispiele helfen Ihnen, sich mit der Syntax für die Verwendung der Systemversionsverwaltung vertraut zu machen. Weitere Hilfe finden Sie unter "Erste Schritte mit systemversionierten zeitlichen Tabellen".

Gilt für: SQL Server 2016 (13.x) und höhere Versionen und Azure SQL-Datenbank.

A. Hinzufügen der Systemversionsverwaltung zu vorhandenen Tabellen

Im Folgenden wird veranschaulicht, wie Sie die Systemversionierung einer vorhandenen Tabelle hinzufügen und eine neue Verlaufstabelle erstellen. In diesem Beispiel wird davon ausgegangen, dass eine Tabelle mit dem Namen InsurancePolicy mit einem festgelegten Primärschlüssel bereits vorhanden ist. Dieses Beispiel füllt die neu erstellten Zeitraumspalten für die Systemversionsverwaltung mit Standardwerten für Start- und Endzeitpunkt auf, da diese Werte nicht NULL sein dürfen. In diesem Beispiel wird die HIDDEN Klausel verwendet, um sicherzustellen, dass vorhandene Anwendungen mit der aktuellen Tabelle interagieren. Außerdem wird HISTORY_RETENTION_PERIOD dies nur in SQL-Datenbank verwendet.

--Alter non-temporal table to define periods for system versioning
ALTER TABLE InsurancePolicy
    ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
            DEFAULT SYSUTCDATETIME() NOT NULL,
        ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
            DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59.99999999') NOT NULL,
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

--Enable system versioning with 1 year retention for historical data
ALTER TABLE InsurancePolicy SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_RETENTION_PERIOD=1 YEAR
    )
);

B. Migrieren einer vorhandenen Projektmappe zur Systemversionsverwaltung

In folgendem Beispiel wird gezeigt, wie Sie die Migration zur Systemversionierung von einer Projektmappe durchführen, die Trigger verwendet, um die Unterstützung für temporale Tabellen nachzuahmen. Im Beispiel wird davon ausgegangen, dass es eine vorhandene Lösung gibt, die eine ProjectTask Tabelle und eine ProjectTaskHistory Tabelle für die vorhandene Lösung verwendet, die die Changed Date Punkte und Revised Date Spalten verwendet, dass diese Punktspalten nicht den Datentyp "datetime2 " verwenden und dass die ProjectTask Tabelle einen Primärschlüssel definiert hat.

-- Drop existing trigger
DROP TRIGGER ProjectTask_HistoryTrigger;

-- Adjust the schema for current and history table
-- Change data types for existing period columns
ALTER TABLE ProjectTask ALTER COLUMN [Changed Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTask ALTER COLUMN [Revised Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Changed Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Revised Date] DATETIME2 NOT NULL;

-- Add SYSTEM_TIME period and set system versioning with linking two existing tables
-- (a certain set of data checks happen in the background)
ALTER TABLE ProjectTask
    ADD PERIOD FOR SYSTEM_TIME ([Changed Date], [Revised Date]);

ALTER TABLE ProjectTask SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE=dbo.ProjectTaskHistory, DATA_CONSISTENCY_CHECK=ON
    )
);

C. Systemversionsverwaltung zum Ändern des Tabellenschemas deaktivieren und erneut aktivieren

In diesem Beispiel wird gezeigt, wie Sie die Systemversionierung in der Department-Tabelle deaktivieren, eine Spalte hinzufügen und die Systemversionierung erneut aktivieren können. Das Deaktivieren der Systemversionsverwaltung ist nötig, um das Tabellenschema ändern zu können. Führen Sie diese Schritte während einer Transaktion durch, um Aktualisierungen beider Tabellen zu verhindern, während das Tabellenschema aktualisiert wird. Dadurch kann der Datenbankadministrator die Datenkonsistenzprüfung überspringen, wenn die Systemversionsverwaltung erneut aktiviert wird. So wird die Leistung verbessert. Für Aufgaben wie das Erstellen von Statistiken, das Wechseln von Partitionen oder das Anwenden der Komprimierung auf eine oder beide Tabellen ist das Deaktivieren der Systemversionsverwaltung nicht erforderlich.

BEGIN TRAN
/* Takes schema lock on both tables */
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF) ;
/* expand table schema for temporal table */
ALTER TABLE Department
     ADD Col5 int NOT NULL DEFAULT 0 ;
/* Expand table schema for history table */
ALTER TABLE DepartmentHistory
    ADD Col5 int NOT NULL DEFAULT 0 ;
/* Re-establish versioning again*/
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DepartmentHistory,
                                 DATA_CONSISTENCY_CHECK = OFF)) ;
COMMIT

D. Systemversionsverwaltung entfernen

In diesem Beispiel wird gezeigt, wie Sie die Systemversionierung aus der Tabelle „Department“ entfernen und die DepartmentHistory-Tabelle vollständig löschen. Sie können auch die Zeitraumspalten löschen, die vom System zum Erfassen von Systemversionierungsinformationen verwendet werden. Die Tabellen Department und DepartmentHistory können nicht gelöscht werden, solange die Systemversionsverwaltung aktiviert ist.

ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE Department
    DROP PERIOD FOR SYSTEM_TIME;

DROP TABLE DepartmentHistory;

Beispiele: Azure Synapse Analytics und Analytics Platform System (PDW)

In allen folgenden Beispielen A bis C wird die FactResellerSales-Tabelle der AdventureWorksPDW2022-Datenbank verwendet.

A. Bestimmen, ob eine Tabelle partitioniert ist

Die folgende Abfrage gibt mindestens eine Zeile zurück, wenn die FactResellerSales -Tabelle partitioniert ist. Wenn die Tabelle nicht partitioniert ist, gibt die Abfrage keine Zeilen zurück.

SELECT *
FROM sys.partitions AS p
     INNER JOIN sys.tables AS t
         ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
      AND t.name = 'FactResellerSales';

B. Grenzwerte für eine partitionierte Tabelle bestimmen

Die folgende Abfrage gibt die Begrenzungswerte für jede Partition in der FactResellerSales -Tabelle zurück.

SELECT t.name AS TableName,
       i.name AS IndexName,
       p.partition_number,
       p.partition_id,
       i.data_space_id,
       f.function_id,
       f.type_desc,
       r.boundary_id,
       r.value AS BoundaryValue
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.partitions AS p
         ON i.object_id = p.object_id
        AND i.index_id = p.index_id
     INNER JOIN sys.partition_schemes AS s
         ON i.data_space_id = s.data_space_id
     INNER JOIN sys.partition_functions AS f
         ON s.function_id = f.function_id
     LEFT OUTER JOIN sys.partition_range_values AS r
         ON f.function_id = r.function_id
        AND r.boundary_id = p.partition_number
WHERE t.name = 'FactResellerSales'
      AND i.type <= 1
ORDER BY p.partition_number;

C. Partitionsspalte für eine partitionierte Tabelle bestimmen

Die folgende Abfrage gibt den Namen der Partitionierungsspalte für die FactResellerSales Tabelle zurück.

SELECT t.object_id AS Object_ID,
       t.name AS TableName,
       ic.column_id AS PartitioningColumnID,
       c.name AS PartitioningColumnName
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.columns AS c
         ON t.object_id = c.object_id
     INNER JOIN sys.partition_schemes AS ps
         ON ps.data_space_id = i.data_space_id
     INNER JOIN sys.index_columns AS ic
         ON ic.object_id = i.object_id
        AND ic.index_id = i.index_id
        AND ic.partition_ordinal > 0
WHERE t.name = 'FactResellerSales'
      AND i.type <= 1
      AND c.column_id = ic.column_id;

D. Zwei Partitionen zusammenführen

In folgendem Beispiel werden zwei Partitionen in einer Tabelle zusammengeführt.

Die Customer-Tabelle verfügt über die folgende Struktur:

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 10, 25, 50, 100)
    )
);

Der folgende Befehl kombiniert die Partitionsgrenzen 10 und 25.

ALTER TABLE Customer MERGE RANGE (10);

Die neue DLL für die Tabelle ist:

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 25, 50, 100)
    )
);

E. Partition teilen

In folgendem Beispiel wird eine Partition in einer Tabelle geteilt.

Die Customer-Tabelle verfügt über die folgende Struktur:

DROP TABLE Customer;

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 10, 25, 50, 100)
    )
);

Der folgende Befehl erstellt eine neue Partitionsgrenze mit dem Wert 75, in der Mitte zwischen 50 und 100.

ALTER TABLE Customer SPLIT RANGE (75);

Die neue DLL für die Tabelle ist:

CREATE TABLE Customer (
   id INT NOT NULL,
   lastName VARCHAR(20),
   orderCount INT,
   orderDate DATE)
   WITH DISTRIBUTION = HASH(id),
   PARTITION ( orderCount (RANGE LEFT
      FOR VALUES (1, 5, 10, 25, 50, 75, 100))) ;

F. SWITCH zum Verschieben einer Partition in eine Verlaufstabelle verwenden

In folgendem Beispiel werden die Daten in einer Partition der Orders-Tabelle in eine Partition der OrdersHistory-Tabelle verschoben.

Die Orders-Tabelle verfügt über die folgende Struktur:

CREATE TABLE Orders
(
    id INT,
    city VARCHAR (25),
    lastUpdateDate DATE,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderDate RANGE RIGHT
        FOR VALUES ('2004-01-01', '2005-01-01', '2006-01-01', '2007-01-01')
    )
);

In diesem Beispiel verfügt die Orders-Tabelle über die folgenden Partitionen. Jede Partition enthält Daten.

• Partition 1 (hat Daten): OrderDate < '2004-01-01'
• Partition 2 (hat Daten): '2004-01-01' <= OrderDate < '2005-01-01'
• Partition 3 (hat Daten): '2005-01-01' <= OrderDate< '2006-01-01'
• Partition 4 (hat Daten): '2006-01-01'<= OrderDate < '2007-01-01'
• Partition 5 (hat Daten): '2007-01-01' <= OrderDate

Die OrdersHistory-Tabelle verfügt über die folgende DLL, die die gleichen Spalten und Spaltennamen wie die Orders-Tabelle aufweist. Diese werden in der id-Spalte mittels Hash verteilt.

CREATE TABLE OrdersHistory
(
    id INT,
    city VARCHAR (25),
    lastUpdateDate DATE,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderDate RANGE RIGHT
        FOR VALUES ('2004-01-01')
    )
);

Obwohl die Spalten und Spaltennamen identisch sein müssen, müssen die Partitionsbegrenzungen nicht übereinstimmen. In diesem Beispiel verfügt die OrdersHistory-Tabelle über zwei Partitionen, die beide leer sind:

• Partition 1 (keine Daten): OrderDate < '2004-01-01'
• Partition 2 (leer): '2004-01-01' <= OrderDate

Für die letzten beiden Tabellen verschiebt der Befehl alle Zeilen mit OrderDate < '2004-01-01' von der Orders-Tabelle in die OrdersHistory-Tabelle.

ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;

Dadurch ist die erste Partition in Orders leer, während die erste Partition in OrdersHistory Daten enthält. Die Tabellen sehen nun wie folgt aus:

Orders-Tabelle

• Partition 1 (leer): OrderDate < '2004-01-01'
• Partition 2 (hat Daten): '2004-01-01' <= OrderDate < '2005-01-01'
• Partition 3 (hat Daten): '2005-01-01' <= OrderDate< '2006-01-01'
• Partition 4 (hat Daten): '2006-01-01'<= OrderDate < '2007-01-01'
• Partition 5 (hat Daten): '2007-01-01' <= OrderDate

OrdersHistory-Tabelle

• Partition 1 (hat Daten): OrderDate < '2004-01-01'
• Partition 2 (leer): '2004-01-01' <= OrderDate

Um die Orders Tabelle zu bereinigen, können Sie die leere Partition entfernen, indem Sie Partitionen 1 zusammenführen und 2 wie folgt:

ALTER TABLE Orders MERGE RANGE ('2004-01-01');

Nach dem Merge verfügt die Orders-Tabelle über die folgenden Partitionen:

Orders-Tabelle

• Partition 1 (hat Daten): OrderDate < '2005-01-01'
• Partition 2 (hat Daten): '2005-01-01' <= OrderDate< '2006-01-01'
• Partition 3 (hat Daten): '2006-01-01'<= OrderDate < '2007-01-01'
• Partition 4 (hat Daten): '2007-01-01' <= OrderDate

Angenommen, es vergeht ein weiteres Jahr, und Sie möchten das Jahr 2005 archivieren. Sie können dem Jahr 2005 eine leere Partition in der OrdersHistory-Tabelle zuweisen, indem Sie die leere Partition folgendermaßen teilen:

ALTER TABLE OrdersHistory SPLIT RANGE ('2005-01-01');

Nach dem Teilen verfügt die OrdersHistory-Tabelle über die folgenden Partitionen:

OrdersHistory-Tabelle

• Partition 1 (hat Daten): OrderDate < '2004-01-01'
• Partition 2 (leer): '2004-01-01' < '2005-01-01'
• Partition 3 (leer): '2005-01-01' <= OrderDate

Verwandte Inhalte

• sys.tables
• sp_rename
• sp_help
• EVENTDATA (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• DROP TABLE (Transact-SQL)
• ALTER TABLE column_constraint (Transact-SQL)
• ALTER TABLE column_definition (Transact-SQL)
• ALTER TABLE computed_column_definition (Transact-SQL)
• ALTER TABLE index_option (Transact-SQL)
• ALTER TABLE table_constraint (Transact-SQL)