ALTER INDEX (Transact-SQL)

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

Convenzioni della sintassi Transact-SQL

Sintassi

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [ 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
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

<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 degli indici devono essere univoci all'interno di una tabella o una vista, ma non necessariamente all'interno di un database. I nomi di indice devono essere conformi alle regole per gli 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 non in linea o di sola lettura oppure l'operazione specificata non è consentita per uno o più tipi di indice, l'istruzione ha esito negativo. Nella tabella seguente vengono indicati i tipi di indice non supportati per le varie operazioni sugli indici.

  Operazione sull'indice con ALL	Indici non supportati (l'istruzione ha esito negativo se la tabella include uno o più di questi indici)
  REBUILD WITH ONLINE = ON	Indici XML Colonne con tipo di dati LOB: image, text, ntext, varchar(max), nvarchar(max), varbinary(max) e xml
  REBUILD PARTITION = partition_number	Indici non partizionati, indici XML o indici disabilitati
  REORGANIZE	Indici con l'opzione ALLOW_PAGE_LOCKS impostata su OFF
  REORGANIZE PARTITION = partition_number	Indici non partizionati, indici XML o indici disabilitati
  SET	Indici disabilitati

  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 ricostruzione 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 della 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à ricostruito con le stesse colonne, lo stesso tipo di indice, lo stesso attributo di unicità e lo stesso tipo di ordinamento. Questa clausola equivale a DBCC DBREINDEX. REBUILD abilita un indice disabilitato. La ricostruzione di un indice cluster non comporta la ricostruzione 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 e IGNORE_DUP_KEY non sono valide per la ricostruzione di un indice XML.

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

  L'operazione di ricostruzione può essere sottoposta a una registrazione minima se viene utilizzato il modello di recupero del database con registrazione minima o con registrazione minima delle transazioni di massa. Per ulteriori informazioni, vedere Scelta di un modello di recupero per le operazioni sugli indici.

  [!NOTA] Quando si ricostruisce un indice XML primario, la tabella utente sottostante non è disponibile per tutta la durata dell'operazione sull'indice.

• PARTITION
  Specifica che verrà ricostruita 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_number
  Numero della partizione di un indice partizionato da ricostruire 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 ricostruisce una sola partizione (PARTITION = n), è possibile specificare le opzioni SORT_IN_TEMPDB e MAXDOP. Le operazioni di ricostruzione di una sola partizione non possono essere eseguite su indici XML.

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

• DISABLE
  Contrassegna l'indice come disabilitato e non disponibile per l'utilizzo nel Motore di database di SQL Server 2005. È 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 ulteriori informazioni, vedere Disattivazione di indici.

• REORGANIZE
  Specifica che verrà riorganizzato il livello foglia dell'indice. Questa clausola equivale a DBCC INDEXDEFRAG. L'istruzione ALTER INDEX REORGANIZE viene sempre eseguita in linea. Ciò significa che i blocchi di lunga durata a livello di tabella 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. La compattazione di questi dati può consentire un miglioramento dell'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 comporta la compattazione di tutte le colonne LOB in esso contenute. La riorganizzazione di un indice non cluster comporta la compattazione di tutte le colonne LOB non chiave (incluse) in esso contenute. Per ulteriori informazioni, vedere Creazione di indici con colonne incluse.

    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 e 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 ricostruire né riorganizzare l'indice. La parola chiave SET non può essere utilizzata con gli indici disabilitati.

• 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 viene omessa e l'opzione PAD_INDEX è impostata su ON, viene utilizzato il valore del fattore di riempimento archiviato in sys.indexes.
  • OFF o mancata impostazione di fillfactor
    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 che indica il livello di riempimento che deve essere applicato dal Motore di database per il livello foglia di ogni pagina di indice durante un'operazione di creazione o modifica di un indice. fillfactor deve essere un valore integer compreso tra 1 e 100. Il valore predefinito è 0.

  [!NOTA] I valori 0 e 100 sono equivalenti.

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

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

  Importante:
  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 dal 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 creazione 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 creazione 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 tempdb e creazione dell'indice.

