CREATE INDEX (Transact-SQL)

Data aggiornamento: 14 aprile 2006

Crea un indice relazionale per una tabella o una vista specificata oppure un indice XML per una tabella specificata. L'indice può essere creato prima dell'immissione dei dati nella tabella. È possibile creare indici per tabelle o viste di un altro database specificando un nome di database completo.

Convenzioni della sintassi Transact-SQL

Sintassi

Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Create XML Index 
CREATE [ PRIMARY ] XML INDEX index_name 
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name 
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_name
}

<xml_index_option> ::=
{ 
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Backward Compatible Relational Index
Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] ) 
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ] 
        table_or_view_name
}

<backward_compatible_index_option> ::=
{ 
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
}

Argomenti

• UNIQUE
  Crea un indice univoco per una tabella o una vista. Un indice univoco non consente l'utilizzo di uno stesso valore di chiave di indice in più righe. Un indice cluster di una vista deve essere univoco.

  In Motore di database di SQL Server 2005 non è possibile creare un indice univoco su colonne che includono già valori duplicati, indipendentemente dal fatto che l'opzione IGNORE_DUP_KEY sia impostata o meno su ON. Se si tenta di eseguire questa operazione, Motore di database visualizza un messaggio di errore. Prima di poter creare un indice univoco su una o più colonne di questo tipo, è necessario rimuovere i valori duplicati. Le colonne utilizzate in un indice univoco devono essere impostate su NOT NULL, perché i valori Null multipli vengono considerati duplicati in fase di creazione dell'indice.

• CLUSTERED
  Crea un indice in cui l'ordine logico dei valori di chiave determina l'ordine fisico delle righe corrispondenti di una tabella. Il livello inferiore, o foglia, dell'indice cluster contiene le righe di dati effettive della tabella. È possibile creare un solo indice cluster alla volta per una tabella o una vista. Per ulteriori informazioni, vedere Strutture degli indici cluster.

  Una vista con un indice cluster univoco viene definita vista indicizzata. La creazione di un indice cluster univoco per una vista materializza fisicamente la vista. È necessario creare un indice cluster univoco per una vista prima di poter definire altri indici per la stessa vista. Per ulteriori informazioni, vedere Progettazione di viste indicizzate.

  Creare l'indice cluster prima di qualsiasi indice non cluster. Quando si crea un indice cluster, gli indici non cluster esistenti delle tabelle vengono ricostruiti.

  Se la parola chiave CLUSTERED viene omessa, viene creato un indice non cluster.

  [!NOTA] Poiché per definizione il livello foglia di un indice cluster corrisponde alle pagine di dati, la creazione di un indice cluster con la clausola ON partition_scheme_name o ON filegroup_name comporta lo spostamento di una tabella dal filegroup in cui è stata creata nel nuovo schema di partizione o filegroup. Prima di creare tabelle o indici in filegroup specifici, verificare i filegroup disponibili e controllare che contengano spazio sufficiente per l'indice. Per ulteriori informazioni, vedere Individuazione dei requisiti di spazio su disco per gli indici.

• NONCLUSTERED
  Crea un indice che specifica l'ordinamento logico di una tabella. Quando si utilizza un indice non cluster, l'ordine fisico delle righe di dati è indipendente dall'ordine delle righe indicizzate. Per ulteriori informazioni, vedere Strutture degli indici non cluster.

  Per ogni tabella è possibile definire al massimo 249 indici non cluster, indipendentemente dal fatto che vengano creati in modo implicito tramite vincoli PRIMARY KEY e UNIQUE oppure in modo esplicito tramite CREATE INDEX.

  Per le viste indicizzate, gli indici non cluster possono essere creati solo è già stato definito un indice cluster univoco.

  Il valore predefinito è NONCLUSTERED.

• index_name
  Nome dell'indice. I nomi degli indici devono essere univoci all'interno di una tabella o una vista, ma non necessariamente all'interno di un database. Inoltre, devono essere conformi alle regole per gli identificatori.

  I nomi di indice XML primario non possono iniziare con i caratteri seguenti: #, ##, @ oppure @@.

• column
  Una o più colonne su cui l'indice è basato. Specificare due o più nomi di colonna per creare un indice composto sui valori combinati delle colonne specificate. Elencare tra parentesi le colonne da includere nell'indice composto, in base alla priorità di ordinamento, dopo l'argomento table_or_view_name.

  In una singola chiave di indice composto è possibile combinare al massimo 16 colonne. Tutte le colonne di una chiave di indice composto devono appartenere alla stessa tabella o vista. I valori combinati dell'indice possono avere una dimensione massima di 900 byte. Per ulteriori informazioni sull'utilizzo di colonne di dimensioni variabili in indici composti, vedere la sezione Osservazioni.

  Le colonne con il tipo di dati LOB (Large Object) ntext, text, varchar(max), nvarchar(max), varbinary(max), xml o image non possono essere specificate come colonne chiave di un indice. Inoltre, una definizione di vista non può includere colonne di tipo ntext, text o image, anche se non vi viene fatto riferimento nell'istruzione CREATE INDEX.

  È possibile creare indici su colonne con tipo CLR definito dall'utente se il tipo supporta l'ordinamento binario. È inoltre possibile creare indici su colonne calcolate che sono definite come chiamate di metodo da una colonna con tipo definito dall'utente, a condizione che i metodi siano contrassegnati come deterministici e non eseguano operazioni di accesso ai dati. Per ulteriori informazioni sull'indicizzazione di colonne con tipo CLR definito dall'utente, vedere CLR User-defined Types (informazioni in lingua inglese).

• [ ASC | DESC ]
  Determina se il tipo di ordinamento della colonna di indice specificata è crescente o decrescente. Il valore predefinito è ASC.

• INCLUDE **(**column [ ,... n ] )
  Specifica le colonne non chiave da aggiungere al livello foglia dell'indice non cluster. L'indice non cluster può essere univoco o non univoco.

  Il numero massimo di colonne non chiave incluse è 1.023, il numero minimo è 1.

  I nomi di colonna non possono essere ripetuti nell'elenco INCLUDE e non possono essere applicati contemporaneamente a colonne chiave e non chiave. Per ulteriori informazioni, vedere Indice con colonne incluse.

  È possibile utilizzare qualsiasi tipo di dati, ad eccezione di text, ntext e image. Se una qualsiasi delle colonne non chiave specificate è di tipo varchar(max), nvarchar(max) o varbinary(max), l'indice deve essere creato o ricostruito in modalità non in linea (ONLINE = OFF).

  Come colonne incluse è possibile utilizzare colonne calcolate che sono deterministiche, sia precise che imprecise. Le colonne calcolate che derivano da tipi di dati image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml possono essere utilizzate come colonne non chiave incluse, a condizione che i tipi di dati delle colonne calcolate siano supportati come colonna inclusa. Per ulteriori informazioni, vedere Creazione di indici per le colonne calcolate.

