ALTER TABLE (Transact-SQL)
van toepassing op:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Warehouse in Microsoft Fabric
Hiermee wijzigt u een tabeldefinitie door kolommen en beperkingen te wijzigen, toe te voegen of te verwijderen. ALTER TABLE kan ook partities opnieuw toewijzen en opnieuw bouwen, of beperkingen en triggers uitschakelen en inschakelen.
Notitie
Momenteel wordt ALTER TABLE in Fabric Warehouse alleen ondersteund voor beperkingen en het toevoegen van null-kolommen. Zie syntaxis voor warehouse in Fabric.
Belangrijk
De syntaxis voor ALTER TABLE verschilt voor tabellen op basis van schijven en tabellen die zijn geoptimaliseerd voor geheugen. Gebruik de volgende koppelingen om u rechtstreeks naar het juiste syntaxisblok voor uw tabeltypen en naar de juiste syntaxisvoorbeelden te leiden:
Tabellen op basis van schijven:
Tabellen die zijn geoptimaliseerd voor geheugen
Zie Transact-SQL syntaxisconventiesvoor meer informatie over de syntaxisconventies.
Syntaxis voor tabellen op basis van schijven
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 } )
}
Notitie
Zie voor meer informatie:
Syntaxis voor tabellen die zijn geoptimaliseerd voor geheugen
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 ]
}
Syntaxis voor Azure Synapse Analytics en 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 }
}
Notitie
Serverloze SQL-pool in Azure Synapse Analytics ondersteunt alleen externe en tijdelijke tabellen.
Syntaxis voor warehouse in Fabric
-- 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
}
Argumenten
database_name
De naam van de database waarin de tabel is gemaakt.
schema_name
De naam van het schema waartoe de tabel behoort.
table_name
De naam van de tabel die moet worden gewijzigd. Als de tabel zich niet in de huidige database bevindt of zich in het schema bevindt dat eigendom is van de huidige gebruiker, moet u expliciet de database en het schema opgeven.
ALTER COLUMN
Hiermee geeft u op dat de benoemde kolom moet worden gewijzigd of gewijzigd.
De gewijzigde kolom kan niet het volgende zijn:
Een kolom met een tijdstempel gegevenstype.
De ROWGUIDCOL voor de tabel.
Een berekende kolom of wordt gebruikt in een berekende kolom.
Wordt gebruikt in statistieken die worden gegenereerd door de instructie CREATE STATISTICS. Gebruikers moeten DROP STATISTICS uitvoeren om de statistieken te verwijderen voordat ALTER COLUMN kan slagen. Voer deze query uit om alle door de gebruiker gemaakte statistieken en statistiekenkolommen voor een tabel op te halen.
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>');Notitie
Statistieken die automatisch door de queryoptimalisatie worden gegenereerd, worden automatisch verwijderd door ALTER COLUMN.
Wordt gebruikt in de beperking PRIMAIRE SLEUTEL of [REFERERENDE SLEUTEL] VERWIJZINGEN.
Wordt gebruikt in een CHECK- of UNIQUE-beperking. Het wijzigen van de lengte van een kolom met variabele lengte die wordt gebruikt in een CHECK- of UNIQUE-beperking is echter toegestaan.
Gekoppeld aan een standaarddefinitie. De lengte, precisie of schaal van een kolom kan echter worden gewijzigd als het gegevenstype niet wordt gewijzigd.
Het gegevenstype van tekst, ntexten afbeelding kolommen kunnen alleen op de volgende manieren worden gewijzigd:
- tekstvarchar(max), nvarchar(max)of xml-
- ntextvarchar(max), nvarchar(max)of xml-
- afbeeldingvarbinary(max)
Sommige wijzigingen in het gegevenstype kunnen een wijziging in de gegevens veroorzaken. Als u bijvoorbeeld een kolom nchar of nvarchar wijzigt in teken of varchar, kan dit leiden tot de conversie van uitgebreide tekens. Zie CAST en CONVERTvoor meer informatie. Het verminderen van de precisie of schaal van een kolom kan leiden tot afkapping van gegevens.
Notitie
Het gegevenstype van een kolom van een gepartitioneerde tabel kan niet worden gewijzigd.
Het gegevenstype van kolommen in een index kan niet worden gewijzigd, tenzij de kolom een varchar, nvarcharof varbinaire gegevenstype is en de nieuwe grootte gelijk is aan of groter is dan de oude grootte.
Een kolom die is opgenomen in een primaire-sleutelbeperking, kan niet worden gewijzigd van NOT NULL- in NULL-.
Wanneer u Always Encrypted (zonder beveiligde enclaves) gebruikt, kunt u het gegevenstype wijzigen in een compatibel gegevenstype (zoals INT naar BIGINT), maar u kunt geen versleutelingsinstellingen wijzigen.
Wanneer u Always Encrypted gebruikt met beveiligde enclaves, kunt u elke versleutelingsinstelling wijzigen als de kolomversleutelingssleutel (en de nieuwe kolomversleutelingssleutel, als u de sleutel wijzigt), enclaveberekeningen ondersteunen (versleuteld met kolomhoofdsleutels met enclave). Zie Always Encrypted met beveiligde enclavesvoor meer informatie.
Wanneer u een kolom wijzigt, houdt de database-engine elke wijziging bij door een rij in een systeemtabel toe te voegen en de vorige kolomwijziging als een verwijderde kolom te markeren. In het zeldzame geval dat u een kolom te vaak wijzigt, kan de database-engine de limiet voor de recordgrootte bereiken. Als dit gebeurt, krijgt u een foutmelding 511 of 1708. U kunt deze fouten voorkomen door de geclusterde index periodiek opnieuw op te bouwen in de tabel of het aantal kolomwijzigingen te verminderen.
column_name
De naam van de kolom die moet worden gewijzigd, toegevoegd of verwijderd. De column_name maximum is 128 tekens. Voor nieuwe kolommen kunt u column_name weglaten voor kolommen die zijn gemaakt met een tijdstempel gegevenstype. De naam tijdstempel wordt gebruikt als u geen column_name opgeeft voor een tijdstempelkolom gegevenstype.
Notitie
Nieuwe kolommen worden toegevoegd nadat alle bestaande kolommen in de tabel zijn gewijzigd.
[ type_schema_name. ] type_name
Het nieuwe gegevenstype voor de gewijzigde kolom of het gegevenstype voor de toegevoegde kolom. U kunt geen type_name opgeven voor bestaande kolommen met gepartitioneerde tabellen. type_name kan een van de volgende typen zijn:
- Een SQL Server-systeemgegevenstype.
- Een aliasgegevenstype op basis van een sql Server-systeemgegevenstype. U maakt aliasgegevenstypen met de instructie CREATE TYPE voordat deze kunnen worden gebruikt in een tabeldefinitie.
- Een door de gebruiker gedefinieerd .NET Framework-type en het schema waartoe het behoort. U maakt door de gebruiker gedefinieerde typen met de instructie CREATE TYPE voordat deze kunnen worden gebruikt in een tabeldefinitie.
Hier volgen criteria voor type_name van een gewijzigde kolom:
- Het vorige gegevenstype moet impliciet worden omgezet in het nieuwe gegevenstype.
- type_name kan niet worden tijdstempel.
- ANSI_NULL standaardinstellingen zijn altijd ingeschakeld voor ALTER COLUMN; als deze niet is opgegeven, kan de kolom null worden.
- ANSI_PADDING opvulling is altijd INGESCHAKELD voor ALTER COLUMN.
- Als de gewijzigde kolom een identiteitskolom is, moet new_data_type een gegevenstype zijn dat ondersteuning biedt voor de identiteitseigenschap.
- De huidige instelling voor SET ARITHABORT wordt genegeerd. ALTER TABLE werkt alsof ARITHABORT is ingesteld op AAN.
Notitie
Als de component COLLATE niet is opgegeven, zorgt het wijzigen van het gegevenstype van een kolom ervoor dat een sortering wordt gewijzigd in de standaardsortering van de database.
precisie
De precisie voor het opgegeven gegevenstype. Zie Precisie, Schaal en Lengtevoor meer informatie over geldige precisiewaarden.
schalen
De schaal voor het opgegeven gegevenstype. Zie Precisie, Schaal en Lengtevoor meer informatie over geldige schaalwaarden.
Max
Is alleen van toepassing op de varchar, nvarcharen varbinary gegevenstypen voor het opslaan van 2^31-1 bytes aan tekens, binaire gegevens en Unicode-gegevens.
xml_schema_collection
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
Is alleen van toepassing op het xml- gegevenstype voor het koppelen van een XML-schema aan het type. Voordat u een xml- kolom naar een schemaverzameling typt, maakt u eerst de schemaverzameling in de database met behulp van XML-SCHEMAVERZAMELING maken.
< COLLATION_NAME> SORTEREN
Hiermee geeft u de nieuwe sortering voor de gewijzigde kolom. Als deze niet is opgegeven, wordt aan de kolom de standaardsortering van de database toegewezen. De sorteringsnaam kan een Windows-sorteringsnaam of een SQL-sorteringsnaam zijn. Zie Windows-sorteringsnaam en SQL Server-sorteringsnaamvoor een lijst en meer informatie.
De COLLATE-component wijzigt de sorteringen alleen van kolommen van het teken, varchar, ncharen nvarchar gegevenstypen. Als u de sortering van een door de gebruiker gedefinieerde aliasgegevenstypekolom wilt wijzigen, gebruikt u afzonderlijke ALTER TABLE-instructies om de kolom te wijzigen in een sql Server-systeemgegevenstype. Wijzig vervolgens de sortering en wijzig de kolom weer in een aliasgegevenstype.
ALTER COLUMN kan geen sorteringswijziging hebben als een of meer van de volgende voorwaarden bestaan:
- Als een CHECK-beperking, REFERERENDE SLEUTELbeperking of berekende kolommen verwijzen naar de kolom is gewijzigd.
- Als er een index, statistieken of volledige-tekstindex wordt gemaakt in de kolom. Statistieken die automatisch worden gemaakt in de kolom worden verwijderd als de kolomsortering wordt gewijzigd.
- Als een schemagebonden weergave of functie verwijst naar de kolom.
Zie COLLATEvoor meer informatie.
NULL | NIET NULL
Hiermee geeft u op of de kolom null-waarden kan accepteren. Kolommen waarvoor null-waarden niet zijn toegestaan, worden alleen toegevoegd met ALTER TABLE als ze een standaardwaarde hebben opgegeven of als de tabel leeg is. U kunt NOT NULL alleen opgeven voor berekende kolommen als u ook PERSISTENTED hebt opgegeven. Als de nieuwe kolom null-waarden toestaat en u geen standaardwaarde opgeeft, bevat de nieuwe kolom een null-waarde voor elke rij in de tabel. Als de nieuwe kolom null-waarden toestaat en u een standaarddefinitie met de nieuwe kolom toevoegt, kunt u MET WAARDEN de standaardwaarde opslaan in de nieuwe kolom voor elke bestaande rij in de tabel.
Als in de nieuwe kolom geen null-waarden zijn toegestaan en de tabel niet leeg is, moet u een STANDAARDdefinitie toevoegen aan de nieuwe kolom. En de nieuwe kolom wordt automatisch geladen met de standaardwaarde in de nieuwe kolommen in elke bestaande rij.
U kunt NULL opgeven in ALTER COLUMN om af te dwingen dat een NOT NULL-kolom null-waarden toestaat, met uitzondering van kolommen in PRIMAIRE SLEUTELbeperkingen. U kunt NOT NULL alleen opgeven in ALTER COLUMN als de kolom geen null-waarden bevat. De null-waarden moeten worden bijgewerkt naar een bepaalde waarde voordat de ALTER COLUMN NOT NULL is toegestaan, bijvoorbeeld:
UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL;
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL;
Wanneer u een tabel maakt of wijzigt met de instructies CREATE TABLE of ALTER TABLE, beïnvloeden de database- en sessie-instellingen de null-waarde van het gegevenstype dat wordt gebruikt in een kolomdefinitie. Zorg ervoor dat u altijd expliciet een kolom definieert als NULL of NOT NULL voor niet-gecomputeerde kolommen.
Als u een kolom met een door de gebruiker gedefinieerd gegevenstype toevoegt, moet u ervoor zorgen dat u de kolom met dezelfde null-waarde definieert als het door de gebruiker gedefinieerde gegevenstype. En geef een standaardwaarde op voor de kolom. Zie CREATE TABLEvoor meer informatie.
Notitie
Als NULL of NIET NULL is opgegeven met ALTER COLUMN, moet new_data_type [(precisie [, schaal ])] ook worden opgegeven. Als het gegevenstype, de precisie en de schaal niet worden gewijzigd, geeft u de huidige kolomwaarden op.
[ {ADD | DROP} ROWGUIDCOL ]
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
Hiermee geeft u op dat de eigenschap ROWGUIDCOL wordt toegevoegd aan of verwijderd uit de opgegeven kolom. ROWGUIDCOL geeft aan dat de kolom een rij-GUID-kolom is. U kunt slechts één uniqueidentifier instellen kolom per tabel als de kolom ROWGUIDCOL. En u kunt de eigenschap ROWGUIDCOL alleen toewijzen aan een uniqueidentifier kolom. U kunt ROWGUIDCOL niet toewijzen aan een kolom van een door de gebruiker gedefinieerd gegevenstype.
ROWGUIDCOL dwingt geen uniekheid af van de waarden die zijn opgeslagen in de kolom en genereert niet automatisch waarden voor nieuwe rijen die in de tabel worden ingevoegd. Als u unieke waarden voor elke kolom wilt genereren, gebruikt u de functie NEWID of NEWSEQUENTIALID in INSERT-instructies. Of geef de functie NEWID of NEWSEQUENTIALID op als de standaardwaarde voor de kolom.
[ {ADD | DROP} PERSISTENT ]
Hiermee geeft u op dat de eigenschap PERSISTENTED wordt toegevoegd aan of verwijderd uit de opgegeven kolom. De kolom moet een berekende kolom zijn die is gedefinieerd met een deterministische expressie. Voor kolommen die zijn opgegeven als PERSISTENTED, worden de berekende waarden fysiek opgeslagen in de tabel en worden de waarden bijgewerkt wanneer andere kolommen waarop de berekende kolom afhankelijk is, worden bijgewerkt. Door een berekende kolom als PERSISTENTED te markeren, kunt u indexen maken voor berekende kolommen die zijn gedefinieerd op expressies die deterministisch zijn, maar niet nauwkeurig. Zie Indexen voor berekende kolommenvoor meer informatie.
SET QUOTED_IDENTIFIER moet aan zijn wanneer u indexen maakt of wijzigt in berekende kolommen of geïndexeerde weergaven. Zie set QUOTED_IDENTIFIER (Transact-SQL)voor meer informatie.
Elke berekende kolom die wordt gebruikt als een partitioneringskolom van een gepartitioneerde tabel, moet expliciet worden gemarkeerd als PERSISTENTED.
NEERZETTEN NIET VOOR REPLICATIE
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
Hiermee geeft u op dat waarden worden verhoogd in identiteitskolommen wanneer replicatieagenten invoegbewerkingen uitvoeren. U kunt deze component alleen opgeven als column_name een identiteitskolom is.
SCHAARS
Geeft aan dat de kolom een sparse-kolom is. De opslag van sparsekolommen is geoptimaliseerd voor null-waarden. U kunt geen sparse kolommen instellen als NOT NULL. Wanneer u een kolom converteert van sparse naar niet-parseren of van niet-parseren naar sparse, wordt met deze optie de tabel vergrendeld voor de duur van de uitvoering van de opdracht. Mogelijk moet u de COMPONENT REBUILD gebruiken om ruimtebesparingen vrij te maken. Zie Sparse-kolommen gebruikenvoor aanvullende beperkingen en meer informatie over sparsekolommen.
ADD MASKED WITH (FUNCTION = ' mask_function ')
van toepassing op: SQL Server (SQL Server 2016 (13.x) en hoger) en Azure SQL Database.
Hiermee geeft u een dynamisch gegevensmasker. mask_function is de naam van de maskeringsfunctie met de juiste parameters. Er zijn drie functies beschikbaar:
- default()
- email()
- partial()
- random()
Vereist ALTER ANY MASK-machtiging.
Als u een masker wilt verwijderen, gebruikt u DROP MASKED. Zie Dynamische gegevensmaskeringvoor functieparameters.
Een masker toevoegen en neerzetten vereist ALTER ANY MASK machtiging.
WITH ( ONLINE = AAN | UIT) <van toepassing op het wijzigen van een kolom>
van toepassing op: SQL Server (SQL Server 2016 (13.x) en hoger) en Azure SQL Database.
Hiermee kunnen veel wijzigingskolomacties worden uitgevoerd terwijl de tabel beschikbaar blijft. De standaardwaarde is UITGESCHAKELD. U kunt de kolom online wijzigen voor kolomwijzigingen met betrekking tot gegevenstype, kolomlengte of precisie, nullabiliteit, parsiteit en sortering.
Met online alter column kunnen gebruikers gemaakte en autostatistics verwijzen naar de gewijzigde kolom voor de duur van de bewerking ALTER COLUMN, waardoor query's op de gebruikelijke manier kunnen worden uitgevoerd. Aan het einde van de bewerking worden autostatsen die verwijzen naar de kolom verwijderd en worden door de gebruiker gemaakte statistieken ongeldig gemaakt. De gebruiker moet door de gebruiker gegenereerde statistieken handmatig bijwerken nadat de bewerking is voltooid. Als de kolom deel uitmaakt van een filterexpressie voor statistieken of indexen, kunt u geen wijzigingskolombewerking uitvoeren.
- Terwijl de online bewerking voor het wijzigen van kolommen wordt uitgevoerd, kunnen alle bewerkingen die afhankelijk zijn van de kolom (index, weergaven, enzovoort) blokkeren of mislukken met een geschikte fout. Dit gedrag garandeert dat de online-wijzigingskolom niet mislukt vanwege afhankelijkheden die zijn geïntroduceerd tijdens het uitvoeren van de bewerking.
- Het wijzigen van een kolom van NOT NULL in NULL wordt niet ondersteund als een onlinebewerking wanneer naar de gewijzigde kolom wordt verwezen door niet-geclusterde indexen.
- Online ALTER wordt niet ondersteund wanneer naar de kolom wordt verwezen door een controlebeperking en de bewerking ALTER de precisie van de kolom (numeriek of datum/tijd) beperkt.
- De optie
WAIT_AT_LOW_PRIORITYkan niet worden gebruikt met een online wijzigende kolom. -
ALTER COLUMN ... ADD/DROP PERSISTEDwordt niet ondersteund voor online alter column. -
ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATIONwordt niet beïnvloed door een online-wijzigingskolom. - Online wijzigen kolom biedt geen ondersteuning voor het wijzigen van een tabel waarbij wijzigingen bijhouden is ingeschakeld of dat is een uitgever van samenvoegreplicatie.
- Online alter column biedt geen ondersteuning voor het wijzigen van of naar CLR-gegevenstypen.
- De online-wijzigingskolom biedt geen ondersteuning voor het wijzigen van een XML-gegevenstype dat een andere schemaverzameling heeft dan de huidige schemaverzameling.
- Bij online wijzigen van kolommen worden de beperkingen voor wanneer een kolom kan worden gewijzigd, niet beperkt. Verwijzingen per index/statistieken, enzovoort, kunnen ertoe leiden dat de wijziging mislukt.
- Online wijzigen kolom biedt geen ondersteuning voor het gelijktijdig wijzigen van meer dan één kolom.
- Online alter column heeft geen effect in een tijdelijke tabel met systeemversies. ALTER-kolom wordt niet als online uitgevoerd, ongeacht welke waarde is opgegeven voor de optie ONLINE.
Online alter column heeft vergelijkbare vereisten, beperkingen en functionaliteit als het opnieuw samenstellen van online indexen, waaronder:
- Het opnieuw opbouwen van online indexen wordt niet ondersteund wanneer de tabel verouderde LOB- of filestreamkolommen bevat of wanneer de tabel een columnstore-index heeft. Dezelfde beperkingen gelden voor online alter column.
- Een bestaande kolom die wordt gewijzigd, vereist twee keer de ruimtetoewijzing, voor de oorspronkelijke kolom en voor de zojuist gemaakte verborgen kolom.
- De vergrendelingsstrategie tijdens een bewerking voor het online wijzigen van kolommen volgt hetzelfde vergrendelingspatroon dat wordt gebruikt voor het bouwen van online indexen.
MET CONTROLE | MET NOCHECK
Hiermee geeft u op of de gegevens in de tabel zijn of niet worden gevalideerd op basis van een nieuw toegevoegde of opnieuw ingeschakelde REFERERENDE SLEUTEL of CHECK-beperking. Als u dit niet opgeeft, wordt MET CHECK ervan uitgegaan dat er nieuwe beperkingen gelden en DAT NOCHECK wordt gebruikt voor beperkingen die opnieuw zijn ingeschakeld.
Als u geen nieuwe beperkingen voor CHECK of REFERERENDE SLEUTELS wilt controleren op basis van bestaande gegevens, gebruikt u WITH NOCHECK. We raden u niet aan dit te doen, behalve in zeldzame gevallen. De nieuwe beperking wordt geëvalueerd in alle latere gegevensupdates. Eventuele schendingen van beperkingen die worden onderdrukt door WITH NOCHECK wanneer de beperking wordt toegevoegd, kunnen ervoor zorgen dat toekomstige updates mislukken als ze rijen bijwerken met gegevens die niet voldoen aan de beperking. De queryoptimalisatie beschouwt geen beperkingen die zijn gedefinieerd MET NOCHECK. Dergelijke beperkingen worden genegeerd totdat ze worden ingeschakeld met behulp van ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL. Zie Beperkingen voor refererende sleutels uitschakelen met INSERT- en UPDATE-instructiesvoor meer informatie.
ALTER INDEX index_name
Hiermee geeft u op dat het aantal buckets voor index_name moet worden gewijzigd of gewijzigd.
De syntaxis ALTER TABLE ... ADD/DROP/ALTER INDEX wordt alleen ondersteund voor tabellen die zijn geoptimaliseerd voor geheugen.
Belangrijk
Zonder een ALTER TABLE-instructie te gebruiken, worden de instructies CREATE INDEX, DROP INDEX, ALTER INDEXen PAD_INDEX niet ondersteund voor indexen in tabellen die zijn geoptimaliseerd voor geheugen.
TOEVOEGEN
Hiermee geeft u op dat een of meer kolomdefinities, berekende kolomdefinities of tabelbeperkingen worden toegevoegd. Of de kolommen die door het systeem worden gebruikt voor systeemversiebeheer, worden toegevoegd. Voor tabellen die zijn geoptimaliseerd voor geheugen, kunt u een index toevoegen.
Notitie
Nieuwe kolommen worden toegevoegd nadat alle bestaande kolommen in de tabel zijn gewijzigd.
Belangrijk
Zonder een ALTER TABLE-instructie te gebruiken, worden de instructies CREATE INDEX, DROP INDEX, ALTER INDEXen PAD_INDEX niet ondersteund voor indexen in tabellen die zijn geoptimaliseerd voor geheugen.
PERIODE VOOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
van toepassing op: SQL Server (SQL Server 2017 (14.x) en hoger) en Azure SQL Database.
Hiermee geeft u de namen op van de kolommen die het systeem gebruikt om de periode vast te leggen waarvoor een record geldig is. U kunt bestaande kolommen opgeven of nieuwe kolommen maken als onderdeel van het argument ADD PERIOD FOR SYSTEM_TIME. Stel de kolommen in met het gegevenstype datetime2 en definieer ze als NOT NULL. Als u een puntkolom definieert als NULL, wordt er een fout weergegeven. U kunt een column_constraint en/of Standaardwaarden opgeven voor kolommen voor de system_start_time en system_end_time kolommen. Zie voorbeeld A in de volgende systeemversiebeheer voorbeelden die laten zien hoe u een standaardwaarde gebruikt voor de kolom system_end_time.
Gebruik dit argument met het argument SET SYSTEM_VERSIONING om een bestaande tabel een tijdelijke tabel te maken. Zie Tijdelijke tabellen en Aan de slag met tijdelijke tabellen in Azure SQL Databasevoor meer informatie.
Vanaf SQL Server 2017 (14.x) kunnen gebruikers een of beide puntkolommen markeren met vlag VERBORGEN om deze kolommen impliciet te verbergen, zodat SELECT * FROM <table_name> geen waarde retourneert voor de kolommen. Standaard worden puntkolommen niet verborgen. Om te kunnen worden gebruikt, moeten verborgen kolommen expliciet worden opgenomen in alle query's die rechtstreeks naar de tijdelijke tabel verwijzen.
DRUPPEL
Hiermee geeft u op dat een of meer kolomdefinities, berekende kolomdefinities of tabelbeperkingen worden verwijderd of om de specificatie te verwijderen voor de kolommen die door het systeem worden gebruikt voor systeemversiebeheer.
Notitie
Kolommen die in grootboektabellen worden verwijderd, worden alleen voorlopig verwijderd. Een verwijderde kolom blijft in de grootboektabel staan, maar wordt gemarkeerd als een verwijderde kolom door de kolom dropped_ledger_table in sys.tables in te stellen op 1. De grootboekweergave van de tabel met verwijderde grootboekgegevens wordt ook gemarkeerd als verwijderd door de kolom dropped_ledger_view in sys.tables in te stellen op 1. De naam van een verwijderde grootboektabel, de geschiedenistabel en de grootboekweergave wordt gewijzigd door een voorvoegsel (MSSQL_DroppedLedgerTable, MSSQL_DroppedLedgerHistory, MSSQL_DroppedLedgerView) toe te voegen en een GUID toe te voegen aan de oorspronkelijke naam.
CONSTRAINT-constraint_name
Hiermee geeft u op dat constraint_name uit de tabel wordt verwijderd. Er kunnen meerdere beperkingen worden vermeld.
U kunt de door de gebruiker gedefinieerde of door het systeem opgegeven naam van de beperking bepalen door een query uit te voeren op de sys.check_constraint, sys.default_constraints, sys.key_constraintsen sys.foreign_keys catalogusweergaven.
Een BEPERKING PRIMAIRE SLEUTEL kan niet worden verwijderd als er een XML-index in de tabel bestaat.
INDEX-index_name
Hiermee geeft u op dat index_name uit de tabel wordt verwijderd.
De syntaxis ALTER TABLE ... ADD/DROP/ALTER INDEX wordt alleen ondersteund voor tabellen die zijn geoptimaliseerd voor geheugen.
Belangrijk
Zonder een ALTER TABLE-instructie te gebruiken, worden de instructies CREATE INDEX, DROP INDEX, ALTER INDEXen PAD_INDEX niet ondersteund voor indexen in tabellen die zijn geoptimaliseerd voor geheugen.
KOLOM column_name
Hiermee geeft u op dat constraint_name of column_name uit de tabel wordt verwijderd. Er kunnen meerdere kolommen worden weergegeven.
Een kolom kan niet worden verwijderd wanneer dit het volgende is:
- Wordt gebruikt in een index, hetzij als sleutelkolom of als INCLUDE
- Wordt gebruikt in een BEPERKING CHECK, FOREIGN KEY, UNIQUE of PRIMARY KEY.
- Gekoppeld aan een standaardwaarde die is gedefinieerd met het trefwoord DEFAULT of gebonden aan een standaardobject.
- Gebonden aan een regel.
Notitie
Als u een kolom verwijdert, wordt de schijfruimte van de kolom niet vrijgemaakt. Mogelijk moet u de schijfruimte van een verwijderde kolom vrijmaken wanneer de rijgrootte van een tabel bijna is bereikt of de limiet ervan is overschreden. Maak ruimte vrij door een geclusterde index in de tabel te maken of een bestaande geclusterde index opnieuw te bouwen met behulp van ALTER INDEX. Zie dit CSS-blogberichtvoor informatie over de impact van het verwijderen van LOB-gegevenstypen.
PERIODE VOOR SYSTEM_TIME
van toepassing op: SQL Server (SQL Server 2016 (13.x) en hoger) en Azure SQL Database.
Hiermee wordt de specificatie verwijderd voor de kolommen die door het systeem worden gebruikt voor systeemversiebeheer.
WITH <drop_clustered_constraint_option>
Hiermee geeft u op dat een of meer opties voor gegroepeerde beperkingen zijn ingesteld.
MAXDOP = max_degree_of_parallelism
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
Overschrijft de maximale mate van parallelle uitvoering configuratieoptie alleen voor de duur van de bewerking. Zie De maximale mate van parallelle configuratie van server configurerenvoor meer informatie.
Gebruik de optie MAXDOP om het aantal processors dat wordt gebruikt in parallelle uitvoering van het plan te beperken. Het maximum is 64 processors.
max_degree_of_parallelism kan een van de volgende waarden zijn:
1Onderdrukt het genereren van parallelle plannen.
>1Hiermee beperkt u het maximum aantal processors dat in een parallelle indexbewerking wordt gebruikt tot het opgegeven getal.
0(standaard) Gebruikt het werkelijke aantal processors of minder op basis van de huidige systeemworkload.
Zie Parallelle indexbewerkingen configurerenvoor meer informatie.
Notitie
Parallelle indexbewerkingen zijn niet beschikbaar in elke editie van SQL Server. Zie Edities en ondersteunde functies van SQL Server 2022voor meer informatie.
ONLINE = { ON | OFF } <van toepassing op drop_clustered_constraint_option>
Hiermee geeft u op of onderliggende tabellen en bijbehorende indexen beschikbaar zijn voor query's en gegevenswijziging tijdens de indexbewerking. De standaardwaarde is UITGESCHAKELD. U kunt REBUILD uitvoeren als onlinebewerking.
OP
Langetermijntabelvergrendelingen worden niet bewaard voor de duur van de indexbewerking. Tijdens de hoofdfase van de indexbewerking wordt alleen een Intent Share-vergrendeling (IS) op de brontabel bewaard. Met dit gedrag kunnen query's of updates voor de onderliggende tabel en indexen worden voortgezet. Aan het begin van de bewerking wordt een gedeelde vergrendeling (S) gedurende korte tijd op het bronobject bewaard. Aan het einde van de bewerking wordt gedurende korte tijd een S-vergrendeling (Gedeeld) verkregen op de bron als er een niet-geclusterde index wordt gemaakt. Of een SCH-M -vergrendeling (schemawijziging) wordt verkregen wanneer een geclusterde index online wordt gemaakt of verwijderd en wanneer een geclusterde of niet-geclusterde index opnieuw wordt opgebouwd. ONLINE kan niet worden ingesteld op AAN wanneer een index wordt gemaakt in een lokale tijdelijke tabel. Alleen herbouwbewerking met één thread met heap is toegestaan.
Als u de DDL wilt uitvoeren voor SWITCH- of het opnieuw samenstellen van online indexen, moeten alle actieve blokkerende transacties die in een bepaalde tabel worden uitgevoerd, worden voltooid. Bij het uitvoeren van de SWITCH- of herbouwbewerking voorkomt u dat nieuwe transacties worden gestart en kunnen deze de doorvoer van de werkbelasting aanzienlijk beïnvloeden en de toegang tot de onderliggende tabel tijdelijk vertragen.
AF
Tabelvergrendelingen zijn van toepassing voor de duur van de indexbewerking. Een offline indexbewerking waarmee een geclusterde index wordt gemaakt, opnieuw wordt opgebouwd of verwijderd, of een niet-geclusterde index wordt opgebouwd of verwijderd, wordt een schemawijziging (Sch-M) voor de tabel verkregen. Deze vergrendeling voorkomt dat alle gebruikers toegang hebben tot de onderliggende tabel voor de duur van de bewerking. Een offline indexbewerking waarmee een niet-geclusterde index wordt gemaakt, verkrijgt een gedeelde (S)-vergrendeling op de tabel. Deze vergrendeling voorkomt updates van de onderliggende tabel, maar staat leesbewerkingen toe, zoals SELECT-instructies. Herbouwbewerkingen met meerdere threads met heap zijn toegestaan.
Zie How Online Index Operations Workvoor meer informatie.
Notitie
Online indexbewerkingen zijn niet beschikbaar in elke editie van SQL Server. Zie Edities en ondersteunde functies van SQL Server 2022voor meer informatie.
MOVE TO { partition_scheme_name(column_name [ ,...n ] ) | bestandsgroep | "standaard" }
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
Hiermee geeft u een locatie op voor het verplaatsen van de gegevensrijen die zich momenteel op het bladniveau van de geclusterde index bevinden. De tabel wordt verplaatst naar de nieuwe locatie. Deze optie is alleen van toepassing op beperkingen waarmee een geclusterde index wordt gemaakt.
Notitie
In deze context is de standaardwaarde geen trefwoord. Het is een id voor de standaardbestandsgroep en moet worden gescheiden, zoals in MOVE TO "standaard" of MOVE TO [standaard]. Als 'standaard' is opgegeven, moet de optie QUOTED_IDENTIFIER zijn ingeschakeld voor de huidige sessie. Dit is de standaardinstelling. Zie SET QUOTED_IDENTIFIERvoor meer informatie.
{ CHECK | NOCHECK } CONSTRAINT
Hiermee geeft u op dat constraint_name is ingeschakeld of uitgeschakeld. Deze optie kan alleen worden gebruikt met BEPERKINGEN VOOR REFERERENDE SLEUTEL en CONTROLE. Wanneer NOCHECK is opgegeven, wordt de beperking uitgeschakeld en worden toekomstige invoegingen of updates van de kolom niet gevalideerd op basis van de beperkingsvoorwaarden. STANDAARD,PRIMAIRE SLEUTEL en UNIEKE beperkingen kunnen niet worden uitgeschakeld.
ALLE
Hiermee geeft u op dat alle beperkingen zijn uitgeschakeld met de optie NOCHECK of ingeschakeld met de optie CHECK.
{ ENABLE | DISABLE } TRIGGER
Hiermee geeft u op dat trigger_name is ingeschakeld of uitgeschakeld. Wanneer een trigger is uitgeschakeld, wordt deze nog steeds gedefinieerd voor de tabel. Wanneer insert-, UPDATE- of DELETE-instructies echter worden uitgevoerd voor de tabel, worden de acties in de trigger pas uitgevoerd als de trigger opnieuw is ingeschakeld.
ALLE
Hiermee geeft u op dat alle triggers in de tabel zijn ingeschakeld of uitgeschakeld.
trigger_name
Hiermee geeft u de naam van de trigger om uit te schakelen of in te schakelen.
{ ENABLE | UITSCHAKELEN } CHANGE_TRACKING
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
Hiermee geeft u op of het bijhouden van wijzigingen is ingeschakeld voor de tabel. Wijzigingen bijhouden is standaard uitgeschakeld.
Deze optie is alleen beschikbaar wanneer wijzigingen bijhouden is ingeschakeld voor de database. Zie ALTER DATABASE SET Optionsvoor meer informatie.
Als u wijzigingen bijhouden wilt inschakelen, moet de tabel een primaire sleutel hebben.
WITH ( TRACK_COLUMNS_UPDATED = { ON | UIT } )
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
Hiermee geeft u op of de database-engine tracks, die bijgehouden kolommen wijzigen, zijn bijgewerkt. De standaardwaarde is UIT.
SCHAKEL OVER [ PARTITIE source_partition_number_expression ] NAAR [ schema_name. ] target_table [ PARTITIE target_partition_number_expression ]
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
Hiermee schakelt u een blok gegevens op een van de volgende manieren uit:
- Alle gegevens van een tabel opnieuw toewijzen als een partitie aan een reeds bestaande gepartitioneerde tabel.
- Hiermee schakelt u een partitie van de ene gepartitioneerde tabel naar een andere.
- Alle gegevens in één partitie van een gepartitioneerde tabel opnieuw toewijzen aan een bestaande niet-gepartitioneerde tabel.
Als tabel een gepartitioneerde tabel is, moet u source_partition_number_expressionopgeven. Als target_table is gepartitioneerd, moet u target_partition_number_expressionopgeven. Wanneer u de gegevens van een tabel opnieuw toedeelt als een partitie aan een reeds bestaande gepartitioneerde tabel of een partitie van de ene gepartitioneerde tabel naar de andere overschakelt, moet de doelpartitie bestaan en moet deze leeg zijn.
Wanneer u de gegevens van één partitie opnieuw toedeelt om één tabel te vormen, moet de doeltabel al bestaan en moet deze leeg zijn. Zowel de brontabel of -partitie als de doeltabel of -partitie moeten zich in dezelfde bestandsgroep bevinden. De bijbehorende indexen, of indexpartities, moeten zich ook in dezelfde bestandsgroep bevinden. Veel extra beperkingen zijn van toepassing op het schakelen tussen partities. tabel en target_table kunnen niet hetzelfde zijn. target_table kan een id met meerdere onderdelen zijn.
Zowel source_partition_number_expression als target_partition_number_expression zijn constante expressies die kunnen verwijzen naar variabelen en functies. Dit zijn door de gebruiker gedefinieerde typevariabelen en door de gebruiker gedefinieerde functies. Ze kunnen niet verwijzen naar Transact-SQL expressies.
Een gepartitioneerde tabel met een geclusterde columnstore-index gedraagt zich als een gepartitioneerde heap:
- De primaire sleutel moet de partitiesleutel bevatten.
- Een unieke index moet de partitiesleutel bevatten. Maar, inclusief de partitiesleutel met een bestaande unieke index, kan de uniekheid wijzigen.
- Als u wilt schakelen tussen partities, moeten alle niet-geclusterde indexen de partitiesleutel bevatten.
Zie Gepartitioneerde tabellen en indexen replicerenvoor switch- beperking.
Niet-geclusterde columnstore-indexen zijn gebouwd in een alleen-lezen indeling voor SQL Server 2016 (13.x) en voor SQL Database vóór versie V12. U moet niet-geclusterde columnstore-indexen opnieuw opbouwen naar de huidige indeling (die kan worden bijgewerkt) voordat partitiebewerkingen kunnen worden uitgevoerd.
beperkingen
Als beide tabellen identiek zijn gepartitioneerd, inclusief niet-geclusterde indexen, en de doeltabel geen niet-geclusterde indexen bevat, krijgt u mogelijk een 4907-fout.
Voorbeelduitvoer:
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 | "standaard" | "NULL" })
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger). Azure SQL Database biedt geen ondersteuning voor FILESTREAM.
Hiermee geeft u op waar FILESTREAM-gegevens worden opgeslagen.
ALTER TABLE met de component SET FILESTREAM_ON slaagt alleen als de tabel geen FILESTREAM-kolommen bevat. U kunt FILESTREAM-kolommen toevoegen met behulp van een tweede ALTER TABLE-instructie.
Als u partition_scheme_nameopgeeft, zijn de regels voor CREATE TABLE van toepassing. Zorg ervoor dat de tabel al is gepartitioneerd voor rijgegevens en het bijbehorende partitieschema dezelfde partitiefunctie en kolommen gebruikt als het FILESTREAM-partitieschema.
filestream_filegroup_name geeft de naam van een FILESTREAM-bestandsgroep op. De bestandsgroep moet één bestand hebben dat is gedefinieerd voor de bestandsgroep met behulp van een CREATE DATABASE of ALTER DATABASE instructie, of een foutresultaten.
'standaard' geeft de FILESTREAM-bestandsgroep op met de eigenschap DEFAULT ingesteld. Als er geen FILESTREAM-bestandsgroep is, treedt er een fout op.
"NULL-" geeft aan dat alle verwijzingen naar FILESTREAM-bestandsgroepen voor de tabel worden verwijderd. Alle FILESTREAM-kolommen moeten eerst worden verwijderd. Gebruik SET FILESTREAM_ON = "NULL-" om alle FILESTREAM-gegevens te verwijderen die aan een tabel zijn gekoppeld.
SET ( SYSTEM_VERSIONING = { UIT | ON [ ( HISTORY_TABLE = schema_name . history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ] } )
van toepassing op: SQL Server (SQL Server 2016 (13.x) en hoger) en Azure SQL Database.
Systeemversiebeheer van een tabel wordt uitgeschakeld of ingeschakeld. Als u systeemversiebeheer van een tabel wilt inschakelen, controleert het systeem of aan de vereisten voor het gegevenstype, de beperking nullabiliteit en de primaire-sleutelbeperking voor systeemversiebeheer wordt voldaan. Het systeem registreert de geschiedenis van elke record in de systeemversietabel in een afzonderlijke geschiedenistabel. Als het argument HISTORY_TABLE niet wordt gebruikt, wordt de naam van deze geschiedenistabel MSSQL_TemporalHistoryFor<primary_table_object_id>. Als de geschiedenistabel niet bestaat, genereert het systeem een nieuwe geschiedenistabel die overeenkomt met het schema van de huidige tabel, maakt u een koppeling tussen de twee tabellen en kan het systeem de geschiedenis van elke record in de huidige tabel in de geschiedenistabel vastleggen. Als u het argument HISTORY_TABLE gebruikt om een koppeling naar een bestaande geschiedenistabel te maken en te gebruiken, maakt het systeem een koppeling tussen de huidige tabel en de opgegeven tabel. Wanneer u een koppeling naar een bestaande geschiedenistabel maakt, kunt u ervoor kiezen om een gegevensconsistentiecontrole uit te voeren. Deze controle op gegevensconsistentie zorgt ervoor dat bestaande records niet overlappen. Het uitvoeren van de controle voor gegevensconsistentie is de standaardinstelling. Gebruik het argument SYSTEM_VERSIONING = ON voor een tabel die is gedefinieerd met de PERIOD FOR SYSTEM_TIME component om de bestaande tabel een tijdelijke tabel te maken. Zie Tijdelijke tabellenvoor meer informatie.
HISTORY_RETENTION_PERIOD = { ONEINDIG | getal {DAG | DAGEN | WEEK | WEKEN | MAAND | MAANDEN | YEAR | YEARS} }
van toepassing op: SQL Server 2017 (14.x) en Azure SQL Database.
Hiermee geeft u eindige of oneindige retentie voor historische gegevens in een tijdelijke tabel. Als u dit weglaat, wordt ervan uitgegaan dat oneindige retentie wordt gebruikt.
DATA_DELETION
van toepassing op: Azure SQL Edge alleen
Hiermee schakelt u het opschonen van oude of verouderde gegevens uit tabellen in een database op basis van bewaarbeleid in. Zie Gegevensretentie in- en uitschakelenvoor meer informatie. De volgende parameters moeten worden opgegeven om gegevensretentie in te schakelen.
FILTER_COLUMN = { column_name }
Hiermee geeft u de kolom op, die moet worden gebruikt om te bepalen of de rijen in de tabel verouderd zijn of niet. De volgende gegevenstypen zijn toegestaan voor de filterkolom.
- Datum
- Datum/tijd
- DateTime2
- SmallDateTime
- DateTimeOffset
RETENTION_PERIOD = { ONEINDIG | getal {DAG | DAGEN | WEEK | WEKEN | MAAND | MAANDEN | YEAR | JAAR }}
Hiermee geeft u het bewaarperiodebeleid voor de tabel. De bewaarperiode wordt opgegeven als een combinatie van een positieve geheel getalwaarde en de datumonderdeeleenheid.
SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
Hiermee geeft u de toegestane methoden voor escalatie van vergrendeling voor een tabel.
AUTO
Met deze optie kan SQL Server Database Engine de granulariteit voor escalatievergrendeling selecteren die geschikt is voor het tabelschema.
- Als de tabel is gepartitioneerd, wordt escalatie van vergrendelingen toegestaan aan de granulariteit van de heap of B-tree (HoBT). Met andere woorden, escalatie wordt toegestaan tot het partitieniveau. Nadat de vergrendeling is geëscaleerd naar het HoBT-niveau, wordt de vergrendeling later niet geëscaleerd naar tabelgranulariteit.
- Als de tabel niet is gepartitioneerd, wordt de escalatie van de vergrendeling uitgevoerd naar de GRANulariteit van DE TABEL.
TAFEL
Escalatie van vergrendelingen wordt uitgevoerd op granulariteit op tabelniveau, ongeacht of de tabel is gepartitioneerd of niet gepartitioneerd. TABLE is de standaardwaarde.
UITSCHAKELEN
Voorkomt escalatie van vergrendeling in de meeste gevallen. Vergrendelingen op tabelniveau zijn niet volledig toegestaan. Wanneer u bijvoorbeeld een tabel scant die geen geclusterde index heeft onder het serialiseerbare isolatieniveau, moet Database Engine een tabelvergrendeling nemen om de gegevensintegriteit te beschermen.
HERBOUWEN
Gebruik de syntaxis REBUILD WITH om een hele tabel, inclusief alle partities in een gepartitioneerde tabel, opnieuw te bouwen. Als de tabel een geclusterde index heeft, wordt met de optie REBUILD de geclusterde index opnieuw opgebouwd. REBUILD kan worden uitgevoerd als onlinebewerking.
Gebruik de syntaxis REBUILD PARTITION om één partitie in een gepartitioneerde tabel opnieuw te bouwen.
PARTITION = ALL
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
Herbouwt alle partities bij het wijzigen van de partitiecompressie-instellingen.
HERBOUWEN MET ( <rebuild_option> )
Alle opties zijn van toepassing op een tabel met een geclusterde index. Als de tabel geen geclusterde index heeft, wordt de heap-structuur alleen beïnvloed door een aantal van de opties.
Wanneer een specifieke compressie-instelling niet is opgegeven met de bewerking REBUILD, wordt de huidige compressie-instelling voor de partitie gebruikt. Als u de huidige instelling wilt retourneren, voert u een query uit op de kolom data_compression in de sys.partitions catalogusweergave.
Zie ALTER TABLE index_optionvoor volledige beschrijvingen van de opties voor opnieuw opbouwen.
DATA_COMPRESSION
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
Hiermee geeft u de optie voor gegevenscompressie voor de opgegeven tabel, partitienummer of bereik van partities. De opties zijn als volgt:
NONE-tabel of opgegeven partities worden niet gecomprimeerd. Deze optie is niet van toepassing op columnstore-tabellen.
RIJtabel of opgegeven partities worden gecomprimeerd met behulp van rijcompressie. Deze optie is niet van toepassing op columnstore-tabellen.
PAGE Table of opgegeven partities worden gecomprimeerd met behulp van paginacompressie. Deze optie is niet van toepassing op columnstore-tabellen.
COLUMNSTORE
van toepassing op: SQL Server (SQL Server 2014 (12.x) en hoger) en Azure SQL Database.
Alleen van toepassing op columnstore-tabellen. COLUMNSTORE geeft aan om een partitie te decomprimeren die is gecomprimeerd met de optie COLUMNSTORE_ARCHIVE. Wanneer de gegevens worden hersteld, worden deze gecomprimeerd met de columnstore-compressie die wordt gebruikt voor alle columnstore-tabellen.
COLUMNSTORE_ARCHIVE
van toepassing op: SQL Server (SQL Server 2014 (12.x) en hoger) en Azure SQL Database.
Is alleen van toepassing op columnstore-tabellen, die zijn opgeslagen met een geclusterde columnstore-index. COLUMNSTORE_ARCHIVE comprimeert de opgegeven partitie verder naar een kleinere grootte. Gebruik deze optie voor archivering of andere situaties waarvoor minder opslag is vereist en die meer tijd kunnen bieden voor opslag en ophalen.
Zie index_optionals u meerdere partities tegelijk opnieuw wilt bouwen. Als de tabel geen geclusterde index heeft, wordt de heap en de niet-geclusterde indexen opnieuw opgebouwd door de gegevenscompressie te wijzigen. Zie Gegevenscompressievoor meer informatie over compressie.
ALTER TABLE REBUILD PARTITION WITH DATA COMPRESSION = ROW of PAGE is niet toegestaan in SQL Database in Microsoft Fabric.
XML_COMPRESSION
van toepassing op: SQL Server 2022 (16.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance.
Hiermee geeft u de optie VOOR XML-compressie voor alle xml- gegevenstypekolommen in de tabel. De opties zijn als volgt:
OP
Kolommen met het xml- gegevenstype worden gecomprimeerd.
AF
Kolommen met het xml- gegevenstype worden niet gecomprimeerd.
ONLINE = { ON | OFF } <van toepassing op single_partition_rebuild_option>
Hiermee geeft u op of één partitie van de onderliggende tabellen en bijbehorende indexen beschikbaar is voor query's en gegevenswijziging tijdens de indexbewerking. De standaardwaarde is UITGESCHAKELD. U kunt REBUILD uitvoeren als onlinebewerking.
OP
Langetermijntabelvergrendelingen worden niet bewaard voor de duur van de indexbewerking. S-lock op de tabel is vereist aan het begin van de index opnieuw opbouwen en een Sch-M vergrendeling op de tabel aan het einde van de online index opnieuw opbouwen. Hoewel beide vergrendelingen korte metagegevensvergrendelingen zijn, moet de Sch-M-vergrendeling wachten tot alle blokkeringstransacties zijn voltooid. Tijdens de wachttijd blokkeert de Sch-M vergrendeling alle andere transacties die achter deze vergrendeling wachten bij het openen van dezelfde tabel.
Notitie
Het opnieuw samenstellen van online indexen kan de low_priority_lock_wait opties instellen die verderop in deze sectie worden beschreven.
AF
Tabelvergrendelingen worden toegepast voor de duur van de indexbewerking. Hiermee voorkomt u dat alle gebruikers toegang hebben tot de onderliggende tabel voor de duur van de bewerking.
COLUMN_SET_NAME XML-COLUMN_SET VOOR ALL_SPARSE_COLUMNS
van toepassing op: SQL Server (SQL Server 2008 (10.0.x) en hoger) en Azure SQL Database.
De naam van de kolomset. Een kolomset is een niet-getypte XML-weergave die alle geparseerde kolommen van een tabel combineert in een gestructureerde uitvoer. Een kolomset kan niet worden toegevoegd aan een tabel met parseringskolommen. Zie Kolomsets gebruikenvoor meer informatie over kolomsets.
{ ENABLE | UITSCHAKELEN } FILETABLE_NAMESPACE
van toepassing op: SQL Server (SQL Server 2012 (11.x) en hoger).
Hiermee schakelt u de door het systeem gedefinieerde beperkingen voor een FileTable in of uit. Kan alleen worden gebruikt met een FileTable.
SET ( FILETABLE_DIRECTORY = directory_name )
van toepassing op: SQL Server (SQL Server 2012 (11.x) en hoger). Azure SQL Database biedt geen ondersteuning voor FILETABLE.
Hiermee geeft u de naam van de windows-compatibele FileTable-map. Deze naam moet uniek zijn voor alle bestandstabelmapnamen in de database. Vergelijking van uniekheid is niet hoofdlettergevoelig, ondanks de SQL-sorteringsinstellingen. Kan alleen worden gebruikt met een FileTable.
REMOTE_DATA_ARCHIVE
van toepassing op: SQL Server (SQL Server 2017 (14.x) en hoger).
Hiermee schakelt u Stretch Database voor een tabel in of uit. Zie Stretch Databasevoor meer informatie.
Belangrijk
Stretch Database is afgeschaft in SQL Server 2022 (16.x) en Azure SQL Database. Deze functie wordt verwijderd in een toekomstige versie van de database-engine. Vermijd het gebruik van deze functie in nieuwe ontwikkelwerkzaamheden en plan om toepassingen te wijzigen die momenteel gebruikmaken van deze functie.
Stretch Database inschakelen voor een tabel
Wanneer u Stretch inschakelt voor een tabel door ONop te geven, moet u ook MIGRATION_STATE = OUTBOUND opgeven om gegevens onmiddellijk te migreren of MIGRATION_STATE = PAUSED om de gegevensmigratie uit te stellen. De standaardwaarde is MIGRATION_STATE = OUTBOUND. Zie Stretch Database inschakelen voor een tabelvoor meer informatie over het inschakelen van Stretch voor een tabel.
vereisten. Voordat u Stretch inschakelt voor een tabel, moet u Stretch inschakelen op de server en in de database. Zie Stretch Database inschakelen voor een databasevoor meer informatie.
machtigingen. Voor het inschakelen van Stretch voor een database of tabel zijn db_owner machtigingen vereist. Als u Stretch voor een tabel inschakelt, zijn ook ALTER-machtigingen voor de tabel vereist.
Stretch Database uitschakelen voor een tabel
Wanneer u Stretch voor een tabel uitschakelt, hebt u twee opties voor de externe gegevens die al naar Azure zijn gemigreerd. Zie Stretch Database uitschakelen en externe gegevens terugbrengenvoor meer informatie.
Voer de volgende opdracht uit om Stretch voor een tabel uit te schakelen en de externe gegevens voor de tabel van Azure terug te kopiëren naar SQL Server. Deze opdracht kan niet worden geannuleerd.
ALTER TABLE <table_name> SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;
Deze bewerking brengt kosten voor gegevensoverdracht in rekening en kan niet worden geannuleerd. Zie Prijsinformatie voor gegevensoverdrachtenvoor meer informatie.
Nadat alle externe gegevens zijn gekopieerd van Azure naar SQL Server, is Stretch uitgeschakeld voor de tabel.
Als u Stretch wilt uitschakelen voor een tabel en de externe gegevens wilt verlaten, voert u de volgende opdracht uit.
ALTER TABLE <table_name> SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;
Nadat u Stretch Database voor een tabel hebt uitgeschakeld, stopt de gegevensmigratie en bevatten queryresultaten geen resultaten meer uit de externe tabel.
Als u Stretch uitschakelt, wordt de externe tabel niet verwijderd. Als u de externe tabel wilt verwijderen, zet u deze neer met behulp van Azure Portal.
[ FILTER_PREDICATE = { null | predicaat } ]
van toepassing op: SQL Server (SQL Server 2017 (14.x) en hoger).
U kunt desgewenst een filterpredicaat opgegeven om rijen te selecteren die moeten worden gemigreerd uit een tabel die zowel historische als huidige gegevens bevat. Het predicaat moet een deterministische inline tabelwaardefunctie aanroepen. Zie Stretch Database inschakelen voor een tabel en Rijen selecteren om te migreren met behulp van een filterfunctie - Stretch Databasevoor meer informatie.
Belangrijk
Als u een filterpredicaat opgeeft dat slecht presteert, presteert de gegevensmigratie ook slecht. Stretch Database past het filterpredicaat toe op de tabel met behulp van de operator CROSS APPLY.
Als u geen filterpredicaat opgeeft, wordt de hele tabel gemigreerd.
Wanneer u een filterpredicaat opgeeft, moet u ook MIGRATION_STATEopgeven.
MIGRATION_STATE = { UITGAAND | INKOMEND | ONDERBROKEN }
van toepassing op: SQL Server (SQL Server 2017 (14.x) en hoger).
Geef
OUTBOUNDop om gegevens van SQL Server naar Azure te migreren.Geef
INBOUNDop om de externe gegevens voor de tabel van Azure terug te kopiëren naar SQL Server en Stretch voor de tabel uit te schakelen. Zie Stretch Database uitschakelen en externe gegevens terugbrengenvoor meer informatie.Deze bewerking brengt kosten voor gegevensoverdracht in rekening en kan niet worden geannuleerd.
Geef
PAUSEDop om de gegevensmigratie te onderbreken of uit te stellen. Zie Gegevensmigratie onderbreken en hervatten - Stretch Databasevoor meer informatie.
WAIT_AT_LOW_PRIORITY
van toepassing op: SQL Server (SQL Server 2014 (12.x) en hoger) en Azure SQL Database.
Bij het opnieuw samenstellen van een online index moet worden gewacht op blokkeringsbewerkingen in deze tabel.
WAIT_AT_LOW_PRIORITY geeft aan dat de onlinebewerking voor het opnieuw opbouwen van indexen wacht op vergrendelingen met lage prioriteit, zodat andere bewerkingen kunnen worden uitgevoerd terwijl de online indexbuildbewerking wacht. Het weglaten van de optie WACHTEN MET LAGE PRIORITEIT is hetzelfde als WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).
MAX_DURATION = tijd [MINUTEN ]
van toepassing op: SQL Server (SQL Server 2014 (12.x) en hoger) en Azure SQL Database.
De wachttijd, een geheel getal dat is opgegeven in minuten, dat de SWITCH of online index opnieuw opbouwen vergrendelingen wachten met lage prioriteit bij het uitvoeren van de DDL-opdracht. Als de bewerking voor de MAX_DURATION tijd wordt geblokkeerd, wordt een van de ABORT_AFTER_WAIT acties uitgevoerd. MAX_DURATION tijd is altijd in minuten en u kunt het woord MINUTENweglaten.
ABORT_AFTER_WAIT = [GEEN | SELF | BLOCKERS } ]
van toepassing op: SQL Server (SQL Server 2014 (12.x) en hoger) en Azure SQL Database.
GEEN
Blijf wachten op de vergrendeling met normale (normale) prioriteit.
ZELF
Sluit de SWITCH- of de DDL-bewerking voor online index opnieuw opbouwen zonder actie te ondernemen.
BLOCKERS
Alle gebruikerstransacties beëindigen die momenteel de DDL-bewerking SWITCH of online index opnieuw samenstellen, zodat de bewerking kan worden voortgezet.
Hiervoor is MACHTIGING VOOR VERBINDING WIJZIGEN vereist.
INDIEN AANWEZIG
van toepassing op: SQL Server (SQL Server 2016 (13.x) en hoger) en Azure SQL Database.
Hiermee wordt de kolom of beperking voorwaardelijk alleen verwijderd als deze al bestaat.
HERVATBAAR = { AAN | UIT}
van toepassing op: SQL Server 2022 (16.x) en hoger.
Hiermee geeft u op of een ALTER TABLE ADD CONSTRAINT bewerking kan worden hervat. De bewerking Tabelbeperking toevoegen kan worden hervat wanneer ON. De bewerking Tabelbeperking toevoegen kan niet worden hervat wanneer OFF. De standaardwaarde is OFF. De optie RESUMABLE kan worden gebruikt als onderdeel van de ALTER TABLE index_option in de ALTER TABLE table_constraint.
MAX_DURATION bij gebruik met RESUMABLE = ON (vereist ONLINE = ON) geeft tijd aan (een geheel getal dat is opgegeven in minuten) dat een hervatbare online beperkingsbewerking voor toevoegen wordt uitgevoerd voordat deze wordt onderbroken. Als dit niet is opgegeven, wordt de bewerking voortgezet totdat deze is voltooid.
Zie Hervatbare tabel beperkingen toevoegenvoor meer informatie over het inschakelen en gebruiken van hervatbare ALTER TABLE ADD CONSTRAINT bewerkingen.
Opmerkingen
Als u nieuwe rijen met gegevens wilt toevoegen, gebruikt u INSERT. Als u rijen met gegevens wilt verwijderen, gebruikt u DELETE of TRUNCATE TABLE. Als u de waarden in bestaande rijen wilt wijzigen, gebruikt u UPDATE-.
Als er uitvoeringsplannen in de procedurecache staan die verwijzen naar de tabel, markeert ALTER TABLE deze om opnieuw te worden gecompileerd bij de volgende uitvoering.
In SQL Database in Microsoft Fabric kunnen sommige tabelfuncties worden gemaakt, maar worden deze niet gespiegeld in de Fabric OneLake-. Zie Beperkingen van sql-databasespiegeling voor infrastructuurresourcesvoor meer informatie.
De grootte van een kolom wijzigen
U kunt de lengte, precisie of schaal van een kolom wijzigen door een nieuwe grootte op te geven voor het kolomgegevenstype. Gebruik de component ALTER COLUMN. Als er gegevens in de kolom aanwezig zijn, kan de nieuwe grootte niet kleiner zijn dan de maximale grootte van de gegevens. U kunt de kolom ook niet definiëren in een index, tenzij de kolom een varchar, nvarcharof varbinaire gegevenstype is en de index niet het resultaat is van een primaire sleutelbeperking. Zie het voorbeeld in de korte sectie met de titel Een kolomdefinitie wijzigen.
Vergrendelingen en ALTER TABLE
Wijzigingen die u in ALTER TABLE opgeeft, worden onmiddellijk geïmplementeerd. Als de wijzigingen wijzigingen van de rijen in de tabel vereisen, worden de rijen bijgewerkt door ALTER TABLE. ALTER TABLE verkrijgt een schemawijziging (SCH-M) vergrendeling op de tabel om ervoor te zorgen dat er tijdens de wijziging geen andere verbindingen verwijzen naar zelfs de metagegevens voor de tabel, met uitzondering van online indexbewerkingen waarvoor een korte SCH-M vergrendeling aan het einde is vereist. In een ALTER TABLE...SWITCH bewerking wordt de vergrendeling verkregen op zowel de bron- als doeltabellen. De wijzigingen in de tabel worden geregistreerd en kunnen volledig worden hersteld. Wijzigingen die van invloed zijn op alle rijen in grote tabellen, zoals het verwijderen van een kolom of, in sommige edities van SQL Server, het toevoegen van een NOT NULL-kolom met een standaardwaarde, kan lang duren en veel logboekrecords genereren. Voer deze ALTER TABLE-instructies uit met dezelfde zorg als een INSERT-, UPDATE- of DELETE-instructie die van invloed is op veel rijen.
is van toepassing op Warehouse in Microsoft Fabric.
ALTER TABLE kan geen deel uitmaken van een expliciete transactie.
XEvents voor partitieswitch
De volgende XEvents zijn gerelateerd aan ALTER TABLE ... SWITCH PARTITION en online index herbouwt.
- lock_request_priority_state
- process_killed_by_abort_blockers
- ddl_with_wait_at_low_priority
NOT NULL-kolommen toevoegen als onlinebewerking
Vanaf SQL Server 2012 (11.x) Enterprise Edition is het toevoegen van een NOT NULL-kolom met een standaardwaarde een onlinebewerking wanneer de standaardwaarde een runtimeconstanteis. Dit betekent dat de bewerking bijna onmiddellijk wordt voltooid ondanks het aantal rijen in de tabel, omdat de bestaande rijen in de tabel niet worden bijgewerkt tijdens de bewerking. In plaats daarvan wordt de standaardwaarde alleen opgeslagen in de metagegevens van de tabel en wordt de waarde zo nodig opgezoekd in query's die toegang hebben tot deze rijen. Dit gedrag is automatisch. Er is geen extra syntaxis vereist voor het implementeren van de onlinebewerking buiten de syntaxis ADD COLUMN. Een runtimeconstante is een expressie die dezelfde waarde produceert tijdens runtime voor elke rij in de tabel, ondanks het determinisme. De constante expressie 'Mijn tijdelijke gegevens' of de systeemfunctie GETUTCDATETIME() zijn bijvoorbeeld runtimeconstanten. De functies NEWID() of NEWSEQUENTIALID() daarentegen geen runtimeconstanten zijn, omdat er voor elke rij in de tabel een unieke waarde wordt geproduceerd. Het toevoegen van een KOLOM NOT NULL met een standaardwaarde die geen runtimeconstante is, wordt altijd offline uitgevoerd en er wordt een exclusieve (SCH-M) vergrendeling verkregen voor de duur van de bewerking.
Hoewel de bestaande rijen verwijzen naar de waarde die is opgeslagen in metagegevens, wordt de standaardwaarde opgeslagen in de rij voor nieuwe rijen die zijn ingevoegd en geen andere waarde voor de kolom opgeven. De standaardwaarde die is opgeslagen in metagegevens, wordt verplaatst naar een bestaande rij wanneer de rij wordt bijgewerkt (zelfs als de werkelijke kolom niet is opgegeven in de UPDATE-instructie), of als de tabel of geclusterde index opnieuw wordt opgebouwd.
Kolommen van het type varchar(max), nvarchar(max), varbinary(max), xml-, tekst, ntext, image, hierarchyid, geometrie, geografieof CLR UDTS kan niet worden toegevoegd in een onlinebewerking. Een kolom kan niet online worden toegevoegd als dit ervoor zorgt dat de maximaal mogelijke rijgrootte de limiet van 8.060 byte overschrijdt. De kolom wordt in dit geval toegevoegd als offlinebewerking.
Parallelle uitvoering van plannen
In SQL Server 2012 (11.x) Enterprise Edition en latere versies wordt het aantal processors dat wordt gebruikt om één ALTER TABLE ADD-instructie (indexgebaseerd) CONSTRAINT of DROP (geclusterde index) CONSTRAINT uit te voeren, bepaald door de maximale mate van parallelle uitvoering configuratieoptie en de huidige workload. Als de database-engine detecteert dat het systeem bezet is, wordt de mate van parallelle uitvoering van de bewerking automatisch verminderd voordat de uitvoering van de instructie wordt gestart. U kunt handmatig het aantal processors configureren dat wordt gebruikt om de instructie uit te voeren door de OPTIE MAXDOP op te geven. Zie De maximale mate van parallelle configuratie van server configurerenvoor meer informatie.
Gepartitioneerde tabellen
Naast het uitvoeren van SWITCH-bewerkingen waarbij gepartitioneerde tabellen zijn betrokken, gebruikt u ALTER TABLE om de status van de kolommen, beperkingen en triggers van een gepartitioneerde tabel te wijzigen, net zoals deze wordt gebruikt voor niet-gepartitioneerde tabellen. Deze instructie kan echter niet worden gebruikt om de manier te wijzigen waarop de tabel zelf wordt gepartitioneerd. Als u een gepartitioneerde tabel opnieuw wilt partitioneren, gebruikt u ALTER PARTITION SCHEME en ALTER PARTITION FUNCTION. Daarnaast kunt u het gegevenstype van een kolom van een gepartitioneerde tabel niet wijzigen.
Beperkingen voor tabellen met schemagebonden weergaven
De beperkingen die van toepassing zijn op ALTER TABLE-instructies voor tabellen met schemagebonden weergaven zijn hetzelfde als de beperkingen die momenteel worden toegepast bij het wijzigen van tabellen met een eenvoudige index. Het toevoegen van een kolom is toegestaan. Het verwijderen of wijzigen van een kolom die deelneemt aan een schemagebonden weergave is echter niet toegestaan. Als voor de instructie ALTER TABLE een kolom moet worden gewijzigd die wordt gebruikt in een schemagebonden weergave, mislukt ALTER TABLE en genereert de database-engine een foutbericht. Zie CREATE VIEWvoor meer informatie over schemabinding en geïndexeerde weergaven.
Het toevoegen of verwijderen van triggers op basistabellen wordt niet beïnvloed door een schemagebonden weergave te maken die verwijst naar de tabellen.
Indexen en ALTER TABLE
Indexen die zijn gemaakt als onderdeel van een beperking, worden verwijderd wanneer de beperking wordt verwijderd. Indexen die zijn gemaakt met CREATE INDEX, moeten worden verwijderd met DROP INDEX. Gebruik de instructie ALTER INDEX om een indexonderdeel van een beperkingsdefinitie opnieuw te bouwen; de beperking hoeft niet te worden verwijderd en opnieuw toegevoegd met ALTER TABLE.
Alle indexen en beperkingen op basis van een kolom moeten worden verwijderd voordat de kolom kan worden verwijderd.
Wanneer u een beperking verwijdert die een geclusterde index heeft gemaakt, worden de gegevensrijen die zijn opgeslagen op het bladniveau van de geclusterde index, opgeslagen in een niet-geclusterde tabel. U kunt de geclusterde index verwijderen en de resulterende tabel verplaatsen naar een andere bestandsgroep of partitieschema in één transactie door de optie VERPLAATSEN NAAR op te geven. De optie VERPLAATSEN NAAR heeft de volgende beperkingen:
- MOVE TO is niet geldig voor geïndexeerde weergaven of niet-geclusterde indexen.
- Het partitieschema of de bestandsgroep moet al bestaan.
- Als MOVE TO niet is opgegeven, bevindt de tabel zich in hetzelfde partitieschema of dezelfde bestandsgroep als die is gedefinieerd voor de geclusterde index.
Wanneer u een geclusterde index verwijdert, geeft u de optie ONLINE **=** ON op, zodat de DROP INDEX-transactie geen query's en wijzigingen in de onderliggende gegevens en gekoppelde niet-geclusterde indexen blokkeert.
ONLINE = ON heeft de volgende beperkingen:
- ONLINE = ON is niet geldig voor geclusterde indexen die ook zijn uitgeschakeld. Uitgeschakelde indexen moeten worden verwijderd met behulp van ONLINE = UIT.
- Er kan slechts één index tegelijk worden verwijderd.
- ONLINE = ON is niet geldig voor geïndexeerde weergaven, niet-geclusterde indexen of indexen in lokale tijdelijke tabellen.
- ONLINE = ON is niet geldig voor columnstore-indexen.
Tijdelijke schijfruimte die gelijk is aan de grootte van de bestaande geclusterde index is vereist om een geclusterde index te verwijderen. Deze extra ruimte wordt vrijgegeven zodra de bewerking is voltooid.
Notitie
De opties onder <drop_clustered_constraint_option> van toepassing op geclusterde indexen in tabellen en kunnen niet worden toegepast op geclusterde indexen in weergaven of niet-geclusterde indexen.
Schemawijzigingen repliceren
Wanneer u ALTER TABLE uitvoert op een gepubliceerde tabel in een SQL Server Publisher, wordt deze wijziging standaard doorgegeven aan alle SQL Server-abonnees. Deze functionaliteit heeft enkele beperkingen. U kunt deze uitschakelen. Zie Schemawijzigingen aanbrengen in publicatiedatabasesvoor meer informatie.
Gegevenscompressie
Systeemtabellen kunnen niet worden ingeschakeld voor compressie. Als de tabel een heap is, wordt de herbouwbewerking voor de ONLINE-modus één thread weergegeven. Gebruik de OFFLINE-modus voor een herbouwbewerking met meerdere threads. Zie Gegevenscompressievoor meer informatie over gegevenscompressie.
Als u wilt evalueren hoe het wijzigen van de compressiestatus van invloed is op een tabel, een index of een partitie, gebruikt u de opgeslagen procedure sp_estimate_data_compression_savings systeem.
De volgende beperkingen gelden voor gepartitioneerde tabellen:
- U kunt de compressie-instelling van één partitie niet wijzigen als de tabel niet-uitgelijnde indexen heeft.
- De
ALTER TABLE <table> REBUILD PARTITION... syntaxis herbouwt de opgegeven partitie. - De
ALTER TABLE <table> REBUILD WITH... syntaxis herbouwt alle partities.
NTEXT-kolommen neerzetten
Wanneer u kolommen neerzet met behulp van het afgeschafte NTEXT-gegevenstype, vindt het opschonen van de verwijderde gegevens plaats als een geserialiseerde bewerking op alle rijen. Voor het opschonen kan een grote hoeveelheid tijd nodig zijn. Wanneer u een NTEXT-kolom in een tabel met veel rijen neer zet, werkt u eerst de NTEXT-kolom bij naar NULL-waarde en zet u de kolom neer. U kunt deze optie uitvoeren met parallelle bewerkingen en deze veel sneller maken.
Online index REBUILD
Als u de DDL-instructie voor het opnieuw samenstellen van een onlineindex wilt uitvoeren, moeten alle actieve blokkerende transacties die in een bepaalde tabel worden uitgevoerd, worden voltooid. Wanneer het opnieuw opbouwen van de onlineindex wordt gestart, worden alle nieuwe transacties geblokkeerd die klaar zijn om in deze tabel te worden uitgevoerd. Hoewel de duur van de vergrendeling voor het opnieuw samenstellen van online indexen kort is, kan het wachten op alle geopende transacties in een bepaalde tabel worden voltooid en de nieuwe transacties worden geblokkeerd om te starten, de doorvoer aanzienlijk beïnvloeden. Dit kan leiden tot een vertraging of time-out voor een workload en de toegang tot de onderliggende tabel aanzienlijk beperken. Met de optie WAIT_AT_LOW_PRIORITY kunnen DBA's de S-vergrendeling en Sch-M vergrendelingen beheren die vereist zijn voor het opnieuw opbouwen van online indexen. In alle drie gevallen: NONE, SELF en BLOCKERS, als er tijdens de wachttijd ((MAX_DURATION =n [minutes])) geen blokkeringsactiviteiten zijn, wordt de online index opnieuw opbouwen onmiddellijk uitgevoerd zonder te wachten en de DDL-instructie is voltooid.
Compatibiliteitsondersteuning
De instructie ALTER TABLE ondersteunt slechts tweedelige tabelnamen (schema.object). In SQL Server mislukt het opgeven van een tabelnaam met behulp van de volgende indelingen tijdens het compileren met fout 117.
server.database.schema.table.database.schema.table..schema.table
In eerdere versies geeft u de indeling op server.database.schema.table fout 4902 geretourneerd. Het opgeven van de indeling .database.schema.table of de indeling ..schema.table geslaagd.
Verwijder het gebruik van een vierdelige voorvoegsel om het probleem op te lossen.
Machtigingen
Vereist ALTER-machtiging voor de tabel.
ALTER TABLE-machtigingen zijn van toepassing op beide tabellen die zijn betrokken bij een ALTER TABLE SWITCH-instructie. Alle gegevens die worden overgeschakeld, nemen de beveiliging van de doeltabel over.
Als u kolommen in de instructie ALTER TABLE hebt gedefinieerd als een common language runtime (CLR) door de gebruiker gedefinieerd type of aliasgegevenstype, is DE MACHTIGING VERWIJZINGEN voor het type vereist.
Voor het toevoegen of wijzigen van een kolom waarmee de rijen van de tabel worden bijgewerkt, is machtiging BIJWERKEN voor de tabel vereist. Als u bijvoorbeeld een kolom NOT NULL met een standaardwaarde toevoegt of een identiteitskolom toevoegt wanneer de tabel niet leeg is.
Voorbeelden
| Categorie | Aanbevolen syntaxiselementen |
|---|---|
| kolommen en beperkingen toevoegen | ADD * PRIMARY KEY with index options * sparse columns and column sets * |
| kolommen en beperkingen verwijderen | DRUPPEL |
| een kolomdefinitie wijzigen | gegevenstype wijzigen * kolomgrootte wijzigen * sortering |
| een tabeldefinitie wijzigen | DATA_COMPRESSION * SCHAKELPARTITIE * ESCALATIE VERGRENDELEN * Wijzigingen bijhouden |
| beperkingen en triggers uitschakelen en inschakelen | CHECK * NO CHECK * ENABLE TRIGGER * DISABLE TRIGGER |
| onlinebewerkingen | ONLINE |
| systeemversiebeheer | SYSTEM_VERSIONING |
Kolommen en beperkingen toevoegen
Voorbeelden in deze sectie laten zien hoe u kolommen en beperkingen toevoegt aan een tabel.
Een. Een nieuwe kolom toevoegen
In het volgende voorbeeld wordt een kolom toegevoegd die null-waarden toestaat en geen waarden bevat via een STANDAARDdefinitie. In de nieuwe kolom heeft elke rij NULL.
CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO
B. Een kolom met een beperking toevoegen
In het volgende voorbeeld wordt een nieuwe kolom met een UNIQUE beperking toegevoegd.
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. Een niet-geverifieerde CHECK-beperking toevoegen aan een bestaande kolom
In het volgende voorbeeld wordt een beperking toegevoegd aan een bestaande kolom in de tabel. De kolom heeft een waarde die de beperking schendt. Daarom wordt WITH NOCHECK gebruikt om te voorkomen dat de beperking wordt gevalideerd op basis van bestaande rijen en om toe te staan dat de beperking wordt toegevoegd.
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. Een STANDAARDbeperking toevoegen aan een bestaande kolom
In het volgende voorbeeld wordt een tabel met twee kolommen gemaakt en wordt een waarde ingevoegd in de eerste kolom en blijft de andere kolom NULL. Vervolgens wordt er een DEFAULT-beperking toegevoegd aan de tweede kolom. Als u wilt controleren of de standaardwaarde is toegepast, wordt er een andere waarde ingevoegd in de eerste kolom en wordt er een query op de tabel uitgevoerd.
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. Meerdere kolommen met beperkingen toevoegen
In het volgende voorbeeld worden verschillende kolommen met beperkingen toegevoegd die zijn gedefinieerd met de nieuwe kolom. De eerste nieuwe kolom heeft een eigenschap IDENTITY. Elke rij in de tabel bevat nieuwe incrementele waarden in de identiteitskolom.
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. Een kolom met null-waarden toevoegen met standaardwaarden
In het volgende voorbeeld wordt een kolom met null-waarden met een definitie van DEFAULT toegevoegd en wordt WITH VALUES gebruikt om waarden op te geven voor elke bestaande rij in de tabel. Als WITH VALUES niet wordt gebruikt, heeft elke rij de waarde NULL in de nieuwe kolom.
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. Een PRIMAIRE-SLEUTELbeperking maken met opties voor index- of gegevenscompressie
In het volgende voorbeeld wordt de beperking PRIMAIRE SLEUTEL gemaakt PK_TransactionHistoryArchive_TransactionID en worden de opties FILLFACTOR, ONLINEen PAD_INDEXingesteld. De resulterende geclusterde index heeft dezelfde naam als de beperking.
van toepassing op: SQL Server 2008 (10.0.x) en hoger en 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
In dit vergelijkbare voorbeeld wordt paginacompressie toegepast tijdens het toepassen van de geclusterde primaire sleutel.
USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (DATA_COMPRESSION = PAGE);
GO
H. Een sparse-kolom toevoegen
In de volgende voorbeelden ziet u hoe u sparsekolommen toevoegt en wijzigt in tabel T1. De code voor het maken van een tabel T1 is als volgt.
CREATE TABLE T1 (
C1 INT PRIMARY KEY,
C2 VARCHAR(50) SPARSE NULL,
C3 INT SPARSE NULL,
C4 INT) ;
GO
Als u een extra sparsekolom C5wilt toevoegen, voert u de volgende instructie uit.
ALTER TABLE T1
ADD C5 CHAR(100) SPARSE NULL ;
GO
Voer de volgende instructie uit om de C4 niet-parse kolom te converteren naar een sparsekolom.
ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO
Als u de C4 sparse-kolom wilt converteren naar een niet-parse kolom, voert u de volgende instructie uit.
ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE ;
GO
Ik. Een kolomset toevoegen
In de volgende voorbeelden ziet u hoe u een kolom toevoegt aan tabel T2. Een kolomset kan niet worden toegevoegd aan een tabel die al sparse kolommen bevat. De code voor het maken van een tabel T2 is als volgt.
CREATE TABLE T2 (
C1 INT PRIMARY KEY,
C2 VARCHAR(50) NULL,
C3 INT NULL,
C4 INT) ;
GO
Met de volgende drie instructies voegt u een kolomset met de naam CStoe en wijzigt u vervolgens kolommen C2 en C3 in SPARSE.
ALTER TABLE T2
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO
ALTER TABLE T2
ALTER COLUMN C2 ADD SPARSE ;
GO
ALTER TABLE T2
ALTER COLUMN C3 ADD SPARSE ;
GO
J. Een versleutelde kolom toevoegen
Met de volgende instructie wordt een versleutelde kolom met de naam PromotionCodetoegevoegd.
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. Een primaire sleutel met hervatbare bewerking toevoegen
Hervatbare ALTER TABLE bewerking voor het toevoegen van een primaire sleutel die is geclusterd op kolom (a) met MAX_DURATION van 240 minuten.
ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);
Kolommen en beperkingen verwijderen
In de voorbeelden in deze sectie ziet u hoe kolommen en beperkingen worden verwijderd.
Een. Een kolom of kolommen verwijderen
In het eerste voorbeeld wordt een tabel gewijzigd om een kolom te verwijderen. In het tweede voorbeeld worden meerdere kolommen verwijderd.
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. Beperkingen en kolommen verwijderen
In het eerste voorbeeld wordt een UNIQUE beperking uit een tabel verwijderd. In het tweede voorbeeld worden twee beperkingen en één kolom verwijderd.
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. Een BEPERKING PRIMAIRE SLEUTEL verwijderen in de ONLINEmodus
In het volgende voorbeeld wordt een beperking PRIMAIRE SLEUTEL verwijderd waarbij de optie ONLINE is ingesteld op ON.
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON) ;
GO
D. Een beperking REFERERENDE SLEUTEL toevoegen en verwijderen
In het volgende voorbeeld wordt de tabel ContactBackupgemaakt en vervolgens de tabel gewijzigd door eerst een FOREIGN KEY beperking toe te voegen die verwijst naar de tabel Person.Personen vervolgens door de FOREIGN KEY beperking te verwijderen.
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 ;
Een kolomdefinitie wijzigen
Een. Het gegevenstype van een kolom wijzigen
In het volgende voorbeeld wordt een kolom van een tabel gewijzigd van INT in 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. De grootte van een kolom wijzigen
In het volgende voorbeeld wordt de grootte van een varchar kolom en de precisie en schaal van een decimale kolom vergroot. Omdat de kolommen gegevens bevatten, kan de kolomgrootte alleen worden verhoogd. U ziet ook dat col_a is gedefinieerd in een unieke index. De grootte van col_a kan nog steeds worden verhoogd omdat het gegevenstype een varchar- is en de index niet het resultaat is van een primaire sleutelbeperking.
-- 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. Kolomsortering wijzigen
In het volgende voorbeeld ziet u hoe u de sortering van een kolom kunt wijzigen. Eerst wordt er een tabel gemaakt met de standaardsortering van gebruikers.
CREATE TABLE T3 (
C1 INT PRIMARY KEY,
C2 VARCHAR(50) NULL,
C3 INT NULL,
C4 INT) ;
GO
Vervolgens wordt C2 sortering gewijzigd in Latin1_General_BIN. Het gegevenstype is vereist, ook al is het niet gewijzigd.
ALTER TABLE T3
ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN ;
GO
D. Een kolom versleutelen
In het volgende voorbeeld ziet u hoe u een kolom versleutelt met behulp van Always Encrypted met beveiligde enclaves.
Eerst wordt er een tabel gemaakt zonder versleutelde kolommen.
CREATE TABLE T3 (
C1 INT PRIMARY KEY,
C2 VARCHAR(50) NULL,
C3 INT NULL,
C4 INT) ;
GO
Vervolgens wordt kolom C2 versleuteld met een kolomversleutelingssleutel, met de naam CEK1en gerandomiseerde versleuteling. De volgende instructie slaagt:
- De kolomversleutelingssleutel moet enclave zijn ingeschakeld. Dit betekent dat deze moet worden versleuteld met een kolomhoofdsleutel die enclaveberekeningen toestaat.
- Het doel-SQL Server-exemplaar moet Always Encrypted ondersteunen met beveiligde enclaves.
- De instructie moet worden uitgegeven via een verbinding die is ingesteld voor Always Encrypted met beveiligde enclaves en met behulp van een ondersteund clientstuurprogramma.
- De aanroepende toepassing moet toegang hebben tot de hoofdsleutel van de kolom en moet
CEK1beveiligen.
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
Een tabeldefinitie wijzigen
In de voorbeelden in deze sectie ziet u hoe u de definitie van een tabel kunt wijzigen.
Een. Een tabel wijzigen om de compressie te wijzigen
In het volgende voorbeeld wordt de compressie van een niet-gepartitioneerde tabel gewijzigd. De heap- of geclusterde index wordt opnieuw opgebouwd. Als de tabel een heap is, worden alle niet-geclusterde indexen opnieuw opgebouwd.
ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE) ;
In het volgende voorbeeld wordt de compressie van een gepartitioneerde tabel gewijzigd. De REBUILD PARTITION = 1 syntaxis zorgt ervoor dat alleen partitienummer 1 opnieuw worden opgebouwd.
van toepassing op: SQL Server 2008 (10.0.x) en hoger en Azure SQL Database.
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =NONE) ;
GO
Dezelfde bewerking met behulp van de volgende alternatieve syntaxis zorgt ervoor dat alle partities in de tabel opnieuw worden opgebouwd.
van toepassing op: SQL Server 2008 (10.0.x) en hoger en Azure SQL Database.
ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1)) ;
Zie Gegevenscompressievoor meer voorbeelden van gegevenscompressie.
B. Een columnstore-tabel wijzigen om archiveringscompressie te wijzigen
In het volgende voorbeeld wordt een columnstore-tabelpartitie verder gecomprimeerd door een extra compressie-algoritme toe te passen. Deze compressie vermindert de tabel tot een kleinere grootte, maar verhoogt ook de tijd die nodig is voor opslag en ophalen. Dit is handig voor archivering of voor situaties die minder ruimte nodig hebben en meer tijd kunnen veroorloven voor opslag en ophalen.
van toepassing op: SQL Server 2014 (12.x) en hoger en Azure SQL Database.
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) ;
GO
In het volgende voorbeeld wordt een columnstore-tabelpartitie gedecomprimeerd die is gecomprimeerd met COLUMNSTORE_ARCHIVE optie. Wanneer de gegevens worden hersteld, blijven deze gecomprimeerd met de columnstore-compressie die wordt gebruikt voor alle columnstore-tabellen.
van toepassing op: SQL Server 2014 (12.x) en hoger en Azure SQL Database.
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = COLUMNSTORE) ;
GO
C. Schakelen tussen partities tussen tabellen
In het volgende voorbeeld wordt een gepartitioneerde tabel gemaakt, ervan uitgaande dat het partitieschema myRangePS1 al in de database is gemaakt. Vervolgens wordt een niet-gepartitioneerde tabel gemaakt met dezelfde structuur als de gepartitioneerde tabel en op dezelfde bestandsgroep als PARTITION 2 van tabel PartitionTable. De gegevens van PARTITION 2 tabel PartitionTable worden vervolgens omgezet in tabel 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. Escalatie van vergrendeling op gepartitioneerde tabellen toestaan
In het volgende voorbeeld wordt escalatie van vergrendelingen naar het partitieniveau in een gepartitioneerde tabel mogelijk. Als de tabel niet is gepartitioneerd, wordt escalatie van vergrendeling ingesteld op TABELniveau.
van toepassing op: SQL Server 2008 (10.0.x) en hoger en Azure SQL Database.
ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO) ;
GO
E. Wijzigingen bijhouden in een tabel configureren
In het volgende voorbeeld kunt u wijzigingen bijhouden in de Person.Person tabel.
van toepassing op: SQL Server 2008 (10.0.x) en hoger en Azure SQL Database.
USE AdventureWorks;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING ;
In het volgende voorbeeld wordt het bijhouden van wijzigingen ingeschakeld en wordt het bijhouden van de kolommen ingeschakeld die tijdens een wijziging worden bijgewerkt.
van toepassing op: SQL Server 2008 (10.0.x) en hoger.
USE AdventureWorks;
GO
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
In het volgende voorbeeld wordt het bijhouden van wijzigingen in de Person.Person tabel uitgeschakeld.
van toepassing op: SQL Server 2008 (10.0.x) en hoger en Azure SQL Database.
USE AdventureWorks;
GO
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING ;
Beperkingen en triggers uitschakelen en inschakelen
Een. Een beperking uitschakelen en opnieuw inschakelen
In het volgende voorbeeld wordt een beperking uitgeschakeld waarmee de salarissen die in de gegevens worden geaccepteerd, worden beperkt.
NOCHECK CONSTRAINT wordt gebruikt met ALTER TABLE om de beperking uit te schakelen en een invoeging toe te staan die doorgaans de beperking schendt.
CHECK CONSTRAINT de beperking opnieuw inschakelt.
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. Een trigger uitschakelen en opnieuw inschakelen
In het volgende voorbeeld wordt de optie DISABLE TRIGGER van ALTER TABLE gebruikt om de trigger uit te schakelen en een invoeging toe te staan die doorgaans de trigger schendt.
ENABLE TRIGGER wordt vervolgens gebruikt om de trigger opnieuw in te schakelen.
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
Onlinebewerkingen
Een. Online index herbouwen met wachtopties met lage prioriteit
In het volgende voorbeeld ziet u hoe u een online index opnieuw opbouwt die de wachtopties met lage prioriteit aangeeft.
van toepassing op: SQL Server 2014 (12.x) en hoger en 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. Online kolom wijzigen
In het volgende voorbeeld ziet u hoe u een bewerking voor een wijzigingskolom uitvoert met de optie ONLINE.
van toepassing op: SQL Server 2016 (13.x) en hoger en 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
Systeemversiebeheer
De volgende vier voorbeelden helpen u vertrouwd te raken met de syntaxis voor het gebruik van systeemversiebeheer. Zie Aan de slag met System-Versioned tijdelijke tabellenvoor meer hulp.
van toepassing op: SQL Server 2016 (13.x) en hoger en Azure SQL Database.
Een. Systeemversiebeheer toevoegen aan bestaande tabellen
In het volgende voorbeeld ziet u hoe u systeemversiebeheer toevoegt aan een bestaande tabel en een toekomstige geschiedenistabel maakt. In dit voorbeeld wordt ervan uitgegaan dat er een bestaande tabel met de naam InsurancePolicy met een primaire sleutel is gedefinieerd. In dit voorbeeld worden de zojuist gemaakte periodekolommen voor systeemversiebeheer ingevuld met behulp van standaardwaarden voor de begin- en eindtijd, omdat deze waarden niet null kunnen zijn. In dit voorbeeld wordt de HIDDEN-component gebruikt om ervoor te zorgen dat bestaande toepassingen geen invloed hebben op de huidige tabel. Er wordt ook gebruikgemaakt van HISTORY_RETENTION_PERIOD die alleen beschikbaar is in 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. Een bestaande oplossing migreren om systeemversiebeheer te gebruiken
In het volgende voorbeeld ziet u hoe u migreert naar systeemversiebeheer vanuit een oplossing die triggers gebruikt om tijdelijke ondersteuning na te bootsen. In het voorbeeld wordt ervan uitgegaan dat er een bestaande oplossing is die gebruikmaakt van een ProjectTask tabel en een ProjectTaskHistory tabel voor de bestaande oplossing, die gebruikmaakt van de kolommen Changed Date en Revised Date voor de perioden, dat deze periodekolommen niet gebruikmaken van het datum/tijd2 gegevenstype en dat voor de ProjectTask tabel een primaire sleutel is gedefinieerd.
-- 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. Systeemversiebeheer uitschakelen en opnieuw inschakelen om het tabelschema te wijzigen
In dit voorbeeld ziet u hoe u systeemversiebeheer uitschakelt in de Department tabel, een kolom toevoegt en systeemversiebeheer opnieuw inschakelt. Het uitschakelen van systeemversiebeheer is vereist om het tabelschema te wijzigen. Voer deze stappen binnen een transactie uit om te voorkomen dat beide tabellen worden bijgewerkt tijdens het bijwerken van het tabelschema, waardoor de DBA de controle van gegevensconsistentie kan overslaan bij het opnieuw inschakelen van systeemversies en een prestatievoordeel krijgt. Voor taken zoals het maken van statistieken, het schakelen tussen partities of het toepassen van compressie op een of beide tabellen is het uitschakelen van systeemversiebeheer niet vereist.
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. Systeemversiebeheer verwijderen
In dit voorbeeld ziet u hoe u systeemversiebeheer volledig verwijdert uit de tabel Afdeling en de DepartmentHistory tabel verwijdert. Desgewenst wilt u ook de periodekolommen die door het systeem worden gebruikt, verwijderen om informatie over systeemversiebeheer vast te leggen. U kunt de Department of de DepartmentHistory tabellen niet verwijderen terwijl systeemversiebeheer is ingeschakeld.
ALTER TABLE Department
SET (SYSTEM_VERSIONING = OFF) ;
ALTER TABLE Department
DROP PERIOD FOR SYSTEM_TIME ;
DROP TABLE DepartmentHistory ;
Voorbeelden: Azure Synapse Analytics and Analytics Platform System (PDW)
In de volgende voorbeelden A tot en met C wordt de FactResellerSales tabel in de AdventureWorksPDW2022-database gebruikt.
Een. Bepalen of een tabel is gepartitioneerd
De volgende query retourneert een of meer rijen als de tabel FactResellerSales is gepartitioneerd. Als de tabel niet is gepartitioneerd, worden er geen rijen geretourneerd.
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. Grenswaarden voor een gepartitioneerde tabel bepalen
De volgende query retourneert de grenswaarden voor elke partitie in de FactResellerSales tabel.
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. De partitiekolom voor een gepartitioneerde tabel bepalen
De volgende query retourneert de naam van de partitioneringskolom voor de tabel.
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. Twee partities samenvoegen
In het volgende voorbeeld worden twee partities in een tabel samengevoegd.
De tabel Customer heeft de volgende definitie:
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))) ;
Met de volgende opdracht worden de partitiegrenzen van 10 en 25 gecombineerd.
ALTER TABLE Customer MERGE RANGE (10);
De nieuwe DDL voor de tabel is:
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. Een partitie splitsen
In het volgende voorbeeld wordt een partitie gesplitst in een tabel.
De Customer tabel heeft de volgende 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 ))) ;
Met de volgende opdracht maakt u een nieuwe partitie die afhankelijk is van de waarde 75, tussen 50 en 100.
ALTER TABLE Customer SPLIT RANGE (75);
De nieuwe DDL voor de tabel is:
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. Schakeloptie gebruiken om een partitie naar een geschiedenistabel te verplaatsen
In het volgende voorbeeld worden de gegevens in een partitie van de Orders tabel verplaatst naar een partitie in de OrdersHistory tabel.
De Orders tabel heeft de volgende 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'))) ;
In dit voorbeeld bevat de Orders tabel de volgende partities. Elke partitie bevat gegevens.
| Verdelen | Heeft u gegevens? | Grensbereik |
|---|---|---|
| 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 |
- Partitie 1 (bevat gegevens): OrderDate < '2004-01-01'
- Partitie 2 (bevat gegevens): '2004-01-01' <= OrderDate < '2005-01-01'
- Partitie 3 (bevat gegevens): '2005-01-01' <= OrderDate< '2006-01-01'
- Partitie 4 (bevat gegevens): '2006-01-01'<= OrderDate < '2007-01-01'
- Partitie 5 (bevat gegevens): '2007-01-01' <= OrderDate
De OrdersHistory tabel heeft de volgende DDL, die identieke kolommen en kolomnamen heeft als de Orders tabel. Beide zijn hash-gedistribueerd op de kolom 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'))) ;
Hoewel de kolommen en kolomnamen hetzelfde moeten zijn, hoeven de partitiegrenzen niet hetzelfde te zijn. In dit voorbeeld bevat de OrdersHistory tabel de volgende twee partities en beide partities zijn leeg:
- Partitie 1 (geen gegevens): OrderDate < '2004-01-01'
- Partitie 2 (leeg): '2004-01-01' <= OrderDatum
Voor de vorige twee tabellen verplaatst de volgende opdracht alle rijen met OrderDate < '2004-01-01' van de Orders tabel naar de OrdersHistory tabel.
ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;
Als gevolg hiervan is de eerste partitie in Orders leeg en bevat de eerste partitie in OrdersHistory gegevens. De tabellen worden nu als volgt weergegeven:
Orders tabel
- Partitie 1 (leeg): OrderDate < '2004-01-01'
- Partitie 2 (bevat gegevens): '2004-01-01' <= OrderDate < '2005-01-01'
- Partitie 3 (bevat gegevens): '2005-01-01' <= OrderDate< '2006-01-01'
- Partitie 4 (bevat gegevens): '2006-01-01'<= OrderDate < '2007-01-01'
- Partitie 5 (bevat gegevens): '2007-01-01' <= OrderDate
OrdersHistory tabel
- Partitie 1 (bevat gegevens): OrderDate < '2004-01-01'
- Partitie 2 (leeg): '2004-01-01' <= OrderDatum
Als u de Orders tabel wilt opschonen, kunt u de lege partitie verwijderen door partities 1 en 2 als volgt samen te voegen:
ALTER TABLE Orders MERGE RANGE ('2004-01-01');
Na de samenvoegbewerking heeft de Orders tabel de volgende partities:
Orders tabel
- Partitie 1 (bevat gegevens): OrderDate < '2005-01-01'
- Partitie 2 (bevat gegevens): '2005-01-01' <= OrderDate< '2006-01-01'
- Partitie 3 (bevat gegevens): '2006-01-01'<= OrderDate < '2007-01-01'
- Partitie 4 (bevat gegevens): '2007-01-01' <= OrderDate
Stel dat een ander jaar voorbij is en u klaar bent om het jaar 2005 te archiveren. U kunt een lege partitie toewijzen voor het jaar 2005 in de OrdersHistory tabel door de lege partitie als volgt te splitsen:
ALTER TABLE OrdersHistory SPLIT RANGE ('2005-01-01');
Na de splitsing heeft de OrdersHistory tabel de volgende partities:
OrdersHistory tabel
- Partitie 1 (bevat gegevens): OrderDate < '2004-01-01'
- Partitie 2 (leeg): '2004-01-01' < '2005-01-01'
- Partitie 3 (leeg): '2005-01-01' <= OrderDate