CREATE INDEX (Transact-SQL)

  • Articolo

Consente di creare un indice relazionale in una tabella specificata o in una vista di una tabella specificata. L'indice può essere creato prima dell'immissione dei dati nella tabella. È possibile creare indici relazionali in tabelle o viste di un altro database specificando un nome di database qualificato.

[!NOTA]

Per informazioni su come creare un indice XML, vedere CREATE XML INDEX (Transact-SQL). Per informazioni su come creare un indice spaziale, vedere CREATE SPATIAL INDEX (Transact-SQL). Per informazioni su come creare un indice columnstore ottimizzato in memoria xVelocity, vedere CREATE COLUMNSTORE INDEX (Transact-SQL).

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

<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
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}

<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::= 
        column_name IN (constant ,...n)

<comparison> ::= 
        column_name <comparison_op> constant 

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<range> ::= 
<partition_number_expression> TO <partition_number_expression>


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 per più righe. L'indice cluster di una vista deve essere univoco.

    In Motore di database 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é più valori Null 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. Nel livello inferiore, o foglia, dell'indice cluster sono contenute le righe di dati effettive della tabella. È possibile creare un solo indice cluster alla volta per una tabella o una vista.

    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 Creazione 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 ricompilati.

    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 oppure 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 dispongano di spazio sufficiente per l'indice.

    In alcuni casi la creazione di un indice cluster può abilitare gli indici precedentemente disabilitati. Per ulteriori informazioni, vedere Abilitazione di indici e vincoli e Disabilitazione di indici e vincoli.

  • 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 indicizzato.

    Per ogni tabella è possibile definire al massimo 999 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 se sulla vista è già stato definito un indice cluster univoco.

    Il valore predefinito è NONCLUSTERED.

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

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

    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 di tipo CLR definito dall'utente se il tipo supporta l'ordinamento binario. È inoltre possibile creare indici su colonne calcolate 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 di tipo CLR definito dall'utente, vedere Tipi CLR definiti dall'utente.

  • [ 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.

    I nomi di colonna non possono essere ripetuti nell'elenco INCLUDE e non possono essere utilizzati contemporaneamente per colonne chiave e non chiave. Gli indici non cluster contengono sempre le colonne dell'indice cluster se nella tabella è definito un indice cluster. Per ulteriori informazioni, vedere Creare indici 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 ricompilato in modalità offline (ONLINE = OFF).

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

    Per informazioni sulle creazione di un indice XML, vedere CREATE XML INDEX (Transact-SQL).

  • WHERE <filter_predicate>
    Crea un indice filtrato specificando le righe da includere nell'indice. L'indice filtrato deve essere un indice non cluster in una tabella. Crea statistiche filtrate per le righe di dati dell'indice filtrato.

    Il predicato del filtro utilizza una logica di confronto semplice e non può fare riferimento a una colonna calcolata, a una colonna con tipo definito dall'utente (UDT), a una colonna con tipo di dati spaziale o a una colonna con tipo di dati hierarchyID. I confronti in cui vengono utilizzati valori letterali NULL non sono consentiti con gli operatori di confronto. In alternativa, utilizzare gli operatori IS NULL e IS NOT NULL.

    Di seguito sono riportati alcuni esempi di predicati di filtro per la tabella Production.BillOfMaterials:

    WHERE StartDate > '20000101' AND EndDate <= '20000630'

    WHERE ComponentID IN (533, 324, 753)

    WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

    Gli indici filtrati non si applicano agli indici XML e full-text. Per gli indici UNIQUE, solo le righe selezionate devono avere valori di indice univoci. Gli indici filtrati non consentono l'opzione IGNORE_DUP_KEY.

  • ON partition_scheme_name**(column_name)**
    Specifica lo schema di partizione che definisce i filegroup a cui verrà eseguito il mapping delle partizioni di un indice partizionato. È necessario che lo schema di partizione sia presente nel database e sia stato creato tramite CREATE PARTITION SCHEME o ALTER PARTITION SCHEME. column_name specifica la colonna in base alla quale verrà partizionato un indice partizionato. La colonna deve corrispondere all'argomento della funzione di partizione utilizzata da partition_scheme_name per tipo di dati, lunghezza e precisione. column_name non è limitato alle colonne 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 posizionato nello stesso schema di partizione e con la stessa colonna di partizionamento della tabella sottostante.

    [!NOTA]

    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 Tabelle e 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à.

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

    In questo contesto il termine default non rappresenta una parola chiave, ma un identificatore per il filegroup predefinito e pertanto deve essere delimitato, ad esempio ON "default" oppure 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).

  • [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
    Specifica la posizione dei dati FILESTREAM per la tabella quando viene creato un indice cluster. La clausola FILESTREAM_ON consente di spostare i dati FILESTREAM in uno schema di partizione o in un filegroup FILESTREAM diverso.

    filestream_filegroup_name è il nome di un filegroup FILESTREAM. Nel filegroup deve essere disponibile un file definito tramite un'istruzione CREATE DATABASE o ALTER DATABASE. In caso contrario, viene generato un errore.

    Se la tabella è partizionata, la clausola FILESTREAM_ON deve essere inclusa e deve specificare uno schema di partizione dei filegroup FILESTREAM che utilizzi la stessa funzione di partizione e le stesse colonne di partizione dello schema di partizione per la tabella. In caso contrario, viene generato un errore.

    Se la tabella non è partizionata, la colonna FILESTREAM non può essere partizionata. I dati FILESTREAM per la tabella devono essere archiviati in un singolo filegroup specificato nella clausola FILESTREAM_ON.

    È possibile specificare FILESTREAM_ON NULL in un'istruzione CREATE INDEX se si sta creando un indice cluster e se nella tabella non è contenuta alcuna colonna FILESTREAM.

    Per ulteriori informazioni, vedere FILESTREAM (SQL Server).

<object>::=

Oggetto con nome completo o non completo 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 fillfactor non specificato
      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
    Viene specificata una percentuale indicante il livello di riempimento del livello foglia di ogni pagina di indice applicato da Motore di database durante la creazione o la ricompilazione. Il parametro fillfactor deve essere un valore intero compreso tra 1 e 100. Se il parametro fillfactor è 100, tramite Motore di database vengono creati indici con pagine foglia riempite fino alla capacità massima.

    L'impostazione di FILLFACTOR viene applicata solo in fase di creazione o di ricompilazione 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.

    Nota importanteImportante

    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 Specificare un fattore di riempimento per un indice.

  • 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 compilazione 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 compilazione 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 Opzione SORT_IN_TEMPDB per gli indici.

    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 l'errore restituito quando un'operazione di inserimento tenta di inserire valori di chiave duplicati in un indice univoco. L'opzione IGNORE_DUP_KEY viene applicata solo alle operazioni di inserimento eseguite dopo la creazione o la ricompilazione dell'indice. L'opzione non ha alcun effetto se si esegue CREATE INDEX, ALTER INDEX o UPDATE. Il valore predefinito è OFF.

    • ON
      Viene visualizzato un messaggio di avviso quando i valori di chiave duplicati vengono inseriti in un indice univoco. Avranno esito negativo solo le righe che violano il vincolo di unicità.

    • OFF
      Viene visualizzato un messaggio di errore quando i valori di chiave duplicati vengono inseriti in un indice univoco. Viene eseguito il rollback dell'intera operazione INSERT.

    L'opzione IGNORE_DUP_KEY non può essere impostata su ON per gli indici creati in una vista, negli indici non univoci, negli indici XML, spaziali e filtrati.

    Per visualizzare IGNORE_DUP_KEY, utilizzare sys.indexes.

    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 le statistiche di distribuzione vengono ricalcolate. Il valore predefinito è OFF.

    • ON
      Le statistiche non aggiornate non vengono ricalcolate automaticamente.

    • OFF
      Abilita 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.

    Nota importanteImportante

    La disabilitazione 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 l'indice cluster o non cluster esistente denominato deve essere eliminato e quindi ricompilato. Il valore predefinito è OFF.

    • ON
      L'indice esistente deve essere eliminato e ricompilato. Il nome di indice specificato deve corrispondere a quello dell'indice esistente, mentre la definizione dell'indice può essere modificata. È possibile, ad esempio, 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.

    Il tipo di indice non può essere modificato tramite l'opzione DROP_EXISTING.

    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 la modifica dei dati durante l'operazione sull'indice. Il valore predefinito è OFF.

    [!NOTA]

    Le operazioni sugli indici online non sono disponibili in tutte le edizioni di Microsoft SQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere Funzionalità supportate dalle edizioni di SQL Server 2012.

    • ON
      I blocchi di tabella a lungo termine non vengono mantenuti per la durata dell'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, per un breve periodo viene acquisito un blocco condiviso (S) sull'origine, in caso di creazione di un indice non cluster. In caso di creazione o di eliminazione di un indice cluster online o di ricompilazione 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 di tabella vengono applicati per la durata dell'operazione sugli indici. Un'operazione sugli indici offline che crea, ricompila o elimina un indice cluster oppure ricompila o elimina un indice non cluster acquisisce un blocco di modifica dello schema (SCH-M) sulla tabella. Il blocco impedisce agli utenti di accedere alla tabella sottostante per la durata dell'operazione. Un'operazione sugli indici offline che crea un indice non cluster acquisisce un blocco condiviso (S) sulla tabella. Il 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 online.

    È possibile creare online 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 nella tabella sottostante sono contenuti tipi di dati LOB: image, ntext, text e tipi spaziali.

    Per ulteriori informazioni, vedere Eseguire operazioni online sugli indici.

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

    • ON
      I blocchi di riga sono consentiti durante l'accesso all'indice. Motore di database determina quando utilizzare blocchi di riga.

    • OFF
      I blocchi 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. Il 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
    Consente di ignorare l'opzione di configurazione Configurare l'opzione di configurazione del server max degree of parallelism per 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 parallele sugli indici.

    [!NOTA]

    Le operazioni parallele sugli indici sono disponibili solo in alcune edizioni di Microsoft SQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere Funzionalità supportate dalle edizioni di SQL Server 2012.

  • DATA_COMPRESSION
    Specifica l'opzione di compressione dei dati per l'indice, il numero di partizione o l'intervallo di partizioni specificato. Sono disponibili le opzioni seguenti:

    • NONE
      L'indice o le partizioni specificate non vengono compressi.

    • ROW
      L'indice o le partizioni specificate vengono compressi utilizzando la compressione di riga.

    • PAGE
      L'indice o le partizioni specificate vengono compressi utilizzando la compressione di pagina.

    Per ulteriori informazioni sulla compressione, vedere Compressione dei dati.

  • ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
    Specifica le partizioni alle quali si applica l'impostazione DATA_COMPRESSION. Se l'indice non è partizionato, l'argomento ON PARTITIONS genererà un errore. Se la clausola ON PARTITIONS non viene fornita, l'opzione DATA_COMPRESSION si applica a tutte le partizioni di un indice partizionato.

    È possibile specificare <partition_number_expression> nei modi seguenti:

    • Fornire il numero di una partizione, ad esempio ON PARTITIONS (2).

    • Fornire i numeri di partizione per più partizioni singole separati da virgole, ad esempio ON PARTITIONS (1, 5).

    • Fornire sia intervalli, sia singole partizioni, ad esempio ON PARTITIONS (2, 4, 6 TO 8).

    È possibile specificare <range> sotto forma di numeri di partizione separati dalla parola TO, ad esempio ON PARTITIONS (6 TO 8).

    Per impostare tipi diversi di compressione dei dati per partizioni diverse, specificare più volte l'opzione DATA_COMPRESSION, ad esempio:

    REBUILD WITH 
(
DATA_COMPRESSION = NONE ON PARTITIONS (1), 
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), 
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)

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 ad analisi un altro indice anziché eseguire un'analisi di tabella. In alcune situazioni è possibile che l'operazione di ordinamento venga eliminata. Nei computer multiprocessore l'istruzione CREATE INDEX può utilizzare più processori per eseguire le operazioni di analisi e ordinamento associate alla creazione dell'indice, in modo identico alle altre query. Per ulteriori informazioni, vedere Configurazione di operazioni parallele sugli indici.