• ON partition_scheme_name**(column_name)**
  Specifica lo schema di partizione che definisce i filegroup a cui verranno mappate le partizioni di un indice partizionato. Lo schema di partizione deve esistere all'interno del database e può essere creato tramite CREATE PARTITION SCHEME o ALTER PARTITION SCHEME. column_name specifica la colonna in base a cui un indice verrà partizionato. Questa colonna deve avere lo stesso tipo di dati, la stessa lunghezza e la stessa precisione dell'argomento della funzione di partizione utilizzata da partition_scheme_name. La colonna specificata in column_name non deve essere necessariamente inclusa nella definizione dell'indice. È possibile specificare qualsiasi colonna della tabella di base. Quando si partiziona un indice UNIQUE, tuttavia, la colonna specificata in column_name deve essere scelta tra quelle utilizzate come chiave univoca. Questa restrizione consente a Motore di database di verificare l'univocità dei valori di chiave all'interno di una singola partizione.

  [!NOTA] Quando si partiziona un indice cluster non univoco, per impostazione predefinita Motore di database aggiunge la colonna di partizionamento all'elenco delle chiavi di indice cluster, se non è già presente. Quando si partiziona un indice non cluster non univoco, Motore di database aggiunge la colonna di partizionamento come colonna non chiave (inclusa) dell'indice, se non è già presente.

  Se partition_scheme_name o filegroup viene omesso e la tabella è partizionata, l'indice viene creato nello stesso schema di partizione e con la stessa colonna di partizionamento della tabella sottostante.

  Non è possibile specificare uno schema di partizione per un indice XML. Se la tabella di base è partizionata, l'indice XML utilizzerà lo stesso schema di partizione della tabella.

  Per ulteriori informazioni sul partizionamento degli indici, vedere Linee guida specifiche per gli indici partizionati.

• ON filegroup_name
  Crea l'indice specificato nel filegroup specificato. Se non viene specificata una posizione e la tabella o la vista non è partizionata, l'indice utilizzerà lo stesso filegroup della tabella o della vista sottostante. È necessario che il filegroup esista già. Gli indici XML utilizzano lo stesso filegroup della tabella.

• ON "default"
  Crea l'indice specificato nel filegroup predefinito.

  In questo contesto il termine default non rappresenta una parola chiave, ma un identificatore del filegroup predefinito e deve pertanto essere delimitato come in ON "default" o ON [default]. Se si specifica "default", l'opzione QUOTED_IDENTIFIER deve essere impostata su ON per la sessione corrente. Questa è l'impostazione predefinita. Per ulteriori informazioni, vedere SET QUOTED_IDENTIFIER (Transact-SQL).

• [PRIMARY] XML
  Crea un indice XML sulla colonna xml specificata. Quando viene specificata la parola chiave PRIMARY, viene creato un indice cluster con una chiave cluster costituita dalla chiave di clustering della tabella utente e da un identificatore di nodo XML. Per ogni tabella è possibile creare al massimo 249 indici XML. Quando si crea un indice XML, è necessario tenere presente quanto segue:

  • È necessario che esista un indice cluster sulla chiave primaria della tabella utente.
  • La chiave di clustering della tabella utente può includere al massimo 15 colonne.
  • Per ogni colonna xml di una tabella è possibile creare un indice XML primario e più indici XML secondari.
  • Prima di poter creare un indice XML secondario su una colonna xml, è necessario che per tale colonna esista un indice XML primario.
  • Gli indici XML devono essere creati su una singola colonna xml. Non è possibile creare un indice XML su una colonna non xml né creare un indice relazionale su una colonna xml.
  • Non è possibile creare un indice XML, primario o secondario, su una colonna xml di una vista, su una variabile valutata a livello di tabella con colonne xml oppure su variabili di tipo xml.
  • Non è possibile creare un indice XML primario su una colonna xml calcolata.
  • Le impostazioni delle opzioni SET devono corrispondere a quelle necessarie per le viste indicizzate e gli indici su colonne calcolate. In particolare, l'opzione ARITHABORT deve essere impostata su ON quando viene creato un indice XML e quando vengono inseriti, eliminati o aggiornati valori nella colonna xml. Per ulteriori informazioni, vedere Opzioni SET che hanno effetto sui risultati.

  Per ulteriori informazioni, vedere Indici nelle colonne con tipo di dati XML.

• xml_column_name
  Colonna xml su cui l'indice è basato. È possibile specificare una sola colonna xml in una singola definizione di indice XML, ma è possibile creare più indici XML secondari su una colonna xml.

• USING XML INDEX xml_index_name
  Specifica l'indice XML primario da utilizzare per la creazione di un indice XML secondario.

• FOR { VALUE | PATH | PROPERTY }
  Specifica il tipo di indice XML secondario.

  • VALUE
    Crea un indice XML secondario su colonne con le colonne chiave (valore di nodo e percorso) dell'indice XML primario.
  • PATH
    Crea un indice XML secondario su colonne con i valori di percorso e i valori di nodo dell'indice XML primario. Nell'indice secondario PATH i valori di nodo e di percorso sono colonne chiave che consentono di eseguire le ricerche di percorsi in modo più efficiente.
  • PROPERTY
    Crea un indice XML secondario su colonne (valore di chiave primaria, percorso e nodo) dell'indice XML primario, dove il valore di chiave primaria è la chiave primaria della tabella di base.

<object>::=

Oggetto con nome completo o non qualificato che si desidera indicizzare.

• database_name
  Nome del database.

• schema_name
  Nome dello schema a cui appartiene la tabella o la vista.

• table_or_view_name
  Nome della tabella o della vista che si desidera indicizzare.

  Per poter creare un indice per una vista, è necessario che la vista sia definita con l'opzione SCHEMABINDING. Prima di creare qualsiasi indice non cluster per una vista, è necessario creare un indice cluster univoco. Per ulteriori informazioni sulle viste indicizzate, vedere la sezione Osservazioni.

