ALTER TABLE (Transact-SQL)

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalysplattformssystem (PDW)Lager i Microsoft FabricSQL-databas i Förhandsversion av Microsoft Fabric

Ändrar en tabelldefinition genom att ändra, lägga till eller släppa kolumner och begränsningar. ALTER TABLE även omtilldela och återskapa partitioner, eller inaktiverar och aktiverar begränsningar och utlösare.

Syntaxen för ALTER TABLE är annorlunda för diskbaserade tabeller och minnesoptimerade tabeller. Använd följande länkar för att ta dig direkt till rätt syntaxblock för dina tabelltyper och till lämpliga syntaxexempel:

Diskbaserade tabeller:

• Syntax
• Examples

Minnesoptimerade tabeller:

• Syntax
• Ändra Memory-Optimized tabeller

Mer information om syntaxkonventionerna finns i Transact-SQL syntaxkonventioner.

Syntax för diskbaserade tabeller

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

Mer information finns i:

• ALTER TABLE column_constraint
• ÄNDRA TABELL column_definition
• ÄNDRA TABELL computed_column_definition
• ÄNDRA TABELL index_option
• ÄNDRA TABELL table_constraint

Syntax för minnesoptimerade tabeller

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

Syntax för Azure Synapse Analytics och Parallel Data Warehouse

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

Syntax för lager i infrastrukturresurser

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> ::=
datetime2 ( n )
| date
| time ( n )
| float [ ( n ) ]
| real [ ( n ) ]
| decimal [ ( precision [ , scale ] ) ]
| numeric [ ( precision [ , scale ] ) ]
| bigint
| int
| smallint
| bit
| varchar [ ( n ) ]
| char [ ( n ) ]
| varbinary [ ( n ) ]
| uniqueidentifier

<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

database_name

Namnet på databasen där tabellen skapades.

schema_name

Namnet på schemat som tabellen tillhör.

table_name

Namnet på tabellen som ska ändras. Om tabellen inte finns i den aktuella databasen eller finns i schemat som ägs av den aktuella användaren måste du uttryckligen ange databasen och schemat.

ÄNDRA KOLUMN

Anger att den namngivna kolumnen ska ändras eller ändras.

Den ändrade kolumnen kan inte vara:

• En kolumn med en tidsstämpel datatyp.

• För ROWGUIDCOL tabellen.

• En beräknad kolumn eller används i en beräknad kolumn.

• Används i statistik som genereras av -instruktionen CREATE STATISTICS . Användarna måste köra DROP STATISTICS för att ta bort statistiken innan ALTER COLUMN de kan lyckas. Kör den här frågan för att hämta alla statistik- och statistikkolumner som skapats av användaren för en tabell.

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

  Note

  Statistik som genereras automatiskt av frågeoptimeraren tas bort automatiskt av ALTER COLUMN.

• Används i en PRIMARY KEY eller [FOREIGN KEY] REFERENCES -begränsning.

• Används i en CHECK eller UNIQUE -begränsning. Men det är tillåtet att ändra längden på en kolumn med variabel längd som används i en CHECK eller UNIQUE -begränsning.

• Associerad med en standarddefinition. En kolumns längd, precision eller skala kan dock ändras om datatypen inte ändras.

Datatypen för text, ntextoch bild kolumner kan bara ändras på följande sätt:

• text till varchar(max), nvarchar(max)eller xml-
• ntext till varchar(max), nvarchar(max)eller xml-
• bild till varbinary(max)

Vissa ändringar av datatyper kan orsaka en ändring i data. Om du till exempel ändrar en nchar- eller nvarchar kolumn, till tecken eller varchar, kan det leda till konvertering av utökade tecken. Mer information finns i CAST och CONVERT. Om du minskar precisionen eller skalan för en kolumn kan det orsaka datatrunkering.

Om kolumnen som ändras krypteras med ENCRYPTED WITHanvänds always encrypted (utan säkra enklaver) kan du ändra datatypen till en kompatibel datatyp (till exempel INT till BIGINT), men du kan inte ändra några krypteringsinställningar.

När du använder Always Encrypted med säkra enklaver kan du ändra alla krypteringsinställningar om kolumnkrypteringsnyckeln skyddar kolumnen (och den nya kolumnkrypteringsnyckeln, om du ändrar nyckeln) stöder enklaverberäkningar (krypterade med enklaveraktiverade kolumnhuvudnycklar). Mer information finns i Always Encrypted med säkra enklaver.

När du ändrar en kolumn håller databasmotorn reda på varje ändring genom att lägga till en rad i en systemtabell och markera föregående kolumnändring som en borttagen kolumn. I sällsynta fall när du ändrar en kolumn för många gånger kan databasmotorn nå poststorleksgränsen. Om detta händer får du fel 511 eller 1708. Undvik dessa fel genom att antingen återskapa det klustrade indexet i tabellen regelbundet eller minska antalet kolumnändringar.

column_name

Namnet på kolumnen som ska ändras, läggas till eller tas bort. Maximalt column_name är 128 tecken. För nya kolumner kan du utelämna column_name för kolumner som skapats med en tidsstämpel datatyp. Namnet tidsstämpel används om du inte anger column_name för en tidsstämpel datatypkolumn.

[ type_schema_name. ] type_name

Den nya datatypen för den ändrade kolumnen eller datatypen för den tillagda kolumnen. Du kan inte ange type_name för befintliga kolumner i partitionerade tabeller. type_name kan vara någon av följande typer:

• En SQL Server-systemdatatyp.
• En aliasdatatyp baserad på en SQL Server-systemdatatyp. Du skapar aliasdatatyper med -instruktionen CREATE TYPE innan de kan användas i en tabelldefinition.
• En .NET Framework-användardefinierad typ och det schema som det tillhör. Du skapar användardefinierade typer med -instruktionen CREATE TYPE innan de kan användas i en tabelldefinition.

Följande är kriterier för type_name av en ändrad kolumn:

• Den tidigare datatypen måste implicit konverteras till den nya datatypen.
• type_name kan inte tidsstämpel.
• ANSI_NULL standardvärden är alltid aktiverade för ALTER COLUMN. Om det inte anges kan kolumnen vara null.
• ANSI_PADDING utfyllnad är alltid ON för ALTER COLUMN.
• Om den ändrade kolumnen är en identitetskolumn måste new_data_type vara en datatyp som stöder identitetsegenskapen.
• Den aktuella inställningen för SET ARITHABORT ignoreras. ALTER TABLE fungerar som om ARITHABORT är inställt på ON.

precision

Precisionen för den angivna datatypen. Mer information om giltiga precisionsvärden finns i Precision, skala och längd.

scale

Skalan för den angivna datatypen. Mer information om giltiga skalningsvärden finns i Precision, skala och längd.

max

Gäller endast för datatyperna varchar, nvarcharoch varbinary för lagring av 2^31–1 byte med tecken, binära data och Unicode-data.

xml_schema_collection

Gäller för: SQL Server och Azure SQL Database.

Gäller endast för XML- datatyp för att associera ett XML-schema med typen . Innan du skriver en xml- kolumn i en schemasamling skapar du först schemasamlingen i databasen med hjälp av CREATE XML SCHEMA COLLECTION.

SORTERA <COLLATION_NAME>

Anger den nya sorteringen för den ändrade kolumnen. Om den inte anges tilldelas kolumnen standardsortering av databasen. Sorteringsnamnet kan vara antingen ett Windows-sorteringsnamn eller ett SQL-sorteringsnamn. En lista och mer information finns i Windows-sorteringsnamn och SQL Server-sorteringsnamn.

Satsen COLLATE ändrar endast sorteringarna för kolumner av datatyperna char, varchar, nchar och nvarchar . Om du vill ändra sortering av en användardefinierad kolumn för aliasdatatyp använder du separata ALTER TABLE instruktioner för att ändra kolumnen till en SQL Server-systemdatatyp. Ändra sedan dess sortering och ändra kolumnen tillbaka till en aliasdatatyp.

ALTER COLUMN kan inte ha någon sorteringsändring om det finns ett eller flera av följande villkor:

• En CHECK begränsning, FOREIGN KEY begränsning eller beräknade kolumner refererar till den ändrade kolumnen.
• Index, statistik eller fulltextindex skapas i kolumnen. Statistik som skapas automatiskt på den ändrade kolumnen tas bort om kolumnsortering ändras.
• En schemabunden vy eller funktion refererar till kolumnen.

Mer information om sortering som stöds finns i COLLATE.

NULL | INTE NULL

Anger om kolumnen kan acceptera null-värden. Kolumner som inte tillåter null-värden läggs bara till med ALTER TABLE om de har ett angivet standardvärde eller om tabellen är tom. Du kan bara ange NOT NULL för beräknade kolumner om du också har angett PERSISTED. Om den nya kolumnen tillåter null-värden och du inte anger något standardvärde innehåller den nya kolumnen ett null-värde för varje rad i tabellen. Om den nya kolumnen tillåter null-värden och du lägger till en standarddefinition med den nya kolumnen kan du använda WITH VALUES för att lagra standardvärdet i den nya kolumnen för varje befintlig rad i tabellen.

Om den nya kolumnen inte tillåter null-värden och tabellen inte är tom måste du lägga till en DEFAULT definition med den nya kolumnen. Och den nya kolumnen läses automatiskt in med standardvärdet i de nya kolumnerna i varje befintlig rad.