• IGNORE_DUP_KEY = { ON | OFF }
  Specifica il tipo di messaggio visualizzato per i valori di chiave duplicati durante una transazione di inserimento che coinvolge più righe in un indice cluster o non cluster univoco. Il valore predefinito è OFF.

  • ON
    Viene visualizzato un messaggio di avviso e la transazione ha esito negativo solo per le righe che violano l'indice UNIQUE.
  • OFF
    Viene visualizzato un messaggio di errore e viene eseguito il rollback dell'intera transazione.

  L'impostazione di IGNORE_DUP_KEY viene applicata solo alle operazioni di inserimento eseguite dopo la creazione o la ricostruzione dell'indice. Questa impostazione non ha alcun effetto durante l'esecuzione dell'operazione sull'indice. IGNORE_DUP_KEY non ha inoltre alcun effetto nelle istruzioni UPDATE.

  L'opzione IGNORE_DUP_KEY non può essere impostata su ON per gli indici XML e gli indici creati per una vista. Per ulteriori informazioni, vedere CREATE INDEX (Transact-SQL).

• STATISTICS_NORECOMPUTE = { ON | OFF }
  Specifica se devono essere ricalcolate le statistiche di distribuzione. 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.

  Importante:
  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 modifiche dei dati durante l'operazione sull'indice. Il valore predefinito è OFF.

  [!NOTA] Le operazioni sugli indici in linea sono disponibili solo in SQL Server 2005 Enterprise Edition.

  • ON
    I blocchi di lunga durata a livello di tabella non vengono mantenuti per la durata di un'operazione sugli indici. Durante la fase principale dell'operazione sull'indice, viene mantenuto attivo solo un blocco preventivo condiviso (IS) 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 in linea di un indice cluster o la ricostruzione 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 a livello di tabella vengono applicati per la durata dell'operazione sugli indici. Un'operazione sugli indici non in linea che crea, ricostruisce o elimina un indice cluster oppure ricostruisce o elimina un indice non cluster acquisisce un blocco di modifica dello schema (SCH-M) sulla tabella. Tale blocco impedisce agli utenti di accedere alla tabella sottostante per la durata dell'operazione. Un'operazione sugli indici non in linea 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 in linea. Per ulteriori informazioni sui blocchi, vedere Modalità blocco.

  È possibile ricostruire in linea tutti gli indici, inclusi quelli di tabelle temporanee globali, ad eccezione dei seguenti:

  • Indici disabilitati
  • Indici XML
  • Indici di tabelle temporanee locali
  • Indici partizionati
  • Indici cluster, se la tabella sottostante contiene tipi di dati LOB
  • Indici non cluster definiti con colonne con un tipo di dati LOB

  È possibile ricostruire in linea indici non cluster se la tabella contiene tipi di dati LOB, ma nessuna di queste colonne viene utilizzata nella definizione dell'indice come colonna chiave o non chiave.

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

  • ON
    I blocchi a livello di riga sono consentiti durante l'accesso all'indice. Il Motore di database determina quando utilizzare blocchi a livello di riga.
  • OFF
    I blocchi a livello 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 Opzione max degree of parallelism. Utilizzare MAXDOP per limitare il numero di processori utilizzati durante l'esecuzione di un piano parallelo. Il valore massimo è 64 processori.

  I possibili valori di max_degree_of_parallelism sono i seguenti:

  • 1
    Disattiva la generazione di piani paralleli.
  • >1
    Consente di limitare al valore specificato il numero massimo di processori utilizzati in un'operazione parallela sugli indici.
  • 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 a indici paralleli.

  [!NOTA] Le operazioni parallele sugli indici sono supportate solo in SQL Server 2005 Enterprise Edition.

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 ricostruzione. 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 scansione 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 a indici paralleli.

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

Ricostruzione di indici

La ricostruzione di un indice consiste nell'eliminazione e nella ricreazione dell'indice. Questa operazione consente di rimuovere la frammentazione, rendere disponibile spazio su disco grazie alla compattazione delle pagine in base all'impostazione del fattore di riempimento esistente o specificata e riordinare le righe dell'indice in pagine contigue. Quando viene specificata la parola chiave ALL, vengono eliminati e ricostruiti tutti gli indici della tabella in una singola transazione. Non è necessario eliminare in anticipo i vincoli FOREIGN KEY. Quando vengono ricostruiti indici con un numero di extent pari o superiore a 128, il Motore di database posticipa le effettive deallocazioni delle pagine e i blocchi associati fino al termine del commit della transazione. Per ulteriori informazioni, vedere Eliminazione e ricostruzione di oggetti di grandi dimensioni.

[!NOTA] In generale, la ricostruzione o la riorganizzazione degli indici di piccole dimensioni spesso non riduce la frammentazione. Le pagine degli indici di piccole dimensioni vengono archiviate negli extent misti. Gli extent misti possono essere condivisi da otto oggetti al massimo e quindi la frammentazione in un indice di piccole dimensioni potrebbe non risultare ridotta dopo la riorganizzazione o la ricostruzione dell'indice. Per ulteriori informazioni sugli extent misti, vedere Pagine ed extent.

