Creare indici con colonne incluse

Si applica a: sìSQL Server (tutte le versioni supportate) Sìdatabase SQL di Azure

In questo argomento si illustra come aggiungere colonne incluse (o non chiave) per estendere la funzionalità di indici non cluster in SQL Server tramite SQL Server Management Studio o Transact-SQL. Con l'inclusione di colonne non chiave è possibile creare indici non cluster in grado di coprire più query. Ciò è possibile perché le colonne non chiave presentano i vantaggi seguenti:

  • Possono essere tipi di dati che non sono consentiti come colonne chiave indice.
  • Non vengono prese in esame dal Motore di database durante il calcolo del numero di colonne chiave indice o della dimensione delle chiavi di indice.

Con un indice con colonne non chiave è possibile aumentare significativamente le prestazioni delle query quando tutte le relative colonne sono incluse nell'indice come colonne chiave o non chiave. I vantaggi nelle prestazioni si ottengono poiché Query Optimizer può individuare tutti i valori delle colonne all'interno dell'indice. In questo modo, la quantità di operazioni di I/O su disco è inferiore dato che non viene eseguito alcun accesso ai dati delle tabelle o degli indici cluster.

Nota

Quando in un indice sono contenute tutte le colonne a cui fa riferimento una query, viene generalmente indicato come copertura della query.

Prima di iniziare

Indicazioni sulla progettazione

  • Progettare nuovamente gli indici non cluster con una chiave di indice di dimensioni elevate in modo da usare come colonne chiave solo le colonne usate per le ricerche. Modificare in colonne non chiave tutte le altre colonne che coprono la query. In questo modo si avranno tutte le colonne necessarie per coprire la query, contenendo al tempo stesso le dimensioni della chiave dell'indice e mantenendone l'efficienza.

  • Includere colonne non chiave in un indice non cluster per evitare il superamento delle limitazioni di dimensione correnti degli indici, ovvero numero massimo di colonne chiave pari a 32 e dimensione massima delle chiavi di indice pari a 1.700 byte (16 colonne chiave e 900 byte prima di SQL Server 2016 (13.x)). Il Motore di database non prende in esame le colonne non chiave durante il calcolo del numero di colonne chiave indice o della dimensione delle chiavi di indice.

  • L'ordine delle colonne non chiave nella definizione dell'indice non influisce sulle prestazioni delle query che utilizzano l'indice.

  • Evitare indici non cluster molto ampi in cui le colonne incluse non rappresentano un subset sufficientemente ristretto delle colonne della tabella sottostante. Se si aggiungono indici di grandi dimensioni, verificare sempre se il costo dell'aggiornamento di un indice di dimensioni molto grandi è compensato dal costo della lettura diretta dalla tabella.

Limitazioni e restrizioni

  • Le colonne non chiave possono essere definite solo negli indici non cluster.

  • È possibile usare come colonne non chiave tutti i tipi di dati eccetto text, ntext e image .

  • Le colonne calcolate deterministiche, precise o imprecise, possono essere colonne non chiave. Per altre informazioni, vedere Indici per le colonne calcolate.

  • Le colonne calcolate derivate dai tipi di dati image, ntext e text possono essere colonne non chiave purché il tipo di dati della colonna calcolata sia consentito come colonna non chiave dell'indice.

  • Non è possibile rimuovere da una tabella le colonne non chiave se non si è prima eliminato l'indice di questa tabella.

  • Non è possibile modificare le colonne non chiave se non per eseguire le operazioni seguenti:

    • Modifica del supporto di valori NULL della colonna da NOT NULL a NULL.

    • Aumento della lunghezza di colonne varchar, nvarchar o varbinary .

Sicurezza

Autorizzazioni

È richiesta l'autorizzazione ALTER per la tabella o la vista. L'utente deve essere un membro del ruolo predefinito del server sysadmin o dei ruoli predefiniti del database db_ddladmin e db_owner .

Utilizzo di SQL Server Management Studio

Per creare un indice con colonne non chiave

  1. In Esplora oggetti fare clic sul segno più per espandere il database contenente la tabella in cui si desidera creare un indice con colonne non chiave.

  2. Fare clic sul segno più per espandere la cartella Tabelle .

  3. Fare clic sul segno più per espandere la tabella in cui si desidera creare un indice con colonne non chiave.

  4. Fare clic con il pulsante destro del mouse sulla cartella Indici, scegliere Nuovo indice e selezionare Indice non cluster.

  5. Nella pagina Generale della finestra di dialogo Nuovo indice immettere il nome del nuovo indice nella casella Nome indice .

  6. Nella scheda Colonne chiave indice scegliere Aggiungi.

  7. Nella finestra di dialogo Seleziona colonne danome_tabella selezionare le caselle di controllo delle colonne della tabella da aggiungere all'indice.

  8. Fare clic su OK.

  9. Nella scheda Colonne incluse scegliere Aggiungi.

  10. Nella finestra di dialogo Seleziona colonne danome_tabella selezionare le caselle di controllo delle colonne di tabella da aggiungere all'indice come colonne non chiave.

  11. Fare clic su OK.

  12. Nella finestra di dialogo Nuovo indice fare clic su OK.

Uso di Transact-SQL

Per creare un indice con colonne non chiave

  1. In Esplora oggetti connettersi a un'istanza del Motore di database.

  2. Sulla barra Standard fare clic su Nuova query.

  3. Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui.

    USE AdventureWorks2012;  
    GO  
    -- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns.   
    -- index key column is PostalCode and the nonkey columns are  
    -- AddressLine1, AddressLine2, City, and StateProvinceID.  
    CREATE NONCLUSTERED INDEX IX_Address_PostalCode  
    ON Person.Address (PostalCode)  
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
    GO  
    

CREATE INDEX (Transact-SQL)
Guida per la progettazione di indici di SQL Server