Creazione di indici (Motore di database)

In questo argomento vengono descritte le principali operazioni di creazione degli indici e vengono fornite linee guida relative all'implementazione e alle prestazioni da considerare prima di procedere alla creazione di un indice.

Operazioni di creazione degli indici

Di seguito sono elencate le operazioni che costituiscono la strategia consigliata per la creazione di indici:

1. Progettare l'indice.
   La progettazione degli indici rappresenta un'attività di fondamentale importanza. Nell'ambito della progettazione degli indici è necessario determinare le colonne da utilizzare, selezionare il tipo di indice, ad esempio cluster o non cluster, selezionare le opzioni di indice appropriate e determinare il posizionamento dello schema di partizione o di filegroup. Per ulteriori informazioni, vedere Progettazione di indici.
2. Determinare il metodo di creazione ottimale. Per la creazione degli indici è possibile utilizzare i metodi seguenti:
   • Definire un vincolo PRIMARY KEY o UNIQUE su una colonna tramite CREATE TABLE o ALTER TABLE.
     In Motore di database di SQL Server 2005 viene creato automaticamente un indice univoco per imporre i requisiti di univocità di un vincolo PRIMARY KEY o UNIQUE. Per impostazione predefinita, viene creato un indice cluster univoco per imporre un vincolo PRIMARY KEY, a meno che nella tabella non esista già un indice cluster oppure non venga specificato un indice non cluster univoco. Per impostazione predefinita, viene creato un indice non cluster univoco per imporre un vincolo UNIQUE a meno che non venga specificato in modo esplicito un indice cluster univoco e nella tabella non esista un indice cluster.
     È inoltre possibile specificare opzioni e posizione dell'indice, nonché lo schema di partizione o di filegroup.
     A un indice creato nell'ambito di un vincolo PRIMARY KEY o UNIQUE viene automaticamente assegnato lo stesso nome del vincolo. Per ulteriori informazioni, vedere Vincoli PRIMARY KEY e Vincoli UNIQUE.
   • Creare un indice indipendente da un vincolo tramite l'istruzione CREATE INDEX oppure la finestra di dialogo Nuovo indice in Esplora oggetti di SQL Server Management Studio.
     È necessario specificare il nome dell'indice, della tabella e delle colonne cui applicare l'indice. È inoltre possibile specificare opzioni e posizione dell'indice, nonché lo schema di partizione o di filegroup. Per impostazione predefinita, viene creato un indice non cluster non univoco se non sono state specificate le opzioni di clustering e di univocità.
3. Creare l'indice.
   La creazione dell'indice in una tabella vuota o in una che contiene dati costituisce un importante fattore da tenere in considerazione. La creazione di un indice in una tabella vuota non influisce sulle prestazioni quando si crea l'indice, ma quando si aggiungono dati alla tabella.
   È opportuno pianificare con attenzione la creazione di indici in tabelle di grandi dimensioni in modo da non influire negativamente sulle prestazioni del database. La strategia ottimale per la creazione di indici in tabelle di grandi dimensioni consiste nel creare innanzitutto l'indice cluster e quindi gli eventuali indici non cluster. Provare a impostare l'opzione ONLINE su ON quando si creano indici in tabelle esistenti. Se questa opzione è impostata su ON, i blocchi a lungo termine a livello di tabella non vengono mantenuti, pertanto l'esecuzione di query o aggiornamenti sulla tabella sottostante può proseguire. Per ulteriori informazioni, vedere Esecuzione di operazioni in linea su indici.

Considerazioni sull'implementazione

Nella tabella seguente sono elencati i valori massimi applicabili a indici cluster, non cluster e XML. A meno che non venga specificato, le limitazioni sono valide per tutti i tipi di indice.

Limiti massimi dell'indice	Valore	Informazioni aggiuntive
Indici cluster per tabella	1
Indici non cluster per tabella	249	Include gli indici non cluster creati tramite i vincoli PRIMARY KEY o UNIQUE, ma non gli indici XML.
Indici XML per tabella	249	Include gli indici XML primario e secondario su colonne il cui tipo di dati è xml. Indici nelle colonne con tipo di dati XML
Numero di colonne chiave per indice	16*	Dimensione massima delle chiavi di indice. L'indice cluster è limitato a 15 colonne se la tabella contiene anche un indice XML primario.
Dimensione massima del record delle chiavi di indice	900 byte*	Non applicabile agli indici XML. Dimensione massima delle chiavi di indice.

*Per ovviare alle limitazioni degli indici non cluster relative al numero di colonne chiave dell'indice e alla dimensione dei record, includere nell'indice colonne non chiave. Per ulteriori informazioni, vedere Indice con colonne incluse.

Tipi di dati

È in genere possibile indicizzare qualsiasi colonna di una tabella o di una vista. Nella tabella seguente sono elencati i tipi di dati per i quali l'indicizzazione può essere soggetta a restrizioni.