L'operazione di creazione dell'indice può essere sottoposta a una registrazione minima se è impostato il modello di recupero del database con registrazione minima o con registrazione minima delle operazioni bulk.

È possibile creare indici per una tabella temporanea. Quando si elimina la tabella o termina la sessione, vengono eliminati anche gli indici associati.

Gli indici supportano proprietà estese.

Indici cluster

La creazione di un indice cluster per una tabella (heap) e l'eliminazione e la ricreazione di un indice cluster esistente richiedono la disponibilità di un'area di lavoro aggiuntiva nel database per contenere l'ordinamento dei dati e una copia temporanea della tabella originale o dei dati dell'indice cluster esistenti. Per ulteriori informazioni sugli indici cluster, vedere Creare indici cluster.

Indici univoci

Quando esiste un indice univoco, ogni volta che vengono aggiunti nuovi dati tramite un'operazione di inserimento, Motore di database verifica l'eventuale presenza di valori duplicati. Le operazioni di inserimento che generano valori di chiave duplicati vengono sottoposte a rollback e in Motore di database viene visualizzato un messaggio di errore, anche nel caso in cui l'operazione di inserimento interessi più righe e crei un solo valore duplicato. Se si tenta di immettere dati per i quali è disponibile 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.

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 crea un indice per una tabella partizionata senza specificare un filegroup in cui inserirlo, l'indice verrà partizionato in modo identico alla tabella sottostante, in quanto per impostazione predefinita gli indici vengono inseriti negli stessi filegroup delle tabelle sottostanti e, nel caso di una tabella partizionata, nello stesso schema di partizione con colonne di partizionamento identiche. Se utilizza lo stesso schema di partizione e la stessa colonna di partizionamento della tabella, l'indice viene allineato.