<relational_index_option>::=

Specifica le opzioni da utilizzare quando si crea l'indice.

• PAD_INDEX = { ON | OFF }
  Specifica il riempimento dell'indice. Il valore predefinito è OFF.

  • ON
    La percentuale di spazio disponibile specificata in fillfactor viene applicata alle pagine di livello intermedio dell'indice.
  • OFF o mancata impostazione di fillfactor
    Le pagine di livello intermedio vengono riempite poco al di sotto della capacità massima, in modo che lo spazio residuo sia sufficiente per almeno una riga della dimensione massima supportata dall'indice, in base al set di chiavi nelle pagine intermedie.

  L'opzione PAD_INDEX risulta utile solo quando si specifica FILLFACTOR, in quanto PAD_INDEX utilizza la percentuale specificata in FILLFACTOR. Se la percentuale specificata in FILLFACTOR non consente l'inserimento di una riga, Motore di database sostituisce internamente tale percentuale in modo da rendere disponibile lo spazio minimo necessario. Il numero di righe di una pagina intermedia dell'indice non è mai minore di due, indipendentemente dal valore di fillfactor.

  Per quanto riguarda la sintassi compatibile con le versioni precedenti, WITH PAD_INDEX equivale a WITH PAD_INDEX = ON.

• FILLFACTOR **=**fillfactor
  Specifica una percentuale che indica il livello di riempimento che deve essere impostato da Motore di database per il livello foglia di ogni pagina di indice durante la creazione o la ricostruzione dell'indice. fillfactor deve essere un valore integer compreso nell'intervallo da 1 a 100. Il valore predefinito è 0. Se fillfactor è 100 o 0, Motore di database crea indici con pagine foglia riempite fino alla capacità massima.

  [!NOTA] I valori 0 e 100 sono equivalenti.

  L'impostazione di FILLFACTOR viene applicata solo in fase di creazione o ricostruzione dell'indice. La percentuale specificata di spazio vuoto delle pagine non viene mantenuta in modo dinamico da Motore di database. Per visualizzare l'impostazione del fattore di riempimento, utilizzare la vista del catalogo sys.indexes.

  Importante:
  La creazione di un indice cluster con un valore FILLFACTOR minore di 100 influisce sulla quantità di spazio di archiviazione occupata dai dati perché i dati vengono ridistribuiti da Motore di database durante la creazione dell'indice cluster.

  Per ulteriori informazioni, vedere Fattore di riempimento.

• SORT_IN_TEMPDB = { ON | OFF }
  Specifica se i risultati temporanei dell'ordinamento devono essere archiviati in tempdb. Il valore predefinito è OFF.

  • ON
    I risultati intermedi dell'ordinamento utilizzati per la creazione dell'indice vengono archiviati in tempdb. Se tempdb si trova in un set di dischi diverso rispetto al database utente, il tempo necessario per creare un indice potrebbe essere minore. La quantità di spazio su disco utilizzata durante la creazione dell'indice sarà tuttavia maggiore.
  • OFF
    I risultati intermedi dell'ordinamento vengono archiviati nello stesso database dell'indice.

  Oltre allo spazio necessario nel database utente per la creazione dell'indice, in tempdb deve essere disponibile una quantità di spazio aggiuntivo pressoché equivalente per l'archiviazione dei risultati intermedi dell'ordinamento. Per ulteriori informazioni, vedere tempdb e creazione dell'indice.

  Per quanto riguarda la sintassi compatibile con le versioni precedenti, WITH SORT_IN_TEMPDB equivale a WITH SORT_IN_TEMPDB = ON.

• IGNORE_DUP_KEY = { ON | OFF }
  Specifica il tipo di risposta in caso di errori di valori di chiave duplicati in un'operazione di inserimento di più righe su un indice cluster o non cluster univoco. Il valore predefinito è OFF.

  • ON
    Viene visualizzato un messaggio di avviso e l'operazione ha esito negativo solo per le righe che violano l'indice univoco.
  • OFF
    Viene visualizzato un messaggio di errore e viene eseguito il rollback dell'intera transazione INSERT.

  L'impostazione di IGNORE_DUP_KEY viene applicata solo alle operazioni di inserimento eseguite dopo la creazione o la ricostruzione dell'indice. Questa impostazione non ha alcun effetto durante l'esecuzione dell'operazione di creazione dell'indice.

  L'opzione IGNORE_DUP_KEY non può essere impostata su ON per gli indici XML e gli indici creati per una vista.

  Per quanto riguarda la sintassi compatibile con le versioni precedenti, WITH IGNORE_DUP_KEY equivale a WITH IGNORE_DUP_KEY = ON.

• STATISTICS_NORECOMPUTE = { ON | OFF }
  Specifica se devono essere ricalcolate le statistiche di distribuzione. Il valore predefinito è OFF.

  • ON
    Le statistiche non aggiornate non vengono ricalcolate automaticamente.
  • OFF
    Attiva l'aggiornamento automatico delle statistiche.

  Per ripristinare l'aggiornamento automatico delle statistiche, impostare l'opzione STATISTICS_NORECOMPUTE su OFF oppure eseguire UPDATE STATISTICS senza la clausola NORECOMPUTE.

  Importante:
  La disattivazione del ricalcolo automatico delle statistiche di distribuzione può compromettere la selezione di piani di esecuzione ottimali per le query riguardanti la tabella in Query Optimizer.

  Per quanto riguarda la sintassi compatibile con le versioni precedenti, WITH STATISTICS_NORECOMPUTE equivale a WITH STATISTICS_NORECOMPUTE = ON.

• DROP_EXISTING = { ON | OFF }
  Specifica che è necessario eliminare e quindi ricostruire l'indice XML, cluster o non cluster esistente specificato. Il valore predefinito è OFF.

  • ON
    L'indice esistente deve essere eliminato e quindi ricostruito. Il nome di indice specificato deve corrispondere a quello dell'indice esistente, mentre la definizione dell'indice può essere modificata. È ad esempio possibile specificare valori diversi per le colonne, il tipo di ordinamento, lo schema di partizione o le opzioni dell'indice.
  • OFF
    Se il nome di indice specificato esiste già, viene visualizzato un messaggio di errore.

  L'opzione DROP_EXISTING non può essere utilizzata per modificare il tipo di indice, relazionale o XML. Inoltre, non è possibile ridefinire un indice XML primario come indice XML secondario o viceversa.

  Per quanto riguarda la sintassi compatibile con le versioni precedenti, WITH DROP_EXISTING equivale a WITH DROP_EXISTING = ON.