Du kan ange NULL i ALTER COLUMN för att tvinga en NOT NULL kolumn att tillåta null-värden, förutom kolumner i PRIMARY KEY begränsningar. Du kan bara ange NOT NULL i ALTER COLUMN om kolumnen inte innehåller några null-värden. Null-värdena måste uppdateras till ett visst värde innan det tillåts ALTER COLUMNNOT NULL , till exempel:

UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL;

ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR (20) NOT NULL;

När du skapar eller ändrar en tabell med CREATE TABLE - eller ALTER TABLE -uttrycken påverkar databas- och sessionsinställningarna och åsidosätter eventuellt nulliteten för den datatyp som används i en kolumndefinition. Se till att du alltid uttryckligen definierar en kolumn som NULL eller NOT NULL för icke-inräknade kolumner.

Om du lägger till en kolumn med en användardefinierad datatyp måste du definiera kolumnen med samma nullbarhet som den användardefinierade datatypen. Och ange ett standardvärde för kolumnen. Mer information finns i CREATE TABLE.

[ {ADD | DROP} ROWGUIDCOL ]

Gäller för: SQL Server och Azure SQL Database.

Anger att egenskapen ROWGUIDCOL läggs till i eller tas bort från den angivna kolumnen. ROWGUIDCOL anger att kolumnen är en GUID-radkolumn. Du kan bara ange en unikidentifierarkolumn per tabell som ROWGUIDCOL kolumn. Och du kan bara tilldela ROWGUIDCOL egenskapen till en unikidentifierarkolumn . Du kan inte tilldela ROWGUIDCOL till en kolumn med en användardefinierad datatyp.

ROWGUIDCOL framtvingar inte unika värden som lagras i kolumnen och genererar inte automatiskt värden för nya rader som infogas i tabellen. Om du vill generera unika värden för varje kolumn använder du antingen funktionen NEWID() eller NEWSEQUENTIALID() i INSERT-instruktioner. Eller ange funktionen NEWID() eller NEWSEQUENTIALID() som standard för kolumnen.

[ {ADD | DROP} BESTÄNDIGA ]

Anger att egenskapen PERSISTED läggs till i eller tas bort från den angivna kolumnen. Kolumnen måste vara en beräknad kolumn som definieras med ett deterministiskt uttryck. För kolumner som anges som PERSISTEDlagrar databasmotorn fysiskt de beräknade värdena i tabellen och uppdaterar värdena när andra kolumner som den beräknade kolumnen är beroende av uppdateras. Genom att markera en beräknad kolumn som PERSISTEDkan du skapa index för beräknade kolumner som definierats för uttryck som är deterministiska, men inte exakta. Mer information finns i Index för beräknade kolumner.

SET QUOTED_IDENTIFIER måste vara ON när du skapar eller ändrar index för beräknade kolumner eller indexerade vyer. Mer information finns i SET QUOTED_IDENTIFIER.

Alla beräknade kolumner som används som en partitioneringskolumn i en partitionerad tabell måste uttryckligen markeras PERSISTED.

SLÄPP INTE FÖR REPLIKERING

Gäller för: SQL Server och Azure SQL Database.

Anger att värden ökas i identitetskolumner när replikeringsagenter utför infogningsåtgärder. Du kan bara ange den här satsen om column_name är en identitetskolumn.

SPARSE

Anger att kolumnen är en gles kolumn. Lagringen av glesa kolumner är optimerad för null-värden. Du kan inte ange glesa kolumner som NOT NULL. När du konverterar en kolumn från gles till icke-spars, eller från icke-sparse till gles, låser det här alternativet tabellen under hela kommandokörningen. Du kan behöva använda REBUILD -satsen för att frigöra utrymmesbesparingar. Ytterligare begränsningar och mer information om glesa kolumner finns i Använda glesa kolumner.

LÄGG TILL MASKERAD MED ( FUNKTION = "mask_function")

Gäller för: SQL Server 2016 (13.x) och senare versioner och Azure SQL Database.

Anger en dynamisk datamask. mask_function är namnet på maskeringsfunktionen med lämpliga parametrar. Tre funktioner är tillgängliga:

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

Kräver ALTER ANY MASK behörighet.

Om du vill släppa en mask använder du DROP MASKED. För funktionsparametrar, se Dynamisk datamaskning.

Lägg till och släpp en mask kräver ALTER ANY MASK behörighet.

WITH ( ONLINE = ON | OFF) <som gäller för att ändra en kolumn>

Gäller för: SQL Server 2016 (13.x) och senare versioner och Azure SQL Database.

Gör att många alter column-åtgärder kan utföras medan tabellen förblir tillgänglig. Standardvärdet är OFF. Du kan köra alter column online för kolumnändringar relaterade till datatyp, kolumnlängd eller precision, nullabilitet, gleshet och sortering.

Med kolumnen Online alter kan användare som skapats och autostatistik referera till den ändrade kolumnen under åtgärdens varaktighet ALTER COLUMN , vilket gör att frågor kan köras som vanligt. I slutet av åtgärden tas autostats som refererar till kolumnen bort och användarskapad statistik ogiltigförklaras. Användaren måste uppdatera användargenererad statistik manuellt när åtgärden har slutförts. Om kolumnen är en del av ett filteruttryck för statistik eller index kan du inte utföra en ändringskolumnåtgärd.

• Medan onlineåtgärden alter column körs blockeras alla DDL-åtgärder som kan vara beroende av den kolumnen (till exempel att skapa eller ändra index, vyer osv.) eller misslyckas med ett lämpligt fel. Det här beteendet garanterar att kolumnen online alter inte misslyckas på grund av beroenden som introducerades när åtgärden kördes.

• Att ändra en kolumn från NOT NULL till NULL stöds inte som en onlineåtgärd när den ändrade kolumnen refereras till av icke-grupperade index.

• Online ALTER stöds inte när kolumnen refereras till av en kontrollbegränsning och ALTER åtgärden begränsar kolumnens precision (numerisk eller datetime).

• Det WAIT_AT_LOW_PRIORITY alternativet kan inte användas med online alter kolumn.

• ALTER COLUMN ... ADD/DROP PERSISTED stöds inte för alter-kolumnen online.

• ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION påverkas inte av kolumnen ändra online.

• Online-ändringskolumnen stöder inte ändring av en tabell där ändringsspårning är aktiverat eller som är utgivare av sammanslagningsreplikering.

• Online-ändringskolumnen stöder inte ändring från eller till CLR-datatyper.

• Online alter column har inte stöd för att ändra till en XML-datatyp som har en annan schemasamling än den aktuella schemasamlingen.

• Online alter column minskar inte begränsningarna för när en kolumn kan ändras. Referenser efter index/statistik och så vidare kan leda till att ändring misslyckas.

• Online alter column har inte stöd för att ändra fler än en kolumn samtidigt.

• Kolumnen Online alter har ingen effekt i en systemversionsbaserad temporal tabell. ALTER kolumnen körs inte som online oavsett vilket värde som angavs för ONLINE alternativet.

Kolumnen Online alter har liknande krav, begränsningar och funktioner som återskapande av onlineindex, vilket omfattar:

• Återskapande av onlineindex stöds inte när tabellen innehåller äldre LOB- eller filströmskolumner eller när tabellen har ett kolumnlagringsindex. Samma begränsningar gäller för kolumnen online alter.
• En befintlig kolumn som ändras kräver dubbelt så mycket utrymme allokering, för den ursprungliga kolumnen och för den nyligen skapade dolda kolumnen.
• Låsningsstrategin under en ändringskolumn online-åtgärd följer samma låsmönster som används för onlineindexgenerering.

MED CHECK | MED NOCHECK

Anger om data i tabellen är eller inte verifieras mot en nyligen tillagd eller återaktiverad FOREIGN KEY eller CHECK begränsning. Om du inte anger WITH CHECK antas det för nya begränsningar och WITH NOCHECK antas för återaktiverade begränsningar.

Om du inte vill verifiera nya CHECK eller FOREIGN KEY begränsningar mot befintliga data använder du WITH NOCHECK. Vi rekommenderar inte att du gör detta, förutom i sällsynta fall. Den nya begränsningen utvärderas i alla senare datauppdateringar. Eventuella begränsningar som undertrycks av WITH NOCHECK när begränsningen läggs till kan leda till att framtida uppdateringar misslyckas om de uppdaterar rader med data som inte följer villkoret. Frågeoptimeraren överväger inte begränsningar som har definierats WITH NOCHECK. Sådana begränsningar ignoreras tills de återaktiveras med hjälp av ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL. Mer information finns i Inaktivera begränsningar för sekundärnyckel med INSERT- och UPDATE-instruktioner.

ALTER INDEX index_name

Anger att bucketantalet för index_name ska ändras eller ändras.

Syntaxen ALTER TABLE ... ADD/DROP/ALTER INDEX stöds endast för minnesoptimerade tabeller.

ADD

Anger att en eller flera kolumndefinitioner, beräknade kolumndefinitioner eller tabellbegränsningar läggs till. Eller så läggs de kolumner som systemet använder för systemversionshantering till. För minnesoptimerade tabeller kan du lägga till ett index.

PERIOD FÖR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )

Gäller för: SQL Server 2017 (14.x) och senare versioner och Azure SQL Database.

Anger namnen på de kolumner som systemet använder för att registrera den tidsperiod för vilken en post är giltig. Du kan ange befintliga kolumner eller skapa nya kolumner som en del av ADD PERIOD FOR SYSTEM_TIME argumentet. Konfigurera kolumnerna med datatypen datetime2 och definiera dem som NOT NULL. Om du definierar en periodkolumn som NULL, resulterar ett fel. Du kan definiera en column_constraint och/eller Ange standardvärden för kolumner för kolumnerna system_start_time och system_end_time. Se Exempel A i följande Systemversioner exempel som visar hur du använder ett standardvärde för kolumnen system_end_time.

