ALTER TABLE (Transact-SQL)
gäller för:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Warehouse i Microsoft Fabric
Ändrar en tabelldefinition genom att ändra, lägga till eller släppa kolumner och begränsningar. ALTER TABLE omtilldelar och återskapar även partitioner eller inaktiverar och aktiverar begränsningar och utlösare.
Not
För närvarande stöds ALTER TABLE i Infrastrukturlager endast för begränsningar och tillägg av null-kolumner. Se syntax för warehouse i Fabric.
Viktig
Syntaxen för ALTER TABLE skiljer sig åt 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:
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 } )
}
Not
Mer information finns i:
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
-- Syntax for Azure Synapse Analytics and 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 }
}
Not
Serverlös SQL-pool i Azure Synapse Analytics stöder endast externa och tillfälliga tabeller.
Syntax för lager i infrastrukturresurser
-- Syntax for Warehouse om Microsoft Fabric:
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 { [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
}
Argument
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.
ROWGUIDCOL för tabellen.
En beräknad kolumn eller används i en beräknad kolumn.
Används i statistik som genereras av CREATE STATISTICS-instruktionen. Användarna måste köra DROP STATISTICS för att ta bort statistiken innan ALTER COLUMN 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>');Not
Statistik som genereras automatiskt av frågeoptimeraren tas bort automatiskt av ALTER COLUMN.
Används i EN PRIMÄRNYCKEL eller [SEKUNDÄR NYCKEL] REFERENSvillkor.
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 datatypen kan orsaka ändringar 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.
Not
Datatypen för en kolumn i en partitionerad tabell kan inte ändras.
Datatypen för kolumner som ingår i ett index kan inte ändras om inte kolumnen är en varchar, nvarchareller varbinary datatyp och den nya storleken är lika med eller större än den gamla storleken.
En kolumn som ingår i en primärnyckelvillkor kan inte ändras från NOT NULL till NULL.
När du använder Always Encrypted (utan säkra enklaver), om kolumnen som ändras krypteras med "ENCRYPTED WITH", 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 felmeddelandet 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.
Not
Nya kolumner läggs till efter att alla befintliga kolumner i tabellen har ändrats.
[ 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 den inte anges kan kolumnen vara null.
- ANSI_PADDING utfyllnad är alltid PÅ 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å PÅ.
Not
Om COLLATE-satsen inte har angetts leder ändring av datatypen för en kolumn till en sorteringsändring av databasens standardsortering.
precision
Precisionen för den angivna datatypen. Mer information om giltiga precisionsvärden finns i Precision, Skala och Längd.
skala
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 (SQL Server 2008 (10.0.x) och senare) 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.
COLLATE-satsen ändrar endast sorteringarna för kolumner i tecken, varchar, ncharoch nvarchar datatyper. 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:
- Om en CHECK-begränsning, FOREIGN KEY-begränsning eller beräknade kolumner refererar till den ändrade kolumnen.
- Om index, statistik eller fulltextindex skapas i kolumnen. Statistik som skapas automatiskt på den ändrade kolumnen tas bort om kolumnsortering ändras.
- Om en schemabunden vy eller funktion refererar till kolumnen.
Mer information 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 STANDARDdefinition 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 begränsningar för PRIMÄRNYCKEL. 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 ALTER COLUMN NOT NULL tillåts, 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 satserna CREATE TABLE eller ALTER TABLE påverkar databas- och sessionsinställningarna och åsidosätter eventuellt ogiltigheten 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.
Not
Om NULL eller NOT NULL anges med ALTER COLUMN måste även new_data_type [(precision [, skala ])] anges. Om datatypen, precisionen och skalan inte ändras anger du de aktuella kolumnvärdena.
[ {ADD | DROP} ROWGUIDCOL ]
gäller för: SQL Server (SQL Server 2008 (10.0.x) och senare) och Azure SQL Database.
Anger att egenskapen ROWGUIDCOL läggs till eller tas bort från den angivna kolumnen. ROWGUIDCOL anger att kolumnen är en GUID-radkolumn. Du kan bara ange en unikidentifierare kolumn per tabell som ROWGUIDCOL-kolumn. Och du kan bara tilldela egenskapen ROWGUIDCOL till en unikidentifierare kolumn. 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 PERSISTED lagrar 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 på. Genom att markera en beräknad kolumn som PERSISTED kan du skapa index på beräknade kolumner som definierats för uttryck som är deterministiska, men inte exakta. Mer information finns i Index på beräknade kolumner.
SET QUOTED_IDENTIFIER måste vara PÅ när du skapar eller ändrar index för beräknade kolumner eller indexerade vyer. Mer information finns i SET QUOTED_IDENTIFIER (Transact-SQL).
Alla beräknade kolumner som används som en partitioneringskolumn i en partitionerad tabell måste uttryckligen markeras SOM PERSISTED.
SLÄPP INTE FÖR REPLIKERING
gäller för: SQL Server (SQL Server 2008 (10.0.x) och senare) 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.
GLES
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 INTE 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 ( FUNCTION = ' mask_function ')
gäller för: SQL Server (SQL Server 2016 (13.x) och senare) 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 datamaskering.
Lägg till och släpp en mask kräver ÄNDRA NÅGON MASK behörighet.
WITH ( ONLINE = ON | OFF) <som gäller för att ändra en kolumn>
gäller för: SQL Server (SQL Server 2016 (13.x) och senare) 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 AV. Du kan köra alter column online för kolumnändringar relaterade till datatyp, kolumnlängd eller precision, nullabilitet, gleshet och sortering.
Med online-alter-kolumnen kan användare som skapats och autostatistik referera till den ändrade kolumnen under alter COLUMN-åtgärdens varaktighet, 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 blockerar eller misslyckas alla åtgärder som kan ha ett beroende av kolumnen (index, vyer och så vidare.) 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 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_PRIORITYalternativet kan inte användas med online alter kolumn. -
ALTER COLUMN ... ADD/DROP PERSISTEDstöds inte för alter-kolumnen online. -
ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATIONpå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 har angetts för onlinealternativet.
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 verifieras eller inte mot en nyligen tillagd eller återaktiverad FOREIGN KEY- eller CHECK-begränsning. Om du inte anger antas WITH CHECK för nya begränsningar och MED 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änsningsöverträdelser som undertrycks av MED 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 MED 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.
Viktig
Utan att använda en ALTER TABLE-instruktion stöds inte uttrycken CREATE INDEX, DROP INDEX, ALTER INDEXoch PAD_INDEX för index i minnesoptimerade tabeller.
ADDERA
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.
Not
Nya kolumner läggs till efter att alla befintliga kolumner i tabellen har ändrats.
Viktig
Utan att använda en ALTER TABLE-instruktion stöds inte uttrycken CREATE INDEX, DROP INDEX, ALTER INDEXoch PAD_INDEX för index på minnesoptimerade tabeller.
PERIOD FÖR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
gäller för: SQL Server (SQL Server 2017 (14.x) och senare) 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 argumentet LÄGG TILL PERIOD FÖR SYSTEM_TIME. Konfigurera kolumnerna med datatypen datetime2 och definiera dem som INTE 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 argumentet SET SYSTEM_VERSIONING 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 i Azure SQL Database.
Från och med SQL Server 2017 (14.x) kan användarna markera en eller båda periodkolumnerna med flaggan HIDDEN för att implicit dölja dessa kolumner så att SELECT * FROM <table_name> inte returnerar något 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.
DROPPE
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.
Not
Kolumner som tas bort i transaktionsregistertabeller tas bara bort mjukt. En borttagen kolumn finns kvar i transaktionsregistret, men den markeras som en borttagen kolumn genom att ange dropped_ledger_table kolumn i sys.tables till 1. Transaktionsregistrets vy för den borttagna transaktionsregistret markeras också som borttagen genom att ange kolumnen dropped_ledger_view i sys.tables till 1. En borttagen transaktionsregistertabell, dess historiktabell och dess transaktionsregistervy byts namn genom att lägga till ett prefix (MSSQL_DroppedLedgerTable, MSSQL_DroppedLedgerHistory, MSSQL_DroppedLedgerView) och lägga till ett GUID till det ursprungliga namnet.
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 går inte att ta bort en primärnyckelbegrä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/DROP/ALTER INDEX stöds endast för minnesoptimerade tabeller.
Viktig
Utan att använda en ALTER TABLE-instruktion stöds inte uttrycken CREATE INDEX, DROP INDEX, ALTER INDEXoch PAD_INDEX för index i 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 villkoret CHECK, FOREIGN KEY, UNIQUE eller PRIMARY KEY.
- Associerad med ett standardvärde som definieras med nyckelordet DEFAULT eller som är bundet till ett standardobjekt.
- Bunden till en regel.
Not
Om du släpper en kolumn frigörs inte diskutrymmet i kolumnen. Du kan behöva frigöra diskutrymmet för en borttagen kolumn när radstorleken för en tabell är nära eller har överskridit gränsen. Frigör utrymme genom att skapa ett klustrat index i tabellen eller återskapa ett befintligt klustrat index med hjälp av ALTER INDEX-. Information om effekten av att släppa LOB-datatyper finns i den här CSS-blogginlägget.
PERIOD FÖR SYSTEM_TIME
gäller för: SQL Server (SQL Server 2016 (13.x) och senare) och Azure SQL Database.
Släpper specifikationen för de kolumner som systemet ska använda 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 (SQL Server 2008 (10.0.x) och senare) och Azure SQL Database.
Åsidosätter maximal grad av parallellitet konfigurationsalternativ endast under åtgärdens varaktighet. Mer information finns i Konfigurera den maximala graden av parallellitet serverkonfigurationsalternativ.
Använd MAXDOP-alternativet 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:
1Undertrycker parallell plangenerering.
>1Begränsar det maximala antalet processorer som används i en parallell indexåtgärd till det angivna antalet.
0(standard) Använder det faktiska antalet processorer eller färre baserat på den aktuella systemarbetsbelastningen.
Mer information finns i Konfigurera parallella indexåtgärder.
Not
Parallella indexå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.
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 AV. Du kan köra REBUILD som en ONLINE-åtgärd.
PÅ
Långsiktiga tabelllås lagras inte under indexåtgärdens varaktighet. Under huvudfasen av indexåtgärden lagras endast ett IS-lås (Intent Share) 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 PÅ 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. Vid körning hindrar SWITCH- eller återskapande åtgärder 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.
BORT
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 How Online Index Operations Work.
Not
Onlineindexåtgärder är inte tillgängliga i varje utgåva 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 (SQL Server 2008 (10.0.x) och senare) 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.
Not
I det här sammanhanget är standard inte ett nyckelord. Det är en identifierare för standardfilgruppen och måste avgränsas, som i FLYTTA TILL "standard" eller FLYTTA TILL [standard]. Om standard anges måste alternativet QUOTED_IDENTIFIER vara PÅ för den aktuella sessionen. Det här är standardinställningen. Mer information finns i SET QUOTED_IDENTIFIER.
{ CHECK | NOCHECK } VILLKOR
Anger att constraint_name är aktiverat eller inaktiverat. Det här alternativet kan endast användas med begränsningarna FOREIGN KEY och CHECK. När NOCHECK har angetts inaktiveras villkoret och framtida infogningar eller uppdateringar av kolumnen verifieras inte mot villkorsvillkoren. Standard-, PRIMÄRNYCKEL- och UNIK-begränsningar kan inte inaktiveras.
ALLA
Anger att alla begränsningar antingen är inaktiverade med alternativet NOCHECK 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.
ALLA
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 (SQL Server 2008 (10.0.x) och senare) 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 Options.
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 (SQL Server 2008 (10.0.x) och senare) och Azure SQL Database.
Anger om databasmotorn spårar, vilken ändring av spårade kolumner som har uppdaterats. Standardvärdet är AV.
VÄXLA [ PARTITION source_partition_number_expression ] TILL [ schema_name. ] target_table [ PARTITION target_partition_number_expression ]
gäller för: SQL Server (SQL Server 2008 (10.0.x) och senare) 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.
Mer information om SWITCH- begränsning 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.
begränsningar
Om båda tabellerna partitioneras identiskt, inklusive icke-illustrerade index, och måltabellen inte har några icke-grupperade 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 (SQL Server 2008 (10.0.x) och senare). Azure SQL Database stöder inte FILESTREAM.
Anger var FILESTREAM-data lagras.
ALTER TABLE med SET FILESTREAM_ON-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 en CREATE DATABASE eller ALTER DATABASE-instruktion eller ett felresultat.
"standard" anger FILESTREAM-filgruppen med standardegenskapsuppsättningen. Om det inte finns någon FILESTREAM-filgrupp resulterar 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 (SQL Server 2016 (13.x) och senare) 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, nullabilitet och primärnyckelbegränsningar för systemversionshantering uppfylls. Systemet registrerar historiken för varje post i den systemversionsbaserade tabellen i en separat historiktabell. Om argumentet HISTORY_TABLE inte används blir namnet på den här historiktabellen MSSQL_TemporalHistoryFor<primary_table_object_id>. 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 | tal {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 kolumnen 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.
- Datum
- DateTime
- DateTime2
- SmallDateTime
- DateTimeOffset
RETENTION_PERIOD = { INFINITE | tal {DAY | DAYS | VECKA | VECKOR | MÅNAD | MÅNADER | ÅR | ÅR }}
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 (SQL Server 2008 (10.0.x) och senare) och Azure SQL Database.
Anger de tillåtna metoderna för låseskalering för en tabell.
BIL
Med det här alternativet kan SQL Server Database Engine välja den låseskaleringskornighet som är lämplig för tabellschemat.
- Om tabellen partitioneras 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 TABELLkornigheten.
BORD
Låseskalering utförs på tabellnivåskornighet oavsett om tabellen är partitionerad eller inte partitionerad. TABLE är standardvärdet.
INAKTIVERA
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.
ÅTERUPPBYGGA
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 återskapar alternativet REBUILD 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 (SQL Server 2008 (10.0.x) och senare) 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 anges 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 (SQL Server 2008 (10.0.x) och senare) och Azure SQL Database.
Anger datakomprimeringsalternativet för den angivna tabellen, partitionsnumret eller partitionsintervallet. Alternativen är följande:
INGEN tabell eller angivna partitioner komprimeras inte. Det här alternativet gäller inte för kolumnlagringstabeller.
ROW-tabell eller angivna partitioner komprimeras med hjälp av radkomprimering. Det här alternativet gäller inte för kolumnlagringstabeller.
PAGE-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 (SQL Server 2014 (12.x) och senare) 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 (SQL Server 2014 (12.x) och senare) och Azure SQL Database.
Gäller endast för columnstore-tabeller, som är tabeller som lagras med ett grupperat columnstore-index. COLUMNSTORE_ARCHIVE komprimerar den angivna partitionen ytterligare 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 tillåts inte i SQL-databasen i 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:
PÅ
Kolumner som använder XML- datatyp komprimeras.
BORT
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 AV. Du kan köra REBUILD som en ONLINE-åtgärd.
PÅ
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.
Not
Återskapa onlineindex kan ange de low_priority_lock_wait alternativ som beskrivs senare i det här avsnittet.
BORT
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 (SQL Server 2008 (10.0.x) och senare) 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 (SQL Server 2012 (11.x) och senare).
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 (SQL Server 2012 (11.x) och senare). 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 (SQL Server 2017 (14.x) och senare).
Aktiverar eller inaktiverar Stretch Database för en tabell. Mer information finns i Stretch Database.
Viktig
Stretch Database är inaktuell i SQL Server 2022 (16.x) och Azure SQL Database. Den här funktionen tas bort i en framtida version av databasmotorn. Undvik att använda den här funktionen i nytt utvecklingsarbete och planera att ändra program som för närvarande använder den här funktionen.
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.
Förutsättningar. 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.
behörigheter. 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 också ALTER-behörigheter i 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 (SQL Server 2017 (14.x) och senare).
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.
Viktig
Om du anger ett filterpredikat som fungerar dåligt presterar även datamigreringen dåligt. Stretch Database tillämpar filterpredikatet på tabellen med hjälp av CROSS APPLY-operatorn.
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 (SQL Server 2017 (14.x) och senare).
Ange
OUTBOUNDför att migrera data från SQL Server till Azure.Ange
INBOUNDfö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
PAUSEDfö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 (SQL Server 2014 (12.x) och senare) 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 onlineindexet 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 VÄNTA MED LÅG PRIORITET är samma som WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).
MAX_DURATION = tid [MINUTER ]
gäller för: SQL Server (SQL Server 2014 (12.x) och senare) 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 tid körs en av de ABORT_AFTER_WAIT åtgärderna. MAX_DURATION tiden är alltid i minuter och du kan utelämna ordet MINUTER.
ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]
gäller för: SQL Server (SQL Server 2014 (12.x) och senare) och Azure SQL Database.
INGEN
Fortsätt att vänta på låset med normal (vanlig) prioritet.
SJÄLV
Avsluta SWITCH- eller onlineindexet återskapa DDL-åtgärden som för närvarande körs utan att vidta några åtgärder.
BLOCKERARE
Avsluta alla användartransaktioner som för närvarande blockerar SWITCH- eller onlineindexet återskapaR DDL-åtgärden så att åtgärden kan fortsätta.
Kräver ÄNDRA ALLA behörigheter för ANSLUTNING.
OM FINNS
gäller för: SQL Server (SQL Server 2016 (13.x) och senare) 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.
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åtgärd för tilläggsbegränsning 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 återupptabara ALTER TABLE ADD CONSTRAINT åtgärder finns i tabellen Återupptas lägga till begränsningar.
Anmärkningar
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 att de ska kompileras om vid nästa körning.
I SQL-databasen i Microsoft Fabric kan vissa tabellfunktioner skapas men kommer inte att speglas i Fabric OneLake-. Mer information finns i begränsningar för sql-databasspegling av infrastrukturresurser.
Ä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. Använd ALTER COLUMN-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 varchar, nvarchareller varbinary datatyp 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 implementerar omedelbart. Om ändringarna kräver ändringar av raderna i tabellen uppdaterar ALTER TABLE 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 försiktighet som någon INSERT-, UPDATE- eller 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.
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
Från och med SQL Server 2012 (11.x) Enterprise Edition ä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 FÖR LÄGG TILL KOLUMN. 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 det konstanta uttrycket "Mina tillfälliga 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 (SCH-M) lås 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 UPDATE-instruktionen) eller om tabellen eller klustrade index återskapas.
Kolumner av typen varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, bild, hierarchyid, geometri, geografieller CLR UDTS, 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-utgåvan och senare versioner bestäms antalet processorer som används för att köra en enskild ALTER TABLE ADD-instruktion (indexbaserad) CONSTRAINT eller DROP (klustrat index) AV maximal grad av parallellitet konfigurationsalternativet 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 Konfigurera den maximala graden av parallellitet serverkonfigurationsalternativ.
Partitionerade tabeller
Förutom att utföra SWITCH-åtgärder som omfattar partitionerade tabeller använder du ALTER TABLE för att ändra status för kolumner, begränsningar och utlösare för en partitionerad tabell precis som den används 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 ALTER TABLE-instruktionen kräver att du ändrar en kolumn som används i en schemabunden vy 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 har skapats med CREATE INDEX måste tas bort med DROP INDEX. Använd ALTER INDEX-instruktionen 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 FLYTTA TILL. Alternativet FLYTTA TILL har följande begränsningar:
- FLYTTA TILL är inte giltigt för indexerade vyer eller icke-illustrerade index.
- Partitionsschemat eller filgruppen måste redan finnas.
- Om FLYTTA TILL inte har angetts finns tabellen i samma partitionsschema eller filgrupp som definierades för det klustrade indexet.
När du släpper ett grupperat index anger du alternativet ONLINE **=** ON så att DROP INDEX-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 grupperade index som också är inaktiverade. Inaktiverade index måste tas bort med hjälp av ONLINE = OFF.
- Endast ett index i taget kan tas bort.
- ONLINE-= PÅ är inte giltigt för indexerade vyer, icke-grupperade index eller index i lokala temporära tabeller.
- ONLINE = ON är inte giltigt för kolumnlagringsindex.
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.
Not
Alternativen som anges under <drop_clustered_constraint_option> gäller för klustrade index i tabeller och kan inte tillämpas på klustrade index i vyer eller icke-grupperade index.
Replikera schemaändringar
När du kör ALTER TABLE i 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 kommer återskapandeåtgärden för ONLINE-läget att vara enkeltrådad. Använd OFFLINE-läge 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.
Ta bort NTEXT-kolumner
När du släpper kolumner med den inaktuella NTEXT-datatypensker rensningen av borttagna data som en serialiserad åtgärd på alla rader. Rensningen kan kräva mycket tid. När du släpper en NTEXT-kolumn i en tabell med många rader uppdaterar du NTEXT-kolumnen till NULL-värde 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. Med alternativet WAIT_AT_LOW_PRIORITY kan dbas 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
ALTER TABLE-instruktionen 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.
Behörigheter
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 kolumner i ALTER TABLE-instruktionen så att de har en användardefinierad typ av eller aliasdatatyp för common language runtime (CLR), krävs behörigheten REFERENSER 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 INTE NULL- kolumn med ett standardvärde eller lägga till en identitetskolumn när tabellen inte är tom.
Exempel
| Kategori | Aktuella syntaxelement |
|---|---|
| Lägga till kolumner och begränsningar | LÄGG TILL * PRIMÄRNYCKEL med indexalternativ * glesa kolumner och kolumnuppsättningar * |
| Ta bort kolumner och begränsningar | DROPPE |
| Ändra en kolumndefinition | ändra datatyp * ändra kolumnstorlek * sortering |
| Ändra en tabelldefinition | DATA_COMPRESSION * VÄXLA PARTITION * LÅSESKALERING * ändringsspårning |
| Inaktivera och aktivera begränsningar och utlösare | KONTROLLERA * INGEN KONTROLL * AKTIVERA UTLÖSARE * INAKTIVERA UTLÖSARE |
| Online-åtgärder | UPPKOPPLAD |
| Systemversioner | SYSTEM_VERSIONING |
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 STANDARD-definition. I den nya kolumnen har varje rad NULL.
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
EXEC 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
EXEC 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
-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,
-- Add a column that references another column in the same table.
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),
-- Add a column with a constraint to enforce that
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO
F. 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 NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO
G. Skapa en PRIMARY KEY-begränsning med index- eller datakomprimeringsalternativ
I följande exempel skapas villkoret PRIMÄRNYCKEL PK_TransactionHistoryArchive_TransactionID och alternativen FILLFACTOR, ONLINEoch PAD_INDEXanges. Det resulterande klustrade indexet har samma namn som villkoret.
gäller för: SQL Server 2008 (10.0.x) och senare och Azure SQL Database.
USE AdventureWorks;
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 AdventureWorks;
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
Jag. 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 ONLINE inställt på ON.
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 en 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 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 kolumnhuvudnyckeln 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 2008 (10.0.x) och senare och Azure SQL Database.
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 2008 (10.0.x) och senare och Azure SQL Database.
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 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 alternativet COLUMNSTORE_ARCHIVE. När data återställs fortsätter de att komprimeras med kolumnlagringskomprimering som används för alla columnstore-tabeller.
gäller för: SQL Server 2014 (12.x) och senare 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å TABELLnivå.
gäller för: SQL Server 2008 (10.0.x) och senare 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 2008 (10.0.x) och senare och Azure SQL Database.
USE AdventureWorks;
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 2008 (10.0.x) och senare.
USE AdventureWorks;
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 2008 (10.0.x) och senare och Azure SQL Database.
USE AdventureWorks;
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 will fail.
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 TRANSACTION
END ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ;
GO
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 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 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
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 att använda systemversionshantering. Mer information finns i Komma igång med System-Versioned temporala tabeller.
gäller för: SQL Server 2016 (13.x) och senare 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 här exemplet används HIDDEN-satsen för att säkerställa att det inte påverkar befintliga program som interagerar med den aktuella tabellen. Den använder också HISTORY_RETENTION_PERIOD som endast är tillgängliga i SQL Database.
--Alter non-temporal table to define periods for system versioning
ALTER TABLE InsurancePolicy
ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),
ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
DEFAULT SYSUTCDATETIME(),
ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999') ;
--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 datetime2 datatyp 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
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
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.partitions AS p
ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS s
ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
ON s.function_id = f.function_id
LEFT 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 för tabellen.
FactResellerSales.
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
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.columns AS c
ON t.object_id = c.object_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = i.data_space_id
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.
| Skifte | Har du data? | Gränsintervall |
|---|---|---|
| 1 | Ja | OrderDate < "2004-01-01" |
| 2 | Ja | "2004-01-01" <= OrderDate < "2005-01-01" |
| 3 | Ja | "2005-01-01" <= OrderDate< "2006-01-01" |
| 4 | Ja | "2006-01-01"<= OrderDate < "2007-01-01" |
| 5 | Ja | "2007-01-01" <= OrderDate |
- 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 tabellen Orders kan du ta bort den tomma partitionen genom att slå samman partitionerna 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