• ONLINE = { ON | OFF }
  Specifica se le tabelle sottostanti e gli indici associati sono disponibili per le query e per modifiche dei dati durante l'operazione sull'indice. Il valore predefinito è OFF.

  [!NOTA] Le operazioni sugli indici in linea sono disponibili solo in SQL Server 2005 Enterprise Edition.

  • ON
    I blocchi di lunga durata a livello di tabella non vengono mantenuti per la durata di un'operazione sugli indici. Durante la fase principale dell'operazione viene mantenuto solo un blocco preventivo condiviso (IS, Intent Shared) sulla tabella di origine, in modo da consentire l'esecuzione di query o l'aggiornamento della tabella sottostante e degli indici. All'inizio dell'operazione viene mantenuto un blocco condiviso (S) sull'oggetto di origine per un periodo molto breve. Al termine dell'operazione di creazione di un indice non cluster, per un breve periodo viene acquisito un blocco condiviso (S) sull'origine. Al termine dell'operazione di creazione o di eliminazione di un indice cluster in linea o di ricostruzione di un indice cluster o non cluster, viene acquisito un blocco di modifica dello schema (SCH-M). Durante la creazione di un indice per una tabella temporanea locale non è possibile impostare ONLINE su ON.
  • OFF
    I blocchi a livello di tabella vengono applicati per la durata dell'operazione sugli indici. Un'operazione sugli indici non in linea che crea, ricostruisce o elimina un indice cluster oppure ricostruisce o elimina un indice non cluster acquisisce un blocco di modifica dello schema (SCH-M) sulla tabella. Tale blocco impedisce agli utenti di accedere alla tabella sottostante per la durata dell'operazione. Un'operazione sugli indici non in linea che crea un indice non cluster acquisisce un blocco condiviso (S) sulla tabella. Tale blocco impedisce l'aggiornamento della tabella sottostante ma consente operazioni di lettura, ad esempio l'esecuzione di istruzioni SELECT.

  Per ulteriori informazioni, vedere Funzionamento delle operazioni sugli indici in linea. Per ulteriori informazioni sui blocchi, vedere Modalità blocco.

  È possibile creare in linea tutti gli indici, inclusi quelli di tabelle temporanee globali, ad eccezione dei seguenti:

  • Indice XML.

  • Indice per una tabella temporanea locale.

  • Indice cluster univoco iniziale per una vista.

  • Indici cluster disabilitati.

  • Indice cluster, se la tabella sottostante contiene tipi di dati LOB: image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml.

  • Indice non cluster definito con colonne con un tipo di dati LOB.

    [!NOTA] È possibile creare in linea un indice non cluster non univoco se la tabella contiene tipi di dati LOB, ma nessuna di queste colonne viene utilizzata nella definizione dell'indice come colonna chiave o non chiave (inclusa).

  Per ulteriori informazioni, vedere Esecuzione di operazioni in linea su indici.

• ALLOW_ROW_LOCKS = { ON | OFF }
  Specifica se sono consentiti blocchi a livello di riga. Il valore predefinito è ON.

  • ON
    I blocchi a livello di riga sono consentiti durante l'accesso all'indice. Motore di database determina quando utilizzare blocchi a livello di riga.
  • OFF
    I blocchi a livello di riga non vengono utilizzati.

• ALLOW_PAGE_LOCKS = { ON | OFF }
  Specifica se sono consentiti blocchi a livello di pagina. Il valore predefinito è ON.

  • ON
    I blocchi a livello di pagina sono consentiti durante l'accesso all'indice. Motore di database determina quando utilizzare blocchi a livello di pagina.
  • OFF
    I blocchi a livello di pagina non vengono utilizzati.

• MAXDOP = max_degree_of_parallelism
  Ignora l'opzione di configurazione max degree of parallelism per tutta la durata dell'operazione sull'indice. Utilizzare MAXDOP per limitare il numero di processori utilizzati durante l'esecuzione di un piano parallelo. Il valore massimo è 64 processori.

  I possibili valori di max_degree_of_parallelism sono i seguenti:

  • 1
    Disattiva la generazione di piani paralleli.
  • >1
    Consente di limitare al valore specificato, o a un valore più basso in base al carico di lavoro corrente del sistema, il numero massimo di processori utilizzati in un'operazione parallela sugli indici.
  • 0 (predefinito)
    Utilizza il numero effettivo di processori o un numero inferiore in base al carico di lavoro corrente del sistema.

  Per ulteriori informazioni, vedere Configurazione di operazioni a indici paralleli.

  [!NOTA] Le operazioni parallele sugli indici sono supportate solo in SQL Server 2005 Enterprise Edition.

Osservazioni

L'istruzione CREATE INDEX viene ottimizzata come qualsiasi altra query. Al fine di limitare le operazioni di I/O, è possibile che Query Processor scelga di sottoporre a scansione un altro indice anziché eseguire una scansione di tabella. In alcune situazioni è possibile che l'operazione di ordinamento venga eliminata. Nei computer multiprocessore con SQL Server 2005 Enterprise Edition l'istruzione CREATE INDEX può utilizzare più processori per eseguire le operazioni di scansione e ordinamento associate alla creazione dell'indice, in modo identico ad altre query. Per ulteriori informazioni, vedere Configurazione di operazioni a indici paralleli.

L'operazione di creazione dell'indice può essere sottoposta a una registrazione minima se viene utilizzato il modello di recupero del database con registrazione minima o con registrazione minima delle transazioni di massa. Per ulteriori informazioni, vedere Scelta di un modello di recupero per le operazioni sugli indici.

È possibile creare indici per una tabella temporanea. Al termine della sessione oppure quando si elimina la tabella, vengono eliminati anche gli indici associati.

Gli indici supportano proprietà estese. Per ulteriori informazioni, vedere Utilizzo di proprietà estese su oggetti di database.

Indici cluster

Per la creazione di un indice cluster per una tabella (heap) oppure per l'eliminazione e la ricreazione di un indice cluster esistente è necessario che nel database sia disponibile un'area di lavoro aggiuntiva, che verrà utilizzata per l'ordinamento dei dati e per una copia temporanea della tabella originale o dei dati dell'indice cluster esistenti. Per ulteriori informazioni, vedere Individuazione dei requisiti di spazio su disco per gli indici. Per ulteriori informazioni sugli indici cluster, vedere Creazione di indici cluster.