Använd det här argumentet med SET SYSTEM_VERSIONING argumentet för att göra en befintlig tabell till en temporal tabell. Mer information finns i Temporala tabeller och Komma igång med temporala tabeller.

Från och med SQL Server 2017 (14.x) kan användarna markera en eller båda periodkolumnerna med HIDDEN flaggan för att implicit dölja dessa kolumner så att SELECT * FROM <table_name> de inte returnerar ett värde för kolumnerna. Periodkolumner är som standard inte dolda. För att kunna användas måste dolda kolumner uttryckligen inkluderas i alla frågor som direkt refererar till den temporala tabellen.

DROP

Anger att en eller flera kolumndefinitioner, beräknade kolumndefinitioner eller tabellbegränsningar tas bort eller att specifikationen för de kolumner som systemet använder för systemversioner tas bort.

BEGRÄNSNING constraint_name

Anger att constraint_name tas bort från tabellen. Flera begränsningar kan visas.

Du kan fastställa villkorets användardefinierade namn eller systemspecifika namn genom att fråga sys.check_constraint, sys.default_constraints, sys.key_constraintsoch sys.foreign_keys katalogvyer.

Det PRIMARY KEY går inte att ta bort en begränsning om det finns ett XML-index i tabellen.

INDEX index_name

Anger att index_name tas bort från tabellen.

Syntaxen ALTER TABLE ... ADD//DROPALTER INDEX stöds endast för minnesoptimerade tabeller.

KOLUMN column_name

Anger att constraint_name eller column_name tas bort från tabellen. Flera kolumner kan visas.

Det går inte att ta bort en kolumn när den är:

• Används i ett index, oavsett om det är som en nyckelkolumn eller som en INCLUDE
• Används i en CHECK, FOREIGN KEY, UNIQUEeller PRIMARY KEY -begränsning.
• Associerad med ett standardvärde som definieras med nyckelordet eller som är bundet DEFAULT till ett standardobjekt.
• Bunden till en regel.

PERIOD FÖR SYSTEM_TIME

Gäller för: SQL Server 2016 (13.x) och senare versioner och Azure SQL Database.

Släpper specifikationen för de kolumner som systemet använder för systemversionshantering.

MED <drop_clustered_constraint_option>

Anger att ett eller flera alternativ för att släppa klustrade villkor har angetts.

MAXDOP = max_degree_of_parallelism

Gäller för: SQL Server och Azure SQL Database.

Åsidosätter maximal grad av parallellitet konfigurationsalternativ endast under åtgärdens varaktighet. Mer information finns i Serverkonfiguration: maximal grad av parallellitet.

Använd alternativet MAXDOP för att begränsa antalet processorer som används vid parallell plankörning. Maximalt är 64 processorer.

max_degree_of_parallelism kan vara något av följande värden:

• 1

  Undertrycker parallell plangenerering.

• >1

  Begränsar det maximala antalet processorer som används i en parallell indexåtgärd till det angivna antalet.

• 0 (standardinställning)

  Använder det faktiska antalet processorer eller färre baserat på den aktuella systemarbetsbelastningen.

Mer information finns i Konfigurera parallella indexåtgärder.

ONLINE = { ON | OFF } <som gäller för drop_clustered_constraint_option>

Anger om underliggande tabeller och associerade index är tillgängliga för frågor och dataändringar under indexåtgärden. Standardvärdet är OFF. Du kan köra REBUILD som en ONLINE åtgärd.

• ON

  Långsiktiga tabelllås lagras inte under indexåtgärdens varaktighet. Under huvudfasen av indexåtgärden lagras endast ett intent share-lås (IS) i källtabellen. Det här beteendet gör att frågor eller uppdateringar av den underliggande tabellen och index kan fortsätta. I början av åtgärden hålls ett delat (S) lås på källobjektet under en kort tid. I slutet av åtgärden hämtas under en kort tid ett S-lås (delat) på källan om ett icke-grupperat index skapas. Eller så hämtas ett lås för Sch-M (schemaändring) när ett klustrat index skapas eller tas bort online och när ett grupperat eller icke-grupperat index återskapas. ONLINE kan inte anges till ON när ett index skapas i en lokal tillfällig tabell. Endast entrådad heap-återskapandeåtgärd tillåts.

  Om du vill köra DDL för SWITCH eller återskapa onlineindex måste alla aktiva blockeringstransaktioner som körs i en viss tabell slutföras. När du kör SWITCH eller återskapar åtgärden hindrar nya transaktioner från att starta och kan avsevärt påverka arbetsbelastningens dataflöde och tillfälligt fördröja åtkomsten till den underliggande tabellen.

• OFF

  Tabelllås gäller under hela indexåtgärden. En offlineindexåtgärd som skapar, återskapar eller släpper ett grupperat index, återskapar eller släpper ett icke-grupperat index, hämtar ett schemaändringslås (Sch-M) i tabellen. Det här låset förhindrar all användaråtkomst till den underliggande tabellen under hela åtgärden. En offlineindexåtgärd som skapar ett icke-grupperat index hämtar ett delat lås (S) i tabellen. Det här låset förhindrar uppdateringar av den underliggande tabellen men tillåter läsåtgärder, till exempel SELECT instruktioner. Flertrådade ombyggnadsåtgärder för heap tillåts.

  Mer information finns i Så här fungerar indexåtgärder online.

  Note

  Onlineindexåtgärder är inte tillgängliga i varje version av SQL Server. Mer information finns i -utgåvor och funktioner som stöds i SQL Server 2022.

FLYTTA TILL { partition_scheme_name(column_name [ ,...n ] ) | filgrupp | "standard" }

Gäller för: SQL Server och Azure SQL Database.

Anger en plats för att flytta de datarader som för närvarande finns på lövnivån för det klustrade indexet. Tabellen flyttas till den nya platsen. Det här alternativet gäller endast för begränsningar som skapar ett grupperat index.

{ CHECK | NOCHECK } VILLKOR

Anger att constraint_name är aktiverat eller inaktiverat. Det här alternativet kan bara användas med FOREIGN KEY och CHECK begränsningar. När NOCHECK har angetts inaktiveras villkoret och framtida infogningar eller uppdateringar av kolumnen verifieras inte mot villkorsvillkoren. DEFAULT, PRIMARY KEYoch UNIQUE begränsningar kan inte inaktiveras.

• ALL

  Anger att alla begränsningar antingen är inaktiverade med NOCHECK alternativet eller aktiverade med alternativet CHECK .

{ AKTIVERA | INAKTIVERA } UTLÖSARE

Anger att trigger_name är aktiverat eller inaktiverat. När en utlösare är inaktiverad definieras den fortfarande för tabellen. Men när INSERT, UPDATE, eller DELETE -instruktioner körs mot tabellen utförs inte åtgärderna i utlösaren förrän utlösaren återaktiveras.

• ALL

  Anger att alla utlösare i tabellen är aktiverade eller inaktiverade.

• trigger_name

  Anger namnet på utlösaren som ska inaktiveras eller aktiveras.

{ AKTIVERA | INAKTIVERA } CHANGE_TRACKING

Gäller för: SQL Server och Azure SQL Database.

Anger om ändringsspårning är inaktiverat för tabellen. Som standard är ändringsspårning inaktiverat.

Det här alternativet är endast tillgängligt när ändringsspårning är aktiverat för databasen. Mer information finns i ALTER DATABASE SET-alternativ.

För att aktivera ändringsspårning måste tabellen ha en primärnyckel.

WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )

Gäller för: SQL Server och Azure SQL Database.

Anger om databasmotorn spårar, vilken ändring av spårade kolumner som har uppdaterats. Standardvärdet är OFF.

VÄXLA [ PARTITION source_partition_number_expression ] TILL [ schema_name. ] target_table [ PARTITION target_partition_number_expression ]

Gäller för: SQL Server och Azure SQL Database.

Växlar ett datablock på något av följande sätt:

• Omtilldelar alla data i en tabell som en partition till en redan befintlig partitionerad tabell.
• Växlar en partition från en partitionerad tabell till en annan.
• Omtilldelar alla data i en partition i en partitionerad tabell till en befintlig tabell som inte är partitionerad.

Om tabell är en partitionerad tabell måste du ange source_partition_number_expression. Om target_table partitioneras måste du ange target_partition_number_expression. När du omtilldelar en tabells data som en partition till en redan befintlig partitionerad tabell eller växlar en partition från en partitionerad tabell till en annan, måste målpartitionen finnas och den måste vara tom.

När du omtilldelar en partitions data till en enda tabell måste måltabellen redan finnas och den måste vara tom. Både källtabellen eller partitionen och måltabellen eller partitionen måste finnas i samma filgrupp. Motsvarande index eller indexpartitioner måste också finnas i samma filgrupp. Många ytterligare begränsningar gäller för att växla partitioner. tabell och target_table kan inte vara samma. target_table kan vara en identifierare i flera delar.

Både source_partition_number_expression och target_partition_number_expression är konstanta uttryck som kan referera till variabler och funktioner. Dessa omfattar användardefinierade typvariabler och användardefinierade funktioner. De kan inte referera till Transact-SQL uttryck.