Nota di attenzioneAttenzione

La creazione e la ricompilazione di indici non allineati per una tabella con oltre 1.000 partizioni sono possibili, ma non supportate. Questo tipo di operazioni può causare riduzioni delle prestazioni e un eccessivo consumo della memoria. Quando il numero di partizioni supera 1.000, si consiglia di utilizzare solo indici allineati.

Quando si partiziona un indice cluster non univoco, per impostazione predefinita nel Motore di database vengono aggiunte tutte le colonne di partizionamento all'elenco di 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.

In SQL Server 2012 le statistiche non vengono create analizzando tutte le righe nella tabella se viene creato o ricompilato un indice partizionato. Query Optimizer utilizza invece l'algoritmo di campionamento predefinito per generare statistiche. Per ottenere statistiche sugli indici partizionati analizzando tutte le righe nella tabella, utilizzare CREATE STATISTICS o UPDATE STATISTICS con la clausola FULLSCAN.

Indici filtrati

Un indice filtrato è un indice non cluster ottimizzato, adatto per le query tramite cui viene selezionata una piccola percentuale di righe da una tabella. Utilizza un predicato del filtro per indicizzare una parte dei dati di una tabella. Un indice filtrato progettato correttamente consente di migliorare le prestazioni di esecuzione delle query e di ridurre i costi di archiviazione e di manutenzione.

