ALTER INDEX (Transact-SQL)

  • Articolo

Consente di modificare un indice di tabella o di vista esistente, di tipo relazionale o XML, tramite la disabilitazione, la ricompilazione o la riorganizzazione dell'indice oppure tramite l'impostazione di opzioni per l'indice.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [PARTITION = ALL]
          [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
          | [ PARTITION = partition_number 
                [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
                ] 
            ]
        ]
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
  | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
    table_or_view_name
}

<rebuild_index_option > ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } }
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

Argomenti

  • index_name
    Nome dell'indice. I nomi di indice devono essere univoci all'interno di una tabella o di una vista ma non all'interno di un database. Devono inoltre essere conformi alle regole degli identificatori.

  • ALL
    Specifica tutti gli indici associati alla tabella o alla vista indipendentemente dal tipo di indice. Se viene specificata la parola chiave ALL e uno o più indici si trovano in un filegroup offline o di sola lettura oppure se l'operazione specificata non è consentita per uno o più tipi di indice, l'istruzione ha esito negativo. Nella tabella seguente vengono elencati le operazioni sugli indici e i tipi di indice non supportati.

    Indicazione di ALL con questa operazione

    Indici non supportati (l'istruzione ha esito negativo se la tabella include uno o più di questi indici)

    REBUILD WITH ONLINE = ON

    Indice XML

    Indice spaziale

    REBUILD PARTITION = partition_number

    Indice non partizionato, indice XML, indice spaziale o indice disabilitato

    REORGANIZE

    Indici con ALLOW_PAGE_LOCKS impostato su OFF

    REORGANIZE PARTITION = partition_number

    Indice non partizionato, indice XML, indice spaziale o indice disabilitato

    IGNORE_DUP_KEY = ON

    Indice spaziale

    Indice XML

    ONLINE = ON

    Indice spaziale

    Indice XML
    Nota di attenzioneAttenzione

    Per informazioni più dettagliate sulle operazioni di indice eseguibili online, vedere Linee guida per operazioni di indice online.

    Se la parola chiave ALL viene specificata con PARTITION = partition_number, tutti gli indici devono essere allineati, il che significa che devono essere partizionati in base a funzioni di partizione equivalenti. L'utilizzo di ALL con PARTITION comporta la ricompilazione o la riorganizzazione di tutte le partizioni degli indici con lo stesso partition_number. Per ulteriori informazioni sugli indici partizionati, vedere Tabelle e indici partizionati.

  • database_name
    Nome del database.

  • schema_name
    Nome dello schema a cui appartiene la tabella o la vista.

  • table_or_view_name
    Nome della tabella o vista associata all'indice. Per visualizzare un report sugli indici di un oggetto, utilizzare la vista del catalogo sys.indexes.

  • REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
    Specifica che l'indice verrà ricompilato con le stesse colonne, lo stesso tipo di indice, lo stesso attributo di univocità e lo stesso tipo di ordinamento. Questa clausola equivale a DBCC DBREINDEX. REBUILD abilita un indice disabilitato. La ricompilazione di un indice cluster non comporta la ricompilazione degli indici non cluster associati, a meno che non venga specificata la parola chiave ALL. Se non vengono specificate opzioni per l'indice, vengono applicati i valori esistenti delle opzioni per gli indici archiviati in sys.indexes. Per le opzioni il cui valore non è archiviato in sys.indexes, viene applicato il valore predefinito indicato nella definizione dell'argomento dell'opzione.

    Le opzioni ONLINE = ON e IGNORE_DUP_KEY = ON non sono valide per la ricompilazione di un indice XML o spaziale.

    Se viene specificata la parola chiave ALL e la tabella sottostante è un heap, l'operazione di ricompilazione non ha effetto sulla tabella. Vengono ricompilati tutti gli indici non cluster associati alla tabella.

    L'operazione di ricompilazione può essere sottoposta a una registrazione minima se viene utilizzato il modello di recupero del database con registrazione minima o con registrazione minima delle operazioni bulk.

    [!NOTA]

    Quando si ricompila un indice XML primario, la tabella utente sottostante non è disponibile per tutta la durata dell'operazione sull'indice.

  • PARTITION
    Specifica che verrà ricompilata o riorganizzata solo una partizione di un indice. La parola chiave PARTITION non può essere utilizzata se l'indice specificato in index_name non è di tipo partizionato.

    PARTITION = ALL consente di ricompilare tutte le partizioni.

    Nota di attenzioneAttenzione

    La creazione e la ricompilazione di indici non allineati per una tabella con oltre 1.000 partizioni sono possibili, ma non supportate. Questo tipo di operazioni può causare riduzioni delle prestazioni e un eccessivo consumo della memoria. Quando il numero di partizioni supera 1.000, si consiglia di utilizzare solo indici allineati.

  • partition_number
    Numero di partizioni di un indice partizionato da ricompilare o riorganizzare. partition_number è un'espressione costante che può fare riferimento a variabili, incluse variabili o funzioni con tipo definito dall'utente e funzioni definite dall'utente, ma non a istruzioni Transact-SQL. Se partition_number non esiste, l'istruzione ha esito negativo.

  • WITH (<single_partition_rebuild_index_option>)
    Quando si ricompila una singola partizione (PARTITION = n), è possibile specificare le opzioni SORT_IN_TEMPDB, MAXDOP e DATA_COMPRESSION. Gli indici XML non possono essere specificati in un'operazione di ricompilazione di una singola partizione.

    La ricompilazione di un indice partizionato non può essere eseguita online. Durante questa operazione, l'intera tabella è bloccata.

  • DISABLE
    Contrassegna l'indice come disabilitato e non disponibile per l'utilizzo in Motore di database. È possibile disabilitare qualsiasi indice. La definizione di un indice disabilitato rimane nel catalogo di sistema senza i dati dell'indice sottostante. La disabilitazione di un indice cluster impedisce all'utente di accedere ai dati della tabella sottostante. Per abilitare un indice, utilizzare ALTER INDEX REBUILD oppure CREATE INDEX WITH DROP_EXISTING. Per altre informazioni, vedere Disabilitazione di indici e vincoli e Abilitazione di indici e vincoli.

  • REORGANIZE
    Specifica che verrà riorganizzato il livello foglia dell'indice. L'istruzione ALTER INDEX REORGANIZE viene sempre eseguita online. Ciò significa che i blocchi di tabella a lungo termine non vengono mantenuti attivi e le query o gli aggiornamenti inerenti la tabella sottostante possono continuare durante la transazione ALTER INDEX REORGANIZE. La parola chiave REORGANIZE non può essere utilizzata con gli indici disabilitati o con gli indici per cui l'opzione ALLOW_PAGE_LOCKS è impostata su OFF.

  • WITH ( LOB_COMPACTION = { ON | OFF } )
    Specifica che tutte le pagine contenenti dati LOB devono essere compattate. I tipi di dati LOB sono image, text, ntext, varchar(max), nvarchar(max), varbinary(max) e xml. Compattando questi dati è possibile migliorare l'utilizzo dello spazio su disco. Il valore predefinito è ON.

    • ON
      Tutte le pagine contenenti dati LOB vengono compattate.

      La riorganizzazione di un indice cluster specificato compatta tutte le colonne LOB contenute nell'indice cluster. La riorganizzazione di un indice non cluster compatta tutte le colonne LOB che sono colonne non chiave (incluse) nell'indice.

      Quando viene specificata la parola chiave ALL, vengono riorganizzati tutti gli indici associati alla tabella o alla vista specificata e vengono compattate tutte le colonne LOB associate all'indice cluster, alla tabella sottostante o all'indice non cluster con colonne incluse.

    • OFF
      Le pagine contenenti dati LOB non vengono compattate.

      OFF non ha alcun effetto su un heap.

    La clausola LOB_COMPACTION viene ignorata se non sono presenti colonne LOB.

  • SET ( <set_index option> [ ,... n] )
    Specifica alcune opzioni per l'indice senza ricompilare né riorganizzare l'indice. La parola chiave SET non può essere specificata per un indice disabilitato.

  • PAD_INDEX = { ON | OFF }
    Specifica il riempimento dell'indice. Il valore predefinito è OFF.

    • ON
      La percentuale di spazio disponibile specificata in FILLFACTOR viene applicata alle pagine di livello intermedio dell'indice. Se l'opzione FILLFACTOR non viene specificata e l'opzione PAD_INDEX è impostata su ON, viene utilizzato il valore del fattore di riempimento archiviato in sys.indexes.

    • OFF o fillfactor non specificato
      Le pagine di livello intermedio vengono riempite poco al di sotto della capacità massima. Lo spazio residuo è sufficiente per almeno una riga della dimensione massima supportata dall'indice, in base al set di chiavi nelle pagine intermedie.

    Per ulteriori informazioni, vedere CREATE INDEX (Transact-SQL).

  • FILLFACTOR = fillfactor
    Specifica una percentuale indicante il livello di riempimento del livello foglia di ogni pagina di indice applicato da Motore di database durante la creazione o la modifica dell'indice. fillfactor deve essere un valore intero compreso tra 1 e 100. Il valore predefinito è 0.

    [!NOTA]

    I valori 0 e 100 relativi al fattore di riempimento sono equivalenti.

    Un'impostazione esplicita dell'opzione FILLFACTOR viene applicata solo in fase di creazione o ricompilazione dell'indice. La percentuale specificata di spazio vuoto delle pagine non viene mantenuta in modo dinamico da Motore di database. Per ulteriori informazioni, vedere CREATE INDEX (Transact-SQL).

    Per visualizzare l'impostazione del fattore di riempimento, utilizzare sys.indexes.

    Nota importanteImportante

    La creazione o la modifica di un indice cluster con un valore FILLFACTOR influisce sulla quantità di spazio di archiviazione occupata dai dati, perché i dati vengono ridistribuiti da Motore di database durante la creazione dell'indice cluster.

  • SORT_IN_TEMPDB = { ON | OFF }
    Specifica se i risultati dell'ordinamento devono essere archiviati in tempdb. Il valore predefinito è OFF.

    • ON
      I risultati intermedi dell'ordinamento utilizzati per la compilazione dell'indice vengono archiviati in tempdb. Se tempdb si trova in un set di dischi diverso rispetto al database utente, il tempo necessario per creare un indice potrebbe essere minore. 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.

    Se non è necessario eseguire un'operazione di ordinamento oppure se l'ordinamento può essere eseguito in memoria, l'opzione SORT_IN_TEMPDB viene ignorata.

    Per ulteriori informazioni, vedere Opzione SORT_IN_TEMPDB per gli indici.

  • IGNORE_DUP_KEY = { ON | OFF }
    Specifica l'errore restituito 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 ricostruzione dell'indice. Il valore predefinito è OFF.

    • ON
      Viene visualizzato un messaggio di avviso quando i valori di chiave duplicati vengono inseriti in un indice univoco. Avranno esito negativo solo le righe che violano il vincolo di univocità.

    • OFF
      Viene visualizzato un messaggio di errore quando i valori di chiave duplicati vengono inseriti in un indice univoco. Viene eseguito il rollback dell'intera operazione INSERT.

    L'opzione IGNORE_DUP_KEY non può essere impostata su ON per gli indici creati in una vista, gli indici non univoci, gli indici XML, spaziali e filtrati.

    Per visualizzare IGNORE_DUP_KEY, utilizzare sys.indexes.

    Per quanto riguarda la sintassi compatibile con le versioni precedenti, WITH IGNORE_DUP_KEY equivale a WITH IGNORE_DUP_KEY = ON.

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Specifica se le statistiche di distribuzione vengono ricalcolate. Il valore predefinito è OFF.

    • ON
      Le statistiche non aggiornate non vengono ricalcolate automaticamente.

    • OFF
      Abilita l'aggiornamento automatico delle statistiche.

    Per ripristinare l'aggiornamento automatico delle statistiche, impostare l'opzione STATISTICS_NORECOMPUTE su OFF oppure eseguire UPDATE STATISTICS senza la clausola NORECOMPUTE.

    Nota importanteImportante

    La disabilitazione del ricalcolo automatico delle statistiche di distribuzione può compromettere la selezione di piani di esecuzione ottimali per le query riguardanti la tabella in Query Optimizer.

  • ONLINE = { ON | OFF }
    Specifica se le tabelle sottostanti e gli indici associati sono disponibili per le query e per la modifica dei dati durante l'operazione sull'indice. Il valore predefinito è OFF.

    Per un indice XML o spaziale, è supportata solo l'opzione ONLINE = OFF e se ONLINE è impostata su ON viene generato un errore.

    [!NOTA]

    Le operazioni sugli indici online sono disponibili solo in alcune edizioni di Microsoft SQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere Funzionalità supportate dalle edizioni di SQL Server 2012.

    • ON
      I blocchi di tabella a lungo termine non vengono mantenuti per la durata dell'operazione sugli indici. Durante la fase principale dell'operazione sull'indice viene mantenuto solo un blocco preventivo condiviso (IS, Intent Shared) sulla tabella di origine, In questo modo, le query o gli aggiornamenti relativi alla tabella e agli indici sottostanti possono continuare. All'inizio dell'operazione viene mantenuto brevemente un blocco condiviso (S) sull'oggetto di origine. Al termine dell'operazione, se è in corso la creazione di un indice non cluster, viene mantenuto un blocco S sull'origine per un periodo di tempo molto breve. Se è in corso la creazione o l'eliminazione online di un indice cluster o la ricompilazione di un indice cluster o non cluster, viene acquisito un blocco di modifica dello schema (SCH-M). L'opzione ONLINE non può essere impostata su ON quando viene creato un indice per una tabella temporanea locale.

    • OFF
      I blocchi di tabella vengono applicati per la durata dell'operazione sugli indici. Un'operazione sull'indice offline che crea, ricompila o elimina un indice cluster, spaziale o XML oppure che 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 sull'indice offline che crea un indice non cluster acquisisce un blocco condiviso (S) sulla tabella. Tale blocco impedisce l'aggiornamento della tabella sottostante ma consente operazioni di lettura, ad esempio l'esecuzione di istruzioni SELECT.

    Per ulteriori informazioni, vedere Funzionamento delle operazioni sugli indici online.

    È possibile ricompilare online tutti gli indici, inclusi quelli di tabelle temporanee globali, ad eccezione dei seguenti:

    • Indici XML

    • Indici di tabelle temporanee locali

    • Un subset di un indice partizionato (è possibile ricompilare online un intero indice partizionato).

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Specifica se sono consentiti blocchi di riga. Il valore predefinito è ON.

    • ON
      I blocchi di riga sono consentiti durante l'accesso all'indice. Motore di database determina quando utilizzare blocchi a livello di riga.

    • OFF
      I blocchi di riga non vengono utilizzati.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Specifica se sono consentiti blocchi a livello di pagina. Il valore predefinito è ON.

    • ON
      I blocchi a livello di pagina sono consentiti durante l'accesso all'indice. Il Motore di database determina quando utilizzare blocchi a livello di pagina.

    • OFF
      I blocchi a livello di pagina non vengono utilizzati.

    [!NOTA]

    Quando l'opzione ALLOW_PAGE_LOCKS è impostata su OFF, non è possibile eseguire operazioni di riorganizzazione degli indici.

  • MAXDOP **=**max_degree_of_parallelism
    Ignora l'opzione di configurazione max degree of parallelism per tutta la durata dell'operazione sull'indice. Per ulteriori informazioni, vedere Configurare l'opzione di configurazione del server max degree of parallelism. Utilizzare MAXDOP per limitare il numero di processori utilizzati in una esecuzione di piani paralleli. Il valore massimo è 64 processori.

    Nota importanteImportante

    Sebbene l'opzione MAXDOP sia supportata a livello di sintassi per tutti gli indici XML, per un indice XML primario o spaziale ALTER INDEX utilizza attualmente solo un processore singolo.

    I possibili valori di max_degree_of_parallelism sono i seguenti:

    • 1
      Disattiva la generazione di piani paralleli.

    • >1
      Limita il numero massimo di processori utilizzati in una operazione parallela sugli indici al numero specificato.

    • 0 (predefinito)
      Utilizza il numero effettivo di processori o un numero inferiore in base al carico di lavoro corrente del sistema.

    Per ulteriori informazioni, vedere Configurazione di operazioni parallele sugli indici.

    [!NOTA]

    Le operazioni parallele sugli indici sono disponibili solo in alcune edizioni di Microsoft SQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere Funzionalità supportate dalle edizioni di SQL Server 2012.

  • DATA_COMPRESSION
    Specifica l'opzione di compressione dei dati per l'indice, il numero di partizione o l'intervallo di partizioni specificato. Sono disponibili le opzioni seguenti:

    • NONE
      L'indice o le partizioni specificate non vengono compressi.

    • ROW
      L'indice o le partizioni specificate vengono compressi utilizzando la compressione di riga.

    • PAGE
      L'indice o le partizioni specificate vengono compressi utilizzando la compressione di pagina.

    Per ulteriori informazioni sulla compressione, vedere Compressione dei dati.

  • ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )
    Specifica le partizioni alle quali si applica l'impostazione DATA_COMPRESSION. Se l'indice non è partizionato, l'argomento ON PARTITIONS genererà un errore. Se la clausola ON PARTITIONS non viene fornita, l'opzione DATA_COMPRESSION si applica a tutte le partizioni di un indice partizionato.

    È possibile specificare <partition_number_expression> nei modi seguenti:

    • Fornire il numero di una partizione, ad esempio ON PARTITIONS (2).

    • Fornire i numeri di partizione per più partizioni singole separati da virgole, ad esempio ON PARTITIONS (1, 5).

    • Fornire 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:

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

