Linee guida per la disabilitazione di indici
La disabilitazione 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 disabilitato automaticamente dal Motore di database di SQL Server durante un aggiornamento di SQL Server. È anche possibile disabilitare manualmente un indice. Per ulteriori informazioni, vedere Disabilitazione di indici.
È possibile disabilitare qualsiasi tipo di indice. Quando un indice è disabilitato, vengono applicate le regole seguenti:
Se l'indice è univoco, vengono disabilitati 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 disabilita 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 disabilitati tutti i vincoli FOREIGN KEY in ingresso e in uscita nella tabella sottostante.
Quando viene disabilitato l'indice, i nomi dei vincoli sono elencati in un messaggio di avviso. Dopo avere ricompilato l'indice, è necessario abilitare manualmente i vincoli utilizzando l'istruzione ALTER TABLE CHECK CONSTRAINT.
Quando un indice è disabilitato, 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 disabilitato con un hint di tabella, inoltre, hanno esito negativo.
Non è possibile creare un indice che utilizzi lo stesso nome di un indice disabilitato esistente, in quanto la definizione dell'indice viene comunque mantenuta nei metadati.
È possibile eliminare un indice disabilitato.
Disabilitazione di indici non cluster
La disabilitazione di un indice non cluster comporta l'eliminazione fisica dei dati dell'indice. La definizione dell'indice, tuttavia, viene mantenuta nei metadati. Per la disabilitazione 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 disabilitati automaticamente quando viene disabilitato l'indice cluster associato e non possono essere abilitati fino all'abilitazione dell'indice cluster nella tabella o nella vista o all'eliminazione dell'indice cluster nella tabella. Gli indici non cluster devono essere abilitati in modo esplicito, a meno che l'indice cluster non sia stato abilitato utilizzando l'istruzione ALTER INDEX ALL REBUILD. Per ulteriori informazioni, vedere Linee guida per l'abilitazione di indici e vincoli.
Disabilitazione di indici cluster
Per la disabilitazione degli indici cluster si applicano le ulteriori linee guida seguenti:
È possibile accedere alle righe di dati degli indici cluster disabilitati solo per eliminare o ricompilare 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 è disabilitato.
Gli indici non cluster e XML esistenti associati alla tabella vengono automaticamente disabilitati e non è possibile accedervi.
Tutti gli indici cluster e non cluster nelle viste che fanno riferimento alla tabella vengono disabilitati. Tali indici devono essere ricompilati immediatamente dopo quelli inclusi nella tabella cui viene fatto riferimento.
Disabilitazione di vincoli
Le ulteriori linee guida elencate di seguito si applicano alla disabilitazione dei vincoli PRIMARY KEY, FOREIGN KEY e UNIQUE:
I vincoli PRIMARY KEY e UNIQUE vengono disabilitati disabilitando l'indice associato tramite l'istruzione ALTER INDEX DISABLE.
Quando viene disabilitato un vincolo PRIMARY KEY, vengono disabilitati 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 disabilitato, 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 è disabilitato oppure, in un aggiornamento di SQL Server, tramite la modifica che ha provocato la disabilitazione dell'indice. È necessario correggere manualmente le righe duplicate prima che sia possibile abilitare 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 disabilitati 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 riabilita 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 disabilitato un vincolo che limita i dati relativi agli stipendi accettabili. Nell'istruzione ALTER TABLE viene specificata la clausola NOCHECK CONSTRAINT per disabilitare il vincolo e consentire un inserimento che in genere violerebbe il vincolo. La clausola WITH CHECK CHECK CONSTRAINT riabilita il vincolo e convalida i dati esistenti in base al vincolo riabilitato.
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;
Disabilitazione di indici nelle viste
La disabilitazione di un indice cluster in una vista comporta l'eliminazione fisica dei dati dell'indice. Per la disabilitazione degli indici nelle viste si applicano le ulteriori linee guida seguenti:
La disabilitazione di un indice cluster in una vista non impedisce di apportare modifiche alla tabella sottostante.
La disabilitazione di un indice cluster in una vista comporta anche la disabilitazione 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 ricompilando l'indice o gli indici.
L'istruzione ALTER INDEX ALL REBUILD consente di ricompilare e abilitare tutti gli indici disabilitati nella tabella, ad eccezione degli indici disabilitati nelle viste. Gli indici nelle viste devono essere abilitati in un'istruzione ALTER INDEX ALL REBUILD distinta.
La ricompilazione dell'indice cluster in una vista non comporta l'abilitazione automatica degli indici non cluster nella vista.
Gli indici non cluster devono essere abilitati manualmente ricompilandoli in seguito alla ricompilazione dell'indice cluster.
Esecuzione di operazioni online su indici disabilitati
È possibile ricompilare online un indice non cluster disabilitato quando la tabella non include un indice cluster disabilitato. Tuttavia, è sempre necessario ricompilare offline un indice disabilitato se si utilizza l'istruzione ALTER INDEX REBUILD o CREATE INDEX WITH DROP_EXISTING. Per ulteriori informazioni sulle operazioni online sugli indici, vedere Esecuzione di operazioni online su indici.
Statistiche su indici disabilitati
Le restrizioni indicate di seguito sono valide per le statistiche dell'indice quando questo è disabilitato:
L'istruzione CREATE STATISTICS non viene eseguita correttamente in una tabella che include un indice cluster disabilitato.
L'opzione di database AUTO_CREATE_STATISTICS crea nuove statistiche per una colonna quando l'indice viene disabilitato 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 disabilitato.
La stored procedure sp_updatestats non aggiorna le statistiche sugli indici cluster disabilitati.
La stored procedure sp_createstats crea statistiche per le colonne che possono essere colonne iniziali di un indice disabilitato. Quando si specifica indexonly, non vengono create statistiche per una colonna di un indice disabilitato, a meno che la colonna non sia anche utilizzata in un altro indice abilitato.
Comandi DBCC
Se un indice cluster è disabilitato, DBCC CHECKDB non può restituire informazioni sulla tabella sottostante. L'istruzione indica invece che l'indice cluster è disabilitato. Non è possibile utilizzare DBCC INDEXDEFRAG per deframmentare un indice disabilitato. L'istruzione ha esito negativo e restituisce un messaggio di errore. È possibile utilizzare DBCC DBREINDEX per ricompilare un indice disabilitato.
Visualizzazione dello stato di un indice disabilitato
Quando viene disabilitato 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 disabilitato di un indice nella vista del catalogo sys.indexes oppure utilizzando la funzione INDEXPROPERTY. È possibile visualizzare lo stato disabilitato 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 disabilitato un indice non cluster nella tabella Employee.
USE AdventureWorks2008R2;
GO
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode
ON HumanResources.Employee DISABLE;
Vedere anche