En partitionerad tabell med ett grupperat columnstore-index fungerar som en partitionerad heap:

• Primärnyckeln måste innehålla partitionsnyckeln.
• Ett unikt index måste innehålla partitionsnyckeln. Men att inkludera partitionsnyckeln med ett befintligt unikt index kan ändra unikheten.
• Om du vill växla partitioner måste alla icke-grupperade index innehålla partitionsnyckeln.

Begränsningar SWITCH när du använder replikering finns i Replikera partitionerade tabeller och index.

Icke-grupperade kolumnlagringsindex skapades i skrivskyddat format före SQL Server 2016 (13.x) och för SQL Database före version V12. Du måste återskapa icke-illustrerade kolumnlagringsindex till det aktuella formatet (som är updatable) innan några PARTITION åtgärder kan köras.

Limitations

Om båda tabellerna partitioneras identiskt, inklusive icke-illustrerade index, och måltabellen inte har några icke-illustrerade index, kan du få ett 4907-fel.

Exempel på utdata:

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 | "default" | "NULL" })

Gäller för: SQL Server. Azure SQL Database stöder inte FILESTREAM.

Anger var FILESTREAM-data lagras.

ALTER TABLE SET FILESTREAM_ON med -satsen lyckas endast om tabellen inte har några FILESTREAM-kolumner. Du kan lägga till FILESTREAM-kolumner med hjälp av en andra ALTER TABLE instruktion.

Om du anger partition_scheme_namegäller reglerna för CREATE TABLE. Kontrollera att tabellen redan är partitionerad för raddata, och dess partitionsschema använder samma partitionsfunktion och kolumner som FILESTREAM-partitionsschemat.

filestream_filegroup_name anger namnet på en FILESTREAM-filgrupp. Filgruppen måste ha en fil som har definierats för filgruppen med hjälp av instruktionen CREATE DATABASE eller ALTER DATABASE , eller så får du ett fel.

"default" anger FILESTREAM-filgruppen med egenskapsuppsättningen DEFAULT . Om det inte finns någon FILESTREAM-filgrupp får du ett fel.

"NULL" anger att alla referenser till FILESTREAM-filgrupper för tabellen tas bort. Alla FILESTREAM-kolumner måste tas bort först. Använd SET FILESTREAM_ON = "NULL" för att ta bort alla FILESTREAM-data som är associerade med en tabell.

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

Gäller för: SQL Server 2016 (13.x) och senare versioner och Azure SQL Database.

Inaktiverar eller aktiverar systemversioner av en tabell. För att aktivera systemversionshantering av en tabell verifierar systemet att kraven på datatyp, nullabilitetsbegränsningar och primärnyckelbegränsningar för systemversionshantering uppfylls. Systemet registrerar historiken för varje post i den systemversionsbaserade tabellen i en separat historiktabell. HISTORY_TABLE Om argumentet inte används är MSSQL_TemporalHistoryFor<primary_table_object_id>namnet på den här historiktabellen . Om historiktabellen inte finns genererar systemet en ny historiktabell som matchar schemat för den aktuella tabellen, skapar en länk mellan de två tabellerna och gör det möjligt för systemet att registrera historiken för varje post i den aktuella tabellen i historiktabellen. Om du använder argumentet HISTORY_TABLE för att skapa en länk till och använda en befintlig historiktabell skapar systemet en länk mellan den aktuella tabellen och den angivna tabellen. När du skapar en länk till en befintlig historiktabell kan du välja att göra en datakonsekvenskontroll. Den här datakonsekvenskontrollen säkerställer att befintliga poster inte överlappar varandra. Att köra kontrollen av datakonsekvens är standard. Använd argumentet SYSTEM_VERSIONING = ON i en tabell som har definierats med satsen PERIOD FOR SYSTEM_TIME för att göra den befintliga tabellen till en tidstabell. Mer information finns i Temporala tabeller.

HISTORY_RETENTION_PERIOD = { INFINITE | number { DAY | DAYS | VECKA | VECKOR | MÅNAD | MÅNADER | ÅR | YEARS } }

gäller för: SQL Server 2017 (14.x) och Azure SQL Database.

Anger begränsad eller oändlig kvarhållning för historiska data i en temporal tabell. Om det utelämnas antas oändlig kvarhållning.

DATA_DELETION

gäller endast för: Azure SQL Edge endast

Aktiverar kvarhållningsprincipbaserad rensning av gamla eller föråldrade data från tabeller i en databas. Mer information finns i Aktivera och inaktivera datakvarhållning. Följande parametrar måste anges för att datakvarhållning ska aktiveras.

• FILTER_COLUMN = { column_name }

  Anger den kolumn som ska användas för att avgöra om raderna i tabellen är föråldrade eller inte. Följande datatyper tillåts för filterkolumnen.

  • date
  • datetime
  • datetime2
  • smalldatetime
  • datetimeoffset

• RETENTION_PERIOD = { INFINITE | tal { DAY | DAYS | VECKA | VECKOR | MÅNAD | MÅNADER | ÅR | YEARS } }

  Anger kvarhållningsperiodprincipen för tabellen. Kvarhållningsperioden anges som en kombination av ett positivt heltalsvärde och datumdelsenheten.

SET ( LOCK_ESCALATION = { AUTO | TABELL | INAKTIVERA } )

Gäller för: SQL Server och Azure SQL Database.

Anger de tillåtna metoderna för låseskalering för en tabell.

• AUTO

  Med det här alternativet kan SQL Server Database Engine välja den låseskaleringskornighet som är lämplig för tabellschemat.

  • Om tabellen är partitionerad tillåts låseskalering till heap- eller B-trädkornigheten (HoBT). Med andra ord tillåts eskalering till partitionsnivå. När låset har eskalerats till HoBT-nivån eskaleras inte låset senare till TABLE kornighet.

  • Om tabellen inte är partitionerad utförs låseskaleringen till kornigheten TABLE .

• TABLE

  Låseskalering utförs på tabellnivåskornighet oavsett om tabellen är partitionerad eller inte partitionerad. TABLE är standardvärdet.

• DISABLE

  Förhindrar låseskalering i de flesta fall. Lås på tabellnivå är inte helt otillåtna. När du till exempel söker igenom en tabell som inte har något klustrat index under den serialiserbara isoleringsnivån måste databasmotorn ta ett tabelllås för att skydda dataintegriteten.

REBUILD

Använd syntaxen REBUILD WITH för att återskapa en hel tabell, inklusive alla partitioner i en partitionerad tabell. Om tabellen har ett grupperat index REBUILD återskapar alternativet det klustrade indexet. REBUILD kan köras som en ONLINE åtgärd.

Använd syntaxen REBUILD PARTITION för att återskapa en enskild partition i en partitionerad tabell.

PARTITION = ALLA

Gäller för: SQL Server och Azure SQL Database.

Återskapar alla partitioner när du ändrar inställningarna för partitionskomprimering.

ÅTERSKAPA MED ( <rebuild_option> )

Alla alternativ gäller för en tabell med ett grupperat index. Om tabellen inte har ett grupperat index påverkas heapstrukturen bara av några av alternativen.

När en specifik komprimeringsinställning inte har angetts med åtgärden REBUILD används den aktuella komprimeringsinställningen för partitionen. Om du vill returnera den aktuella inställningen frågar du kolumnen data_compression i sys.partitions katalogvyn.

Fullständiga beskrivningar av alternativen för återskapande finns i ALTER TABLE index_option.

DATA_COMPRESSION

Gäller för: SQL Server och Azure SQL Database.

Anger datakomprimeringsalternativet för den angivna tabellen, partitionsnumret eller partitionsintervallet. Alternativen är följande:

• NONE

  Tabell eller angivna partitioner komprimeras inte. Det här alternativet gäller inte för kolumnlagringstabeller.

• RAD

  Tabell eller angivna partitioner komprimeras med hjälp av radkomprimering. Det här alternativet gäller inte för kolumnlagringstabeller.

• SIDA

  Tabell eller angivna partitioner komprimeras med hjälp av sidkomprimering. Det här alternativet gäller inte för kolumnlagringstabeller.

• COLUMNSTORE

  Gäller för: SQL Server 2014 (12.x) och senare versioner och Azure SQL Database.

  Gäller endast för columnstore-tabeller. COLUMNSTORE anger att dekomprimera en partition som komprimerades med alternativet COLUMNSTORE_ARCHIVE . När data återställs fortsätter de att komprimeras med den kolumnlagringskomprimering som används för alla columnstore-tabeller.

• COLUMNSTORE_ARCHIVE

  Gäller för: SQL Server 2014 (12.x) och senare versioner och Azure SQL Database.

  Gäller endast för columnstore-tabeller, som är tabeller som lagras med ett grupperat columnstore-index. COLUMNSTORE_ARCHIVE komprimerar ytterligare den angivna partitionen till en mindre storlek. Använd det här alternativet för arkivering eller andra situationer som kräver mindre lagring och har råd med mer tid för lagring och hämtning.

  Information om hur du återskapar flera partitioner samtidigt finns i index_option. Om tabellen inte har ett klustrat index återskapas heapen och de icke-grupperade indexen genom att ändra datakomprimering. Mer information om komprimering finns i Datakomprimering.

  ALTER TABLE REBUILD PARTITION WITH DATA COMPRESSION = ROW eller PAGE är inte tillåten i SQL-databasen i Förhandsversionen av Microsoft Fabric.

XML_COMPRESSION

gäller för: SQL Server 2022 (16.x) och senare versioner, Azure SQL Database och Azure SQL Managed Instance.