Osservazioni

L'istruzione ALTER INDEX non può essere utilizzata per ripartizionare un indice o spostarlo in un filegroup diverso né per modificare la definizione dell'indice, ad esempio per aggiungere o eliminare colonne oppure per modificarne l'ordine. Per eseguire queste operazioni, utilizzare CREATE INDEX con la clausola DROP_EXISTING.

Quando un'opzione non viene specificata in modo esplicito, viene applicata l'impostazione corrente. Se, ad esempio, non viene specificata un'impostazione per FILLFACTOR nella clausola REBUILD, verrà utilizzato il valore del fattore di riempimento archiviato nel catalogo di sistema durante il processo di ricompilazione. Per visualizzare le impostazioni correnti delle opzioni per gli indici, utilizzare sys.indexes.

[!NOTA]

I valori di ONLINE, MAXDOP e SORT_IN_TEMPDB non vengono archiviati nel catalogo di sistema. Se non viene specificato un valore nell'istruzione dell'indice, viene utilizzato il valore predefinito dell'opzione.

Nei computer multiprocessore l'istruzione ALTER INDEX REBUILD utilizza automaticamente più processori per eseguire le operazioni di analisi e ordinamento associate alla modifica dell'indice, in modo identico ad altre query. Quando si esegue ALTER INDEX REORGANIZE, con o senza LOB_COMPACTION, il valore di max degree of parallelism corrisponde a un'operazione a thread singolo. Per ulteriori informazioni, vedere Configurazione di operazioni parallele sugli indici.