Nelle versioni precedenti di SQL Server è talvolta possibile ricostruire un indice non cluster per risolvere le inconsistenze causate da errori hardware. In SQL Server 2005 è ancora possibile correggere tali inconsistenze tra l'indice e l'indice cluster tramite la ricostruzione di un indice non cluster non in linea, Non è possibile, tuttavia, correggere le inconsistenze di indici non cluster tramite la ricostruzione dell'indice in linea, in quanto il meccanismo di ricostruzione in linea utilizza l'indice non cluster esistente come base per la ricostruzione e di conseguenza l'inconsistenza persiste. La ricostruzione dell'indice non in linea, invece, forza la scansione dell'indice cluster (o dell'heap). L'inconsistenza viene quindi rimossa. Come nelle versioni precedenti, il metodo consigliato per il recupero in seguito all'individuazione di inconsistenze consiste nel ripristino da backup dei dati interessati. È tuttavia possibile correggere le inconsistenze dell'indice tramite la ricostruzione non in linea 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. Questa operazione deframmenta il livello foglia di indici cluster e non cluster di tabelle e viste tramite il riordinamento fisico delle pagine al livello foglia in base all'ordine logico, da sinistra verso destra, dei nodi foglia. La riorganizzazione consente inoltre di compattare 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 che 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 Riorganizzazione e ricostruzione degli 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 non cluster o di un indice cluster di una vista elimina fisicamente i dati dell'indice. La disabilitazione di un indice cluster impedisce l'accesso ai dati, i quali tuttavia rimangono archiviati in forma non gestita nella struttura b-tree fino all'eliminazione o alla ricostruzione 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 si trova in una pubblicazione per la replica transazionale, non è possibile disabilitare gli indici associati alle colonne chiave primaria Tali indici sono necessari per la replica. Per disabilitare un indice, eliminare prima 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 ricostruire un indice cluster disabilitato. Per ulteriori informazioni, vedere Disattivazione di indici.

Impostazione di opzioni

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

Opzioni per blocchi a livello di riga e di pagina

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

Quando ALLOW_ROW_LOCKS = OFF e ALLOW_PAGE_LOCK = OFF, è consentito solo un blocco a livello di tabella durante l'accesso all'indice. Per ulteriori informazioni sulla configurazione della granularità dei blocchi per un indice, vedere Personalizzazione dei blocchi per un indice.

Se si specifica la parola chiave ALL durante l'impostazione di opzioni per blocchi a livello 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. Il Motore di database può comunque acquisire un blocco preventivo a livello di pagina (IS, IU o IX) per scopi interni.

Per ulteriori informazioni, vedere Escalation dei blocchi (Motore di database).

Operazioni sugli indici in linea

Quando si ricostruisce 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 esclusivi a livello di tabella vengono mantenuti attivi per un periodo di tempo molto limitato durante il processo di modifica.

La riorganizzazione di un indice viene sempre eseguita in linea. Questo processo non mantiene attivi i blocchi di lunga durata 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 in linea seguenti:

• Creazione di più indici non cluster.
• Riorganizzazione di indici diversi della stessa tabella.
• Riorganizzazione di indici diversi durante la ricostruzione di indici non sovrapposti della stessa tabella.

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

Per ulteriori informazioni, vedere Esecuzione di operazioni in linea su indici.

Autorizzazioni

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

Esempi

A. Ricostruzione di un indice

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

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

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

Nell'esempio seguente viene specificata la parola chiave ALL, che determina la ricostruzione di tutti gli indici associati alla tabella. Vengono inoltre impostate tre opzioni.

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

C. Ricostruzione 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 AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID 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 AdventureWorks;
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 ricostruzione dell'indice PRIMARY KEY.

USE AdventureWorks;
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. Ricostruzione di un indice partizionato

Nell'esempio seguente viene ricostruita una singola partizione, contrassegnata con il numero 5, dell'indice partizionato IX_TransactionHistory_TransactionDate. In questo esempio si presuppone che sia stato installato l'esempio di indice partizionato. Per informazioni sull'installazione, vedere Readme_PartitioningScript.

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

Vedere anche

Riferimento

CREATE INDEX (Transact-SQL)
sys.dm_db_index_physical_stats
EVENTDATA (Transact-SQL)

Altre risorse

Disattivazione di indici
Indici nelle colonne con tipo di dati XML
Esecuzione di operazioni in linea su indici
Riorganizzazione e ricostruzione degli indici

Guida in linea e informazioni

Assistenza su SQL Server 2005