Opzioni SET necessarie per gli indici filtrati

Le opzioni SET nella colonna Required Value sono obbligatorie ogni volta che si verifica una delle condizioni seguenti:

  • Viene creato un indice filtrato.

  • I dati di un indice filtrato vengono modificati tramite un'operazione INSERT, UPDATE, DELETE o MERGE.

  • L'indice filtrato viene utilizzato da Query Optimizer nel piano di esecuzione di una query.

    Opzioni SET

    Valore obbligatorio

    ANSI_NULLS

    ON

    ANSI_PADDING

    ON

    ANSI_WARNINGS*

    ON

    ARITHABORT

    ON

    CONCAT_NULL_YIELDS_NULL

    ON

    NUMERIC_ROUNDABORT

    OFF

    QUOTED_IDENTIFIER

    ON

    *Quando il livello di compatibilità del database è impostato su 90 o su un valore maggiore, l'impostazione di ANSI_WARNINGS su ON comporta anche l'impostazione implicita di ARITHABORT su ON. Se il livello di compatibilità del database è impostato su 80 o su un valore inferiore, l'opzione ARITHABORT deve essere impostata su ON in modo esplicito.

Se le opzioni SET non sono corrette, possono verificarsi le condizioni seguenti:

  • L'indice filtrato non viene creato.

  • Motore di database genera un errore ed esegue il rollback delle istruzioni INSERT, UPDATE, DELETE o MERGE tramite le quali vengono modificati i dati dell'indice.

  • Query Optimizer non considera l'indice nel piano di esecuzione per le istruzioni Transact-SQL.