Non è possibile riorganizzare o ricompilare indici contenuti in un filegroup offline o di sola lettura. Quando viene specificata la parola chiave ALL e uno o più indici si trovano in un filegroup offline o di sola lettura, l'istruzione ha esito negativo.

Ricompilazione di indici

La ricompilazione di un indice consiste nell'eliminazione e nella ricompilazione dell'indice. In questo modo si rimuove la frammentazione, si rende disponibile ulteriore spazio su disco grazie alla compattazione delle pagine in base all'impostazione del fattore di riempimento esistente o specificata e si riordinano le righe dell'indice in pagine contigue. Quando si specifica la parola chiave ALL, tutti gli indici della tabella vengono eliminati e ricompilati in una singola transazione. Non è necessario eliminare in anticipo i vincoli FOREIGN KEY. Quando vengono ricompilati indici con un numero di extent pari o superiore a 128, Motore di database posticipa le effettive deallocazioni delle pagine e i blocchi associati fino al termine del commit della transazione.

La ricompilazione o la riorganizzazione degli indici di dimensioni ridotte spesso non riduce la frammentazione. Le pagine di indici di dimensioni ridotte vengono archiviate in extent misti. Poiché gli extent misti possono essere condivisi al massimo da otto oggetti, la frammentazione in un indice di dimensioni ridotte potrebbe non ridursi dopo la riorganizzazione o la ricompilazione dell'indice.