Indici univoci

Quando esiste un indice univoco, ogni volta che vengono aggiunti nuovi dati tramite un'operazione di inserimento Motore di database esegue una verifica per controllare che non vengano creati valori duplicati. Le operazioni di inserimento che provocherebbero valori di chiave duplicati vengono sottoposte a rollback e causano la visualizzazione di un messaggio di errore da parte di Motore di database, anche nel caso in cui l'operazione di inserimento interessi più righe e generi un solo valore duplicato. Se si tenta di immettere dati per i quali esiste un indice univoco e la clausola IGNORE_DUP_KEY è impostata su ON, l'operazione ha esito negativo solo per le righe che violano l'indice UNIQUE. Per ulteriori informazioni sugli indici univoci, vedere Creazione di indici univoci.

Indici partizionati

Gli indici partizionati vengono creati e gestiti in modo analogo alle tabelle partizionate, ma, come gli indici normali, vengono trattati come oggetti di database separati. È possibile creare un indice partizionato per una tabella non partizionata nonché creare un indice non partizionato per una tabella partizionata.

Se si desidera creare un indice per una tabella partizionata e non si specifica un filegroup in cui inserire l'indice, l'indice verrà partizionato in modo identico alla tabella sottostante. Questo perché gli indici per impostazione predefinita vengono inseriti negli stessi filegroup delle tabelle sottostanti e, nel caso di una tabella partizionata, nello stesso schema di partizione con le stesse colonne di partizionamento.

Quando si partiziona un indice cluster non univoco, per impostazione predefinita Motore di database aggiunge tutte le colonne di partizionamento all'elenco delle chiavi di indice cluster, se non sono già presenti.

È possibile creare viste indicizzate per tabelle partizionate in modo analogo agli indici delle tabelle. Per ulteriori informazioni sugli indici partizionati, vedere Tabelle e indici partizionati.

Viste indicizzate

La creazione di un indice cluster univoco per una vista consente un miglioramento delle prestazioni delle query, in quanto la vista viene archiviata nel database in modo analogo a una tabella con un indice cluster. Le viste indicizzate possono essere utilizzate da Query Optimizer per velocizzare l'esecuzione delle query. Non è necessario fare riferimento alla vista nella query affinché Query Optimizer tenga in considerazione tale vista per una sostituzione.

Per una corretta implementazione di una vista indicizzata, è importante eseguire le operazioni seguenti:

1. Verificare che le opzioni SET siano impostate in modo corretto per tutte le tabelle esistenti a cui verrà riferimento nella vista.
2. Verificare che le opzioni SET della sessione siano impostate in modo corretto prima di creare nuove tabelle e la vista.
3. Verificare che la definizione della vista sia deterministica.
4. Creare la vista con l'opzione WITH SCHEMABINDING.
5. Creare l'indice cluster univoco per la vista.

Opzioni SET necessarie per le viste indicizzate

La valutazione di una stessa espressione può produrre risultati diversi in Motore di database se le opzioni SET attivate quando la query viene eseguita sono diverse. Ad esempio, se l'opzione SET CONCAT_NULL_YIELDS_NULL è impostata su ON, l'espressione 'abc' + NULL restituisce il valore NULL. Se l'opzione CONCAT_NULL_YIEDS_NULL è impostata su OFF, la stessa espressione restituisce 'abc'.

Per essere certi che le viste possano essere gestite in modo corretto e restituiscano risultati consistenti, è necessario utilizzare valori fissi per varie opzioni SET delle viste indicizzate. Le opzioni SET specificate nella tabella seguente devono essere impostate sui valori indicati nella colonna Valoreobbligatorio nelle seguenti circostanze:

• Quando la vista indicizzata viene creata.

• Quando viene eseguita un'operazione di inserimento, aggiornamento o eliminazione su una qualsiasi tabella utilizzata nella vista indicizzata, incluse operazioni quali la copia di massa, la replica e le query distribuite.

• Quando la vista indicizzata viene utilizzata in Query Optimizer per generare il piano di query.

  Opzione SET	Valore obbligatorio	Valore server predefinito	Valore OLE DB e ODBC predefinito	Valore DB-Library predefinito
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS*	ON	ON	ON	OFF
  ARITHABORT	ON	ON	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ON	ON	ON	OFF

  *In SQL Server 2005 l'impostazione di ANSI_WARNINGS su ON comporta l'impostazione implicita di ARITHABORT su ON quando il livello di compatibilità del database è impostato su 90. Se il livello di compatibilità del database viene impostato su 80 o su un valore inferiore, l'opzione ARITHABORT deve essere impostata esplicitamente su ON.

Se si utilizza una connessione server OLE DB o ODBC, l'unico valore da modificare è l'impostazione ARITHABORT. Tutti i valori DB-Library devono essere impostati in modo corretto a livello di server tramite sp_configure oppure dall'applicazione tramite il comando SET. Per ulteriori informazioni sulle opzioni SET, vedere Utilizzo delle opzioni di SQL Server.

Importante:
È consigliabile impostare l'opzione utente ARITHABORT su ON per l'intero server immediatamente dopo la creazione della prima vista indicizzata o del primo indice una colonna calcolata in qualsiasi database del server.

Funzioni deterministiche

La definizione di una vista indicizzata deve essere deterministica. Una vista è deterministica se tutte le espressioni nell'elenco di selezione nonché nelle clausole WHERE e GROUP BY sono deterministiche. Le espressioni deterministiche restituiscono sempre lo stesso risultato ogni volta che vengono valutate con un set specifico di valori di input. Nelle espressioni deterministiche è possibile utilizzare solo funzioni deterministiche. La funzione DATEADD, ad esempio, è deterministica perché restituisce sempre lo stesso risultato per un dato set di valori dei relativi tre parametri. GETDATE non è deterministica perché viene sempre richiamata con lo stesso argomento, ma il valore restituito cambia ogni volta che viene eseguita. Per ulteriori informazioni, vedere Funzioni deterministiche e non deterministiche.

Se un'espressione deterministica contiene espressioni float, il risultato esatto può dipendere dall'architettura del processore o dalla versione del microcodice. Per garantire l'integrità dei dati, le espressioni di questo tipo possono essere utilizzate solo come colonne non chiave di viste indicizzate. Le espressioni deterministiche che non contengono espressioni float sono definite precise. Solo le espressioni deterministiche precise possono essere utilizzate in colonne chiave e clausole WHERE o GROUP BY di viste indicizzate.