Per ulteriori informazioni sugli indici filtrati, vedere Creare indici filtrati.

Indici spaziali

Per informazioni sugli indici spaziali, vedere CREATE SPATIAL INDEX (Transact-SQL) e Panoramica degli indici spaziali.

Indici XML

Per informazioni sugli indici XML, vedere CREATE XML INDEX (Transact-SQL) e Indici XML (SQL Server).

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 determinano l'aumento della dimensione totale oltre i 900 byte avranno tuttavia esito negativo. 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 in una colonna varchar e i dati esistenti si trovano nell'unità di allocazione IN_ROW_DATA, le azioni di inserimento o aggiornamento successive eseguite nella colonna che comporterebbero lo spostamento dei dati all'esterno di righe non verranno eseguite correttamente.

Negli indici non cluster possono essere incluse colonne non chiave nel relativo livello foglia. Queste colonne non vengono considerate dal Motore di database durante il calcolo della dimensione della chiave di indice. Per ulteriori informazioni, vedere Creare indici con colonne incluse.

[!NOTA]

Quando le tabelle vengono partizionate, le colonne della chiave di partizionamento vengono aggiunte all'indice dal Motore di database, se non sono già presenti in un indice cluster non univoco. Le dimensioni combinate delle colonne indicizzate, senza le colonne incluse, più tutte le colonne di partizionamento aggiunte non possono superare 1800 byte in un indice cluster non univoco.

Colonne calcolate

Gli indici possono essere creati su colonne calcolate. Per le colonne calcolate è inoltre possibile impostare la proprietà PERSISTED. 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 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 derivate dai tipi di dati image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml possono essere indicizzate come colonna chiave o come 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 di 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 viene eseguita correttamente.

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 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 di esecuzione delle query tramite la copertura della query. Questo significa che tutte le colonne a cui la query fa riferimento sono incluse nell'indice come colonne chiave o non chiave. In questo modo, per individuare tutte le informazioni necessarie, in Query Optimizer verrà eseguita un'analisi dell'indice, senza necessità di accedere ai dati della tabella o dell'indice cluster. Per ulteriori informazioni, vedere Creare indici 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, sia 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 ricompilare 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 mantenendo 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 ricompilati una sola volta e poi solo in caso di modifica della relativa definizione. La clausola DROP_EXISTING non ricompila gli indici non cluster quando la definizione dell'indice contiene gli stessi valori dell'indice originale relativi al nome di indice, alle colonne chiave e di partizione, all'attributo di univocità e al tipo di ordinamento.

Indipendentemente dal fatto che gli indici non cluster vengano ricompilati o meno, rimangono sempre nei filegroup o negli schemi di partizione originali e utilizzano le funzioni di partizione originali. Se un indice cluster viene ricompilato in un filegroup o in uno schema di partizione diverso, gli indici non cluster non vengono spostati in funzione della 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 degli indici partizionati, vedere.

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 ricompilati indici con un numero di extent pari o superiore a 128, tramite il Motore di database vengono posticipate le effettive deallocazioni delle pagine e i blocchi associati fino al termine del commit della transazione.

Opzione ONLINE

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

  • Non è possibile modificare, troncare o eliminare la tabella sottostante mentre è in corso un'operazione sull'indice online.

  • Durante l'operazione sull'indice lo spazio su disco necessario aumenta temporaneamente.

  • È possibile eseguire operazioni online su indici partizionati e indici che contengono colonne calcolate persistenti o colonne incluse.