In SQL Server 2012 le statistiche non vengono create analizzando tutte le righe nella tabella se viene creato o ricompilato un indice partizionato. Query Optimizer utilizza invece l'algoritmo di campionamento predefinito per generare statistiche. Per ottenere statistiche sugli indici partizionati analizzando tutte le righe nella tabella, utilizzare CREATE STATISTICS o UPDATE STATISTICS con la clausola FULLSCAN.

Nelle versioni precedenti di SQL Server è talvolta possibile ricompilare un indice non cluster per risolvere le inconsistenze causate da errori hardware. In SQL Server 2008 e nelle versioni successive, è ancora possibile correggere tali incoerenze tra l'indice e l'indice cluster tramite la ricompilazione di un indice non cluster offline. Non è possibile, tuttavia, correggere le incoerenze di indici non cluster tramite la ricompilazione dell'indice online, in quanto il meccanismo di ricompilazione online utilizza l'indice non cluster esistente come base per la ricompilazione e di conseguenza l'inconsistenza persiste. La ricompilazione dell'indice offline, invece, forza l'analisi dell'indice cluster (o dell'heap). L'incoerenza viene quindi rimossa. Come nelle versioni precedenti, il metodo consigliato per il recupero in seguito all'individuazione di incoerenze consiste nel ripristino da backup dei dati interessati. È tuttavia possibile correggere le incoerenze dell'indice tramite la ricompilazione offline dell'indice non cluster. Per ulteriori informazioni, vedere DBCC CHECKDB (Transact-SQL).

Riorganizzazione di indici

La riorganizzazione di un indice richiede una quantità minima di risorse di sistema. Tramite questa operazione si deframmenta il livello foglia di indici cluster e non cluster in tabelle e viste riordinando fisicamente le pagine al livello foglia in base all'ordine logico, da sinistra verso destra, dei nodi foglia. Tramite la riorganizzazione vengono inoltre compattate le pagine di indice in base al valore del fattore di riempimento esistente. Per visualizzare l'impostazione del fattore di riempimento, utilizzare sys.indexes.

Quando viene specificata la parola chiave ALL, vengono riorganizzati gli indici relazionali, sia cluster sia non cluster, e gli indici XML della tabella. La parola chiave ALL prevede alcune restrizioni. Per informazioni, vedere la relativa definizione nella sezione Argomenti.

Per ulteriori informazioni, vedere Riorganizzare e ricompilare gli indici.

Disabilitazione di indici

La disabilitazione di un indice impedisce agli utenti di accedere all'indice e, nel caso di indici cluster, ai dati della tabella sottostante. La definizione dell'indice rimane archiviata nel catalogo di sistema. La disabilitazione di un indice cluster o non cluster in una vista comporta l'eliminazione fisica dei dati dell'indice. La disabilitazione di un indice cluster impedisce l'accesso ai dati, i quali tuttavia rimangono archiviati in forma non gestita nell'albero B fino all'eliminazione o alla ricompilazione dell'indice. Per visualizzare lo stato di un indice abilitato o disabilitato, eseguire una query sulla colonna is_disabled della vista del catalogo sys.indexes.

Se una tabella è inclusa in una pubblicazione per la replica transazionale, non è possibile disabilitare alcun indice associato alle colonne chiave primaria. Questi indici sono necessari per la replica. Per disabilitare un indice, è innanzitutto necessario eliminare la tabella dalla pubblicazione. Per ulteriori informazioni, vedere Pubblicazione di dati e oggetti di database.

Per abilitare l'indice, utilizzare l'istruzione ALTER INDEX REBUILD oppure CREATE INDEX WITH DROP_EXISTING. Quando l'opzione ONLINE è impostata su ON, non è possibile ricompilare un indice cluster disabilitato. Per ulteriori informazioni, vedere Disabilitazione di indici e vincoli.

Impostazione delle opzioni

È possibile impostare le opzioni ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY e STATISTICS_NORECOMPUTE per un indice specificato senza ricompilare o riorganizzare l'indice. I valori modificati vengono applicati immediatamente all'indice. Per visualizzare tali impostazioni, utilizzare sys.indexes. Per ulteriori informazioni, vedere Impostare le opzioni di indice.

Opzioni per blocchi di riga e di pagina

Se ALLOW_ROW_LOCKS = ON e ALLOW_PAGE_LOCK = ON, sono consentiti blocchi di riga, di pagina e di tabella per l'accesso all'indice. Motore di database sceglie il blocco appropriato e può eseguire un'escalation del blocco da un blocco di riga o di pagina a un blocco di tabella.

Se ALLOW_ROW_LOCKS = OFF e ALLOW_PAGE_LOCK = OFF, sono consentiti solo blocchi a livello di tabella per l'accesso all'indice.

Se si specifica la parola chiave ALL durante l'impostazione di opzioni per blocchi di riga o di pagina, le impostazioni vengono applicate a tutti gli indici. Se la tabella sottostante è un heap, le impostazioni vengono applicate nei modi seguenti:

ALLOW_ROW_LOCKS = ON o OFF

Viene applicata all'heap e a tutti gli indici non cluster associati.

ALLOW_PAGE_LOCKS = ON

Viene applicata all'heap e a tutti gli indici non cluster associati.

ALLOW_PAGE_LOCKS = OFF

Viene applicata agli indici non cluster. Ciò significa che negli indici non cluster non è consentito alcun blocco a livello di pagina. Nell'heap non sono consentiti solo i blocchi condivisi (S), di aggiornamento (U) ed esclusivi (X) per la pagina. Motore di database può comunque acquisire un blocco preventivo a livello di pagina (IS, IU o IX) per scopi interni.

Operazioni sugli indici online

Quando si ricompila un indice e l'opzione ONLINE è impostata su ON, gli oggetti sottostanti, ovvero le tabelle e gli indici associati, risultano disponibili per query e operazioni di modifica dei dati. I blocchi di tabella esclusivi vengono mantenuti attivi per un periodo di tempo molto limitato durante il processo di modifica.

La riorganizzazione di un indice viene sempre eseguita online. Questo processo non mantiene attivi i blocchi a lungo termine e non blocca pertanto le query o gli aggiornamenti in corso.

In una stessa tabella è possibile eseguire in modo simultaneo solo le operazioni sugli indici online seguenti:

  • Creazione di più indici non cluster.

  • Riorganizzazione di indici diversi della stessa tabella.

  • Riorganizzazione di indici diversi durante la ricompilazione di indici non sovrapposti della stessa tabella.

Qualsiasi altra operazione sugli indici online eseguita contemporaneamente ha esito negativo. Non è ad esempio possibile ricompilare due o più indici della stessa tabella simultaneamente né creare un nuovo indice durante la ricompilazione di un indice esistente nella stessa tabella.

Per ulteriori informazioni, vedere Eseguire operazioni online sugli indici.

Restrizioni relative agli indici spaziali

Quando si ricompila un indice spaziale, la tabella utente sottostante non è disponibile per tutta la durata dell'operazione sull'indice, in quanto l'indice spaziale acquisisce un blocco di schema.

Il vincolo PRIMARY KEY nella tabella utente non può essere modificato se in una colonna di tale tabella è definito un indice spaziale. Per modificare il vincolo PRIMARY KEY, eliminare innanzitutto ogni indice spaziale dalla tabella. Dopo avere modificato il vincolo PRIMARY KEY, è possibile ricreare ognuno degli indici spaziali.

In un'operazione di ricompilazione di una singola partizione, non è possibile specificare indici spaziali. È tuttavia possibile specificare indici spaziali in una ricompilazione di partizioni completa.

Per modificare opzioni specifiche di un indice spaziale, ad esempio BOUNDING_BOX o GRID, è possibile utilizzare un'istruzione CREATE SPATIAL INDEX che specifica DROP_EXISTING = ON oppure rimuovere l'indice spaziale e crearne uno nuovo. Per un esempio, vedere CREATE SPATIAL INDEX (Transact-SQL).

Restrizioni relative agli indici columnstore

Ad eccezione dell'opzione REBUILD, un indice columnstore ottimizzato della memoria xVelocity non può essere modificato. Eliminare e ricreare invece l'indice columnstore.

Compressione dei dati

Per ulteriori 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, utilizzare la stored procedure sp_estimate_data_compression_savings.

Agli indici partizionati vengono applicate le restrizioni seguenti:

  • Quando si utilizza ALTER INDEX ALL ..., non è possibile modificare l'impostazione di compressione di una singola partizione se la tabella include indici non allineati.

  • La sintassi ALTER INDEX <index> ... REBUILD PARTITION... consente di ricompilare la partizione specificata dell'indice.

  • La sintassi ALTER INDEX <index> ... REBUILD WITH ... consente di ricompilare tutte le partizioni dell'indice.

Statistiche

Quando si esegue ALTER INDEX ALL … su una tabella, vengono aggiornate solo le statistiche associate agli indici. Le statistiche automatiche o manuali create sulla tabella, anziché su un indice, non vengono aggiornate.

Autorizzazioni

Per eseguire l'istruzione ALTER INDEX, è necessario disporre almeno dell'autorizzazione ALTER per la tabella o la vista.

Esempi

A. Ricompilazione di un indice

Nell'esempio seguente viene ricompilato un singolo indice della tabella Employee.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

B. Ricompilazione di tutti gli indici di una tabella e impostazione di opzioni

Nell'esempio seguente viene specificata la parola chiave ALL Tutti gli indici associati alla tabella vengono ricompilati. Vengono inoltre specificate tre opzioni.

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

C. Ricompilazione di un indice con la compattazione di dati LOB

Nell'esempio seguente viene riorganizzato un singolo indice cluster. Poiché l'indice contiene un tipo di dati LOB al livello foglia, l'istruzione compatta inoltre tutte le pagine contenenti dati LOB. Si noti che non è necessario specificare l'opzione WITH (LOB_COMPACTION) perché il valore predefinito è ON.

USE AdventureWorks2012;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

D. Impostazione di opzioni per un indice

Nell'esempio seguente vengono impostate varie opzioni per l'indice AK_SalesOrderHeader_SalesOrderNumber.

USE AdventureWorks2012;
GO
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Disabilitazione di un indice

Nell'esempio seguente viene disabilitato un indice non cluster della tabella Employee.

USE AdventureWorks2012;
GO
ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee
DISABLE ;
GO

F. Disabilitazione di vincoli

Nell'esempio seguente viene disabilitato un vincolo PRIMARY KEY tramite la disabilitazione dell'indice PRIMARY KEY. Il vincolo FOREIGN KEY della tabella sottostante viene disabilitato automaticamente e viene visualizzato un messaggio di avviso.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;
GO

Nel set di risultati viene restituito il messaggio di avviso seguente.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'

on table 'EmployeeDepartmentHistory' referencing table 'Department'

was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Abilitazione di vincoli

Nell'esempio seguente vengono abilitati i vincoli PRIMARY KEY e FOREIGN KEY disabilitati nell'esempio F.

Il vincolo PRIMARY KEY viene abilitato tramite la ricompilazione dell'indice PRIMARY KEY.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;
GO

Viene quindi abilitato il vincolo FOREIGN KEY.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Ricompilazione di un indice partizionato

Nell'esempio seguente viene ricompilata una singola partizione, contrassegnata con il numero 5, dell'indice partizionato IX_TransactionHistory_TransactionDate.

USE AdventureWorks;
GO
-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5;
GO

I. Modifica dell'impostazione di compressione di un indice

Nell'esempio seguente viene ricompilato un indice in una tabella non partizionata.

ALTER INDEX IX_INDEX1 
ON T1
REBUILD 
WITH ( DATA_COMPRESSION = PAGE )
GO

Per ulteriori esempi sulla compressione dei dati, vedere Compressione dei dati.

Vedere anche

Riferimento

CREATE INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

EVENTDATA (Transact-SQL)

Concetti

Disabilitazione di indici e vincoli

Indici XML (SQL Server)

Eseguire operazioni online sugli indici

Riorganizzare e ricompilare gli indici