Creare indici con colonne incluse

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

In questo articolo 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.

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 hanno 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 dell'indice o della dimensione delle chiavi dell'indice.

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

  • Evitare indici non cluster molto ampi in cui le colonne incluse non rappresentano un subset sufficientemente stretto delle colonne della tabella sottostante. Se si aggiungono indici di grandi dimensioni, verificare sempre se il costo dell'aggiornamento di un indice extra wide scosta il costo di lettura direttamente 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, ntexte 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, ntexte 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, nvarcharo 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 .

Uso 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. Selezionare il segno di addizione per espandere la cartella Tabelle.

  3. Fare clic sul segno di addizione 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 indicee 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, selezionare Aggiungi….

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

  8. Seleziona OK.

  9. Nella scheda Colonne incluse selezionare Aggiungi….

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

  11. Seleziona OK.

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

Per creare un indice con colonne non chiave utilizzando Transact-SQL

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

  2. Sulla barra Standard selezionare Nuova query.

  3. Copiare e incollare l'esempio seguente nella finestra di query e selezionare Esegui.

    USE AdventureWorks2022;
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

Contenuto correlato