Anger XML-komprimeringsalternativet för alla xml- datatypskolumner i tabellen. Alternativen är följande:

• ON

  Kolumner som använder XML- datatyp komprimeras.

• OFF

  Kolumner som använder XML- datatyp komprimeras inte.

ONLINE = { ON | OFF } <som gäller för single_partition_rebuild_option>

Anger om en enskild partition av de underliggande tabellerna och associerade index är tillgängliga för frågor och dataändringar under indexåtgärden. Standardvärdet är OFF. Du kan köra REBUILD som en ONLINE åtgärd.

• ON

  Långsiktiga tabelllås lagras inte under indexåtgärdens varaktighet. S-lock i tabellen krävs i början av indexet återskapas och ett Sch-M lås på tabellen i slutet av onlineindexet återskapas. Även om båda låsen är korta metadatalås måste Sch-M-låset vänta tills alla blockerande transaktioner har slutförts. Under väntetiden blockerar Sch-M lås alla andra transaktioner som väntar bakom det här låset vid åtkomst till samma tabell.

  Note

  Återskapa onlineindex kan ange de low_priority_lock_wait alternativ som beskrivs senare i det här avsnittet.

• OFF

  Tabelllås tillämpas under indexåtgärdens varaktighet. Detta förhindrar all användaråtkomst till den underliggande tabellen under hela åtgärden.

column_set_name XML-COLUMN_SET FÖR ALL_SPARSE_COLUMNS

Gäller för: SQL Server och Azure SQL Database.

Namnet på kolumnuppsättningen. En kolumnuppsättning är en otypad XML-representation som kombinerar alla glesa kolumner i en tabell till strukturerade utdata. Det går inte att lägga till en kolumnuppsättning i en tabell som innehåller glesa kolumner. Mer information om kolumnuppsättningar finns i Använda kolumnuppsättningar.

{ AKTIVERA | INAKTIVERA } FILETABLE_NAMESPACE

Gäller för: SQL Server.

Aktiverar eller inaktiverar systemdefinierade begränsningar för en FileTable. Kan bara användas med en FileTable.

SET ( FILETABLE_DIRECTORY = directory_name )

Gäller för: SQL Server. Azure SQL Database stöder inte FileTable.

Anger det Windows-kompatibla filtabellkatalognamnet. Det här namnet bör vara unikt bland alla Filtabellkatalognamn i databasen. Unikhetsjämförelse är skiftlägeskänslig, trots SQL-sorteringsinställningarna. Kan bara användas med en FileTable.

REMOTE_DATA_ARCHIVE

Gäller för: SQL Server 2017 (14.x) och senare versioner.

Aktiverar eller inaktiverar Stretch Database för en tabell. Mer information finns i Stretch Database.

Aktivera Stretch Database för en tabell

När du aktiverar Stretch för en tabell genom att ange ONmåste du också ange MIGRATION_STATE = OUTBOUND att börja migrera data omedelbart eller MIGRATION_STATE = PAUSED för att skjuta upp datamigreringen. Standardvärdet är MIGRATION_STATE = OUTBOUND. Mer information om hur du aktiverar Stretch för en tabell finns i Aktivera Stretch Database för en tabell.

Prerequisites. Innan du aktiverar Stretch för en tabell måste du aktivera Stretch på servern och i databasen. Mer information finns i Aktivera Stretch Database för en databas.

Permissions. Att aktivera Stretch för en databas eller en tabell kräver db_owner behörigheter. Att aktivera Stretch för en tabell kräver ALTER också behörigheter för tabellen.

Inaktivera Stretch Database för en tabell

När du inaktiverar Stretch för en tabell har du två alternativ för fjärrdata som redan har migrerats till Azure. Mer information finns i Inaktivera Stretch Database och ta tillbaka fjärrdata.

• Om du vill inaktivera Stretch för en tabell och kopiera fjärrdata för tabellen från Azure tillbaka till SQL Server kör du följande kommando. Det går inte att avbryta det här kommandot.

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

Den här åtgärden medför kostnader för dataöverföring och kan inte avbrytas. Mer information finns i Prisinformation om dataöverföringar.

När alla fjärrdata har kopierats från Azure tillbaka till SQL Server inaktiveras Stretch för tabellen.

• Om du vill inaktivera Stretch för en tabell och avbryta fjärrdata kör du följande kommando.

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

När du har inaktiverat Stretch Database för en tabell stoppas datamigreringen och frågeresultaten innehåller inte längre resultat från fjärrtabellen.

Om du inaktiverar Stretch tar du inte bort fjärrtabellen. Om du vill ta bort fjärrtabellen släpper du den med hjälp av Azure-portalen.

[ FILTER_PREDICATE = { null | predikat } ]

Gäller för: SQL Server 2017 (14.x) och senare versioner.

Du kan också ange ett filterpredikat för att välja rader som ska migreras från en tabell som innehåller både historiska och aktuella data. Predikatet måste anropa en deterministisk infogad tabellvärdesfunktion. Mer information finns i Aktivera Stretch Database för en tabell och Välj rader att migrera med hjälp av en filterfunktion – Stretch Database.

Om du inte anger ett filterpredikat migreras hela tabellen.

När du anger ett filterpredikat måste du också ange MIGRATION_STATE.

MIGRATION_STATE = { OUTBOUND | INKOMMANDE | PAUSAD }

Gäller för: SQL Server 2017 (14.x) och senare versioner.

• Ange OUTBOUND för att migrera data från SQL Server till Azure.

• Ange INBOUND för att kopiera fjärrdata för tabellen från Azure tillbaka till SQL Server och inaktivera Stretch för tabellen. Mer information finns i Inaktivera Stretch Database och ta tillbaka fjärrdata.

  Den här åtgärden medför kostnader för dataöverföring och kan inte avbrytas.

• Ange PAUSED för att pausa eller skjuta upp datamigreringen. Mer information finns i Pausa och återuppta datamigrering – Stretch Database.

WAIT_AT_LOW_PRIORITY

Gäller för: SQL Server 2014 (12.x) och senare versioner och Azure SQL Database.

En återskapad onlineindex måste vänta på blockeringsåtgärder i den här tabellen. WAIT_AT_LOW_PRIORITY anger att återskapande av onlineindex väntar på lås med låg prioritet, vilket gör att andra åtgärder kan fortsätta medan onlineindexet byggs. Om du utelämnar alternativet WAIT AT LOW PRIORITY är det samma som WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = tid [ MINUTER ]

Gäller för: SQL Server 2014 (12.x) och senare versioner och Azure SQL Database.

Väntetiden, som är ett heltalsvärde som anges i minuter, som SWITCH eller onlineindexet återskapar väntar med låg prioritet när DDL-kommandot körs. Om åtgärden blockeras för MAX_DURATION tiden körs en av ABORT_AFTER_WAIT åtgärderna. MAX_DURATION tiden är alltid i minuter och du kan utelämna ordet MINUTES.

ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS }

Gäller för: SQL Server 2014 (12.x) och senare versioner och Azure SQL Database.

• NONE

  Fortsätt att vänta på låset med normal (vanlig) prioritet.

• SELF

  SWITCH Avsluta DDL-åtgärden eller onlineindexet som för närvarande körs utan att vidta några åtgärder.

• BLOCKERS

  Avsluta alla användartransaktioner som för närvarande blockerar SWITCH DDL-åtgärden eller onlineindexet så att åtgärden kan fortsätta.

  Kräver ALTER ANY CONNECTION behörighet.

OM FINNS

Gäller för: SQL Server 2016 (13.x) och senare versioner och Azure SQL Database.

Villkorligt tar endast bort kolumnen eller villkoret om den redan finns.

RESUMABLE = { ON | OFF}

Gäller för: SQL Server 2022 (16.x) och senare versioner.

Anger om en ALTER TABLE ADD CONSTRAINT åtgärd kan återupptas. Åtgärden Lägg till tabellbegränsning kan återupptas när ON. Åtgärden Lägg till tabellbegränsning kan inte återupptas när OFF. Standardvärdet är OFF. Alternativet RESUMABLE kan användas som en del av ALTER TABLE index_option i ALTER TABLE table_constraint.

MAX_DURATION när det används med RESUMABLE = ON (kräver ONLINE = ON) anger tid (ett heltalsvärde som anges i minuter) att en återupptabar online-tilläggsbegränsningsåtgärd körs innan den pausas. Om inte anges fortsätter åtgärden tills den har slutförts.

Mer information om hur du aktiverar och använder återanvändbara åtgärder finns ALTER TABLE ADD CONSTRAINT i Återuppta tabellbegränsningar.

Remarks

Om du vill lägga till nya rader med data använder du INSERT-. Om du vill ta bort rader med data använder du DELETE eller TRUNCATE TABLE. Om du vill ändra värdena i befintliga rader använder du UPDATE.

Om det finns några körningsplaner i procedurcachen som refererar till tabellen markerar ALTER TABLE de dem som ska kompileras om vid nästa körning.

I SQL Database i Microsoft Fabric Preview kan vissa tabellfunktioner skapas men speglas inte i Fabric OneLake. Mer information finns i Begränsningar för SQL-databasspegling i infrastrukturresurser (förhandsversion).

Ändra storleken på en kolumn