Utilizzare la proprietà IsDeterministic della funzione COLUMNPROPERTY per determinare se una colonna della vista è deterministica. Utilizzare la proprietà IsPrecise della funzione COLUMNPROPERTY per determinare se una colonna deterministica di una vista con associazione a schema è precisa. La funzione COLUMNPROPERTY restituisce 1 se la proprietà è TRUE, 0 se la proprietà è FALSE e NULL se il valore di input non è valido. Questo significa che la colonna non è deterministica o non è precisa.

Requisiti aggiuntivi

Oltre alle impostazioni delle opzioni SET e ai requisiti relativi alle funzioni deterministiche, è necessario rispettare i requisiti seguenti:

• L'utente che esegue CREATE INDEX deve essere il proprietario della vista.

• Se la definizione della vista include una clausola GROUP BY, la chiave dell'indice cluster univoco può contenere riferimenti solo alle colonne specificate nella clausola GROUP BY.

• Le opzioni SET delle tabelle di base devono essere impostate in modo corretto quando viene creata la tabella, altrimenti la vista con associazione a schema non potrà fare riferimento a tali tabelle.

• I riferimenti a tabelle devono essere specificati come nomi composti da due parti, ovvero schema**.**tablename, nella definizione della vista.

• Le funzioni definite dall'utente devono essere create con l'opzione WITH SCHEMABINDING.

• I riferimenti a funzioni definite dall'utente devono essere specificati come nomi composti da due parti, ovvero schema**.**function.

• La vista deve essere creata con l'opzione WITH SCHEMABINDING.

• La vista deve contenere riferimenti solo a tabelle di base nello stesso database, non ad altre viste.

• La definizione della vista non deve contenere i seguenti elementi:

  COUNT(*)	Funzione ROWSET
  Tabella derivata	Self join
  DISTINCT	STDEV, VARIANCE, AVG
  Colonne di tipo float*, text, ntext o image	Subquery
  Predicati full-text (CONTAIN, FREETEXT)	SUM in espressioni che supportano valori Null
  Funzione di aggregazione CLR definita dall'utente	TOP
  MIN, MAX	UNION

  *La vista indicizzata può contenere colonne di tipo float, che però non possono essere incluse nella chiave di indice cluster.

Se è presente la clausola GROUP BY, la definizione di VIEW deve contenere COUNT_BIG(*) e non deve contenere HAVING. Queste restrizioni relative alla clausola GROUP BY vengono applicate solo alla definizione della vista indicizzata. Una query può utilizzare una vista indicizzata nel relativo piano di esecuzione anche se non soddisfa tali restrizioni.

È possibile creare viste indicizzate per una tabella partizionata e anche partizionare questo tipo di viste. Per ulteriori informazioni sul partizionamento, vedere la sezione precedente "Indici partizionati".

Per impedire l'utilizzo di viste indicizzate in Motore di database, includere l'hint OPTION (EXPAND VIEWS) nella query. Inoltre, un'errata impostazione di una qualsiasi delle opzione elencate impedisce l'utilizzo degli indici delle viste in Query Optimizer. Per ulteriori informazioni sull'hint OPTION (EXPAND VIEWS), vedere SELECT (Transact-SQL).

Il livello di compatibilità del database non può essere inferiore a 80. Se un database contiene una vista indicizzata, non è possibile impostare il livello di compatibilità su un valore inferiore a 80.

Indici XML

Per ulteriori informazioni, vedere Indici nelle colonne con tipo di dati XML.

Dimensione della chiave di indice

La dimensione massima di una chiave di indice è 900 byte. È possibile creare indici su colonne varchar con una dimensione superiore a 900 byte se i dati esistenti nelle colonne non superano i 900 byte quando l'indice viene creato. Le successive operazioni di inserimento o aggiornamento nelle colonne che causerebbero un aumento della dimensione totale oltre i 900 byte avranno tuttavia esito negativo. Per ulteriori informazioni, vedere Dimensione massima delle chiavi di indice. La chiave di indice di un indice cluster non può contenere colonne di tipo varchar con dati esistenti nell'unità di allocazione ROW_OVERFLOW_DATA. Se viene creato un indice cluster su una colonna di tipo varchar e i dati esistenti si trovano nell'unità di allocazione IN_ROW_DATA, le successive operazioni di inserimento o aggiornamento nella colonna che causerebbero uno spostamento dei dati all'esterno delle righe avranno esito negativo. Per ulteriori informazioni sulle unità di allocazione, vedere Organizzazione di tabelle e indici.

In SQL Server 2005 gli indici non cluster possono includere colonne non chiave nel livello foglia. Queste colonne non vengono considerate da Motore di database durante il calcolo della dimensione della chiave di indice. Per ulteriori informazioni, vedere Indice con colonne incluse.

Colonne calcolate

Gli indici possono essere creati su colonne calcolate. In SQL Server 2005 è possibile impostare la proprietà PERSISTED per le colonne calcolate. Questo significa che Motore di database archivia i valori calcolati nella tabella e li aggiorna quando vengono aggiornate altre colonne da cui dipende la colonna calcolata. Motore di database utilizza questi valori persistenti quando crea un indice sulla colonna e quando viene fatto riferimento all'indice all'interno di una query.

Per indicizzare una colonna calcolata, è necessario che tale colonna sia deterministica e precisa. La proprietà PERSISTED consente tuttavia di espandere i tipi di colonne calcolate indicizzabili, includendo i tipi di colonne seguenti:

• Colonne calcolate basate su funzioni Transact-SQL e CLR e metodi con tipo CLR definito dall'utente contrassegnati come deterministici dall'utente.
• Colonne calcolate basate su espressioni che sono deterministiche, secondo quanto definito in Motore di database, ma imprecise.

Per le colonne calcolate persistenti è necessario impostare le opzioni SET seguenti come illustrato nella sezione precedente "Opzioni SET necessarie per le viste indicizzate".

Il vincolo UNIQUE o PRIMARY KEY può includere una colonna calcolata a condizione che vengano soddisfatte tutte le condizioni per l'indicizzazione. In particolare, la colonna calcolata deve essere deterministica e precisa oppure deterministica e persistente. Per ulteriori informazioni sulle funzioni deterministiche, vedere Funzioni deterministiche e non deterministiche.

