Linee guida per la disattivazione di indici

Articolo
12/15/2008

La disattivazione di un indice impedisce all'utente di accedere all'indice e, per gli indici cluster, ai dati della tabella sottostante. È possibile che un indice venga disattivato automaticamente da Motore di database di SQL Server 2005 durante un aggiornamento di SQL Server. È anche possibile disattivare manualmente un indice. Per ulteriori informazioni, vedere Disattivazione di indici.

È possibile disattivare qualsiasi tipo di indice. Quando un indice è disattivato, vengono applicate le regole seguenti:

Se l'indice è univoco, vengono disattivati anche il vincolo PRIMARY KEY o UNIQUE e tutti i vincoli FOREIGN KEY che fanno riferimento a colonne indicizzate di altre tabelle. L'utente che disattiva l'indice deve disporre di autorizzazioni ALTER sulle tabelle. In caso contrario, l'istruzione ALTER INDEX DISABLE ha esito negativo. Se si tratta di un indice cluster, vengono disattivati tutti i vincoli FOREIGN KEY in ingresso e in uscita nella tabella sottostante.
Quando viene disattivato l'indice, i nomi dei vincoli sono elencati in un messaggio di avviso. Dopo avere ricostruito l'indice, è necessario attivare manualmente i vincoli utilizzando l'istruzione ALTER TABLE CHECK CONSTRAINT.
Quando un indice è disattivato, non ne viene eseguita la manutenzione.
In Query Optimizer l'indice non viene considerato durante la creazione dei piani di esecuzione della query. Le query che fanno riferimento all'indice disattivato con un hint di tabella, inoltre, hanno esito negativo.
Non è possibile creare un indice che utilizzi lo stesso nome di un indice disattivato esistente, in quanto la definizione dell'indice viene comunque mantenuta nei metadati.
È possibile eliminare un indice disattivato.

Disattivazione di indici non cluster

La disattivazione di un indice non cluster comporta l'eliminazione fisica dei dati dell'indice. La definizione dell'indice, tuttavia, viene mantenuta nei metadati. Per la disattivazione degli indici non cluster si applicano le ulteriori linee guida seguenti:

Le statistiche per l'indice vengono mantenute e aggiornate automaticamente in base alle necessità.
Gli indici non cluster vengono disattivati automaticamente quando viene disattivato l'indice cluster associato e non possono essere attivati fino all'attivazione dell'indice cluster nella tabella o nella vista o all'eliminazione dell'indice cluster nella tabella. Gli indici non cluster devono essere attivati in modo esplicito, a meno che l'indice cluster non sia stato attivato utilizzando l'istruzione ALTER INDEX ALL REBUILD. Per ulteriori informazioni, vedere Linee guida per l'attivazione di indici e vincoli.

Disattivazione di indici cluster

Per la disattivazione degli indici cluster si applicano le ulteriori linee guida seguenti:

È possibile accedere alle righe di dati degli indici cluster disattivati solo per eliminare o ricostruire l'indice cluster. Ne conseguono le osservazioni seguenti:
- Hanno esito negativo le istruzioni SELECT, UPDATE, DELETE, INSERT, CREATE INDEX, CREATE STATISTICS, UPDATE STATISTICS (nell'indice) e ALTER TABLE che modificano colonne o vincoli di tabella.
- Hanno esito positivo le istruzioni CREATE VIEW, DROP VIEW, CREATE TRIGGER, DROP TRIGGER, DROP INDEX, ALTER TABLE ENABLE/DISABLE TRIGGER, TRUNCATE TABLE e DROP TABLE.
- Non è possibile creare indici non cluster quando l'indice cluster è disattivato.
Gli indici non cluster e XML esistenti associati alla tabella vengono automaticamente disattivati e non è possibile accedervi.
Tutti gli indici cluster e non cluster nelle viste che fanno riferimento alla tabella vengono disattivati. Tali indici devono essere ricostruiti immediatamente dopo quelli inclusi nella tabella cui viene fatto riferimento.

Disattivazione di vincoli

Le ulteriori linee guida elencate di seguito si applicano alla disattivazione dei vincoli PRIMARY KEY, FOREIGN KEY e UNIQUE:

I vincoli PRIMARY KEY e UNIQUE vengono disattivati disattivando l'indice associato tramite l'istruzione ALTER INDEX DISABLE.
Quando viene disattivato un vincolo PRIMARY KEY, vengono disattivati anche tutti i vincoli FOREIGN KEY associati. Questa operazione equivale a impostare l'opzione NOCHECK CONSTRAINT nel vincolo.
È necessario disporre dell'autorizzazione ALTER o CONTROL sulle tabelle cui viene fatto riferimento.
Se in un riferimento di chiave esterna viene dichiarata un'azione CASCADE UPDATE o DELETE e il riferimento viene disattivato, qualsiasi aggiornamento o istruzione DELETE che provocherebbe la propagazione della modifica da parte del vincolo alla tabella di riferimento ha esito negativo.
È possibile che vengano accidentalmente aggiunti valori duplicati a una tabella mentre l'indice PRIMARY KEY o UNIQUE è disattivato oppure, in un aggiornamento di SQL Server, tramite la modifica che ha provocato la disattivazione dell'indice. È necessario correggere manualmente le righe duplicate prima che sia possibile attivare l'indice. È possibile adottare le soluzioni seguenti:
- Rimuovere o modificare manualmente i valori duplicati.
- Se l'indice UNIQUE non è stato creato come conseguenza della creazione di un vincolo UNIQUE, utilizzare CREATE INDEX WITH DROP_EXISTING per ricreare l'indice senza specificare UNIQUE.
- Se l'indice è stato creato come risultato di un vincolo PRIMARY KEY o UNIQUE, è necessario eliminare il vincolo. L'indice viene quindi eliminato. Per un vincolo PRIMARY KEY, è necessario eliminare qualsiasi vincolo FOREIGN KEY.
I vincoli FOREIGN KEY e CHECK disattivati vengono contrassegnati come is_not_trusted e sono visualizzabili nelle viste del catalogo sys.check_constraints e sys.foreign_keys. Ciò significa che il vincolo non viene più verificato dal sistema per tutte le righe della tabella. Anche quando si riattiva il vincolo, le righe esistenti non verranno più verificate in base alla tabella, a meno che non si specifichi l'opzione WITH CHECK di ALTER TABLE. Se si specifica WITH CHECK, il vincolo viene contrassegnato nuovamente come attendibile.
Nell'esempio seguente viene disattivato un vincolo che limita i dati relativi agli stipendi accettabili. Nell'istruzione ALTER TABLE viene specificata la clausola NOCHECK CONSTRAINT per disattivare il vincolo e consentire un inserimento che in genere violerebbe il vincolo. La clausola WITH CHECK CHECK CONSTRAINT riattiva il vincolo e convalida i dati esistenti in base al vincolo riattivato.
```
CREATE TABLE cnst_example 
(id INT NOT NULL,
    name VARCHAR(10) NOT NULL,
    salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000);
)

-- Disable the constraint.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap;

-- Reenable the constraint.
ALTER TABLE cnst_example WITH CHECK CHECK CONSTRAINT salary_cap;
```

Disattivazione di indici nelle viste

La disattivazione di un indice cluster in una vista comporta l'eliminazione fisica dei dati dell'indice. Per la disattivazione degli indici nelle viste si applicano le ulteriori linee guida seguenti:

La disattivazione di un indice cluster in una vista non impedisce di apportare modifiche alla tabella sottostante.
La disattivazione di un indice cluster in una vista comporta anche la disattivazione di tutti gli indici non cluster nella vista.
Le righe di dati dell'indice per gli indici cluster e non cluster vengono eliminate. Le definizioni della vista e dell'indice, tuttavia, vengono mantenute nei metadati e possono essere ricreate ricostruendo l'indice o gli indici.
L'istruzione ALTER INDEX ALL REBUILD consente di ricostruire e attivare tutti gli indici disattivati nella tabella, ad eccezione degli indici disattivati nelle viste. Gli indici nelle viste devono essere attivati in un'istruzione ALTER INDEX ALL REBUILD distinta.
La ricostruzione dell'indice cluster in una vista non comporta l'attivazione automatica degli indici non cluster nella vista.
Gli indici non cluster devono essere attivati manualmente ricostruendoli in seguito alla ricostruzione dell'indice cluster.

Esecuzione di operazioni in linea su indici disattivati

È possibile ricostruire in linea un indice non cluster disattivato quando la tabella non include un indice cluster disattivato. Tuttavia, è sempre necessario ricostruire non in linea un indice disattivato se si utilizza l'istruzione ALTER INDEX REBUILD o CREATE INDEX WITH DROP_EXISTING. Per ulteriori informazioni sulle operazioni in linea sugli indici, vedere Esecuzione di operazioni in linea su indici.

Statistiche su indici disattivati

Le restrizioni indicate di seguito sono valide per le statistiche dell'indice quando questo è disattivato:

L'istruzione CREATE STATISTICS non viene eseguita correttamente in una tabella che include un indice cluster disattivato.
L'opzione di database AUTO_CREATE_STATISTICS crea nuove statistiche per una colonna quando l'indice viene disattivato e si verificano le condizioni seguenti:
- L'opzione AUTO_CREATE_STATISTICS è impostata su ON
- Non è presente alcuna statistica esistente per la colonna.
- Le statistiche sono necessarie durante l'ottimizzazione delle query.
La stored procedure sp_autostats non viene eseguita correttamente quando la tabella specificata include un indice cluster disattivato.
La stored procedure sp_updatestats non aggiorna le statistiche sugli indici cluster disattivati.
La stored procedure sp_createstats crea statistiche per le colonne che possono essere colonne iniziali di un indice disattivato. Quando si specifica indexonly, non vengono create statistiche per una colonna di un indice disattivato, a meno che la colonna non sia anche utilizzata in un altro indice attivato.

Comandi DBCC

Se un indice cluster è disattivato, DBCC CHECKDB non può restituire informazioni sulla tabella sottostante. L'istruzione indica invece che l'indice cluster è disattivato. Non è possibile utilizzare DBCC INDEXDEFRAG per deframmentare un indice disattivato. L'istruzione ha esito negativo e restituisce un messaggio di errore. È possibile utilizzare DBCC DBREINDEX per ricostruire un indice disattivato.

Visualizzazione dello stato di un indice disattivato

Quando viene disattivato un indice o un vincolo PRIMARY KEY o UNIQUE, viene visualizzato un messaggio di avviso indicante tutti gli indici e i vincoli FOREIGN KEY o CHECK interessati. È inoltre possibile visualizzare lo stato disattivato di un indice nella vista del catalogo sys.indexes oppure utilizzando la funzione INDEXPROPERTY. È possibile visualizzare lo stato disattivato dei vincoli FOREIGN KEY e CHECK rispettivamente nelle viste del catalogo sys.foreign_keys e sys.check_constraints. Per ulteriori informazioni, vedere Visualizzazione delle informazioni relative agli indici.

Esempi

Nell'esempio seguente viene disattivato un indice non cluster nella tabella Employee.

USE AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID 
ON HumanResources.Employee DISABLE;

Vedere anche

Condividi tramite