Tipo di dati	Indicizzazione	Informazioni aggiuntive
Tipo CLR definito dall'utente	Può essere indicizzato se è supportato l'ordinamento binario.	Utilizzo di tipi CLR definiti dall'utente
Tipi di dati LOB (Large Object): image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml	Non può essere una colonna chiave dell'indice. Una colonna xml può tuttavia essere una colonna chiave in un indice XML primario o secondario di una tabella. Può essere indicizzato come colonne non chiave (incluse) di un indice non cluster, ad eccezione di image, ntext e text. Può essere indicizzato se è incluso in un'espressione di colonna calcolata.	Indice con colonne incluse Indici nelle colonne con tipo di dati XML
Colonne calcolate	Può essere indicizzato. Sono incluse le colonne calcolate definite come chiamate di metodo di una colonna di tipo CLR definito dall'utente, purché i metodi siano contrassegnati come deterministici. Le colonne calcolate derivate da tipi di dati LOB possono essere indicizzate come colonne chiave o non chiave, purché il tipo di dati della colonna calcolata possa essere utilizzato per una colonna chiave di indice o una colonna non chiave.	Creazione di indici per le colonne calcolate
Colonne Varchar spostate all'esterno delle righe	La chiave di un indice cluster non può contenere colonne varchar per le quali sono presenti dati nell'unità di allocazione ROW_OVERFLOW_DATA. Se si crea un indice cluster su una colonna varchar e nell'unità di allocazione IN_ROW_DATA sono disponibili dati esistenti, le successive operazioni di inserimento o aggiornamento eseguite sulla colonna e che comportano uno spostamento dei dati all'esterno delle righe avranno esito negativo.	Organizzazione di tabelle e indici Dati di overflow della riga che superano 8 KB

Considerazioni aggiuntive

Di seguito sono riportate ulteriori considerazioni relative alla creazione di un indice:

• È possibile creare un indice se si dispone dell'autorizzazione CONTROL o ALTER per la tabella.
• Dopo la creazione, l'indice viene automaticamente attivato ed è disponibile per l'utilizzo. Per rimuovere l'accesso a un indice, è necessario disattivarlo. Per ulteriori informazioni, vedere Disattivazione di indici.

Requisiti di spazio su disco

La quantità di spazio su disco necessaria per l'archiviazione dell'indice dipende dai fattori seguenti:

• Dimensione delle singole righe di dati della tabella e numero di righe per pagina. Questo fattore determina il numero di pagine di dati che è necessario leggere dal disco per la creazione dell'indice.
• Colonne nell'indice e relativi tipi di dati. Determina il numero di pagine di indice da scrivere nel disco. Per ulteriori informazioni, vedere Stima delle dimensioni di un indice cluster e Stima delle dimensioni di un indice non cluster.
• Spazio su disco temporaneo necessario durante il processo di creazione dell'indice. Per ulteriori informazioni, vedere Individuazione dei requisiti di spazio su disco per gli indici.

Considerazioni sulle prestazioni

Il tempo necessario per la creazione fisica di un indice dipende in larga misura dal sottosistema disco. Di seguito vengono indicati i fattori da considerare:

• Modello di recupero del database. Il modello di recupero con registrazione minima delle transazioni di massa garantisce prestazioni migliori e richiede meno spazio per i log durante l'operazione di creazione dell'indice rispetto al modello di recupero con registrazione completa. Questo modello di recupero comporta tuttavia una riduzione della flessibilità nel caso dei recuperi temporizzati. Per ulteriori informazioni, vedere Scelta di un modello di recupero per le operazioni sugli indici.
• Livello RAID (Redundant Array of Independent Disks) utilizzato per l'archiviazione dei file di database e dei file di log delle transazioni. La larghezza di banda di I/O è in genere migliore con i livelli RAID che utilizzano lo striping.
• Numero di dischi nell'array, se si utilizza RAID. La velocità di trasferimento dati aumenta proporzionalmente al numero di unità dell'array.
• Posizione di archiviazione degli ordinamenti intermedi dei dati. L'utilizzo dell'opzione SORT_IN_TEMPDB può determinare una riduzione del tempo necessario per la creazione di un indice quando tempdb è posizionato in un set di dischi diverso rispetto al database utente. Per ulteriori informazioni, vedere tempdb e creazione dell'indice.
• Creazione dell'indice in linea o non in linea.
  Quando si crea un indice non in linea (impostazione predefinita), i blocchi esclusivi vengono mantenuti sulla tabella sottostante fino al completamento della transazione per la creazione dell'indice. Tale tabella è inaccessibile agli utenti durante la creazione dell'indice.
  In SQL Server 2005 è possibile scegliere di creare l'indice in linea. Se l'opzione Online è impostata su ON, i blocchi a lungo termine a livello di tabella non vengono mantenuti, pertanto l'esecuzione di query o aggiornamenti sulla tabella sottostante può proseguire anche durante la creazione dell'indice. Le operazioni basate su indice in linea sono consigliabili purché si tenga tuttavia conto dell'ambiente e dei requisiti specifici. Potrebbe infatti essere preferibile eseguire questo tipo di operazioni non in linea. In tal modo, gli utenti disporranno di accesso limitato ai dati per la durata dell'operazione, tuttavia il completamento dell'operazione richiederà meno tempo e un numero inferiore di risorse. Per ulteriori informazioni, vedere Esecuzione di operazioni in linea su indici.

Per creare un vincolo PRIMARY KEY o UNIQUE durante la creazione di una tabella

CREATE TABLE

Per creare un vincolo PRIMARY KEY o UNIQUE in una tabella esistente

ALTER TABLE

Per creare un indice

CREATE INDEX

Vedere anche

Concetti

Creazione di indici cluster
Creazione di indici con colonne incluse
Creazione di indici non cluster
Creazione di indici univoci
Implementazione di indici e tabelle partizionate
Vincoli PRIMARY KEY
Vincoli UNIQUE
Indici nelle colonne con tipo di dati XML

Altre risorse

ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
Introduzione alla ricerca full-text

Guida in linea e informazioni

Assistenza su SQL Server 2005