Du kan ändra längden, precisionen eller skalan för en kolumn genom att ange en ny storlek för kolumndatatypen. ALTER COLUMN Använd -satsen. Om data finns i kolumnen kan den nya storleken inte vara mindre än datans maximala storlek. Du kan inte heller definiera kolumnen i ett index, såvida inte kolumnen är en datatyp av typen varchar, nvarchar eller varbinary och indexet inte är resultatet av en PRIMARY KEY begränsning. Se exemplet i det korta avsnittet Ändra en kolumndefinition.

Lås och ALTER TABLE

Ändringar som du anger i ALTER TABLE implementera omedelbart. Om ändringarna kräver ändringar av raderna i tabellen ALTER TABLE uppdaterar du raderna. ALTER TABLE hämtar ett schemaändringslås (Sch-M) i tabellen för att se till att inga andra anslutningar refererar till även metadata för tabellen under ändringen, förutom onlineindexåtgärder som kräver en kort Sch-M lås i slutet. I en ALTER TABLE...SWITCH-åtgärd hämtas låset på både käll- och måltabellerna. De ändringar som görs i tabellen loggas och kan återställas helt. Ändringar som påverkar alla rader i stora tabeller, till exempel att släppa en kolumn eller, i vissa utgåvor av SQL Server, lägga till en NOT NULL kolumn med ett standardvärde, kan ta lång tid att slutföra och generera många loggposter. Kör dessa ALTER TABLE instruktioner med samma omsorg som någon INSERT, UPDATEeller DELETE -instruktion som påverkar många rader.

gäller för Warehouse i Microsoft Fabric.

ALTER TABLE kan inte ingå i en explicit transaktion.

Utökade händelser (XEvents) för partitionsväxel

Följande XEvents är relaterade till ALTER TABLE ... SWITCH PARTITION och onlineindex återskapar.

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Lägg till NOT NULL-kolumner som en onlineåtgärd

I SQL Server 2012 (11.x) Enterprise Edition och senare versioner är det en onlineåtgärd att lägga till en NOT NULL kolumn med ett standardvärde när standardvärdet är en körningskonstant. Det innebär att åtgärden slutförs nästan omedelbart trots antalet rader i tabellen, eftersom de befintliga raderna i tabellen inte uppdateras under åtgärden. I stället lagras standardvärdet endast i tabellens metadata och värdet slås upp efter behov i frågor som har åtkomst till dessa rader. Det här beteendet är automatiskt. Ingen ytterligare syntax krävs för att implementera onlineåtgärden utöver syntaxen ADD COLUMN . En körningskonstant är ett uttryck som genererar samma värde vid körning för varje rad i tabellen trots dess determinism. Till exempel är konstantuttrycket "My temporary data", eller systemfunktionen GETUTCDATETIME() körningskonstanter. Däremot är funktionerna NEWID() eller NEWSEQUENTIALID() inte körningskonstanter, eftersom ett unikt värde skapas för varje rad i tabellen. Att lägga till en NOT NULL kolumn med ett standardvärde som inte är en körningskonstant körs alltid offline och ett exklusivt lås (Sch-M) hämtas under hela åtgärden.

Medan de befintliga raderna refererar till värdet som lagras i metadata lagras standardvärdet på raden för alla nya rader som infogas och anger inte något annat värde för kolumnen. Standardvärdet som lagras i metadata flyttas till en befintlig rad när raden uppdateras (även om den faktiska kolumnen inte anges i -instruktionen UPDATE ) eller om tabellen eller klustrade index återskapas.

Kolumner av typen varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometri, geografi eller CLR-användardefinierade typer kan inte läggas till i en onlineåtgärd. Det går inte att lägga till en kolumn online om det gör att den maximala möjliga radstorleken överskrider gränsen på 8 060 byte. Kolumnen läggs till som en offlineåtgärd i det här fallet.

Parallell plankörning

I SQL Server 2012 (11.x) Enterprise Edition och senare versioner bestäms antalet processorer som används för att köra en enda ALTER TABLE ADD (indexbaserad) CONSTRAINT eller DROP (klustrad index) CONSTRAINT -instruktion av konfigurationsalternativet för maximal grad av parallellitet och den aktuella arbetsbelastningen. Om databasmotorn upptäcker att systemet är upptaget minskas automatiskt graden av parallellitet för åtgärden innan instruktionskörningen startar. Du kan konfigurera antalet processorer som används för att köra -instruktionen manuellt genom att ange alternativet MAXDOP . Mer information finns i Serverkonfiguration: maximal grad av parallellitet.

Partitionerade tabeller

Förutom att utföra SWITCH-åtgärder som omfattar partitionerade tabeller kan du använda ALTER TABLE för att ändra status för kolumner, begränsningar och utlösare för en partitionerad tabell precis som för icke-partitionerade tabeller. Den här instruktionen kan dock inte användas för att ändra hur själva tabellen partitioneras. Om du vill partitionera om en partitionerad tabell använder du ALTER PARTITION SCHEME och ALTER PARTITION FUNCTION. Dessutom kan du inte ändra datatypen för en kolumn i en partitionerad tabell.

Begränsningar för tabeller med schemabundna vyer

De begränsningar som gäller för ALTER TABLE instruktioner för tabeller med schemabundna vyer är desamma som de begränsningar som för närvarande tillämpas vid ändring av tabeller med ett enkelt index. Det är tillåtet att lägga till en kolumn. Det är dock inte tillåtet att ta bort eller ändra en kolumn som deltar i en schemabunden vy. Om instruktionen ALTER TABLE kräver att en kolumn som används i en schemabunden vy ändras misslyckas ALTER TABLE och databasmotorn genererar ett felmeddelande. Mer information om schemabindning och indexerade vyer finns i CREATE VIEW.

Att lägga till eller ta bort utlösare i bastabeller påverkas inte av att skapa en schemabunden vy som refererar till tabellerna.

Index och ALTER TABLE

Index som skapats som en del av en begränsning tas bort när villkoret tas bort. Index som skapades med CREATE INDEX måste tas bort med DROP INDEX. Använd instruktionen ALTER INDEX för att återskapa en indexdel av en villkorsdefinition. Villkoret behöver inte tas bort och läggas till igen med ALTER TABLE.

Alla index och begränsningar som baseras på en kolumn måste tas bort innan kolumnen kan tas bort.

När du tar bort en begränsning som skapade ett grupperat index lagras de datarader som lagrades på lövnivån för det klustrade indexet i en icke-grupperad tabell. Du kan släppa det klustrade indexet och flytta den resulterande tabellen till ett annat filgrupps- eller partitionsschema i en enda transaktion genom att ange alternativet MOVE TO . Alternativet MOVE TO har följande begränsningar:

• MOVE TO är inte giltigt för indexerade vyer eller icke-grupperade index.

• Partitionsschemat eller filgruppen måste redan finnas.

• Om MOVE TO inte anges finns tabellen i samma partitionsschema eller filgrupp som definierades för det klustrade indexet.

När du släpper ett grupperat index anger du ONLINE = ON alternativet så DROP INDEX att transaktionen inte blockerar frågor och ändringar av underliggande data och associerade icke-grupperade index.

ONLINE = ON har följande begränsningar:

• ONLINE = ON är inte giltigt för klustrade index som också är inaktiverade. Inaktiverade index måste tas bort med hjälp ONLINE = OFFav .
• Endast ett index i taget kan tas bort.
• ONLINE = ON är inte giltigt för indexerade vyer, icke-grupperade index eller index i lokala temporära tabeller.
• ONLINE = ON är inte giltigt för columnstore-index.

Tillfälligt diskutrymme som är lika med storleken på det befintliga klustrade indexet krävs för att släppa ett klustrade index. Det här extra utrymmet frigörs så snart åtgärden har slutförts.

Replikera schemaändringar

När du kör ALTER TABLE en publicerad tabell i en SQL Server Publisher sprids ändringen som standard till alla SQL Server-prenumeranter. Den här funktionen har vissa begränsningar. Du kan inaktivera det. Mer information finns i Göra schemaändringar i publikationsdatabaser.

Datakomprimering

Systemtabeller kan inte aktiveras för komprimering. Om tabellen är en heap är återskapandeåtgärden för ONLINE läget enkeltrådad. Användningsläge OFFLINE för en flertrådad heap-ombyggnadsåtgärd. Mer information om datakomprimering finns i Datakomprimering.

Om du vill utvärdera hur ändring av komprimeringstillståndet påverkar en tabell, ett index eller en partition använder du den sp_estimate_data_compression_savings system lagrade proceduren.

Följande begränsningar gäller för partitionerade tabeller:

• Du kan inte ändra komprimeringsinställningen för en enskild partition om tabellen har icke-berättigade index.
• Den ALTER TABLE <table> REBUILD PARTITION ... syntax återskapar den angivna partitionen.
• Den ALTER TABLE <table> REBUILD WITH ... syntax återskapar alla partitioner.

Släpp ntextkolumner

När du släpper kolumner med den inaktuella datatypen ntext sker rensningen av borttagna data som en serialiserad åtgärd på alla rader. Rensningen kan kräva mycket tid. När du släpper en ntextkolumn i en tabell med många rader uppdaterar du ntextkolumnen till NULL värdet först och släpper sedan kolumnen. Du kan köra det här alternativet med parallella åtgärder och göra det mycket snabbare.

Återskapa onlineindex