Per ulteriori informazioni, vedere Eseguire operazioni online sugli indici.

Opzioni per blocchi di riga e di pagina

Se ALLOW_ROW_LOCKS = ON e ALLOW_PAGE_LOCK = ON, sono consentiti blocchi di riga, pagina e tabella per l'accesso all'indice. Motore di database sceglie il blocco appropriato e può eseguire un'escalation del blocco da un blocco di riga o di pagina a un blocco di tabella.

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

Visualizzazione delle informazioni degli indici

Per ottenere informazioni sugli indici, è possibile utilizzare viste del catalogo, funzioni di sistema e stored procedure di sistema.

Compressione dei dati

La compressione dei dati è descritta nell'argomento Compressione dei dati. Di seguito sono illustrati i punti principali da considerare:

  • La compressione può consentire di archiviare più righe in una pagina, ma non di modificare la dimensione massima della riga.

  • Alle pagine non foglia di un indice non può essere applicata la compressione a livello di pagina, ma può essere applicata la compressione a livello di riga.

  • Ogni indice non cluster dispone di un'impostazione di compressione separata e non eredita l'impostazione di compressione della tabella sottostante.

  • Quando un indice cluster viene creato in un heap, tale indice eredita lo stato di compressione dell'heap, a meno che non venga specificato uno stato di compressione alternativo.

Agli indici partizionati vengono applicate le restrizioni seguenti:

  • Non è possibile modificare l'impostazione di compressione di una singola partizione se la tabella include indici non allineati.

  • La sintassi ALTER INDEX <index> ... La sintassi REBUILD PARTITION ... consente di ricompilare la partizione specificata dell'indice.

  • La sintassi ALTER INDEX <index> ... La sintassi REBUILD WITH ... consente di ricompilare tutte le partizioni dell'indice.

Per valutare il modo in cui la modifica dello stato di compressione influirà su una tabella, un indice o una partizione, utilizzare la stored procedure sp_estimate_data_compression_savings.

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.

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 AdventureWorks2012;
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 (BusinessEntityID); 
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 AdventureWorks2012
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 impone l'univocità dei dati inseriti nella colonna Name.

USE AdventureWorks2012;
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 genererà intenzionalmente un valore duplicato quando verrà eseguita la seconda istruzione INSERT su più righe. Il calcolo delle righe della tabella restituisce il numero di righe inserite.

USE AdventureWorks2012;
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 nuovamente le stesse istruzioni, ma l'opzione IGNORE_DUP_KEY è impostata su OFF.

USE AdentureWorks2012;
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, sebbene la violazione del vincolo dell'indice UNIQUE fosse determinata 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 AdventureWorks2012;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

F.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 AdventureWorks2012;
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 incluse (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, nel menu Query di SQL Server Management Studio selezionare Includi piano di esecuzione effettivo prima di eseguire la query.

USE AdventureWorks2012;
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 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.

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

I.Creazione di un indice filtrato

Nell'esempio seguente viene creato un indice filtrato nella tabella Production.BillOfMaterials. Il predicato del filtro può includere colonne che non sono colonne chiave nell'indice filtrato. Il predicato in questo esempio consente di selezionare solo le righe in cui EndDate non ha un valore NULL.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

J.Creazione di un indice compresso

Nell'esempio seguente viene creato un indice in una tabella non partizionata utilizzando la compressione di riga.

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

Nell'esempio seguente viene creato un indice in una tabella partizionata utilizzando la compressione di riga in tutte le partizioni dell'indice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

Nell'esempio seguente viene creato un indice in una tabella partizionata utilizzando la compressione di pagina nella partizione 1 dell'indice e la compressione di riga nelle partizioni da 2 a 4 dell'indice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO

Vedere anche

Riferimento

ALTER INDEX (Transact-SQL)

CREATE PARTITION FUNCTION (Transact-SQL)

CREATE PARTITION SCHEME (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE STATISTICS (Transact-SQL)

CREATE TABLE (Transact-SQL)

CREATE XML INDEX (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)

Concetti

Indici XML (SQL Server)