ALTER TABLE index_option (Transact-SQL)
Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure database SQL in Microsoft Fabric
Specifica un set di opzioni che possono essere applicate a un indice incluso in una definizione di vincolo creata tramite l'istruzione ALTER TABLE.
Per una descrizione completa delle opzioni di indice, vedere CREATE INDEX.
Convenzioni relative alla sintassi Transact-SQL
Sintassi
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
| SORT_IN_TEMPDB = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
| XML_COMPRESSION = { ON | OFF }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
| ONLINE = { ON | OFF }
| RESUMABLE = { ON | OFF }
| MAX_DURATION = <time> [ MINUTES ]
}
<range> ::=
<partition_number_expression> TO <partition_number_expression>
<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 } )
}
Argomenti
PAD_INDEX = { ON | OFF }
Si applica a: SQL Server 2008 (10.0.x) e versioni successive
Specifica il riempimento dell'indice. Il valore predefinito è OFF
.
In...
La percentuale di spazio disponibile specificata da
FILLFACTOR
viene applicata alle pagine di livello intermedio dell'indice.OFF o fillfactor non è specificato
Le pagine di livello intermedio vengono riempite quasi completamente, ma viene lasciato spazio sufficiente per almeno una riga avente le dimensioni massime consentite dall'indice, in base al set di chiavi nelle pagine intermedie.
FILLFACTOR = fillfactor
Si applica a: SQL Server 2008 (10.0.x) e versioni successive
Specifica una percentuale che indica quanto deve essere riempito il livello foglia di ogni pagina di indice dal motore di database durante la creazione o la modifica dell'indice. Il valore specificato deve essere un valore integer compreso tra 1 e 100. Il valore predefinito è 0.
Nota
I valori 0 e 100 relativi al fattore di riempimento sono equivalenti.
IGNORE_DUP_KEY = { ON | OFF }
Specifica il tipo di risposta quando un'operazione di inserimento tenta di inserire valori di chiave duplicati in un indice univoco. L'opzione IGNORE_DUP_KEY
viene applicata solo alle operazioni di inserimento eseguite dopo la creazione o la ricompilazione dell'indice. L'opzione non ha alcun effetto se si esegue CREATE INDEX, ALTER INDEX o UPDATE. Il valore predefinito è OFF
.
In...
Viene visualizzato un messaggio di avviso quando in un indice univoco vengono inseriti valori chiave duplicati. Vengono considerate errate solo le righe che violano il vincolo di unicità.
OFF
Viene visualizzato un messaggio di errore quando in un indice univoco vengono inseriti valori chiave duplicati. Viene eseguito il rollback dell'intera
INSERT
operazione.
IGNORE_DUP_KEY
non può essere impostato su ON
per gli indici creati in una vista, indici non univoci, indici XML, indici spaziali e indici filtrati.
Per visualizzare IGNORE_DUP_KEY
, usare sys.indexes.
Nella sintassi compatibile con le versioni precedenti WITH IGNORE_DUP_KEY
equivale a WITH IGNORE_DUP_KEY = ON
.
STATISTICS_NORECOMPUTE = { ON | OFF }
Disabilitare o abilitare l'opzione di aggiornamento automatico delle statistiche, AUTO_STATISTICS_UPDATE
, per le statistiche correlate agli indici specificati. Il valore predefinito è OFF
.
In...
Gli aggiornamenti automatici delle statistiche vengono disabilitati dopo la ricompilazione dell'indice.
OFF
Gli aggiornamenti automatici delle statistiche vengono abilitati dopo la ricompilazione dell'indice.
Per ripristinare l'aggiornamento automatico delle statistiche, impostare su STATISTICS_NORECOMPUTE
OFF
o eseguire UPDATE STATISTICS
senza la NORECOMPUTE
clausola .
Avviso
Se si disabilita l'aggiornamento automatico delle statistiche, è possibile impedire a Query Optimizer di scegliere piani di esecuzione ottimali per le query che coinvolgono la tabella. È consigliabile usare questa opzione con moderazione e solo da un amministratore di database qualificato.
Questa impostazione non impedisce un aggiornamento automatico con analisi completa delle statistiche correlate all'indice durante l'operazione di ricompilazione.
ALLOW_ROW_LOCKS = { ON | OFF }
Si applica a: SQL Server 2008 (10.0.x) e versioni successive
Specifica se sono consentiti blocchi di riga. Il valore predefinito è ON.
In...
I blocchi di riga sono consentiti durante l'accesso all'indice. Il motore di database determina quando usare i blocchi di riga.
OFF
I blocchi di riga non vengono usati.
ALLOW_PAGE_LOCKS = { ON | OFF }
Si applica a: SQL Server 2008 (10.0.x) e versioni successive
Specifica se sono consentiti blocchi a livello di pagina. Il valore predefinito è ON.
In...
I blocchi a livello di pagina sono consentiti durante l'accesso all'indice. Il motore di database determina quando usare i blocchi di pagina.
OFF
I blocchi di pagina non vengono usati.
OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
Si applica a: SQL Server 2019 (15.x) e versioni successive
Specifica se eseguire o meno l'ottimizzazione per la contesa di inserimento dell'ultima pagina. Il valore predefinito è OFF
. Per altre informazioni, vedere la sezione Chiavi sequenziali dell'articolo CREATE INDEX
.
SORT_IN_TEMPDB = { ON | OFF }
Si applica a: SQL Server 2008 (10.0.x) e versioni successive
Specifica se archiviare i risultati dell'ordinamento in tempdb
. Il valore predefinito è OFF
.
In...
I risultati di ordinamento intermedi usati per compilare l'indice vengono archiviati in
tempdb
. Questo potrebbe ridurre il tempo necessario per creare un indice setempdb
si trova in un set di dischi diverso rispetto al database utente. La quantità di spazio su disco utilizzata durante la compilazione dell'indice sarà tuttavia maggiore.OFF
I risultati intermedi dell'ordinamento vengono archiviati nello stesso database dell'indice.
ONLINE = { ON | OFF }
Si applica a: SQL Server 2008 (10.0.x) e versioni successive
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
. REBUILD
può essere eseguita come ONLINE
operazione.
Nota
Non è possibile creare indici non cluster univoci online. Sono inclusi gli indici creati a causa di un UNIQUE
vincolo o PRIMARY KEY
.
In...
I blocchi di tabella a lungo termine non vengono mantenuti durante l'operazione sull'indice. Durante la fase principale dell'operazione viene mantenuto solo un blocco preventivo condiviso (IS, Intent Shared) sulla tabella di origine. in modo da consentire l'esecuzione di query o l'aggiornamento della tabella sottostante e degli indici. All'inizio dell'operazione, un blocco Condiviso (S) viene mantenuto sull'oggetto di origine per un breve periodo di tempo. Alla fine dell'operazione, per un breve periodo di tempo, viene acquisito un blocco S (condiviso) sull'origine se viene creato un indice non cluster; o un blocco Sch-M (modifica dello schema) viene acquisito quando un indice cluster viene creato o eliminato online e quando viene ricompilato un indice cluster o non cluster. Sebbene i blocchi sull'indice online siano blocchi di metadati brevi, soprattutto il blocco Sch-M deve attendere il completamento di tutte le transazioni bloccanti su questa tabella. 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.
ONLINE
non può essere impostato suON
quando viene creato un indice in una tabella temporanea locale.Nota
Con la ricompilazione dell'indice online è possibile impostare le opzioni low_priority_lock_wait descritte più avanti in questa sezione. L'opzione low_priority_lock_wait gestisce la priorità dei blocchi Sch-M e S durante la ricompilazione dell'indice online.
OFF
I blocchi di tabella vengono applicati durante l'operazione sull'indice. Il blocco impedisce a tutti gli utenti di accedere alla tabella sottostante durante l'operazione. 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 a tutti gli utenti di accedere alla tabella sottostante durante l'operazione. Un'operazione sugli indici offline che crea un indice non cluster acquisisce un blocco condiviso (S) sulla tabella. Ciò impedisce gli aggiornamenti alla tabella sottostante, ma consente operazioni di lettura, ad esempio
SELECT
istruzioni.
Per altre informazioni, vedere Funzionamento delle operazioni sugli indici online.
Nota
Le operazioni online sugli indici non sono disponibili in tutte le edizioni di Microsoft SQL Server. Per un elenco delle funzionalità supportate dalle varie edizioni di SQL Server, vedere Edizioni e funzionalità supportate di SQL Server 2022.
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
. Se l'opzione RESUMABLE
è impostata su ON
, l'opzione ONLINE = ON
è obbligatoria.
MAX_DURATION
se usato con RESUMABLE = ON
(richiede ONLINE = ON
) indica l'ora (un valore intero specificato in minuti) che un'operazione di aggiunta online ripristinabile viene eseguita prima di essere sospesa. Se l'opzione non è specificata, l'operazione continua fino al completamento. MAXDOP
è supportato anche con RESUMABLE = ON
.
Per altre informazioni sull'abilitazione e sull'uso di operazioni ALTER TABLE ADD CONSTRAINT
ripristinabili, vedere Aggiungere vincoli di tabella ripristinabili.
MAXDOP = max_degree_of_parallelism
Si applica a: SQL Server 2008 (10.0.x) e versioni successive
Sostituisce l'opzione di configurazione Massimo grado di parallelismo durante l'operazione sull'indice. Per altre informazioni, vedere Configurare il massimo grado di parallelismo (opzione di configurazione del server). Usare MAXDOP
per limitare il numero di processori usati in un'esecuzione di piano parallela. Il valore massimo è 64 processori.
max_degree_of_parallelism può essere:
1
: elimina la generazione di piani paralleli.>1
: limita il numero massimo di processori utilizzati in un'operazione di indice parallelo 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 ogni edizione di Microsoft SQL Server. Per un elenco delle funzionalità supportate dalle varie edizioni di SQL Server, vedere Edizioni e funzionalità supportate di SQL Server 2017.
DATA_COMPRESSION
Si applica a: SQL Server 2008 (10.0.x) e versioni successive
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. Si applica solo alle tabelle rowstore, non si applica alle tabelle columnstore.
ROW
La tabella o le partizioni specificate vengono compresse utilizzando la compressione di riga. Si applica solo alle tabelle rowstore, non si applica alle tabelle columnstore.
PAGE
La tabella o le partizioni specificate vengono compresse utilizzando la compressione di pagina. Si applica solo alle tabelle rowstore, non si applica alle tabelle columnstore.
COLUMNSTORE
Si applica a: SQL Server 2014 (12.x) e versioni successive
Si applica solo alle tabelle columnstore.
COLUMNSTORE
specifica di decomprimere una partizione compressa con l'opzioneCOLUMNSTORE_ARCHIVE
. Quando i dati vengono ripristinati, l'indiceCOLUMNSTORE
continua a essere compresso con la compressione columnstore usata per tutte le tabelle columnstore.COLUMNSTORE_ARCHIVE
Si applica a: SQL Server 2014 (12.x) e versioni successive
Si applica solo alle tabelle columnstore, ovvero tabelle archiviate con un indice columnstore cluster.
COLUMNSTORE_ARCHIVE
comprime ulteriormente la partizione specificata in una dimensione inferiore. Può essere utilizzata per l'archiviazione o in altre situazioni in cui sono richieste dimensioni di archiviazione inferiori ed è possibile concedere più tempo per l'archiviazione e il recupero.
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.
ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
Si applica a: SQL Server 2008 (10.0.x) e versioni successive
Specifica le partizioni a cui si applicano le impostazioni DATA_COMPRESSION
o XML_COMPRESSION
. Se la tabella non è partizionata, l'argomento ON PARTITIONS
genera un errore. Se la ON PARTITIONS
clausola non viene specificata, l'opzione DATA_COMPRESSION
o XML_COMPRESSION
si applica a tutte le partizioni di una tabella partizionata.
<partition_number_expression>
può essere specificato nei modi seguenti:
- Specificare il numero di una partizione, ad esempio :
ON PARTITIONS (2)
. - Specificare i numeri di partizione per più partizioni singole separati da virgole, ad esempio:
ON PARTITIONS (1, 5)
. - Specificare sia intervalli sia singole partizioni, ad esempio:
ON PARTITIONS (2, 4, 6 TO 8)
.
È possibile specificare <range>
sotto forma di numeri di partizione separati dalla parola TO, ad esempio: ON PARTITIONS (6 TO 8)
.
Per impostare tipi diversi di compressione dei dati per partizioni diverse, specificare più volte l'opzione DATA_COMPRESSION
, ad esempio:
--For rowstore tables
REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)
--For columnstore tables
REBUILD WITH
(
DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1, 3, 5),
DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4, 6 TO 8)
)
<single_partition_rebuild__option>
Nella maggior parte dei casi, la ricompilazione di un indice determina la ricompilazione di tutte le partizioni di un indice partizionato. Se le opzioni riportate di seguito sono applicate a una partizione singola, non determinano la ricompilazione di tutte le partizioni.
SORT_IN_TEMPDB
MAXDOP
DATA_COMPRESSION
XML_COMPRESSION
low_priority_lock_wait
Si applica a: SQL Server 2014 (12.x) e versioni successive
La ricompilazione di un SWITCH
indice online o viene completata non appena non sono presenti operazioni di blocco per questa tabella. WAIT_AT_LOW_PRIORITY indica che se l'operazione di ricompilazione dell'indice SWITCH
online o non può essere completata immediatamente, attende. L'operazione conserva i blocchi con priorità bassa, consentendo ad altre operazioni con blocchi in conflitto con l'istruzione DDL di continuare. L'omissione dell'opzione WAIT AT LOW PRIORITY
equivale a WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)
.
MAX_DURATION = tempo [ MINUTES ]
Tempo di attesa (valore intero specificato in minuti) che il blocco di ricompilazione dell'indice SWITCH
online o che deve essere acquisito, attende durante l'esecuzione del comando DDL. L'operazione SWITCH
di ricompilazione dell'indice online o tenta di completare immediatamente. Se l'operazione viene bloccata per il MAX_DURATION
tempo, viene eseguita una delle ABORT_AFTER_WAIT
azioni. MAX_DURATION
il tempo è sempre espresso in minuti e la parola MINUTES
può essere omessa.
ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS }
NONE
Continua l'operazione di ricompilazione dell'indice
SWITCH
online o senza modificare la priorità di blocco (usando la priorità regolare).SELF
Esce dall'operazione DDL di ricompilazione dell'indice
SWITCH
online o attualmente in esecuzione senza eseguire alcuna azione.BLOCKERS
Termina tutte le transazioni utente che bloccano attualmente l'operazione DDL di ricompilazione dell'indice
SWITCH
online o in modo che l'operazione possa continuare.BLOCKERS
richiede l'autorizzazioneALTER ANY CONNECTION
.