Om du vill köra DDL-instruktionen för ett återskapande av onlineindex måste alla aktiva blockerande transaktioner som körs i en viss tabell slutföras. När onlineindexet återskapas blockeras alla nya transaktioner som är redo att börja köras i den här tabellen. Låsets varaktighet för återskapande av onlineindex är kort, men om du väntar på att alla öppna transaktioner i en viss tabell ska slutföras och de nya transaktionerna blockeras kan det påverka dataflödet avsevärt. Detta kan orsaka en långsam eller tidsgräns för arbetsbelastningen och avsevärt begränsa åtkomsten till den underliggande tabellen. Alternativet WAIT_AT_LOW_PRIORITY gör det möjligt för DBA:er att hantera S-lås och Sch-M lås som krävs för återskapade onlineindex. I alla tre fallen: NONE, SELF, och BLOCKERS, om det under väntetiden ((MAX_DURATION = n [minutes])) inte finns några blockerande aktiviteter körs onlineindexet återskapas omedelbart utan att vänta och DDL-instruktionen slutförs.

Stöd för kompatibilitet

Instruktionen ALTER TABLE stöder endast tabellnamn i två delar (schema.object). Det går inte att ange ett tabellnamn i SQL Server med hjälp av följande format vid kompileringstillfället med fel 117.

• server.database.schema.table
• .database.schema.table
• ..schema.table

I tidigare versioner anger du formatet server.database.schema.table returnerade fel 4902. Att ange formatet .database.schema.table eller formatet ..schema.table lyckades.

Lös problemet genom att ta bort användningen av ett prefix i fyra delar.

Permissions

Kräver ALTER behörighet i tabellen.

ALTER TABLE behörigheter gäller för båda tabellerna som ingår i en ALTER TABLE SWITCH instruktion. Alla data som växlas ärver säkerheten för måltabellen.

Om du har definierat några kolumner i -instruktionen ALTER TABLE som av en användardefinierad typ eller aliasdatatyp REFERENCES för common language runtime (CLR) krävs behörighet för typen.

Att lägga till eller ändra en kolumn som uppdaterar raderna i tabellen kräver UPDATE behörighet i tabellen. Du kan till exempel lägga till en NOT NULL kolumn med ett standardvärde eller lägga till en identitetskolumn när tabellen inte är tom.

Examples

Kodexemplen i den här artikeln använder exempeldatabasen AdventureWorks2022 eller AdventureWorksDW2022 som du kan ladda ned från startsidan för Microsoft SQL Server-exempel och Community Projects .

Lägga till kolumner och begränsningar

Exempel i det här avsnittet visar hur du lägger till kolumner och begränsningar i en tabell.

A. Lägg till en ny kolumn

I följande exempel läggs en kolumn som tillåter null-värden och som inte har några värden som tillhandahålls via en DEFAULT definition. I den nya kolumnen har NULLvarje rad .

CREATE TABLE dbo.doc_exa (column_a INT);
GO

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

B. Lägga till en kolumn med en begränsning

I följande exempel läggs en ny kolumn till med en UNIQUE begränsning.

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. Lägga till en overifierad CHECK-begränsning i en befintlig kolumn

I följande exempel läggs en begränsning till i en befintlig kolumn i tabellen. Kolumnen har ett värde som bryter mot villkoret. Därför används WITH NOCHECK för att förhindra att villkoret verifieras mot befintliga rader och för att tillåta att villkoret läggs till.

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. Lägga till en STANDARD-begränsning i en befintlig kolumn

I följande exempel skapas en tabell med två kolumner och ett värde infogas i den första kolumnen, och den andra kolumnen förblir NULL. Ett DEFAULT villkor läggs sedan till i den andra kolumnen. För att kontrollera att standardvärdet tillämpas infogas ett annat värde i den första kolumnen och tabellen efterfrågas.

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. Lägga till flera kolumner med begränsningar

I följande exempel läggs flera kolumner med begränsningar som definierats med den nya kolumnen. Den första nya kolumnen har en egenskap för IDENTITY. Varje rad i tabellen har nya inkrementella värden i identitetskolumnen.

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. Lägga till en nullbar kolumn med standardvärden

Följande exempel lägger till en nullbar kolumn med en DEFAULT definition och använder WITH VALUES för att ange värden för varje befintlig rad i tabellen. Om WITH VALUES inte används har varje rad värdet NULL i den nya kolumnen.

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. Skapa en PRIMARY KEY-begränsning med index- eller datakomprimeringsalternativ

I följande exempel skapas villkoret PRIMARY KEYPK_TransactionHistoryArchive_TransactionID och alternativen FILLFACTOR, ONLINEoch PAD_INDEX. Det resulterande klustrade indexet har samma namn som villkoret.

Gäller för: SQL Server och Azure SQL Database.

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

Det här liknande exemplet tillämpar sidkomprimering när den klustrade primärnyckeln används.

USE AdventureWorks2022;
GO

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

H. Lägga till en gles kolumn

I följande exempel visas hur du lägger till och ändrar glesa kolumner i tabell T1. Koden för att skapa tabell T1 är följande.

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

Om du vill lägga till ytterligare en gles kolumn C5kör du följande instruktion.

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

Om du vill konvertera C4 icke-gles kolumn till en gles kolumn kör du följande instruktion.

ALTER TABLE T1
    ALTER COLUMN C4 ADD SPARSE;
GO

Om du vill konvertera den C4 glesa kolumnen till en icke-sparse-kolumn kör du följande instruktion.

ALTER TABLE T1
    ALTER COLUMN C4 DROP SPARSE;
GO

I. Lägga till en kolumnuppsättning

I följande exempel visas hur du lägger till en kolumn i tabellen T2. Det går inte att lägga till en kolumnuppsättning i en tabell som redan innehåller glesa kolumner. Koden för att skapa tabell T2 är följande.

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

Följande tre instruktioner lägger till en kolumnuppsättning med namnet CSoch ändrar sedan kolumner C2 och C3 till 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. Lägga till en krypterad kolumn

Följande instruktion lägger till en krypterad kolumn med namnet PromotionCode.

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. Lägga till en primärnyckel med återupptabar åtgärd

Återupptas ALTER TABLE åtgärd för att lägga till en primärnyckel klustrad i kolumnen (a) med MAX_DURATION på 240 minuter.

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

Ta bort kolumner och begränsningar

Exemplen i det här avsnittet visar hur du släpper kolumner och begränsningar.

A. Släppa en kolumn eller kolumner

I det första exemplet ändras en tabell för att ta bort en kolumn. Det andra exemplet tar bort flera kolumner.

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. Släpp begränsningar och kolumner

Det första exemplet tar bort en UNIQUE begränsning från en tabell. Det andra exemplet tar bort två begränsningar och en enda kolumn.

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. Ta bort en begränsning för PRIMÄRNYCKEL i ONLINE-läge

I följande exempel tas en PRIMARY KEY begränsning bort med alternativet inställt på ONLINEON.

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

D. Lägga till och ta bort en FOREIGN KEY-begränsning

I följande exempel skapas tabellen ContactBackupoch ändrar sedan tabellen, först genom att lägga till en FOREIGN KEY villkor som refererar till tabellen Person.Personoch sedan genom att släppa villkoret FOREIGN KEY.

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;

Ändra en kolumndefinition

A. Ändra datatypen för en kolumn

I följande exempel ändras en kolumn i en tabell från INT till DECIMAL.

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. Ändra storleken på en kolumn

I följande exempel ökar storleken på en kolumn och precisionen och skalan för en decimal kolumn. Eftersom kolumnerna innehåller data kan kolumnstorleken bara ökas. Observera också att col_a definieras i ett unikt index. Storleken på col_a kan fortfarande ökas eftersom datatypen är ett varchar och indexet inte är resultatet av en PRIMARY KEY begränsning.

-- 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. Ändra kolumnsortering

I följande exempel visas hur du ändrar sortering av en kolumn. Först skapas en tabell med standardanvändarsortering.

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

Därefter ändras kolumn C2 sortering till Latin1_General_BIN. Datatypen krävs, även om den inte ändras.

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

D. Kryptera en kolumn

I följande exempel visas hur du krypterar en kolumn med Always Encrypted med säkra enklaver.

Först skapas en tabell utan krypterade kolumner.

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

Därefter krypteras kolumnen C2 med en kolumnkrypteringsnyckel med namnet CEK1och randomiserad kryptering. För att följande instruktion ska lyckas:

• Kolumnkrypteringsnyckeln måste vara enklavaktiverad. Det innebär att den måste krypteras med en kolumnhuvudnyckel (CMK) som tillåter enklaverberäkningar.
• SQL Server-målinstansen måste ha stöd för Always Encrypted med säkra enklaver.
• -instruktionen måste utfärdas via en anslutningsuppsättning för Always Encrypted med säkra enklaver och med hjälp av en klientdrivrutin som stöds.
• Det anropande programmet måste ha åtkomst till CMK och skydda 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

Ändra en tabelldefinition

Exemplen i det här avsnittet visar hur du ändrar definitionen av en tabell.

A. Ändra en tabell för att ändra komprimering

I följande exempel ändras komprimering av en icke-partitionerad tabell. Heap- eller klustrade index återskapas. Om tabellen är en heap återskapas alla icke-grupperade index.

ALTER TABLE T1 REBUILD
    WITH (DATA_COMPRESSION = PAGE);

I följande exempel ändras komprimering av en partitionerad tabell. Syntaxen REBUILD PARTITION = 1 gör att endast partitionsnummer 1 återskapas.

Gäller för: SQL Server.

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

Samma åtgärd med hjälp av följande alternativa syntax gör att alla partitioner i tabellen återskapas.

Gäller för: SQL Server.

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

Ytterligare exempel på datakomprimering finns i Datakomprimering.

B. Ändra en kolumnlagringstabell för att ändra arkiveringskomprimering