Le colonne calcolate che derivano da tipi di dati image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml possono essere indicizzate come colonna chiave o colonna non chiave inclusa a condizione che il tipo di dati della colonna calcolata sia supportato come colonna chiave o colonna non chiave dell'indice. Ad esempio, non è possibile creare un indice XML primario su una colonna xml calcolata. Se la dimensione della chiave di indice supera i 900 byte, viene visualizzato un messaggio di avviso.

La creazione di un indice su una colonna calcolata può impedire l'esecuzione di un'operazione di inserimento o aggiornamento che in precedenza veniva eseguita correttamente. Questo problema si può verificare quando la colonna calcolata genera un errore aritmetico. Nella tabella seguente, ad esempio, nonostante la colonna calcolata c generi un errore aritmetico, l'istruzione INSERT ha esito positivo.

CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Se invece,dopo la creazione della tabella, viene creato un indice sulla colonna calcolata c, la stessa istruzione INSERT avrà esito negativo.

CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Per ulteriori informazioni, vedere Creazione di indici per le colonne calcolate.

Colonne incluse di indici

È possibile aggiungere colonne non chiave, o incluse, al livello foglia di un indice non cluster per migliorare le prestazioni delle query mediante una copertura della query. Questo significa che tutte le colonne a cui viene fatto riferimento nella query vengono incluse nell'indice come colonne chiave o non chiave. In questo modo, per individuare tutte le informazioni necessarie, in Query Optimizer verrà eseguita semplicemente una scansione dell'indice, senza necessità di accedere ai dati della tabella o dell'indice cluster. Per ulteriori informazioni, vedere Indice con colonne incluse.

Impostazione di opzioni per gli indici

In SQL Server 2005 sono state introdotte nuove opzioni per gli indici ed è stata modificata la modalità di impostazione di tali opzioni. Per quanto riguarda la sintassi compatibile con le versioni precedenti, WITH option_name equivale a WITH ( <option_name> = ON ). Quando si impostano opzioni per gli indici, è necessario rispettare le regole seguenti:

• Per specificare nuove opzioni per gli indici è necessario utilizzare WITH (option_name= ON | OFF**)**.
• Non è possibile specificare opzioni utilizzando in una stessa istruzione sia la sintassi compatibile con le versioni precedenti che la nuova sintassi. Se, ad esempio, si specifica WITH (DROP_EXISTING, ONLINE = ON**)**, l'istruzione avrà esito negativo.
• Quando si crea un indice XML, le opzioni devono essere specificate con la sintassi WITH (option_name= ON | OFF**)**.

Clausola DROP_EXISTING

È possibile utilizzare la clausola DROP_EXISTING per ricostruire l'indice, aggiungere o eliminare colonne, modificare opzioni, modificare il tipo di ordinamento delle colonne oppure cambiare lo schema di partizione o il filegroup.

Se l'indice applica un vincolo PRIMARY KEY o UNIQUE e la definizione dell'indice non viene modificata in alcun modo, l'indice verrà eliminato e ricreato con il vincolo esistente. Se invece la definizione dell'indice viene modificata, l'istruzione avrà esito negativo. Per modificare la definizione di un vincolo PRIMARY KEY o UNIQUE, eliminare il vincolo e aggiungere un vincolo con la nuova definizione.

La clausola DROP_EXISTING consente un miglioramento delle prestazioni quando viene ricreato un indice cluster, con un set di chiavi identico oppure diverso, per una tabella che include anche indici non cluster. La clausola DROP_EXISTING sostituisce l'esecuzione di un'istruzione DROP INDEX sull'indice cluster precedente e quindi di un'istruzione CREATE INDEX per il nuovo indice cluster. Gli indici non cluster vengono ricostruiti una sola volta e poi solo in caso di modifica della relativa definizione. La clausola DROP_EXISTING non ricostruisce gli indici non cluster quando la definizione dell'indice contiene gli stessi valori dell'indice originale per il nome di indice, le colonne chiave e di partizione, l'attributo di univocità e il tipo di ordinamento.

Indipendentemente dal fatto che gli indici non cluster vengano ricostruiti oppure meno, rimangono sempre nei filegroup o negli schemi di partizione originali e utilizzano sempre le funzioni di partizione originali. Se un indice cluster viene ricostruito in un filegroup o uno schema di partizione diverso, gli indici non cluster non vengono spostati in base alla nuova posizione dell'indice cluster. Pertanto, anche gli indici non cluster in precedenza allineati con l'indice cluster potrebbero non essere più allineati. Per ulteriori informazioni sull'allineamento di indici partizionati, vedere Linee guida specifiche per gli indici partizionati.

La clausola DROP_EXISTING non ripete l'ordinamento dei dati se vengono utilizzate le stesse colonne chiave indice nello stesso ordine e con lo stesso tipo di ordinamento crescente o decrescente, a meno che nell'istruzione dell'indice non venga specificato un indice non cluster e l'opzione ONLINE sia impostata su OFF. Se l'indice cluster è disabilitato, l'operazione CREATE INDEX WITH DROP_EXISTING deve essere eseguita con l'opzione ONLINE impostata su OFF. Se un indice non cluster è disabilitato e non è associato a un indice cluster disabilitato, l'operazione CREATE INDEX WITH DROP_EXISTING può essere eseguita con l'opzione ONLINE impostata su OFF o ON.

Quando vengono eliminati o ricostruiti indici con un numero di extent pari o superiore a 128, Motore di database posticipa le effettive deallocazioni delle pagine e i blocchi associati fino al termine del commit della transazione. Per ulteriori informazioni, vedere Eliminazione e ricostruzione di oggetti di grandi dimensioni.

Opzione ONLINE

Per l'esecuzione di operazioni sugli indici in linea, è necessario attenersi alle indicazioni seguenti:

• Non è possibile modificare, troncare o eliminare la tabella sottostante mentre è in corso un'operazione sull'indice in linea.
• Durante l'operazione sull'indice lo spazio su disco necessario aumenta temporaneamente. Per ulteriori informazioni, vedere Individuazione dei requisiti di spazio su disco per gli indici.
• È possibile eseguire operazioni in linea su indici partizionati e indici che contengono colonne calcolate persistenti o colonne incluse.

Per ulteriori informazioni, vedere Esecuzione di operazioni in linea su indici.

Opzioni per blocchi a livello di riga e di pagina

