ALTER TABLE (Transact-SQL)
Si applica a:
SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Analytics Platform System (PDW) Warehouse in Microsoft Fabric
Modifica una definizione di tabella tramite la modifica, l'aggiunta o l'eliminazione di colonne e vincoli. ALTER TABLE consente inoltre di riassegnare e ricompilare partizioni, oltre a disabilitare e abilitare vincoli e trigger.
Nota
Attualmente, ALTER TABLE in Fabric Warehouse è supportato solo per i vincoli e l'aggiunta di colonne nullable. Vedere Sintassi per Warehouse in Fabric.
Importante
La sintassi di ALTER TABLE è diversa per le tabelle basate su disco e le tabelle ottimizzate per la memoria. Usare i collegamenti seguenti per passare direttamente al blocco di sintassi appropriata per i tipi di tabella e agli esempi di sintassi appropriata:
Per altre informazioni sulle convenzioni della sintassi, vedere Convenzioni della sintassi Transact-SQL.
Sintassi per le tabelle basate su disco
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 } )
}
Nota
Per altre informazioni, vedi:
Sintassi per le tabelle con ottimizzazione per la memoria
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 ]
}
Sintassi per Azure Synapse Analytics e 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 }
}
Nota
Il pool SQL serverless di Azure Synapse Analytics supporta solo tabelle esterne e temporanee.
Sintassi per warehouse in infrastruttura
-- 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
}
Argomenti
database_name
Nome del database in cui è stata creata la tabella.
schema_name
Nome del processo a cui appartiene la tabella.
table_name
Nome della tabella da modificare. Se la tabella non è inclusa nel database corrente o nello schema di proprietà dell'utente corrente, è necessario specificare in modo esplicito il database e lo schema.
ALTER COLUMN
Specifica che la colonna denominata deve essere cambiata o modificata.
Non è consentita la modifica di queste colonne:
Colonna con tipo di dati timestamp.
Colonna ROWGUIDCOL della tabella.
Colonne calcolate o utilizzate in una colonna calcolata.
Colonne usate in statistiche generate dall'istruzione CREATE STATISTICS. Gli utenti devono eseguire DROP STATISTICS per eliminare le statistiche prima di poter completare ALTER COLUMN con esito positivo. Eseguire questa query per ottenere tutte le statistiche e le colonne delle statistiche create dall'utente per una tabella.
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>');Nota
Le statistiche generate in modo automatico da Query Optimizer vengono eliminate automaticamente da ALTER COLUMN.
Colonne utilizzate in un vincolo PRIMARY KEY o [FOREIGN KEY] REFERENCES.
Colonne utilizzate in un vincolo CHECK o UNIQUE. È in ogni caso possibile modificare la lunghezza di una colonna a lunghezza variabile usata in un vincolo CHECK o UNIQUE.
Colonne associate a una definizione DEFAULT. Se il tipo di dati non viene modificato, è tuttavia possibile modificare la lunghezza, la precisione o la scala di una colonna.
Il tipo di dati di colonne text, ntext e image può essere modificato solo nei modi seguenti:
- Da text a varchar(max), nvarchar(max) o xml
- Da ntext a varchar(max), nvarchar(max) o xml
- Da image a varbinary(max)
Alcune modifiche del tipo di dati possono comportare la modifica dei dati. Ad esempio, la modifica di una colonna nchar o nvarchar in char o varchar potrebbe causare la conversione di caratteri estesi. Per altre informazioni, vedere CAST e CONVERT. La riduzione della precisione o della scala di una colonna può causare il troncamento dei dati.
Nota
Non è possibile modificare il tipo di dati di una colonna di una tabella partizionata.
Il tipo di dati delle colonne in un indice non può essere modificato. Fanno eccezione le colonne per cui il tipo di dati è varchar, nvarchar o varbinary e le nuove dimensioni sono uguali o maggiori di quelle precedenti.
Le colonne incluse in un vincolo di chiave primaria non possono essere modificate da NOT NULL a NULL.
Quando si usa Always Encrypted (senza enclave sicuri), se la colonna da modificare è crittografata con "ENCRYPTED WITH", è possibile modificare il tipo di dati in un tipo di dati compatibile, ad esempio da INT a BIGINT, ma non è possibile modificarne le impostazioni di crittografia.
Quando si usa Always Encrypted con enclave sicuri, è possibile modificare qualsiasi impostazione di crittografia, se la chiave di crittografia che protegge la colonna (e la nuova chiave di crittografia della colonna, se si modifica la chiave) supporta i calcoli dell'enclave (crittografati con le chiavi master della colonna abilitate per le enclave). Per informazioni dettagliate, vedere Always Encrypted con enclave sicuri.
Quando si modifica una colonna, il motore di database tiene traccia di ogni modifica aggiungendo una riga in una tabella di sistema e contrassegnando la modifica della colonna precedente come colonna eliminata. Nel raro caso in cui si modifica una colonna troppe volte, il motore di database potrebbe raggiungere il limite di dimensioni del record. In questo caso, verrà visualizzato l'errore 511 o 1708. Per evitare questi errori, ricompilare periodicamente l'indice cluster nella tabella o ridurre il numero di modifiche alle colonne.
column_name
Nome della colonna da modificare, aggiungere o eliminare. column_name può essere composto da un massimo di 128 caratteri. Nel caso di nuove colonne, è possibile omettere column_name per le colonne che sono state create con il tipo di dati timestamp. Viene usato il nome timestamp se non si specifica il nome column_name per una colonna con il tipo di dati timestamp.
Nota
Le nuove colonne vengono aggiunte dopo la modifica di tutte le colonne esistenti nella tabella.
[ type_schema_name. ] type_name
Nuovo tipo di dati per la colonna modificata o tipo di dati per la colonna aggiunta. Non è possibile specificare type_name per colonne esistenti di tabelle partizionate. type_name può essere uno dei tipi seguenti:
- Tipo di dati di sistema di SQL Server.
- Tipo di dati alias basato su un tipo di dati di sistema di SQL Server. Per consentirne l'uso in una definizione di tabella, si creano tipi di dati alias con l'istruzione CREATE TYPE.
- Tipo definito dall'utente di .NET Framework e schema di appartenenza. Per consentirne l'uso in una definizione di tabella, si creano tipi definiti dall'utente con l'istruzione CREATE TYPE.
Di seguito sono riportati i criteri per type_name di una colonna modificata:
- Il tipo di dati precedente deve supportare la conversione implicita nel nuovo tipo di dati.
- type_name non può essere timestamp.
- I valori predefiniti di ANSI_NULL sono sempre attivi per ALTER COLUMN. Se non diversamente specificato, la colonna ammette i valori Null.
- Il riempimento con ANSI_PADDING è sempre attivo per ALTER COLUMN.
- Se la colonna modificata è una colonna Identity, il tipo di dati di new_data_type deve supportare la proprietà Identity.
- L'impostazione corrente di SET ARITHABORT viene ignorata. Il funzionamento di ALTER TABLE presume l'impostazione di ARITHABORT su ON.
Nota
Se la clausola COLLATE è omessa, la modifica del tipo di dati di una colonna causa la modifica delle regole di confronto predefinite del database.
precision
Precisione del tipo di dati specificato. Per altre informazioni sui valori di precisione validi, vedere Precisione, scala e lunghezza.
scale
Scala per il tipo di dati specificato. Per altre informazioni sui valori di scala validi, vedere Precisione, scala e lunghezza.
max
Viene applicato solo ai tipi di dati varchar, nvarchar e varbinary per l'archiviazione di 2^31-1 byte di dati di tipo carattere, binario e Unicode.
xml_schema_collection
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Viene applicato solo al tipo di dati xml per l'associazione di uno XML Schema al tipo. Prima di tipizzare una colonna xml in una raccolta di schemi, si crea la raccolta nel database usando CREATE XML SCHEMA COLLECTION.
COLLATE <collation_name>
Specifica le nuove regole di confronto per la colonna modificata. Se viene omesso, alla colonna vengono assegnate le regole di confronto predefinite del database. È possibile usare nomi di regole di confronto di Windows o SQL. Per un elenco e altre informazioni, vedere Nome delle regole di confronto di Windows e Nome delle regole di confronto di SQL Server.
La clausola COLLATE modifica le regole di confronto solo per le colonne con tipo di dati char, varchar, nchar e nvarchar. Per modificare le regole di confronto di una colonna con un tipo di dati alias definito dall'utente, usare istruzioni ALTER TABLE separate in modo da modificare il tipo di dati della colonna in un tipo di dati di sistema di SQL Server. Modificare quindi le regole di confronto e ripristinare un tipo di dati alias per la colonna.
Non è possibile specificare una modifica delle regole di confronto per ALTER COLUMN se si verifica una delle condizioni seguenti:
- Un vincolo CHECK o FOREIGN KEY o una colonna calcolata fa riferimento alla colonna modificata.
- Nella colonna viene creato un indice, un indice full-text o una serie di statistiche. Le statistiche create automaticamente nella colonna modificata vengono eliminate se si modificano le regole di confronto della colonna.
- Una funzione o una vista associata allo schema fa riferimento alla colonna.
Per altre informazioni, vedere COLLATE.
NULL | NOT NULL
Specifica se la colonna consente valori Null. L'istruzione ALTER TABLE consente di aggiungere colonne che non consentono valori Null solo se alle colonne è associato un valore predefinito oppure se la tabella è vuota. È possibile specificare NOT NULL per le colonne calcolate solo se è stato specificato anche PERSISTED. Le nuove colonne che consentono valori Null ma a cui non è associato alcun valore predefinito contengono un valore Null per ogni riga della tabella. Se a una nuova colonna che consente valori Null si aggiunge una definizione DEFAULT, è possibile usare WITH VALUES per l'archiviazione del valore predefinito nella nuova colonna per ogni riga della tabella.
Se la nuova colonna non consente valori Null e la tabella non è vuota, è necessario aggiungervi una definizione DEFAULT. Il valore predefinito viene quindi caricato automaticamente in ogni riga esistente delle nuove colonne.
È possibile specificare NULL in ALTER COLUMN per forzare l'uso di valori Null nelle colonne NOT NULL, fatta eccezione per le colonne nei vincoli PRIMARY KEY. È possibile specificare NOT NULL in ALTER COLUMN solo se la colonna non contiene valori Null. Per utilizzare ALTER COLUMN NOT NULL, è necessario aggiornare i valori Null con un valore specifico, ad esempio:
UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL;
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL;
Quando si crea o si modifica una tabella mediante un'istruzione CREATE TABLE o ALTER TABLE, le impostazioni del database e della sessione influiscono sull'impostazione che consente l'uso dei valori Null del tipo di dati usato in una definizione di colonna. In questo caso, tale impostazione può essere sostituita. Nel caso di colonne non calcolate, assicurarsi di definire sempre in modo esplicito una colonna come NULL o NOT NULL.
Se si aggiunge una colonna con un tipo di dati definito dall'utente, assicurarsi di definire per la colonna la stessa impostazione relativa al supporto dei valori Null del tipo di dati definito dall'utente. Specificare quindi un valore predefinito per la colonna. Per altre informazioni, vedere CREATE TABLE.
Nota
Se si specifica NULL o NOT NULL con ALTER COLUMN, è necessario specificare anche new_data_type [(precision [, scale ])]. Se il tipo di dati, la precisione e la scala non vengono modificati, specificare i valori correnti della colonna.
[ {ADD | DROP} ROWGUIDCOL ]
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Specifica l'aggiunta o l'eliminazione della proprietà ROWGUIDCOL nella colonna specificata. ROWGUIDCOL indica che la colonna è di tipo rowguid. È possibile impostare come colonna ROWGUIDCOL una sola colonna di tipo uniqueidentifier per ogni tabella. È inoltre possibile assegnare la proprietà ROWGUIDCOL solo a una colonna uniqueidentifier. Non è possibile assegnare ROWGUIDCOL a una colonna con un tipo di dati definito dall'utente.
ROWGUIDCOL non impone l'unicità dei valori archiviati nella colonna e non genera automaticamente valori per le nuove righe inserite nella tabella. Per generare valori univoci per ogni colonna, usare la funzione NEWID o NEWSEQUENTIALID nelle istruzioni INSERT. In alternativa, specificare la funzione NEWID o NEWSEQUENTIALID come valore predefinito per la colonna.
[ {ADD | DROP} PERSISTED ]
Specifica l'aggiunta o l'eliminazione della proprietà PERSISTED nella colonna specificata. La colonna interessata deve essere una colonna calcolata definita con un'espressione deterministica. Per le colonne specificate come PERSISTED, i valori calcolati nella tabella vengono archiviati fisicamente nel motore di database e vengono aggiornati durante l'aggiornamento di qualsiasi altra colonna da cui dipendono le colonne calcolate. Se si contrassegna una colonna calcolata come PERSISTED, è possibile creare indici in colonne calcolate definite in base a espressioni deterministiche ma imprecise. Per altre informazioni, vedere Indici per le colonne calcolate.
È necessario che l'opzione SET QUOTED_IDENTIFIER sia impostata su ON durante la creazione o la modifica di indici in colonne calcolate o viste indicizzate. Per altre informazioni, vedere SET QUOTED_IDENTIFIER (Transact-SQL).
Tutte le colonne calcolate usate come colonne di partizionamento di tabelle partizionate devono essere contrassegnate come PERSISTED in modo esplicito.
DROP NOT FOR REPLICATION
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Specifica che i valori vengono incrementati nelle colonne Identity quando gli agenti di replica eseguono operazioni di inserimento. È possibile specificare questa clausola solo se column_name corrisponde a una colonna Identity.
SPARSE
Indica che la colonna è di tipo sparse. L'archiviazione delle colonne di tipo sparse è ottimizzata per valori Null. Non è possibile impostare le colonne di tipo sparse come NOT NULL. Quando si converte una colonna da sparse a nonsparse o da nonsparse a sparse, questa opzione blocca la tabella per la durata dell'esecuzione del comando. Potrebbe essere necessario utilizzare la clausola REBUILD per recuperare spazio. Per altre restrizioni e informazioni relative alle colonne di tipo sparse, vedere Usare le colonne di tipo sparse.
ADD MASKED WITH ( FUNCTION = ' mask_function ')
Si applica a: SQL Server 2016 (13.x) e versioni successive e Database SQL di Azure.
Specifica una maschera dati dinamica. mask_function è il nome della funzione di maschera con i parametri appropriati. Sono disponibili tre funzioni:
- default()
- email()
- partial()
- random()
È richiesta l'autorizzazione ALTER ANY MASK.
Per eliminare una maschera, usare DROP MASKED. Per i parametri di funzione, vedere Mascheramento dati dinamici.
L'aggiunta e il rilascio di una maschera richiedono l'autorizzazione ALTER ANY MASK.
WITH ( ONLINE = ON | OFF) <come si applica per modificare una colonna>
Si applica a: SQL Server 2016 (13.x) e versioni successive e Database SQL di Azure.
Consente l'esecuzione di molte azioni di modifica colonna mentre la tabella rimane disponibile. L'impostazione predefinita è OFF. È possibile eseguire l'operazione di modifica colonna online per le modifiche relative al tipo di dati, alla lunghezza o precisione della colonna, al supporto dei valori Null, all'impostazione del tipo sparse e alle regole di confronto.
L'operazione di modifica colonna online consente alle statistiche automatiche o create dall'utente di fare riferimento alla colonna modificata per la durata dell'operazione ALTER COLUMN, il che consente di eseguire le query come al solito. Al termine dell'operazione, le statistiche automatiche che fanno riferimento alla colonna vengono eliminate e le statistiche create dall'utente vengono invalidate. L'utente deve aggiornare manualmente le statistiche generate dall'utente dopo il completamento dell'operazione. Se la colonna fa parte di un'espressione filtro per statistiche o indici, non è possibile eseguire un'operazione di modifica colonna.
- Durante l'esecuzione di un'operazione di modifica colonna online, tutte le operazioni che potrebbero dipendere dalla colonna (indici, viste e così via) vengono bloccate o hanno esito negativo generando un errore appropriato. Questo comportamento garantisce che l'operazione di modifica colonna online non avrà esito negativo a causa delle dipendenze introdotte durante l'esecuzione dell'operazione.
- La modifica di una colonna da NOT NULL a NULL non è supportata come operazione online quando gli indici non cluster fanno riferimento alla colonna modificata.
- L'operazione di modifica online non è supportata quando un vincolo CHECK fa riferimento alla colonna e l'operazione di modifica limita la precisione della colonna (valori numerici o datetime).
- L'opzione
WAIT_AT_LOW_PRIORITYnon può essere usata con l'operazione di modifica colonna online. ALTER COLUMN ... ADD/DROP PERSISTEDnon è supportato per l'operazione di modifica colonna online.ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATIONnon è interessato dall'operazione di modifica colonna online.- L'operazione di modifica colonna online non supporta la modifica di una tabella in cui è abilitato il rilevamento modifiche o che è una tabella di pubblicazione della replica di tipo merge.
- L'operazione di modifica colonna online non supporta la modifica dai tipi di dati CLR o viceversa.
- L'operazione di modifica colonna online non supporta la modifica in un tipo di dati XML con una raccolta di schemi diversa dalla raccolta di schemi corrente.
- L'operazione di modifica colonna online non consente di ridurre le restrizioni che stabiliscono quando una colonna può essere modificata. Riferimenti da indici/statistiche e così via potrebbero causare l'esito negativo dell'operazione di modifica.
- L'operazione di modifica colonna online non supporta la modifica di più colonne contemporaneamente.
- L'operazione di modifica colonna online non influisce in caso di una tabella temporale con controllo delle versioni di sistema. La colonna ALTER non viene eseguita online indipendentemente dal valore che è stato specificato per l'opzione ONLINE.
L'operazione di modifica colonna online prevede requisiti, restrizioni e funzionalità simili a quelli dell'operazione di ricompilazione indice online, ad esempio:
- L'operazione di ricompilazione indice online non è supportata quando la tabella contiene colonne LOB o filestream legacy oppure quando la tabella include un indice columnstore. Le stesse limitazioni si applicano all'operazione di modifica colonna online.
- La modifica di una colonna esistente richiede un'allocazione dello spazio doppia: per la colonna originale e per la colonna nascosta appena creata.
- La strategia di blocco durante un'operazione di modifica colonna online segue lo stesso criterio di blocco usato per l'operazione di compilazione indice online.
WITH CHECK | WITH NOCHECK
Specifica se i dati nella tabella vengono convalidati in base a un vincolo FOREIGN KEY o CHECK nuovo o riabilitato. Se non lo si specifica, viene usata la clausola WITH CHECK per nuovi vincoli e WITH NOCHECK per vincoli riabilitati.
Se non si vogliono verificare nuovi vincoli CHECK o FOREIGN KEY in base ai dati esistenti, usare WITH NOCHECK. È tuttavia consigliabile effettuare questa scelta solo in casi rari. Il nuovo vincolo viene valutato in tutti gli aggiornamenti successivi dei dati. Le eventuali violazioni del vincolo soppresse da WITH NOCHECK quando si aggiunge il vincolo possono causare il mancato completamento dei successivi aggiornamenti di righe contenenti dati che non seguono il vincolo. Query Optimizer non considera i vincoli definiti con WITH NOCHECK, Tali vincoli vengono ignorati fino a quando non vengono abilitati usando ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL. Per altre informazioni, vedere Disabilitare i vincoli di chiave esterna con le istruzioni INSERT e UPDATE.
ALTER INDEX index_name
Specifica che il numero di bucket per index_name deve essere cambiato o modificato.
La sintassi ALTER TABLE … ADD/DROP/ALTER INDEX è supportata solo per le tabelle ottimizzate per la memoria.
Importante
Se non si usa l'istruzione ALTER TABLE, le istruzioni CREATE INDEX, DROP INDEX, ALTER INDEX e PAD_INDEX non sono supportate per gli indici nelle tabelle ottimizzate per la memoria.
ADD
Specifica l'aggiunta di una o più definizioni di colonna, definizioni di colonna calcolata o vincoli di tabella. In alternativa, specifica l'aggiunta delle colonne che il sistema usa per il controllo delle versioni di sistema. Per le tabelle ottimizzate per la memoria, è possibile aggiungere un indice.
Nota
Le nuove colonne vengono aggiunte dopo la modifica di tutte le colonne esistenti nella tabella.
Importante
Se non si usa l'istruzione ALTER TABLE, le istruzioni CREATE INDEX, DROP INDEX, ALTER INDEX e PAD_INDEX non sono supportate per gli indici nelle tabelle ottimizzate per la memoria.
PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
Si applica a: SQL Server (SQL Server 2017 (14.x) e versioni successive), Database SQL di Azure.
Specifica i nomi delle colonne che il sistema usa per registrare il periodo di validità di un record. È possibile specificare le colonne esistenti o creare nuove colonne come parte dell'argomento ADD PERIOD FOR SYSTEM_TIME. Configurare le colonne con il tipo di dati datetime2 e definirle come NOT NULL. Se si definisce una colonna periodo NULL, verrà generato un errore. È possibile definire un oggetto column_constraint e/o specificare i valori predefiniti per le colonne system_start_time e system_end_time. Vedere l'esempio A negli esempi di Controllo delle versioni di sistema seguenti, che illustrano l'uso di un valore predefinito per la colonna system_end_time.
Usare questo argomento con l'argomento SET SYSTEM_VERSIONING per trasformare una tabella esistente in una tabella temporale. Per altre informazioni, vedere Tabelle temporali e Introduzione alle tabelle temporali nel database SQL di Azure.
A partire da SQL Server 2017 (14.x), gli utenti possono contrassegnare una o entrambe le colonne periodo con il flag HIDDEN per nascondere in modo implicito tali colonne. In questo modo, SELECT * FROM <table_name> non restituirà alcun valore per le colonne. Per impostazione predefinita, le colonne periodo non vengono nascoste. Per poter essere usate, le colonne nascoste devono essere incluse in modo esplicito in tutte le query che fanno direttamente riferimento alla tabella temporale.
DROP
Specifica la rimozione di una o più definizioni di colonna, definizioni di colonna calcolata o vincoli di tabella o l'eliminazione della specifica per le colonne che il sistema usa per il controllo delle versioni di sistema.
Nota
Le colonne delle tabelle libro mastro vengono eliminate solo in modo simulato. Una colonna eliminata rimane nella tabella libro mastro, ma è contrassegnata come colonna eliminata impostando la dropped_ledger_table colonna in sys.tables su 1. Anche la visualizzazione libro mastro della tabella libro mastro eliminato viene contrassegnata come eliminata impostando la dropped_ledger_view colonna in sys.tables su 1. Una tabella libro mastro eliminato, la relativa tabella di cronologia e la relativa vista mastro vengono rinominate aggiungendo un prefisso (MSSQL_DroppedLedgerTable, MSSQL_DroppedLedgerHistory, MSSQL_DroppedLedgerView) e aggiungendo un GUID al nome originale.
CONSTRAINT constraint_name
Specifica che constraint_name viene rimosso dalla tabella. È possibile elencare più vincoli.
È possibile determinare il nome definito dall'utente o fornito dal sistema del vincolo eseguendo una query sulle sys.check_constraintviste del catalogo , sys.default_constraints, sys.key_constraintse sys.foreign_keys .
Se nella tabella è presente un indice XML, non è possibile eliminare un vincolo PRIMARY KEY.
INDEX index_name
Specifica che index_name viene rimosso dalla tabella.
La sintassi ALTER TABLE … ADD/DROP/ALTER INDEX è supportata solo per le tabelle ottimizzate per la memoria.
Importante
Se non si usa l'istruzione ALTER TABLE, le istruzioni CREATE INDEX, DROP INDEX, ALTER INDEX e PAD_INDEX non sono supportate per gli indici nelle tabelle ottimizzate per la memoria.
COLUMN column_name
Specifica che constraint_name o column_name viene rimosso dalla tabella. È possibile elencare più colonne.
Non è possibile eliminare una colonna se:
- È usata in un indice, come colonna chiave o come INCLUDE
- Viene utilizzata in un vincolo CHECK, FOREIGN KEY, UNIQUE o PRIMARY KEY.
- È associata a un valore predefinito creato con la parola chiave DEFAULT o a un oggetto predefinito.
- È associata a una regola.
Nota
L'eliminazione di una colonna non consente di recuperare lo spazio su disco corrispondente. Può essere necessario recuperare lo spazio su disco di una colonna rimossa quando le dimensioni delle righe della tabella sono prossime al limite o lo hanno superato. Per recuperare spazio, creare un indice cluster nella tabella o ricompilare un indice cluster esistente usando ALTER INDEX. Per informazioni sull'impatto dell'eliminazione dei tipi di dati LOB, vedere questo intervento sul blog di CSS.
PERIOD FOR SYSTEM_TIME
Si applica a: SQL Server 2016 (13.x) e versioni successive e Database SQL di Azure.
Elimina la specifica per le colonne che il sistema userà per il controllo delle versioni di sistema.
WITH <drop_clustered_constraint_option>
Specifica l'impostazione di una o più opzioni di eliminazione dei vincoli cluster.
MAXDOP = max_degree_of_parallelism
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Esegue l'override dell'opzione di configurazione max degree of parallelism solo per la durata dell'operazione. Per altre informazioni, vedere Configurare l'opzione di configurazione del server max degree of parallelism.
L'opzione MAXDOP consente di limitare il numero di processori utilizzati per l'esecuzione di piani paralleli. Il valore massimo è 64 processori.
max_degree_of_parallelism può essere uno dei valori seguenti:
1Disattiva la generazione di piani paralleli.
>1Limita il numero massimo di processori utilizzati in un'operazione parallela sull'indice in base al numero specificato.
0(impostazione predefinita) Usa il numero effettivo di processori o meno in base al carico di lavoro del sistema corrente.
Per altre informazioni, vedere Configurazione di operazioni parallele sugli indici.
Nota
Le operazioni parallele sugli indici non sono disponibili in tutte le edizioni di SQL Server. Per altre informazioni, vedere Edizioni e funzionalità supportate di SQL Server 2022.
ONLINE = { ON | OFF } <come si applica a drop_clustered_constraint_option>
Specifica se le tabelle sottostanti e gli indici associati sono disponibili per le query e la modifica dei dati durante l'operazione sugli indici. Il valore predefinito è OFF. È possibile eseguire REBUILD come operazione ONLINE.
In...
I blocchi di tabella a lungo termine non vengono mantenuti per la durata dell'operazione sugli indici. Durante la fase principale dell'operazione viene mantenuto solo un blocco preventivo condiviso (IS, Intent Shared) sulla tabella di origine. Questo comportamento consente l'esecuzione di query o l'aggiornamento della tabella sottostante e degli indici. All'inizio dell'operazione viene mantenuto un blocco condiviso (S) sull'oggetto di origine per un breve periodo. Al termine dell'operazione, per un breve periodo viene acquisito un blocco condiviso (S) sull'origine, se viene creato un indice non cluster. In alternativa, viene acquisito un blocco di modifica dello schema (SCH-M) quando un indice cluster viene creato o eliminato online e quando un indice cluster o non cluster viene ricompilato. L'opzione ONLINE non può essere impostata su ON quando viene creato un indice per una tabella temporanea locale. È consentita solo l'operazione di ricompilazione dell'heap a thread singolo.
Per eseguire l'istruzione DDL per un'operazione SWITCH o la ricompilazione dell'indice online, è necessario completare tutte le transazioni bloccanti attive in esecuzione in una specifica tabella. Durante l'esecuzione, l'operazione SWITCH o di ricompilazione impedisce l'avvio di nuove transazioni e può influire in modo significativo sulla velocità effettiva del carico di lavoro e ritardare temporaneamente l'accesso alla tabella sottostante.
OFF
I blocchi di tabella si applicano per la durata dell'operazione sugli indici. Un'operazione sugli indici offline che crea, ricompila o elimina un indice cluster oppure ricompila o elimina un indice non cluster acquisisce un blocco di modifica dello schema (SCH-M) sulla tabella. Il blocco impedisce agli utenti di accedere alla tabella sottostante per la durata dell'operazione. Un'operazione sugli indici offline che crea un indice non cluster acquisisce un blocco condiviso (S) sulla tabella. Il blocco impedisce l'aggiornamento della tabella sottostante ma consente operazioni di lettura, ad esempio l'esecuzione di istruzioni SELECT. Sono consentite operazioni di ricompilazione dell'heap multithread.
Per altre informazioni, vedere Funzionamento delle operazioni sugli indici online.
Nota
Le operazioni online sugli indici non sono disponibili in tutte le edizioni di SQL Server. Per altre informazioni, vedere Edizioni e funzionalità supportate di SQL Server 2022.
MOVE TO { partition_scheme_name(column_name [ ,...n ] ) | filegroup | "default" }
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Specifica una posizione in cui spostare le righe di dati attualmente presenti a livello foglia nell'indice cluster. La tabella viene spostata nella nuova posizione. Questa opzione è valida solo per i vincoli che creano un indice cluster.
Nota
In questo contesto default non è una parola chiave, ma un identificatore per il filegroup predefinito e deve essere delimitato, come in MOVE TO "default" o MOVE TO [default]. Se si specifica ""default", l'opzione QUOTED_IDENTIFIER deve essere impostata su ON per la sessione corrente. Si tratta dell'impostazione predefinita. Per altre informazioni, vedere SET QUOTED_IDENTIFIER.
{ CHECK | NOCHECK } CONSTRAINT
Specifica che constraint_name è abilitato o disabilitato. È possibile utilizzare questa opzione solo con vincoli FOREIGN KEY e CHECK. Quando si specifica NOCHECK, il vincolo viene disabilitato e gli inserimenti o gli aggiornamenti futuri alla colonna non vengono convalidati in base alle condizioni del vincolo. I vincoli DEFAULT, PRIMARY KEY e UNIQUE non possono essere disabilitati.
ALL
Specifica che tutti i vincoli sono disabilitati con l'opzione NOCHECK o abilitati con l'opzione CHECK.
{ ENABLE | DISABLE } TRIGGER
Specifica che trigger_name è abilitato o disabilitato. Quando un trigger è disabilitato, è comunque definito per la tabella. Tuttavia, quando si esegue un'istruzione INSERT, UPDATE o DELETE sulla tabella, le azioni nel trigger vengono eseguite solo dopo che il trigger è stato abilitato nuovamente.
ALL
Specifica l'abilitazione o la disabilitazione di tutti i trigger della tabella.
trigger_name
Specifica il nome del trigger da abilitare o disabilitare.
{ ENABLE | DISABLE } CHANGE_TRACKING
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Specifica se il rilevamento delle modifiche è abilitato o disabilitato per la tabella. Per impostazione predefinita, il rilevamento delle modifiche è disabilitato.
Questa opzione è disponibile solo quando il rilevamento delle modifiche è abilitato per il database. Per altre informazioni, vedere Opzioni ALTER DATABASE SET.
Per abilitare il rilevamento delle modifiche, nella tabella deve essere presente una chiave primaria.
WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Indica se il motore di database tiene traccia dell'aggiornamento delle colonne per le quali è abilitato il rilevamento delle modifiche. Il valore predefinito è OFF.
SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_partition_number_expression ]
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Trasferisce un blocco di dati in uno dei modi seguenti:
- Riassegna tutti i dati di una tabella come partizione a una tabella partizionata già esistente.
- Sposta una partizione da una tabella partizionata a un'altra.
- Riassegna tutti i dati in una partizione di una tabella partizionata a una tabella non partizionata esistente.
Se table è una tabella partizionata, è necessario specificare source_partition_number_expression. Se target_table è partizionata, è necessario specificare target_partition_number_expression. Quando si riassegnano i dati di una tabella come partizione a una tabella esistente già partizionata o se si sposta una partizione da una tabella partizionata a un'altra, la partizione di destinazione deve essere già esistente e vuota.
Quando si riassegnano i dati di una partizione per formare un'unica tabella, la tabella di destinazione deve esistere già ed essere vuota. Sia la tabella o la partizione di origine che la tabella o la partizione di destinazione devono trovarsi nello stesso filegroup. È inoltre necessario che gli indici o le partizioni degli indici corrispondenti si trovino nello stesso filegroup. Al trasferimento di partizioni vengono applicate molte ulteriori restrizioni. table e target_table non possono essere la stessa tabella. target_table può essere un identificatore in più parti.
source_partition_number_expression e target_partition_number_expression sono espressioni costanti che possono fare riferimento a variabili e funzioni, incluse variabili con tipo definito dall'utente (UDT) e funzioni definite dall'utente, ma non a espressioni Transact-SQL.
Una tabella partizionata con un indice columstore cluster presenta lo stesso comportamento di un heap partizionato:
- La chiave primaria deve includere la chiave di partizione.
- La chiave di partizione deve essere inclusa in indice univoco. Ma l'inclusione della chiave di partizione con un indice univoco esistente può modificarne l'univocità.
- Per cambiare le partizioni, tutti gli indici non cluster devono includere la chiave di partizione.
Per la restrizione SWITCH durante la replica, vedere Replicare tabelle e indici partizionati.
Gli indici columnstore non cluster sono stati compilati in un formato di sola lettura prima di SQL Server 2016 (13.x) e per database SQL prima della versione V12. Prima di poter eseguire un'operazione PARTITION, pertanto, è necessario ricompilare gli indici columnstore non cluster nel formato corrente, vale a dire un formato aggiornabile.
Limitazioni
Se entrambe le tabelle sono partizionate in modo identico, inclusi gli indici non cluster e la tabella di destinazione non include indici non cluster, è possibile che venga visualizzato un errore 4907.
Output di esempio:
Msg 4907, Level 16, State 1, Line 38
'ALTER TABLE SWITCH' statement failed. The table 'MyDB.dbo.PrtTable1' has 4 partitions while index 'MS1' has 6 partitions.
SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" })
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive. Il database SQL di Azure non supporta FILESTREAM.
Specifica dove vengono archiviati i dati FILESTREAM.
L'istruzione ALTER TABLE con la clausola SET FILESTREAM_ON viene eseguita in modo corretto solo se nella tabella non sono presenti colonne FILESTREAM. È possibile aggiungere colonne FILESTREAM tramite una seconda istruzione ALTER TABLE.
Se si specifica partition_scheme_name, vengono applicate le regole per CREATE TABLE. Assicurarsi che la tabella sia già partizionata per i dati delle righe e che il relativo schema di partizione usi la stessa funzione e le stesse colonne di partizione dello schema di partizione FILESTREAM.
filestream_filegroup_name specifica il nome di un filegroup FILESTREAM. È necessario che per il filegroup sia definito un file tramite un'istruzione CREATE DATABASE o ALTER DATABASE. In caso contrario, viene generato un errore.
"default" specifica il filegroup FILESTREAM con il set di proprietà DEFAULT. Se non è presente alcun filegroup FILESTREAM, viene generato un errore.
"NULL" specifica che tutti i riferimenti al filegroup FILESTREAM per la tabella vengono rimossi. È necessario eliminare innanzitutto tutte le colonne FILESTREAM. Usare SET FILESTREAM_ON="NULL" per eliminare tutti i dati FILESTREAM associati a una tabella.
SET ( SYSTEM_VERSIONING = { OFF | ON [ ( HISTORY_TABLE = schema_name . history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ] } )
Si applica a: SQL Server 2016 (13.x) e versioni successive e Database SQL di Azure.
Disabilita o abilita il controllo delle versioni di sistema di una tabella. Per abilitare il controllo delle versioni di sistema di una tabella, il sistema verifica che il tipo di dati, il vincolo per il supporto dei valori Null e i requisiti di vincolo della chiave primaria per il controllo delle versioni di sistema siano soddisfatti. Il sistema registrerà la cronologia di ogni record della tabella con versione di sistema all'interno di una tabella della cronologia separata. Se l'argomento HISTORY_TABLE non viene usato, il nome di questa tabella di cronologia sarà MSSQL_TemporalHistoryFor<primary_table_object_id>. Se la tabella di cronologia non esiste, il sistema genera una nuova tabella di cronologia corrispondente allo schema della tabella corrente, crea un collegamento tra le due tabelle e consente al sistema di registrare la cronologia di ogni record nella tabella corrente nella tabella di cronologia. Se si usa l'argomento HISTORY_TABLE per creare un collegamento e usare una tabella di cronologia esistente, il sistema crea un collegamento tra la tabella corrente e la tabella specificata. Quando si crea un collegamento a una tabella di cronologia esistente, è possibile scegliere di eseguire una verifica coerenza dei dati. Questa verifica coerenza dei dati garantisce che i record esistenti non si sovrappongano. L'impostazione predefinita prevede l'esecuzione della verifica coerenza dei dati. Usare l'argomento SYSTEM_VERSIONING = ON in una tabella definita con la clausola PERIOD FOR SYSTEM_TIME per trasformare la tabella esistente in una tabella temporale. Per altre informazioni, vedere Temporal Tables.
HISTORY_RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS} }
Si applica a: SQL Server 2017 (14.x), Database SQL di Azure.
Specifica il periodo di conservazione finito o infinito per i dati cronologici in una tabella temporale. Se è omesso, si applicherà il periodo di conservazione infinito.
DATA_DELETION
Si applica a: solo SQL Edge di Azure
Abilita la pulizia basata sui criteri di conservazione dei dati non recenti o obsoleti dalle tabelle all'interno di un database. Per altre informazioni, vedere Abilitare e disabilitare la conservazione dei dati. Per abilitare la conservazione dei dati, è necessario specificare i parametri seguenti.
FILTER_COLUMN = { column_name }
Specifica la colonna da usare per determinare se le righe della tabella sono obsolete o meno. Per la colonna di filtro sono consentiti i tipi di dati seguenti.
- Data
- Data/Ora
- DateTime2
- SmallDateTime
- DateTimeOffset
RETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS }}
Specifica i criteri del periodo di conservazione per la tabella. Il periodo di conservazione viene specificato come combinazione tra un valore intero positivo e l'unità della parte della data.
SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Specifica i metodi consentiti di escalation blocchi per una tabella.
AUTO
Questa opzione consente al motore di database di SQL Server di selezionare la granularità dell'escalation blocchi appropriata per lo schema della tabella.
- Se la tabella è partizionata, sarà consentita l'escalation dei blocchi nella granularità a livello di heap o albero B (HoBT). In altre parole, l'escalation sarà consentita al livello di partizione. Dopo l'escalation del blocco al livello HoBT, il blocco non verrà inoltrato in un secondo momento alla granularità TABLE.
- Se la tabella non è partizionata, l'escalation blocchi viene eseguita nella granularità TABLE.
TABLE
L'escalation blocchi viene eseguita con una granularità a livello di tabella, indipendentemente dal partizionamento o meno della tabella. TABLE rappresenta il valore predefinito.
DISABLE
Evita che venga eseguita l'escalation blocchi nella maggior parte dei casi. I blocchi a livello di tabella non vengono completamente disattivati. Ad esempio, quando si esegue l'analisi di una tabella in cui non è presente alcun indice cluster in corrispondenza del livello di isolamento serializzabile, il motore di database dovrà acquisire un blocco di tabella per proteggere l'integrità dei dati.
REBUILD
Utilizzare la sintassi REBUILD WITH per ricompilare un'intera tabella che include tutte le partizioni in una tabella partizionata. Se nella tabella è presente un indice cluster, l'opzione REBUILD consente di ricompilare l'indice stesso. L'opzione REBUILD può essere eseguita come operazione ONLINE.
Utilizzare la sintassi REBUILD PARTITION per ricompilare un'unica partizione in una tabella partizionata.
PARTITION = ALL
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Ricompila tutte le partizioni in caso di modifica delle impostazioni di compressione della partizione.
REBUILD WITH ( <rebuild_option> )
Tutte le opzioni vengono applicate a una tabella con un indice cluster. Se nella tabella non è presente un indice cluster, sulla struttura di heap influiranno solo alcune opzioni.
Se con l'operazione REBUILD non viene indicata un'impostazione di compressione specifica, verrà usata l'impostazione di compressione corrente per la partizione. Per restituire l'impostazione corrente, eseguire una query sulla data_compression colonna nella vista del sys.partitions catalogo.
Per una descrizione completa delle opzioni di ricompilazione, vedere ALTER TABLE index_option.
DATA_COMPRESSION
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Specifica l'opzione di compressione dei dati per la tabella, il numero di partizione o l'intervallo di partizioni specificato. Le opzioni sono le seguenti:
NONE: la tabella o le partizioni specificate non vengono compresse. Questa opzione non si applica alle tabelle columnstore.
ROW: la tabella o le partizioni specificate vengono compresse usando la compressione di riga. Questa opzione non si applica alle tabelle columnstore.
PAGE: la tabella o le partizioni specificate vengono compresse usando la compressione di pagina. Questa opzione non si applica alle tabelle columnstore.
COLUMNSTORE
Si applica a: SQL Server (SQL Server 2014 (12.x) e versioni successive), Database SQL di Azure.
Si applica solo alle tabelle columnstore. Con COLUMNSTORE si specifica di decomprimere una partizione compressa con l'opzione COLUMNSTORE_ARCHIVE. Quando i dati vengono ripristinati, continuano a essere compressi con la compressione columnstore usata per tutte le tabelle columnstore.
COLUMNSTORE_ARCHIVE
Si applica a: SQL Server (SQL Server 2014 (12.x) e versioni successive), Database SQL di Azure.
Si applica solo alle tabelle columnstore, ovvero tabelle archiviate con un indice columnstore cluster. COLUMNSTORE_ARCHIVE comprimerà ulteriormente la partizione specificata a una dimensione inferiore. Usare questa opzione per l'archiviazione o altre situazioni in cui sono richieste dimensioni di archiviazione inferiori ed è possibile concedere più tempo per l'archiviazione e il recupero.
Per ricompilare contemporaneamente più partizioni, vedere index_option. Se la tabella non dispone di un indice cluster, la modifica della compressione dei dati comporta la ricompilazione dell'heap e degli indici non cluster. Per altre informazioni sulla compressione, vedere Compressione dei dati.
XML_COMPRESSION
Si applica a: SQL Server 2022 (16.x) e versioni successive Database SQL di Azure e Istanza gestita di SQL di Azure
Specifica l'opzione di compressione XML per tutte le colonne con tipo di dati xml incluse nella tabella. Le opzioni sono le seguenti:
In...
Le colonne che usano il tipo di dati xml vengono compresse.
OFF
Le colonne che usano il tipo di dati xml non vengono compresse.
ONLINE = { ON | OFF } <come si applica a single_partition_rebuild_option>
Specifica se una singola partizione delle tabelle sottostanti e degli indici associati è disponibile per le query e le modifiche dei dati durante l'operazione sull'indice. Il valore predefinito è OFF. È possibile eseguire REBUILD come operazione ONLINE.
In...
I blocchi di tabella a lungo termine non vengono mantenuti per la durata dell'operazione sugli indici. È necessario un blocco condiviso (S) sulla tabella all'inizio della ricompilazione dell'indice e un blocco di modifica schema (Sch-M) sulla tabella alla fine della ricompilazione dell'indice online. Sebbene entrambi i blocchi siano blocchi di metadati brevi, il blocco Sch-M deve attendere il completamento di tutte le transazioni bloccanti. Durante il tempo di attesa, il blocco Sch-M impedisce tutte le altre transazioni in attesa dovute a questo blocco in caso di accesso alla stessa tabella.
Nota
Con la ricompilazione dell'indice online è possibile impostare le opzioni low_priority_lock_wait descritte più avanti in questa sezione.
OFF
I blocchi di tabella vengono applicati per la durata dell'operazione sugli indici. Il blocco impedisce agli utenti di accedere alla tabella sottostante per la durata dell'operazione.
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
Si applica a: SQL Server (SQL Server 2008 (10.0.x) e versioni successive) e database SQL di Azure.
Nome del set di colonne. Un set di colonne è una rappresentazione XML non tipizzata che combina tutte le colonne di tipo sparse di una tabella in un output strutturato. Un set di colonne non può essere aggiunto a una tabella che contiene colonne di tipo sparse. Per altre informazioni sui set di colonne, vedere Utilizzare set di colonne.
{ ENABLE | DISABLE } FILETABLE_NAMESPACE
Si applica a: SQL Server (SQL Server 2012 (11.x) e versioni successive).
Consente di abilitare o disabilitare i vincoli definiti dal sistema su una tabella FileTable. Può essere utilizzato solo con una tabella FileTable.
SET ( FILETABLE_DIRECTORY = directory_name )
Si applica a: SQL Server (SQL Server 2012 (11.x) e versioni successive). Il database SQL di Azure non supporta FILETABLE.
Specifica un nome di directory FileTable compatibile con Windows. Questo nome deve essere univoco tra tutti i nomi di directory FileTable nel database. Il confronto di univocità non supporta la distinzione tra maiuscole e minuscole, nonostante le impostazioni delle regole di confronto SQL. Può essere utilizzato solo con una tabella FileTable.
REMOTE_DATA_ARCHIVE
Si applica a: SQL Server (SQL Server 2017 (14.x) e versioni successive).
Consente di abilitare o disabilitare Stretch Database per una tabella. Per altre informazioni, vedere Stretch Database.
Importante
Stretch Database è deprecato in SQL Server 2022 (16.x) e database SQL di Azure. Questa funzionalità verrà rimossa nelle versioni future del motore di database. Evitare di usare questa funzionalità in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente implementata.
Abilitazione di Stretch Database per una tabella
Quando si specifica ON per abilitare Stretch per una tabella, è necessario specificare anche MIGRATION_STATE = OUTBOUND per iniziare subito la migrazione dei dati o MIGRATION_STATE = PAUSED per posticiparla. Il valore predefinito è MIGRATION_STATE = OUTBOUND. Per altre informazioni sull'abilitazione di Stretch per una tabella, vedere Abilitare Stretch Database per una tabella.
Prerequisiti. Prima di abilitare Stretch per una tabella, è necessario abilitare la funzionalità nel server e nel database. Per altre informazioni, vedere Abilitare Stretch Database per un database.
Autorizzazione. L'abilitazione di Stretch per un database o una tabella richiede autorizzazioni db_owner. L'abilitazione di Stretch per una tabella richiede anche autorizzazioni ALTER per la tabella.
Disabilitazione di Stretch Database per una tabella
Quando si disabilita Stretch per una tabella, esistono due opzioni disponibili per i dati remoti che sono già stati migrati in Azure. Per altre informazioni, vedere Disabilitare Stretch Database e ripristinare i dati remoti.
Per disabilitare l'estensione per una tabella e copiare i dati remoti per la tabella da Azure a SQL Server, eseguire il comando seguente. Questo comando non può essere annullato.
ALTER TABLE <table_name> SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;
Questa operazione comporta costi di trasferimento dati e non può essere annullata. Per altre informazioni, vedere i dettagli sui prezzi dei trasferimenti di dati.
Dopo aver copiato tutti i dati remoti da Azure a SQL Server, l'estensione viene disabilitata per la tabella.
Per disabilitare l'estensione per una tabella e abbandonare i dati remoti, eseguire il comando seguente.
ALTER TABLE <table_name> SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;
Dopo aver disabilitato Stretch Database per una tabella, la migrazione di dati viene interrotta e i risultati della query non includono più i risultati della tabella remota.
Se si disabilita Stretch, la tabella remota non viene rimossa. Se si vuole eliminare la tabella remota, eseguire l'operazione tramite il portale di Azure.
[ FILTER_PREDICATE = { null | predicate } ]
Si applica a: SQL Server (SQL Server 2017 (14.x) e versioni successive).
Specifica facoltativamente un predicato di filtro per selezionare le righe di cui eseguire la migrazione da una tabella che contiene sia dati cronologici sia dati correnti. Il predicato deve eseguire la chiamata a una funzione inline con valori di tabella. Per altre informazioni, vedere Abilitare Stretch Database per una tabella e Selezionare le righe di cui eseguire la migrazione tramite una funzione di filtro.
Importante
Se si specifica un predicato del filtro inefficace, anche la migrazione dei dati risulterà inefficace. Stretch Database applica il predicato del filtro alla tabella usando l'operatore CROSS APPLY.
Se non si specifica un predicato del filtro, viene eseguita la migrazione dell'intera tabella.
Quando si specifica un predicato di filtro, è necessario specificare anche MIGRATION_STATE.
MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }
Si applica a: SQL Server (SQL Server 2017 (14.x) e versioni successive).
Specificare
OUTBOUNDper eseguire la migrazione dei dati da SQL Server ad Azure.Specificare
INBOUNDper copiare i dati remoti per la tabella da Azure a SQL Server e disabilitare Stretch per la tabella. Per altre informazioni, vedere Disabilitare Stretch Database e ripristinare i dati remoti.Questa operazione comporta costi di trasferimento dati e non può essere annullata.
Specificare
PAUSEDper sospendere o posticipare la migrazione dei dati. Per altre informazioni, vedere Sospendere e riprendere la migrazione dei dati.
WAIT_AT_LOW_PRIORITY
Si applica a: SQL Server (SQL Server 2014 (12.x) e versioni successive), Database SQL di Azure.
Per una ricompilazione di indice online è necessario attendere il blocco delle operazioni su questa tabella. WAIT_AT_LOW_PRIORITY indica che l'operazione di ricompilazione dell'indice online rimane in attesa di blocchi a priorità bassa, consentendo alle altre operazioni di proseguire mentre la compilazione dell'indice online è in attesa. L'omissione dell'opzione WAIT AT LOW PRIORITY equivale a WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).
MAX_DURATION = time [MINUTES]
Si applica a: SQL Server (SQL Server 2014 (12.x) e versioni successive), Database SQL di Azure.
Tempo di attesa, espresso con un valore intero specificato in minuti, dell'operazione SWITCH o dei blocchi di ricompilazione dell'indice online a priorità bassa durante l'esecuzione del comando DDL. Se l'operazione viene bloccata per il tempo specificato in MAX_DURATION, una delle azioni ABORT_AFTER_WAIT verrà eseguita. Il tempo MAX_DURATION è sempre espresso in minuti ed è possibile omettere la parola MINUTES.
ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]
Si applica a: SQL Server (SQL Server 2014 (12.x) e versioni successive), Database SQL di Azure.
NONE
Continuare ad attendere il blocco con priorità normale (regolare).
SELF
Esce dall'operazione SWITCH o DDL di ricompilazione dell'indice online attualmente in esecuzione senza eseguire alcuna azione.
BLOCKERS
Termina tutte le transazioni utente che attualmente bloccano l'operazione SWITCH o DDL di ricompilazione dell'indice online in modo da poter continuare l'operazione.
È necessaria l'autorizzazione ALTER ANY CONNECTION.
IF EXISTS
Si applica a: SQL Server 2016 (13.x) e versioni successive e Database SQL di Azure.
Elimina in modo condizionale la colonna o il vincolo solo se è già esistente.
RESUMABLE = { ON | OFF}
Si applica a: SQL Server 2022 (16.x) e versioni successive.
Specifica se un'operazione ALTER TABLE ADD CONSTRAINT sull'indice online è ripristinabile. L'operazione di aggiunta di un vincolo alla tabella è ripristinabile quando l'impostazione è ON. L'operazione Aggiungi vincolo di tabella non è ripristinabile quando OFF. Il valore predefinito è OFF. L'opzione RESUMABLE può essere usata come parte dell'istruzione ALTER TABLE index_option nell'istruzione ALTER TABLE table_constraint.
Quando MAX_DURATION viene usato con RESUMABLE = ON (richiede ONLINE = ON), indica il tempo (un valore intero specificato in minuti) per cui viene eseguita un'operazione online ripristinabile di aggiunta di un vincolo prima di essere sospesa. Se l'opzione non è specificata, l'operazione continua fino al completamento.
Per altre informazioni sull'abilitazione e sull'uso di operazioni ALTER TABLE ADD CONSTRAINT ripristinabili, vedere Aggiungere vincoli di tabella ripristinabili.
Osservazioni:
Per aggiungere nuove righe di dati, usare INSERT. Per rimuovere righe di dati, usare DELETE o TRUNCATE TABLE. Per modificare i valori nelle righe esistenti, usare UPDATE.
Se la cache delle procedure include piani di esecuzione che fanno riferimento alla tabella, l'istruzione ALTER TABLE li contrassegna per la ricompilazione durante l'esecuzione successiva.
Modificare le dimensioni di una colonna
È possibile modificare la lunghezza, la precisione o la scala di una colonna specificando nuove dimensioni per il tipo di dati della colonna. Usare la clausola ALTER COLUMN. Se nella colonna sono presenti dati, le nuove dimensioni non possono essere minori delle dimensioni massime dei dati. Inoltre, non è possibile definire la colonna in un indice, tranne nel caso in cui il tipo di dati della colonna sia varchar, nvarchar o varbinary e l'indice sia diverso dal risultato di un vincolo PRIMARY KEY. Vedere l'esempio nella sezione breve intitolata Modifica di una definizione di colonna.
Blocchi e ALTER TABLE
Le modifiche specificate in ALTER TABLE vengono implementate immediatamente. Se le modifiche richiedono l'alterazione delle righe nella tabella, le righe vengono aggiornate tramite ALTER TABLE. ALTER TABLE acquisisce un blocco di modifica dello schema (SCH-M) sulla tabella per verificare che durante la modifica nessun'altra connessione faccia riferimento ai dati o ai metadati della tabella, ad eccezione delle operazioni sugli indici online al termine delle quali è richiesto un breve blocco SCH-M. In un'operazione ALTER TABLE...SWITCH il blocco viene acquisito sia sulle tabelle di origine sia in quelle di destinazione. Le modifiche apportate alla tabella vengono registrate e possono essere recuperate completamente. Le modifiche che influiscono su tutte le righe di tabelle di grandi dimensioni, ad esempio l'eliminazione di una colonna o, in alcune edizioni di SQL Server, l'aggiunta di una colonna NOT NULL con un valore predefinito, possono richiedere molto tempo e generare un elevato numero di record del log. Eseguire queste istruzioni ALTER TABLE con la stessa attenzione dedicata alle istruzioni INSERT, UPDATE o DELETE che influiscono su molte righe.
Si applica al warehouse in Microsoft Fabric.
ALTER TABLE non può far parte di una transazione explict.
XEvents per l'opzione di partizione
I seguenti eventi XEvent sono correlati alle ALTER TABLE ... SWITCH PARTITION ricompilazione degli indici online e online.
- lock_request_priority_state
- process_killed_by_abort_blockers
- ddl_with_wait_at_low_priority
Aggiungere colonne NOT NULL come operazione online
A partire da SQL Server 2012 (11.x) Enterprise Edition, l'aggiunta di una colonna NOT NULL con un valore predefinito viene eseguita come operazione online quando il valore predefinito è una costante di runtime. Questo implica che l'operazione viene completata quasi istantaneamente nonostante l'elevato numero di righe nella tabella: durante l'operazione, le righe esistenti nella tabella non vengono aggiornate, ma il valore predefinito viene archiviato solo nei metadati della tabella e il valore viene cercato in base alle necessità nelle query che accedono a tali righe. Questo comportamento è automatico. Oltre alla sintassi ADD COLUMN, non è necessaria alcuna sintassi aggiuntiva per implementare l'operazione online. Una costante di runtime è un'espressione che produce lo stesso valore durante il runtime per ogni riga nella tabella nonostante il relativo determinismo. Esempi di costanti di runtime sono l'espressione costante "Dati temporanei personali" o la funzione di sistema GETUTCDATETIME(). Al contrario, le funzioni NEWID() e NEWSEQUENTIALID() non sono costanti di runtime perché viene generato un valore univoco per ogni riga della tabella. L'aggiunta di una colonna NOT NULL con un valore predefinito che non è una costante di runtime viene eseguita sempre offline e per tutta la durata dell'operazione viene acquisito un blocco esclusivo (SCH-M).
Mentre le righe esistenti fanno riferimento al valore archiviato nei metadati, il valore predefinito viene archiviato nella riga per tutte le nuove righe inserite e che non specificano un altro valore per la colonna. Il valore predefinito archiviato nei metadati viene spostato in una riga esistente quando la riga viene aggiornata, anche se la colonna effettiva non viene specificata nell'istruzione UPDATE, o se la tabella o l'indice cluster viene ricompilato.
Le colonne di tipo varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography o CLR UDTS non possono essere aggiunte in un'operazione online. Non è possibile aggiungere una colonna online se con tale operazione le dimensioni massime possibili per la riga superano il limite di 8.060 byte. In tal caso, la colonna viene aggiunta come operazione offline.
Esecuzione di piani paralleli
In SQL Server 2012 (11.x) Enterprise Edition e versioni successive, il numero di processori impiegati per eseguire una singola istruzione ALTER TABLE ADD (index-based) CONSTRAINT o DROP (indice cluster) CONSTRAINT è determinata dall'opzione di configurazione max degree of parallelism e dal carico di lavoro corrente. Se il motore di database rileva che il sistema è occupato, il grado di parallelismo dell'operazione viene ridotto automaticamente prima dell'avvio dell'esecuzione dell'istruzione. È possibile configurare manualmente il numero di processori utilizzati per eseguire l'istruzione mediante l'opzione MAXDOP. Per altre informazioni, vedere Configurare l'opzione di configurazione del server max degree of parallelism.
Tabelle partizionate
Oltre all'esecuzione di operazioni SWITCH che interessano tabelle partizionate, usare ALTER TABLE per modificare lo stato di colonne, vincoli e trigger di tali tabelle così come per le tabelle non partizionate. Non è tuttavia possibile usare questa istruzione per modificare il modo in cui la tabella stessa è partizionata. Per la ripartizione di una tabella partizionata, usare ALTER PARTITION SCHEME e ALTER PARTITION FUNCTION. Non è inoltre possibile modificare il tipo di dati di una colonna di una tabella partizionata.
Restrizioni per tabelle con viste associate a schema
Le restrizioni che si applicano a istruzioni ALTER TABLE eseguite su tabelle con viste associate a schema sono le stesse che vengono applicate alla modifica di tabelle con un indice semplice. È possibile aggiungere una colonna mentre non è consentito rimuovere o modificare una colonna che fa parte di una vista associata a uno schema. Se l'istruzione ALTER TABLE richiede la modifica di una colonna usata in una vista associata allo schema, ALTER TABLE ha esito negativo e il motore di database genera un messaggio di errore. Per altre informazioni sull'associazione allo schema e sulle viste indicizzate, vedere CREATE VIEW.
La creazione di una vista associata a uno schema che fa riferimento a tabelle di base non influisce sull'aggiunta o sulla rimozione di trigger in tali tabelle.
Indici e ALTER TABLE
Gli indici creati nell'ambito di un vincolo vengono eliminati con l'eliminazione del vincolo. Gli indici creati mediante CREATE INDEX devono essere eliminati mediante DROP INDEX. Usare l'istruzione ALTER INDEX per ricompilare un indice che costituisce una parte di una definizione di vincolo. Non è necessario eliminare e quindi aggiungere nuovamente il vincolo con ALTER TABLE.
Tutti gli indici e i vincoli basati su una colonna devono essere rimossi prima della rimozione della colonna.
Quando si elimina un vincolo con cui è stato creato un indice cluster, le righe di dati archiviate a livello foglia nell'indice cluster vengono archiviate in una tabella non cluster. È possibile eliminare l'indice cluster e spostare la tabella risultante in un altro filegroup o schema di partizione in una singola transazione specificando l'opzione MOVE TO. Per l'opzione MOVE TO vengono applicate le seguenti restrizioni:
- MOVE TO non può essere usata per viste indicizzate o indici non cluster.
- Lo schema di partizione o il filegroup deve essere già esistente.
- Se non si specifica MOVE TO, la tabella viene inserita nello stesso schema di partizione o nello stesso filegroup definito per l'indice cluster.
Quando si elimina un indice cluster, specificare l'opzione ONLINE **=** ON per evitare che la transazione DROP INDEX blocchi l'esecuzione di query e modifiche nei dati sottostanti e negli indici non cluster associati.
Per l'opzione ONLINE = ON vengono applicate le restrizioni seguenti:
- ONLINE = ON non è valida per gli indici cluster che sono anche disabilitati. Per eliminare gli indici disabilitati è necessario usare l'opzione ONLINE = OFF.
- È possibile eliminare un solo indice alla volta.
- ONLINE = ON non è valida per viste indicizzate, indici non cluster o indici su tabelle temporanee locali.
- ONLINE = ON non è valida per gli indici columnstore.
Per l'eliminazione di un indice cluster, lo spazio su disco temporaneo deve essere uguale alle dimensioni dell'indice cluster esistente. Questo spazio aggiuntivo viene rilasciato al termine dell'operazione.
Nota
Le opzioni elencate in <drop_clustered_constraint_option> si applicano a indici cluster in tabelle e non possono essere applicate a indici cluster in viste o a indici non cluster.
Replicare le modifiche dello schema
Per impostazione predefinita, quando si esegue l'istruzione ALTER TABLE in una tabella pubblicata in un server di pubblicazione SQL Server, la modifica si propaga a tutti i sottoscrittori di SQL Server. Questa funzionalità presenta alcune restrizioni e può essere disabilitata. Per altre informazioni, vedere Apportare modifiche allo schema nei database di pubblicazione.
Compressione dei dati
Le tabelle di sistema non possono essere abilitate per la compressione. Se la tabella è un heap, l'operazione di ricompilazione per la modalità ONLINE sarà a thread singolo. Utilizzare la modalità OFFLINE per un'operazione di ricompilazione di heap multithread. Per altre informazioni sulla compressione dei dati, vedere Compressione dei dati.
Per valutare il modo in cui la modifica dello stato di compressione influirà su una tabella, un indice o una partizione, usare la stored procedure di sistema sp_estimate_data_compression_savings.
Alle tabelle partizionate vengono applicate le restrizioni seguenti:
- Non è possibile modificare l'impostazione di compressione di una singola partizione se la tabella include indici non allineati.
- La sintassi
ALTER TABLE <table> REBUILD PARTITION... consente di ricompilare la partizione specificata. - La sintassi
ALTER TABLE <table> REBUILD WITH... consente di ricompilare tutte le partizioni.
Eliminare colonne NTEXT
Quando si eliminano le colonne che usano il tipo di dati NTEXT deprecato, la pulizia dei dati eliminati viene eseguita come operazione serializzata per tutte le righe. L'operazione di pulizia può richiedere una grande quantità di tempo. Per eliminare una colonna NTEXT in una tabella con molte righe, aggiornare la colonna NTEXT su un valore NULL, quindi eliminare la colonna. È possibile eseguire questa opzione con operazioni parallele rendendola molto più rapida.
RICOMPILAZIONE di indici online
Per eseguire l'istruzione DDL per una ricompilazione dell'indice online, è necessario completare tutte le transazioni bloccanti attive in esecuzione in una specifica tabella. Quando la ricompilazione dell'indice online viene avviata, blocca tutte le nuove transazioni pronte per l'esecuzione in questa tabella. Sebbene la durata del blocco della ricompilazione dell'indice online sia breve, l'attesa del completamento di tutte le transazioni aperte in una tabella specificata e il blocco dell'avvio di nuove transazioni potrebbero influire in modo significativo sulla velocità effettiva. Ciò può provocare un rallentamento o un timeout del carico di lavoro e limitare notevolmente l'accesso alla tabella sottostante. L'opzione WAIT_AT_LOW_PRIORITY consente agli amministratori di database di gestire i blocchi S e Sch-M necessari per le ricompilazioni degli indici online. In tutti e tre i casi, ovvero NONE, SELF e BLOCKERS, se durante il tempo di attesa ((MAX_DURATION =n [minutes])), non sono presenti attività di blocco, la ricompilazione dell'indice online viene eseguita immediatamente, senza attendere il completamento dell'istruzione DDL.
Informazioni sulla compatibilità
L'istruzione ALTER TABLE supporta unicamente nomi di tabella in due parti (schema.object). In SQL Server l'uso di un nome di tabella basato sui formati riportati di seguito comporta la generazione dell'errore 117 in fase di compilazione.
server.database.schema.table.database.schema.table..schema.table
Nelle versioni precedenti l'uso del formato server.database.schema.table genera l'errore 4902. La specifica del formato .database.schema.table o del formato ..schema.table ha esito positivo.
Per risolvere il problema, rimuovere l'uso di un prefisso in quattro parti.
Autorizzazioni
È necessario disporre dell'autorizzazione ALTER per la tabella.
Le autorizzazioni ALTER TABLE si applicano a entrambe le tabelle coinvolte in un'istruzione ALTER TABLE SWITCH. Tutti i dati trasferiti ereditano la sicurezza della tabella di destinazione.
Se nell'istruzione ALTER TABLE sono state definite colonne di tipo Common Language Runtime (CLR) definito dall'utente (UDT) o di tipo di dati alias, è necessaria l'autorizzazione REFERENCES per il tipo desiderato.
Per aggiungere o modificare una colonna con la quale vengono aggiornate le righe della tabella è necessaria l'autorizzazione UPDATE per la tabella. ad esempio per aggiungere una colonna NOT NULL con un valore predefinito o una colonna Identity quando la tabella non è vuota.
Esempi
| Categoria | Elementi di sintassi inclusi |
|---|---|
| Aggiunta di colonne e vincoli | ADD * PRIMARY KEY con opzioni per gli indici * colonne di tipo sparse e set di colonne * |
| Eliminazione di colonne e vincoli | DROP |
| Modifica della definizione di colonna | cambiare tipo di dati * cambiare dimensioni delle colonna * regole di confronto |
| Modifica della definizione di una tabella | DATA_COMPRESSION * SWITCH PARTITION OF * LOCK ESCALATION * rilevamento delle modifiche |
| Disabilitazione e abilitazione di vincoli e trigger | CHECK * NO CHECK * ENABLE TRIGGER * DISABLE TRIGGER |
| Operazioni online | ONLINE |
| Controllo delle versioni di sistema | SYSTEM_VERSIONING |
Aggiungere colonne e vincoli
Negli esempi di questa sezione viene illustrata l'aggiunta di colonne e vincoli a una tabella.
R. Aggiungere una nuova colonna
Nell'esempio seguente viene aggiunta una colonna che consente valori Null e alla quale non sono forniti valori mediante una definizione DEFAULT. In ogni riga della nuova colonna sarà indicato NULL.
CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO
B. Aggiungere una colonna con un vincolo
Nell'esempio seguente viene aggiunta una nuova colonna con un vincolo UNIQUE.
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. Aggiungere un vincolo CHECK non verificato a una colonna esistente
Nell'esempio seguente viene aggiunto un vincolo a una colonna esistente nella tabella. Nella colonna è presente un valore che viola il vincolo. Pertanto, viene utilizzato WITH NOCHECK per evitare che il vincolo venga convalidato in base alle righe esistenti e consentire l'aggiunta del vincolo.
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. Aggiungere un vincolo DEFAULT a una colonna esistente
Nell'esempio seguente viene creata una tabella con due colonne e viene inserito un valore nella prima colonna mentre i valori nell'altra colonna rimangono NULL. Viene quindi aggiunto un vincolo DEFAULT alla seconda colonna. Per verificare l'applicazione del vincolo, viene inserito un altro valore nella prima colonna e viene eseguita una query sulla tabella.
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. Aggiungere diverse colonne con vincoli
Nell'esempio seguente vengono aggiunte più colonne con vincoli. I vincoli vengono definiti con la nuova colonna. Alla prima colonna è associata la proprietà IDENTITY. Nella colonna Identity di ogni riga della tabella sono presenti nuovi valori incrementali.
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. Aggiungere una colonna nullable con valori predefiniti
Nell'esempio seguente viene aggiunta una colonna che ammette i valori Null con una definizione DEFAULT e viene specificato WITH VALUES per l'assegnazione di valori a ogni riga della tabella. Se non si usa WITH VALUES, a ogni riga della nuova colonna viene associato il valore NULL.
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. Creare un vincolo PRIMARY KEY con opzioni di compressione di indice o dati
Nell'esempio seguente viene creato il vincolo PRIMARY KEY PK_TransactionHistoryArchive_TransactionID e vengono impostate le opzioni FILLFACTOR, ONLINE e PAD_INDEX. All'indice cluster risultante sarà assegnato lo stesso nome del vincolo.
Si applica a: SQL Server 2008 (10.0.x) e versioni successive e database SQL di Azure.
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 questo esempio simile viene applicata la compressione di pagina durante l'applicazione della chiave primaria in cluster.
USE AdventureWorks;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (DATA_COMPRESSION = PAGE);
GO
H. Aggiungere una colonna di tipo sparse
Negli esempi seguenti si illustrano l'aggiunta e la modifica di colonne di tipo sparse nella tabella T1. Il codice per creare la tabella T1 è il seguente:
CREATE TABLE T1 (
C1 INT PRIMARY KEY,
C2 VARCHAR(50) SPARSE NULL,
C3 INT SPARSE NULL,
C4 INT) ;
GO
Per aggiungere una colonna di tipo sparse aggiuntiva C5, eseguire l'istruzione riportata di seguito.
ALTER TABLE T1
ADD C5 CHAR(100) SPARSE NULL ;
GO
Per convertire la colonna non di tipo sparse C4 in una colonna di tipo sparse, eseguire l'istruzione riportata di seguito.
ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO
Per convertire la colonna di tipo sparse C4 in una colonna non di tipo sparse, eseguire l'istruzione riportata di seguito.
ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE ;
GO
I. Aggiungere un set di colonne
Negli esempi seguenti viene illustrata l'aggiunta di una colonna alla tabella T2. Un set di colonne non può essere aggiunto a una tabella che contiene già colonne di tipo sparse. Il codice per creare la tabella T2 è il seguente:
CREATE TABLE T2 (
C1 INT PRIMARY KEY,
C2 VARCHAR(50) NULL,
C3 INT NULL,
C4 INT) ;
GO
Le tre istruzioni seguenti determinano l'aggiunta di un set di colonne denominato CS, quindi la modifica delle colonne C2 e 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. Aggiungere una colonna crittografata
L'istruzione seguente aggiunge una colonna crittografata denominata PromotionCode.
ALTER TABLE Customers ADD
PromotionCode nvarchar(100)
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') ;
K. Aggiungere una chiave primaria con un'operazione ripristinabile
Operazione ALTER TABLE ripristinabile per l'aggiunta di una chiave primaria in cluster nella colonna (a) con MAX_DURATION di 240 minuti.
ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);
Eliminare colonne e vincoli
Negli esempi di questa sezione viene illustrata l'eliminazione di colonne e vincoli.
R. Eliminare una colonna o colonne
Nel primo esempio viene modificata una tabella per rimuovere una colonna. Nel secondo esempio vengono rimosse più colonne.
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. Eliminare vincoli e colonne
Nel primo esempio viene rimosso un vincolo UNIQUE da una tabella. Nel secondo esempio vengono rimossi due vincoli e una singola colonna.
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. Eliminare un vincolo PRIMARY KEY nella modalità ONLINE
Nell'esempio seguente viene eliminato un vincolo PRIMARY KEY con l'opzione ONLINE impostata su ON.
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON) ;
GO
D. Aggiungere ed eliminare un vincolo FOREIGN KEY
Nell'esempio seguente viene creata la tabella ContactBackup, successivamente modificata con l'aggiunta di un vincolo FOREIGN KEY che fa riferimento alla tabella Person.Person. Il vincolo FOREIGN KEY viene quindi rimosso.
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 ;
Modificare una definizione di colonna
R. Modificare il tipo di dati di una colonna
Nell'esempio seguente la colonna di una tabella viene modificata da INT a 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. Modificare le dimensioni di una colonna
Nell'esempio seguente vengono aumentate le dimensioni di una colonna varchar e la precisione e la scala di una colonna decimal. Poiché le colonne contengono dati, le relative dimensioni possono solo essere aumentate. Si noti inoltre che col_a è definito in un indice univoco. Le dimensioni di col_a possono ancora essere aumentate poiché il tipo di dati è varchar e l'indice non è il risultato di un vincolo PRIMARY KEY.
-- 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. Modificare le regole di confronto delle colonne
Nell'esempio seguente si illustra come modificare le regole di confronto di una colonna. Innanzitutto, viene creata una tabella con le regole di confronto predefinite dell'utente.
CREATE TABLE T3 (
C1 INT PRIMARY KEY,
C2 VARCHAR(50) NULL,
C3 INT NULL,
C4 INT) ;
GO
In seguito le regole di confronto della colonna C2 vengono impostate su Latin1_General_BIN. Il tipo di dati è richiesto, anche se non è modificato.
ALTER TABLE T3
ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN ;
GO
D. Crittografare una colonna
L'esempio seguente illustra come crittografare una colonna usando Always Encrypted con enclave sicuri.
In primo luogo, viene creata una tabella senza colonne crittografate.
CREATE TABLE T3 (
C1 INT PRIMARY KEY,
C2 VARCHAR(50) NULL,
C3 INT NULL,
C4 INT) ;
GO
Successivamente, la colonna "C2" viene crittografata con una chiave di crittografia, denominata CEK1, e la crittografia casuale. Perché l'istruzione seguente abbia esito positivo:
- La chiave di crittografia della colonna deve essere abilitata per l'enclave, vale a dire che deve essere crittografata con una chiave master della colonna che consente i calcoli dell'enclave.
- L'istanza di SQL Server di destinazione deve supportare Always Encrypted con enclave sicuri.
- L'istruzione deve essere eseguita tramite una connessione configurata per Always Encrypted con enclave sicuri e usando un driver client supportato.
- L'applicazione chiamante deve poter accedere alla chiave master della colonna, la chiave
CEK1di protezione.
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
Modificare una definizione di tabella
Negli esempi di questa sezione viene illustrato come modificare la definizione di una tabella.
R. Modificare una tabella per modificare la compressione
Nell'esempio seguente viene modificata la compressione di una tabella non partizionata. L'heap o l'indice cluster verrà ricompilato. Se la tabella è un heap, tutti gli indici non cluster verranno ricompilati.
ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE) ;
Nell'esempio seguente viene modificata la compressione di una tabella partizionata. La sintassi REBUILD PARTITION = 1 consente di ricompilare solo il numero di partizione 1.
Si applica a: SQL Server 2008 (10.0.x) e versioni successive e database SQL di Azure.
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =NONE) ;
GO
Se per la stessa operazione viene utilizzata la sintassi alternativa seguente, vengono ricompilate tutte le partizioni della tabella.
Si applica a: SQL Server 2008 (10.0.x) e versioni successive e database SQL di Azure.
ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1)) ;
Per altri esempi sulla compressione dei dati, vedere Compressione dei dati.
B. Modificare una tabella columnstore per modificare la compressione dell'archivio
Nell'esempio seguente viene compressa una partizione di tabella columnstore applicando un algoritmo di compressione aggiuntivo. Questa compressione riduce le dimensioni della tabella, ma aumenta il tempo necessario per l'archiviazione e il recupero. È utile per l'archiviazione o in situazioni in cui è richiesto uno spazio inferiore ed è possibile concedere più tempo per l'archiviazione e il recupero.
Si applica a: SQL Server 2014 (12.x) e versioni successive, Database SQL di Azure.
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) ;
GO
Nell'esempio seguente viene decompressa una partizione di tabella columnstore compressa con l'opzione COLUMNSTORE_ARCHIVE. Quando i dati vengono ripristinati, continueranno a essere compressi con la compressione columnstore usata per tutte le tabelle columnstore.
Si applica a: SQL Server 2014 (12.x) e versioni successive, Database SQL di Azure.
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = COLUMNSTORE) ;
GO
C. Passare da una tabella all'altra
Nell'esempio seguente viene creata una tabella partizionata, presupponendo che nel database sia già stato creato lo schema di partizione myRangePS1. Verrà quindi creata una tabella non partizionata con la stessa struttura della tabella partizionata e nello stesso filegroup di PARTITION 2 della tabella PartitionTable. I dati di PARTITION 2 della tabella PartitionTable vengono quindi trasferiti nella tabella 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. Consenti escalation blocchi nelle tabelle partizionate
Nell'esempio seguente viene abilitata l'escalation blocchi a livello di partizione in una tabella partizionata. Se la tabella non è partizionata, l'escalation blocchi viene impostata a livello TABLE.
Si applica a: SQL Server 2008 (10.0.x) e versioni successive e database SQL di Azure.
ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO) ;
GO
E. Configurare il rilevamento delle modifiche in una tabella
Nell'esempio seguente viene abilitato il rilevamento delle modifiche per la tabella Person.Person.
Si applica a: SQL Server 2008 (10.0.x) e versioni successive e database SQL di Azure.
USE AdventureWorks;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING ;
Nell'esempio seguente viene abilitato il rilevamento delle modifiche e il rilevamento delle colonne aggiornate durante una modifica.
Si applica a: SQL Server 2008 (10.0.x) e versioni successive.
USE AdventureWorks;
GO
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
Nell'esempio seguente viene disabilitato il rilevamento delle modifiche per la tabella Person.Person.
Si applica a: SQL Server 2008 (10.0.x) e versioni successive e database SQL di Azure.
USE AdventureWorks;
GO
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING ;
Disabilitare e abilitare vincoli e trigger
R. Disabilitare e riabilitare un vincolo
Nell'esempio seguente viene disabilitato un vincolo che limita i dati relativi agli stipendi accettabili. NOCHECK CONSTRAINT viene utilizzata con ALTER TABLE per disabilitare il vincolo e consentire un inserimento che in genere violerebbe il vincolo. CHECK CONSTRAINT abilita nuovamente il vincolo.
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. Disabilitare e riabilitare un trigger
Nell'esempio seguente viene utilizzata l'opzione DISABLE TRIGGER di ALTER TABLE per disabilitare il trigger e consentire un inserimento che altrimenti violerebbe il trigger. ENABLE TRIGGER viene quindi utilizzato per abilitare nuovamente il trigger.
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
Operazioni online
R. Ricompilazione dell'indice online usando le opzioni di attesa con priorità bassa
L'esempio seguente illustra come eseguire la ricompilazione di un indice online specificando le opzioni di attesa con priorità bassa.
Si applica a: SQL Server 2014 (12.x) e versioni successive, Database SQL di Azure.
ALTER TABLE T1
REBUILD WITH
(
PAD_INDEX = ON,
ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES,
ABORT_AFTER_WAIT = BLOCKERS ) )
) ;
B. Modifica colonna online
L'esempio seguente illustra come eseguire un'operazione di modifica colonna con l'opzione ONLINE.
Si applica a: SQL Server 2016 (13.x) e versioni successive, Database SQL di Azure.
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
Controllo delle versioni di sistema
I quattro esempi riportati di seguito consentono di acquisire familiarità con la sintassi per l'uso del controllo delle versioni di sistema. Per altre istruzioni, vedere Introduzione alle tabelle temporali con controllo delle versioni di sistema.
Si applica a: SQL Server 2016 (13.x) e versioni successive, Database SQL di Azure.
R. Aggiungere il controllo delle versioni di sistema a tabelle esistenti
Nell'esempio seguente viene illustrato come aggiungere il controllo delle versioni di sistema a una tabella esistente e creare una tabella di cronologia futura. In questo esempio si presuppone l'esistenza di una tabella denominata InsurancePolicy con una chiave primaria definita. In questo esempio si popolano le colonne periodo appena create per il controllo delle versioni del sistema usando valori predefiniti per l'ora di inizio e di fine in quanto questi valori non possono essere Null. In questo esempio viene usata la clausola HIDDEN per evitare impatti sulle applicazioni esistenti che interagiscono con la tabella corrente. Viene anche usato HISTORY_RETENTION_PERIOD, disponibile solo nel database SQL.
--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. Eseguire la migrazione di una soluzione esistente per usare il controllo delle versioni di sistema
Nell'esempio seguente viene illustrato come eseguire la migrazione per il controllo delle versioni di sistema da una soluzione che usa i trigger per simulare il supporto temporale. In questo esempio si presuppone la disponibilità di una soluzione in cui siano usate una tabella ProjectTask e una tabella ProjectTaskHistory per la soluzione esistente, vale a dire le colonne Changed Date e Revised Date per i periodi. Tali colonne di periodo non devono usare il tipo di dati datetime2 e la tabella ProjectTask deve avere una chiave primaria definita.
-- 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. Disabilitare e riabilitare il controllo delle versioni del sistema per modificare lo schema della tabella
In questo esempio viene illustrato come disabilitare il controllo delle versioni di sistema nella tabella Department, aggiungere una colonna e riabilitare il controllo delle versioni di sistema. Per modificare lo schema tabella, è necessario disabilitare il controllo delle versioni di sistema. Eseguire questi passaggi all'interno di una transazione per impedire l'aggiornamento di entrambe le tabelle durante l'aggiornamento dello schema tabella. In questo modo, gli amministratori di database non dovranno verificare la coerenza dei dati quando il controllo delle versioni di sistema viene riabilitato e si garantiscono prestazioni migliori. Per altre attività come la creazione delle statistiche, il cambio delle partizioni o l'applicazione della compressione a una o entrambe le tabelle, non è necessaria la disabilitazione del controllo delle versioni di sistema.
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. Rimuovere il controllo delle versioni di sistema
In questo esempio viene illustrato come rimuovere completamente il controllo delle versioni di sistema della tabella Department ed eliminare la tabella DepartmentHistory. Facoltativamente, è anche possibile eliminare le colonne periodo usate dal sistema per registrare informazioni sul controllo delle versioni di sistema. Non è possibile eliminare le tabelle Department o DepartmentHistory mentre il controllo delle versioni di sistema è abilitato.
ALTER TABLE Department
SET (SYSTEM_VERSIONING = OFF) ;
ALTER TABLE Department
DROP PERIOD FOR SYSTEM_TIME ;
DROP TABLE DepartmentHistory ;
Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)
Gli esempi seguenti da A a C usano la FactResellerSales tabella nel database AdventureWorksPDW2022 .
R. Determinare se una tabella è partizionata
Tramite la query seguente vengono restituite una o più righe se la tabella FactResellerSales è partizionata. Se la tabella non è partizionata, non viene restituita alcuna riga.
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. Determinare i valori limite per una tabella partizionata
Tramite la query seguente vengono restituiti i valori limite per ogni partizione nella tabella FactResellerSales .
SELECT t.name AS TableName, i.name AS IndexName, p.partition_number,
p.partition_id, i.data_space_id, f.function_id, f.type_desc,
r.boundary_id, r.value AS BoundaryValue
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.partitions AS p
ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS s
ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
ON f.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE t.name = 'FactResellerSales' AND i.type <= 1
ORDER BY p.partition_number ;
C. Determinare la colonna di partizione per una tabella partizionata
La query seguente restituisce il nome della colonna di partizionamento per la tabella . 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. Unire due partizioni
Nell'esempio seguente si uniscono due partizioni in una tabella.
La tabella Customer presenta la definizione seguente:
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))) ;
Il comando seguente unisce i limiti delle partizioni 10 e 25.
ALTER TABLE Customer MERGE RANGE (10);
La nuova istruzione DDL per la tabella è la seguente:
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. Dividere una partizione
Nell'esempio seguente si suddivide una partizione in una tabella.
La tabella Customer presenta l'istruzione DDL seguente:
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 ))) ;
Il comando seguente consente di creare una nuova partizione associata al valore 75, compresa tra 50 e 100.
ALTER TABLE Customer SPLIT RANGE (75);
La nuova istruzione DDL per la tabella è la seguente:
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. Usare SWITCH per spostare una partizione in una tabella di cronologia
Nell'esempio seguente si spostano i dati in una partizione della tabella Orders in una partizione della tabella OrdersHistory.
La tabella Orders presenta l'istruzione DDL seguente:
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 questo esempio la tabella Orders contiene le partizioni seguenti. Ogni partizione contiene dati.
| Partizione | Contiene dati? | Intervallo limite |
|---|---|---|
| 1 | Sì | OrderDate < '2004-01-01' |
| 2 | Sì | '2004-01-01' <= OrderDate < '2005-01-01' |
| 3 | Sì | '2005-01-01' <= OrderDate< '2006-01-01' |
| 4 | Sì | '2006-01-01'<= OrderDate < '2007-01-01' |
| 5 | Sì | '2007-01-01' <= OrderDate |
- Partizione 1 (contiene dati): OrderDate < '2004-01-01'
- Partizione 2 (contiene dati): '2004-01-01' <= OrderDate < '2005-01-01'
- Partizione 3 (contiene dati): '2005-01-01' <= OrderDate< '2006-01-01'
- Partizione 4 (contiene dati): '2006-01-01'<= OrderDate < '2007-01-01'
- Partizione 5 (contiene dati): '2007-01-01' <= OrderDate
La tabella OrdersHistory contiene l'istruzione DDL seguente, con colonne identiche e gli stessi nomi di colonna della tabella Orders. Sono entrambe tabelle hash distribuite nella colonna 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'))) ;
Mentre le colonne e i nomi di colonna devono essere gli stessi, non è necessario che i limiti delle partizioni siano uguali. In questo esempio la tabella OrdersHistory contiene le due partizioni seguenti. Entrambe sono vuote:
- Partizione 1 (non contiene dati): OrderDate < '2004-01-01'
- Partizione 2 (vuota): '2004-01-01' <= OrderDate
Per le due tabelle precedenti, il comando seguente sposta tutte le righe con OrderDate < '2004-01-01' dalla tabella Orders alla tabella OrdersHistory.
ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;
Di conseguenza, la prima partizione in Orders è vuota e la prima partizione in OrdersHistory contiene dati. Le tabelle vengono visualizzate come indicato di seguito:
Tabella Orders
- Partizione 1 (vuota): OrderDate < '2004-01-01'
- Partizione 2 (contiene dati): '2004-01-01' <= OrderDate < '2005-01-01'
- Partizione 3 (contiene dati): '2005-01-01' <= OrderDate< '2006-01-01'
- Partizione 4 (contiene dati): '2006-01-01'<= OrderDate < '2007-01-01'
- Partizione 5 (contiene dati): '2007-01-01' <= OrderDate
Tabella OrdersHistory
- Partizione 1 (contiene dati): OrderDate < '2004-01-01'
- Partizione 2 (vuota): '2004-01-01' <= OrderDate
Per pulire la tabella Orders, è possibile rimuovere la partizione vuota unendo le partizioni 1 e 2 come indicato di seguito:
ALTER TABLE Orders MERGE RANGE ('2004-01-01');
Dopo l'unione, la tabella Orders conterrà le partizioni seguenti:
Tabella Orders
- Partizione 1 (contiene dati): OrderDate < '2005-01-01'
- Partizione 2 (contiene dati): '2005-01-01' <= OrderDate< '2006-01-01'
- Partizione 3 (contiene dati): '2006-01-01'<= OrderDate < '2007-01-01'
- Partizione 4 (contiene dati): '2007-01-01' <= OrderDate
Si supponga che sia passato un anno e che si voglia archiviare l'anno 2005. È possibile allocare una partizione vuota per l'anno 2005 nella tabella OrdersHistory suddividendo la partizione vuota come indicato di seguito:
ALTER TABLE OrdersHistory SPLIT RANGE ('2005-01-01');
Dopo la suddivisione, la tabella OrdersHistory conterrà le partizioni seguenti:
Tabella OrdersHistory
- Partizione 1 (contiene dati): OrderDate < '2004-01-01'
- Partizione 2 (vuota): '2004-01-01' < '2005-01-01'
- Partizione 3 (vuota): '2005-01-01' <= OrderDate