I följande exempel komprimeras ytterligare en kolumnlagringstabellpartition genom att tillämpa ytterligare en komprimeringsalgoritm. Den här komprimering minskar tabellen till en mindre storlek, men ökar också den tid som krävs för lagring och hämtning. Detta är användbart för arkivering eller för situationer som kräver mindre utrymme och har råd med mer tid för lagring och hämtning.

Gäller för: SQL Server 2014 (12.x) och senare versioner och Azure SQL Database.

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

I följande exempel expanderas en kolumnlagringstabellpartition som komprimerades med COLUMNSTORE_ARCHIVE alternativet . När data återställs fortsätter de att komprimeras med den kolumnlagringskomprimering som används för alla columnstore-tabeller.

Gäller för: SQL Server 2014 (12.x) och senare versioner och Azure SQL Database.

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

C. Växla partitioner mellan tabeller

I följande exempel skapas en partitionerad tabell, förutsatt att partitionsschemat myRangePS1 redan har skapats i databasen. Därefter skapas en icke-partitionerad tabell med samma struktur som den partitionerade tabellen och i samma filgrupp som PARTITION 2 tabell PartitionTable. Data för PARTITION 2 i tabell PartitionTable växlas sedan till tabell NonPartitionTable.

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. Tillåt låseskalering på partitionerade tabeller

I följande exempel aktiveras låseskalering till partitionsnivå i en partitionerad tabell. Om tabellen inte är partitionerad anges låseskalering på nivån TABLE .

Gäller för: SQL Server och Azure SQL Database.

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

E. Konfigurera ändringsspårning i en tabell

I följande exempel aktiveras ändringsspårning i tabellen Person.Person.

Gäller för: SQL Server och Azure SQL Database.

USE AdventureWorks2022;

ALTER TABLE Person.Person ENABLE CHANGE_TRACKING;

I följande exempel möjliggörs ändringsspårning och spårning av kolumner som uppdateras under en ändring.

Gäller för: SQL Server.

USE AdventureWorks2022;
GO

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

I följande exempel inaktiveras ändringsspårning i tabellen Person.Person.

Gäller för: SQL Server och Azure SQL Database.

USE AdventureWorks2022;
GO

ALTER TABLE Person.Person DISABLE CHANGE_TRACKING;

Inaktivera och aktivera begränsningar och utlösare

A. Inaktivera och återaktivera en begränsning

I följande exempel inaktiveras en begränsning som begränsar de löner som accepteras i data. NOCHECK CONSTRAINT används med ALTER TABLE för att inaktivera begränsningen och tillåta en infogning som vanligtvis bryter mot villkoret. CHECK CONSTRAINT återaktivering av villkoret.

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. Inaktivera och återaktivera en utlösare

I följande exempel används alternativet DISABLE TRIGGERALTER TABLE för att inaktivera utlösaren och tillåta en infogning som vanligtvis bryter mot utlösaren. ENABLE TRIGGER används sedan för att återaktivera utlösaren.

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

Onlineåtgärder

A. Återskapa onlineindex med hjälp av väntealternativ med låg prioritet

I följande exempel visas hur du utför ett återskapande av onlineindex som anger väntetidsalternativ med låg prioritet.

Gäller för: SQL Server 2014 (12.x) och senare versioner och Azure SQL Database.

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

B. Ändra kolumn online

I följande exempel visas hur du kör en ändringskolumnåtgärd med alternativet ONLINE .

Gäller för: SQL Server 2016 (13.x) och senare versioner och Azure SQL Database.

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

Systemversioner

Följande fyra exempel hjälper dig att bekanta dig med syntaxen för användning av systemversioner. Mer information finns i Komma igång med systemversionsbaserade temporala tabeller.

Gäller för: SQL Server 2016 (13.x) och senare versioner och Azure SQL Database.

A. Lägga till systemversioner i befintliga tabeller

I följande exempel visas hur du lägger till systemversionshantering i en befintlig tabell och skapar en framtida historiktabell. Det här exemplet förutsätter att det finns en befintlig tabell som heter InsurancePolicy med en primärnyckel definierad. Det här exemplet fyller i de nyligen skapade periodkolumnerna för systemversionshantering med standardvärden för start- och sluttider eftersom dessa värden inte kan vara null. I det HIDDEN här exemplet används -satsen för att säkerställa att inga effekter på befintliga program interagerar med den aktuella tabellen. Den använder HISTORY_RETENTION_PERIOD också som endast är tillgänglig i SQL Database.

--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. Migrera en befintlig lösning för att använda systemversioner

I följande exempel visas hur du migrerar till systemversioner från en lösning som använder utlösare för att efterlikna tidsstöd. Exemplet förutsätter att det finns en befintlig lösning som använder en ProjectTask tabell och en ProjectTaskHistory tabell för sin befintliga lösning, som använder kolumnerna Changed Date och Revised Date för sina perioder, att dessa periodkolumner inte använder datatypen datetime2 och att ProjectTask tabellen har en definierad primärnyckel.

-- 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. Inaktivera och återaktivera systemversioner för att ändra tabellschema

Det här exemplet visar hur du inaktiverar systemversioner i tabellen Department, lägger till en kolumn och återaktiverar systemversioner. Det krävs inaktivering av systemversioner för att ändra tabellschemat. Utför de här stegen i en transaktion för att förhindra uppdateringar av båda tabellerna när du uppdaterar tabellschemat, vilket gör att DBA kan hoppa över datakonsekvenskontrollen när systemversionshantering aktiveras igen och få en prestandafördel. Uppgifter som att skapa statistik, växla partitioner eller tillämpa komprimering på en eller båda tabellerna kräver inte inaktivering av systemversioner.

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. Ta bort systemversioner

Det här exemplet visar hur du tar bort systemversioner helt från tabellen Avdelning och släpper tabellen DepartmentHistory. Du kan också ta bort periodkolumnerna som används av systemet för att registrera information om systemversioner. Du kan inte släppa antingen Department- eller DepartmentHistory-tabellerna när systemversionshantering är aktiverat.

ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE Department
    DROP PERIOD FOR SYSTEM_TIME;

DROP TABLE DepartmentHistory;

Exempel: Azure Synapse Analytics and Analytics Platform System (PDW)

I följande exempel använder A till och med C tabellen FactResellerSales i databasen AdventureWorksPDW2022.

A. Kontrollera om en tabell är partitionerad

Följande fråga returnerar en eller flera rader om tabellen FactResellerSales är partitionerad. Om tabellen inte är partitionerad returneras inga rader.

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. Fastställa gränsvärden för en partitionerad tabell

Följande fråga returnerar gränsvärdena för varje partition i tabellen FactResellerSales.

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. Fastställa partitionskolumnen för en partitionerad tabell

Följande fråga returnerar namnet på partitioneringskolumnen FactResellerSales för tabellen.

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. Sammanfoga två partitioner

I följande exempel sammanfogas två partitioner i en tabell.

Tabellen Customer har följande definition:

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

Följande kommando kombinerar 10 och 25 partitionsgränser.

ALTER TABLE Customer MERGE RANGE (10);

Den nya DDL:n för tabellen är:

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. Dela en partition

I följande exempel delas en partition i en tabell.

Tabellen Customer har följande DDL:

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

Följande kommando skapar en ny partition som är bunden av värdet 75, mellan 50 och 100.

ALTER TABLE Customer SPLIT RANGE (75);

Den nya DDL:n för tabellen är:

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. Använda SWITCH för att flytta en partition till en historiktabell

I följande exempel flyttas data i en partition av Orders-tabellen till en partition i tabellen OrdersHistory.

Tabellen Orders har följande DDL:

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

I det här exemplet har tabellen Orders följande partitioner. Varje partition innehåller data.

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

Den OrdersHistory tabellen har följande DDL, som har identiska kolumner och kolumnnamn som den Orders tabellen. Båda är hash-distribuerade i kolumnen id.

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

Även om kolumnerna och kolumnnamnen måste vara desamma behöver partitionsgränserna inte vara desamma. I det här exemplet har tabellen OrdersHistory följande två partitioner och båda partitionerna är tomma:

• Partition 1 (inga data): OrderDate < '2004-01-01'
• Partition 2 (tom): '2004-01-01' <= OrderDate

För de föregående två tabellerna flyttar följande kommando alla rader med OrderDate < '2004-01-01' från tabellen Orders till tabellen OrdersHistory.

ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;

Därför är den första partitionen i Orders tom och den första partitionen i OrdersHistory innehåller data. Tabellerna visas nu på följande sätt:

Orders tabell

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

OrdersHistory tabell

• Partition 1 (har data): OrderDate < '2004-01-01'
• Partition 2 (tom): '2004-01-01' <= OrderDate

Om du vill rensa Orders tabellen kan du ta bort den tomma partitionen genom att sammanfoga partitioner 1 och 2 på följande sätt:

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

Efter sammanfogningen har tabellen Orders följande partitioner:

Orders tabell

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

Anta att ytterligare ett år går och att du är redo att arkivera år 2005. Du kan allokera en tom partition för år 2005 i tabellen OrdersHistory genom att dela upp den tomma partitionen på följande sätt:

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

Efter delningen har tabellen OrdersHistory följande partitioner:

OrdersHistory tabell

• Partition 1 (har data): OrderDate < '2004-01-01'
• Partition 2 (tom): '2004-01-01' < '2005-01-01'
• Partition 3 (tom): '2005-01-01' <= OrderDate

Relaterat innehåll

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