Se ALLOW_ROW_LOCKS = ON e ALLOW_PAGE_LOCK = ON, sono consentiti blocchi a livello di riga, pagina e tabella per l'accesso all'indice. Motore di database sceglie il tipo di blocco più appropriato e può eseguire l'escalation del blocco dal livello di riga o pagina al livello di tabella. Per ulteriori informazioni, vedere Escalation dei blocchi (Motore di database).

Se ALLOW_ROW_LOCKS = OFF e ALLOW_PAGE_LOCK = OFF, sono consentiti solo blocchi a livello di tabella per l'accesso all'indice.

Per ulteriori informazioni sulla configurazione della granularità dei blocchi per un indice, vedere Personalizzazione dei blocchi per un indice.

Visualizzazione delle informazioni degli indici

Per ottenere informazioni sugli indici, è possibile utilizzare viste del catalogo, funzioni di sistema e stored procedure di sistema. Per ulteriori informazioni, vedere Visualizzazione delle informazioni relative agli indici.

Autorizzazioni

Richiede 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.

Esempi

A. Creazione di un indice non cluster semplice

Nell'esempio seguente viene creato un indice non cluster sulla colonna VendorID della tabella Purchasing.ProductVendor .

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (VendorID); 
GO

B. Creazione di un indice composto non cluster semplice

Nell'esempio seguente viene creato un indice composto non cluster sulle colonne SalesQuota e SalesYTD della tabella Sales.SalesPerson.

USE AdventureWorks
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

C. Creazione di un indice non cluster univoco

Nell'esempio seguente viene creato un indice non cluster univoco sulla colonna Name della tabella Production.UnitMeasure. Questo indice applicherà il requisito dell'univocità per i dati inseriti nella colonna Name.

USE AdventureWorks;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

Nella query seguente viene verificato il vincolo di univocità mediante un tentativo di inserimento di una riga con lo stesso valore di una riga esistente.

--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
    VALUES ('OC', 'Ounces', GetDate());

Il messaggio di errore risultante è:

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

D. Utilizzo dell'opzione IGNORE_DUP_KEY

Nell'esempio seguente viene illustrato l'effetto dell'opzione IGNORE_DUP_KEY tramite l'inserimento di più righe in una tabella temporanea prima con questa opzione impostata su ON e quindi con questa opzione impostata su OFF. Nella tabella #Test viene inserita una singola riga che causerà intenzionalmente un valore duplicato quando verrà eseguita la seconda istruzione INSERT su più righe. Il numero di righe inserite viene ottenuto mediante un calcolo delle righe della tabella.

USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

Di seguito sono riportati i risultati della seconda istruzione INSERT.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows 
-------------- 
38

Si noti che le righe della tabella Production.UnitMeasure che non violano il vincolo di univocità sono state inserite correttamente. Nonostante sia stato visualizzato un messaggio di avviso e sia stata ignorata la riga duplicata, non è stato eseguito un rollback dell'intera transazione.

A questo punto vengono eseguite di nuovo le stesse istruzioni con l'opzione IGNORE_DUP_KEY impostata su OFF.

USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

Di seguito sono riportati i risultati della seconda istruzione INSERT.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows 
-------------- 
1

Si noti che nella tabella non è stata inserita alcuna riga della tabella Production.UnitMeasure, anche se il problema di violazione del vincolo dell'indice UNIQUE era causato da una sola riga.

E. Utilizzo di DROP_EXISTING per l'eliminazione e la ricreazione di un indice

Nell'esempio seguente viene eliminato e ricreato un indice esistente sulla colonna ProductID della tabella Production.WorkOrder tramite l'opzione DROP_EXISTING. Vengono inoltre impostate le opzioni FILLFACTOR e PAD_INDEX.

USE AdventureWorks;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

G. Creazione di un indice per una vista

Nell'esempio seguente vengono creati una vista e un indice per tale vista, quindi vengono eseguite due query che utilizzano la vista indicizzata.

USE AdventureWorks;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

G. Creazione di un indice con colonne non chiave

Nell'esempio seguente viene creato un indice non cluster con una colonna chiave (PostalCode) e quattro colonne non chiave (AddressLine1, AddressLine2, City, StateProvinceID), quindi viene eseguita una query che utilizza tale indice. Per visualizzare l'indice selezionato da Query Optimizer, scegliere Includi piano di esecuzione effettivo dal menu Query di SQL Server Management Studio prima di eseguire la query.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
    ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

H. Creazione di un indice XML primario

Nell'esempio seguente viene creato un indice XML primario sulla colonna CatalogDescription della tabella Production.ProductModel.

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription 
        ON Production.ProductModel;
GO
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);
GO

I. Creazione di un indice XML secondario

Nell'esempio seguente viene creato un indice XML secondario sulla colonna CatalogDescription della tabella Production.ProductModel.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;
GO
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path 
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

J. Creazione di un indice partizionato

Nell'esempio seguente viene creato un indice partizionato non cluster nello schema di partizione esistente TransactionsPS1. In questo esempio si presuppone che sia stato installato l'esempio di indice partizionato. Per informazioni sull'installazione, vedere Readme_PartitioningScript.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_TransactionHistory_ReferenceOrderID')
    DROP INDEX IX_TransactionHistory_ReferenceOrderID
        ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
ON Production.TransactionHistory (ReferenceOrderID)
ON TransactionsPS1 (TransactionDate);
GO

Vedere anche

Riferimento

ALTER INDEX (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
CREATE TABLE (Transact-SQL)
Tipi di dati (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
DROP INDEX (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)
sys.xml_indexes (Transact-SQL)
EVENTDATA (Transact-SQL)

Altre risorse

Individuazione dei requisiti di spazio su disco per gli indici
Linee guida generali per la progettazione di indici
Indici nelle colonne con tipo di dati XML
Architettura di tabelle e indici

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione	Cronologia
14 aprile 2006	Contenuto aggiornato: Rimozione degli indici non cluster univoci dall'elenco degli indici che non possono essere creati in linea. Questa modifica si applica a SQL Server 2005 Service Pack 1 e versioni successive. Aggiunta di una nota a piè di pagina alla tabella Opzione SET relativa all'effetto sull'impostazione ARITHABORT quando ANSI_WARNINGS è impostata su ON.
5 dicembre 2005	Nuovo contenuto Aggiunta degli indici non cluster univoci all'elenco degli indici che non possono essere creati in linea.