CREATE INDEX (Transact-SQL)

Si applica a: SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)

Crea un indice relazionale per una tabella o una vista. Viene detto anche indice rowstore, perché è un indice ad albero b-tree sia cluster che non cluster. È possibile creare un indice rowstore prima che siano presenti dati nella tabella. Usare un indice rowstore per migliorare le prestazioni delle query, in particolare quando le query effettuano le selezioni da colonne specifiche o richiedono valori da organizzare in base a un ordine particolare.

Azure Synapse Analytics e la piattaforma di strumenti analitici attualmente non supportano vincoli univoci. Tutti gli esempi che fanno riferimento a vincoli univoci sono applicabili solo a SQL Server, al database SQL di Azure e a Istanza gestita di SQL di Azure.

Per informazioni sulle linee guida sulla progettazione degli indici, vedere la guida alla progettazione degli indici di SQL Server.

Esempi:

1. Creare un indice non cluster in una tabella o una vista

   CREATE INDEX index1 ON schema1.table1 (column1);
   

2. Creare un indice cluster in una tabella e usare un nome in 3 parti per la tabella

   CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
   

3. Creare un indice non cluster con un vincolo univoco e specificare l'ordinamento

   CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
   

Scenario chiave:

A partire da SQL Server 2016 (13.x), nel database SQL di Azure e in Istanza gestita di SQL di Azure, è possibile usare un indice non cluster in un indice columnstore per migliorare le prestazioni delle query di data warehousing. Per altre informazioni, vedere Indici columnstore - data warehouse.

Per altri tipi di indici, vedere:

• CREATE XML INDEX
• CREATE SPATIAL INDEX
• CREATE COLUMNSTORE INDEX

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per SQL Server, database SQL di Azure Istanza gestita di SQL di Azure

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.table_or_view_name | schema_name.table_or_view_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 }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [MINUTES]
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
     [ ON PARTITIONS ( { <partition_number_expression> | <range> }
     [ , ...n ] ) ]
}

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

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

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

<comparison> ::=
        column_name <comparison_op> constant

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

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

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

Indice relazionale compatibile con le versioni precedenti

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
}

Sintassi per Azure Synapse Analytics e Parallel Data Warehouse

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

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.

Il motore di database non consente la creazione di un indice univoco nelle colonne che includono già valori duplicati, indipendentemente dal fatto che sia impostato o meno IGNORE_DUP_KEY su ON. Se si tenta di eseguire questa operazione, il 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.

Un UNIQUE vincolo viene NULL trattato come valore. Se una colonna è nullable e nella colonna esiste un UNIQUE vincolo, è consentita al massimo una riga con un oggetto NULL .

CLUSTERED

Crea un indice in cui l'ordinamento specificato per le colonne chiave di indice determina l'ordine di pagina nella struttura di indice su disco. Le righe nelle pagine nella parte inferiore o foglia del livello dell'indice cluster contengono sempre tutte le colonne della tabella. Le righe nelle pagine nei livelli superiori dell'indice contengono solo colonne chiave.

Una tabella può avere un solo indice cluster. Se un indice cluster esiste in una tabella, contiene tutti i dati nella tabella. Una tabella senza un indice cluster è denominata heap.

Una vista con un indice cluster univoco viene definita vista indicizzata. Una vista indicizzata può avere un solo indice cluster. 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 altre informazioni, vedere Creare viste indicizzate.

Creare l'indice cluster prima di qualsiasi indice non cluster. Gli indici non cluster esistenti nelle tabelle vengono ricompilati quando viene creato un indice cluster, che è un'operazione a elevato utilizzo di risorse se la tabella è di grandi dimensioni.

Se CLUSTERED viene omesso, viene creato un indice non cluster.

In alcuni casi, la creazione di un indice cluster può abilitare indici disabilitati in precedenza. Per altre informazioni, vedere Abilitare indici e vincoli e Disabilitare indici e vincoli.

NONCLUSTERED

Crea un indice in cui l'ordinamento specificato per le colonne chiave di indice determina l'ordine di pagina nella struttura di indice su disco. A differenza dell'indice cluster, le righe nelle pagine del livello foglia di un indice non cluster contengono solo le colonne chiave dell'indice. Facoltativamente, è possibile includere un subset di colonne non chiave usando la INCLUDE clausola .

Ogni tabella può avere fino a 999 indici non cluster, indipendentemente dalla modalità di creazione degli indici: in modo implicito con i PRIMARY KEY vincoli e UNIQUE oppure in modo esplicito con CREATE INDEX.

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

Se non è specificato diversamente, il tipo di indice predefinito è non cluster.

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. Devono essere anche conformi alle regole degli 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 32 colonne. Tutte le colonne di una chiave di indice composto devono appartenere alla stessa tabella o vista. La dimensione massima consentita dei valori combinati dell'indice è 900 byte per un indice cluster o 1700 per un indice non cluster. I limiti sono 16 colonne e 900 byte per le versioni precedenti a database SQL e SQL Server 2016 (13.x).

Le colonne con 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 indicizzata non può includere colonne ntext, text o image , anche se non vi si fa 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 altre informazioni sull'indicizzazione di colonne di tipo CLR definite 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 di un indice non cluster. L'indice non cluster può essere univoco o non univoco.

I nomi delle colonne non possono essere ripetuti nell'elenco INCLUDE e non possono essere usati contemporaneamente come colonne chiave e non chiave. Gli indici non cluster contengono sempre in modo implicito le colonne dell'indice cluster se nella tabella è definito un indice cluster. Per altre informazioni, vedere Creare indici con colonne incluse.

È possibile usare qualsiasi tipo di dati, ad eccezione di text, ntexte image. A partire da SQL Server 2012 (11.x), nel database SQL di Azure e in Istanza gestita di SQL di Azure, se una delle colonne non chiave specificate è varchar(max), nvarchar(max)o varbinary(max), l'indice può essere compilato o ricompilato usando l'opzione ONLINE .

Come colonne incluse è possibile utilizzare colonne calcolate che sono deterministiche, sia precise che imprecise. Le colonne calcolate derivate dai tipi di dati image, ntext, text, varchar(max), nvarchar(max), varbinary(max)e xml possono essere incluse purché il tipo di dati della colonna calcolata sia consentito come colonna inclusa. Per altre informazioni, vedere Indici per le colonne calcolate.

Per informazioni sulla creazione di un indice XML, vedere CREATE XML INDEX.

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 di filtro usa una logica di confronto semplice e non può fare riferimento a una colonna calcolata, a una colonna del tipo di dati definito dall'utente (UDT), a una colonna del tipo di dati spaziale o a una colonna di tipo di dati hierarchyid . Non sono consentiti confronti con NULL valori letterali che usano gli operatori di confronto. Usare invece 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 UNIQUE gli indici, 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 vengono mappate le partizioni di un indice partizionato. È necessario che lo schema di partizione sia presente nel database e sia stato creato eseguendo CREATE PARTITION SCHEME o ALTER PARTITION SCHEME. column_name specifica la colonna di partizionamento per l'indice. La colonna deve corrispondere all'argomento della funzione di partizione usata 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 nella tabella di base, ad eccezione del partizionamento di un indice univoco, column_name deve essere scelta tra quelle usate come chiave univoca. Questa restrizione consente al motore di database di verificare l'univocità dei valori di chiave solo all'interno di una singola partizione.

Se non si specifica partition_scheme_name o filegroup e la tabella è partizionata, l'indice viene posizionato nello stesso schema di partizione, usando la stessa colonna di partizionamento della tabella sottostante.

Per altre informazioni sul partizionamento degli indici, sulle tabelle e sugli 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 userà lo stesso filegroup della tabella o della vista sottostante. Il filegroup deve essere già esistente.

ON [impostazione predefinita]

Crea l'indice specificato nello stesso filegroup o schema di partizione della tabella o della vista.

Il termine default, in questo contesto, non è una parola chiave. Si tratta di un identificatore per il filegroup o lo schema partizionato della tabella o della vista e deve essere delimitato, come in ON "default" o ON [default]. Se "default" è specificato, l'opzione QUOTED_IDENTIFIER deve essere ON per la sessione corrente. Si tratta dell'impostazione predefinita. Per altre informazioni, vedere SET QUOTED_IDENTIFIER.

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

Il filestream_filegroup_name è il nome di un filegroup FILESTREAM. Nel filegroup deve essere disponibile un file definito usando 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 usi 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 altre informazioni, vedere FILESTREAM (SQL Server).

<object>::=

Oggetto con nome completo o non completo da 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 creare un indice in una vista, è necessario definire la vista con SCHEMABINDING. Prima di creare qualsiasi indice non cluster per una vista, è necessario creare un indice cluster univoco. Per altre informazioni sulle viste indicizzate, vedere Osservazioni.

A partire da SQL Server 2016 (13.x), l'oggetto può essere una tabella archiviata con un indice columnstore cluster.

Il database SQL di Azure supporta il formato del nome in tre parti <database_name>.<schema_name>.<object_name> quando <database_name> è il nome del database corrente o <database_name> è tempdb e <object_name> inizia con # o ##. Se il nome dello schema è dbo, è possibile omettere <schema_name>.

< >relational_index_option::=

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

PAD_INDEX = { ON | OFF }

Specifica il riempimento dell'indice. Il valore predefinito è OFF.

• In...

  La percentuale di spazio disponibile specificata dal fattore di riempimento viene applicata alle pagine di livello intermedio dell'indice. Se FILLFACTOR non viene specificato contemporaneamente PAD_INDEX è impostato su ON, viene usato il valore del fattore di riempimento in sys.indexes.

• OFF

  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. Ciò si verifica anche se PAD_INDEX è impostato su ON ma il fattore di riempimento non è specificato.

L'opzione PAD_INDEX è utile solo quando FILLFACTOR viene specificata, perché PAD_INDEX usa la percentuale specificata da FILLFACTOR. Se la percentuale specificata per FILLFACTOR non è sufficientemente grande da consentire una riga, il motore di database esegue internamente l'override della percentuale per consentire il minimo. Il numero di righe in una pagina di indice intermedia non è mai minore di due, indipendentemente dal valore minimo di FILLFACTOR.

Nella 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 del livello foglia di ogni pagina di indice da parte del motore di database durante la creazione o la ricompilazione dell'indice. Il valore fillfactor deve essere un valore intero compreso tra 1 e 100. I valori 0 e 100 relativi al fattore di riempimento sono equivalenti. Se fillfactor è 100, il motore di database crea indici con pagine foglia riempite fino alla capacità massima.

L'impostazione FILLFACTOR viene applicata solo in fase di creazione o ricompilazione dell'indice. La percentuale specificata di spazio vuoto delle pagine non viene mantenuta in modo dinamico dal motore di database.

Per visualizzare l'impostazione del fattore di riempimento, usare la fill_factor colonna nella vista del catalogo sys.indexes .

Per altre informazioni, vedere Specificare il fattore di riempimento per un indice.

SORT_IN_TEMPDB = { ON | OFF }

Specifica se archiviare i risultati temporanei dell'ordinamento in tempdb. Il valore predefinito è OFF ad eccezione di database SQL di Azure Hyperscale. Per tutte le operazioni di compilazione degli indici in Hyperscale, SORT_IN_TEMPDB è sempre ON a meno che non venga usata una compilazione di indice ripristinabile. Per le compilazioni degli indici ripristinabili, SORT_IN_TEMPDB è sempre OFF.

• In...

  I risultati di ordinamento intermedi usati per compilare l'indice vengono archiviati in tempdb. Ciò potrebbe ridurre il tempo necessario per creare un indice. 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 creare l'indice, tempdb deve avere circa la stessa quantità di spazio aggiuntivo per contenere i risultati dell'ordinamento intermedio. Per altre informazioni, vedere opzione SORT_IN_TEMPDB per gli indici.

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

• In...

  Viene visualizzato un messaggio di avviso quando in un indice univoco vengono inseriti valori chiave duplicati. Solo le righe che violano il vincolo di univocità non vengono inserite.

• OFF

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

IGNORE_DUP_KEY non può essere impostato su ON per gli indici creati in una vista, indici non univoci, indici XML, indici spaziali e indici filtrati.

Per visualizzare l'impostazione IGNORE_DUP_KEY per un indice, usare la colonna ignore_dup_key nella vista del catalogo sys.indexes.

Nella 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 vengono ricalcolate. Il valore predefinito è OFF.

• In...

  Le statistiche non aggiornate non vengono ricalcolate automaticamente.

• OFF

  Abilita l'aggiornamento automatico delle statistiche.

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

L'impostazione di STATISTICS_NORECOMPUTE su ON non impedisce l'aggiornamento delle statistiche sugli indici che si verificano durante l'operazione di ricompilazione dell'indice.

Nella sintassi compatibile con le versioni precedenti WITH STATISTICS_NORECOMPUTE equivale a WITH STATISTICS_NORECOMPUTE = ON.

STATISTICS_INCREMENTAL = { ON | OFF }

si applica a: SQL Server 2014 (12.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

Quando ON, le statistiche create sono per statistiche di partizione. Quando OFF, l'albero delle statistiche viene eliminato e SQL Server calcola nuovamente le statistiche. Il valore predefinito è OFF.

Se le statistiche per partizione non sono supportate, l'opzione viene ignorata e viene generato un avviso. Le statistiche incrementali non sono supportate nei casi seguenti:

• Statistiche create con indici che non hanno il partizionamento allineato con la tabella di base.
• Statistiche create per i database secondari leggibili Always On.
• Statistiche create per i database di sola lettura.
• Statistiche create per gli indici filtrati.
• Statistiche create per le viste.
• Statistiche create per le tabelle interne.
• Statistiche create con indici spaziali o indici XML.

DROP_EXISTING = { ON | OFF }

Consente di eliminare e ricompilare l'indice cluster o non cluster esistente con le specifiche colonne modificate e di mantenere lo stesso nome per l'indice. Il valore predefinito è OFF.

• In...

  Specifica che l'indice esistente deve essere eliminato e ricompilato e che deve avere lo stesso nome del parametro index_name.

• OFF

  Specifica che l'indice esistente non deve essere eliminato e ricompilato. Se il nome di indice specificato esiste già, SQL Server visualizza un messaggio di errore.

Con DROP_EXISTING è possibile modificare:

• Un indice rowstore non cluster in un indice rowstore cluster.

Con DROP_EXISTING non è possibile modificare:

• Un indice rowstore cluster in un indice rowstore non cluster.
• Un indice columnstore cluster in qualsiasi tipo di indice rowstore.

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

• In...

  I blocchi di tabella a lungo termine non vengono mantenuti per la durata dell'operazione sugli indici. Durante la fase principale dell'operazione sull'indice, nella tabella di origine viene mantenuto solo un blocco condiviso (IS). in modo da consentire l'esecuzione di query o l'aggiornamento della tabella sottostante e degli indici. All'inizio dell'operazione, un blocco condiviso (S) viene mantenuto sull'oggetto di origine per un breve periodo di tempo. Alla fine dell'operazione, per un breve periodo di tempo, viene acquisito un blocco condiviso (S) sull'oggetto se viene creato un indice non cluster. Un blocco di modifica dello schema (Sch-M) viene acquisito quando un indice cluster viene creato o eliminato online e quando viene ricompilato un indice cluster o non cluster. ONLINE non può essere impostato su ON quando viene creato un indice in una tabella temporanea locale.

  Nota

  È possibile usare l'opzione WAIT_AT_LOW_PRIORITY per ridurre o evitare il blocco durante le operazioni sugli indici online. Per altre informazioni, vedere WAIT_AT_LOW_PRIORITY con operazioni sugli indici online.

• OFF

  I blocchi di tabella vengono applicati per la durata dell'operazione sugli indici. Un'operazione di indice offline che crea, ricompila o elimina un indice cluster, spaziale o XML oppure ricompila o elimina un indice non cluster, acquisisce un blocco dello schema (Sch-M) sulla tabella. Il blocco impedisce agli utenti di accedere alla tabella sottostante per la durata dell'operazione. Un'operazione sull'indice offline che crea un indice non cluster acquisisce inizialmente un blocco condiviso (S) sulla tabella. Ciò impedisce modifiche della definizione di tabella sottostante, ma consente la lettura e la modifica dei dati nella tabella mentre la compilazione dell'indice è in corso.

Per altre informazioni, vedere Eseguire operazioni sugli indici online e linee guida per le operazioni sugli indici online.

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

• Indice XML
• Indice di una tabella temporanea locale
• Indice cluster univoco iniziale su una vista
• Indici cluster disabilitati
• Indici columnstore cluster in SQL Server 2017 (14.x)) e versioni precedenti
• Indici columnstore non cluster in SQL Server 2016 (13.x)) e versioni precedenti
• Indice cluster, se la tabella sottostante contiene tipi di dati LOB (image, ntext, text) e tipi spaziali
• Le colonne varchar(max) e varbinary(max) non possono far parte di una chiave di indice. In SQL Server (a partire da SQL Server 2012 (11.x)), nel database SQL di Azure e in Istanza gestita di SQL di Azure, quando una tabella contiene colonne varchar(max) o varbinary(max), un indice cluster contenente altre colonne può essere compilato o ricompilato usando l'opzione ONLINE .
• Indici non cluster in una tabella con un indice columnstore cluster

Per altre informazioni, vedere Funzionamento delle operazioni sugli indici online.

RESUMABLE = { ON | OFF }

Si applica a: SQL Server 2019 (15.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

Specifica se un'operazione sull'indice online è ripristinabile. Per altre informazioni, vedere Operazioni sugli indici ripristinabili e Considerazioni sull'indice ripristinabili.

• In...

  L'operazione sull'indice è ripristinabile.

• OFF

  L'operazione sull'indice non è ripristinabile.

MAX_DURATION = tempo [MINUTES] usato con RESUMABLE = ON (richiede ONLINE = ON)

Si applica a: SQL Server 2019 (15.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

Specifica per quanto tempo, in minuti, viene eseguita un'operazione di indice ripristinabile prima che venga sospesa.

ALLOW_ROW_LOCKS = { ON | OFF }

Specifica se sono consentiti blocchi di riga. Il valore predefinito è ON.

• In...

  I blocchi di riga sono consentiti durante l'accesso all'indice. Il motore di database determina quando usare i 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.

• In...

  I blocchi a livello di pagina sono consentiti durante l'accesso all'indice. Il motore di database determina quando usare i blocchi di pagina.

• OFF

  I blocchi a livello di pagina non vengono utilizzati.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

Si applica a: SQL Server 2019 (15.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

Specifica se ottimizzare per evitare conflitti di inserimento dell'ultima pagina. Il valore predefinito è OFF. Per altre informazioni, vedere la sezione Chiavi sequenziali .

MAXDOP = max_degree_of_parallelism

Esegue l'override dell'opzione di configurazione massimo grado di parallelismo per l'operazione sull'indice. Per altre informazioni, vedere Configurare l'opzione di configurazione del server max degree of parallelism. Usare MAXDOP per limitare il grado di parallelismo e il consumo di risorse risultante per un'operazione di compilazione dell'indice.

max_degree_of_parallelism può essere:

• 1

  Disattiva la generazione di piani paralleli.

• >1

  Limita il grado massimo di parallelismo usato in un'operazione di indice parallelo al numero specificato o meno in base al carico di lavoro di sistema corrente.

• 0 (predefinito)

  Usa il grado di parallelismo specificato a livello di server, database o gruppo del carico di lavoro, a meno che non venga ridotto in base al carico di lavoro di sistema corrente.

Per altre informazioni, vedere Configurare le operazioni parallele sugli indici.

DATA_COMPRESSION

Specifica l'opzione di compressione dei dati per l'indice, il numero di partizione o l'intervallo di partizioni specificato. Le opzioni sono le seguenti:

• NONE

  L'indice o le partizioni specificate non vengono compressi. Non si applica agli indici columnstore.

• ROW

  L'indice o le partizioni specificate vengono compressi utilizzando la compressione di riga. Non si applica agli indici columnstore.

• PAGE

  L'indice o le partizioni specificate vengono compressi utilizzando la compressione di pagina. Non si applica agli indici columnstore.

• COLUMNSTORE

  si applica a: SQL Server 2014 (12.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

  Si applica solo agli indici columnstore, inclusi gli indici columnstore cluster e quelli non cluster.

• COLUMNSTORE_ARCHIVE

  si applica a: SQL Server 2014 (12.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

  Si applica solo agli indici columnstore, inclusi gli indici columnstore cluster e quelli non cluster. COLUMNSTORE_ARCHIVE comprime ulteriormente la partizione specificata in una dimensione inferiore. Può essere utilizzata per l'archiviazione o in altre situazioni in cui sono richieste dimensioni di archiviazione inferiori ed è possibile concedere più tempo per l'archiviazione e il recupero.

Per altre informazioni sulla compressione, vedere Compressione dei dati.

XML_COMPRESSION

si applica a: SQL Server 2022 (16.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

Specifica l'opzione di compressione XML per l'indice specificato che contiene una o più colonne del tipo di dati xml. Le opzioni sono le seguenti:

• In...

  L'indice o le partizioni specificate vengono compressi usando la compressione XML.

• OFF

  L'indice o le partizioni specificate non vengono compresse tramite la compressione XML.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

Specifica le partizioni a cui si applicano le impostazioni DATA_COMPRESSION o XML_COMPRESSION. Se l'indice non è partizionato, l'argomento ON PARTITIONS genera un errore. Se la clausola ON PARTITIONS non viene specificata, l'opzione DATA_COMPRESSION o XML_COMPRESSION verrà applicata a tutte le partizioni di un indice partizionato.

<partition_number_expression> può essere specificato nei modi seguenti:

• Specificare il numero di una partizione, ad esempio: ON PARTITIONS (2).
• Specificare i numeri di partizione per più partizioni singole separati da virgole, ad esempio: ON PARTITIONS (1, 5).
• Specificare sia intervalli sia singole partizioni, ad esempio: ON PARTITIONS (2, 4, 6 TO 8).

<range> può essere specificato come numeri di partizione separati dalla parola chiave 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)
);

È anche possibile specificare più volte l'opzione XML_COMPRESSION, ad esempio:

REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

Osservazioni:

Quando si crea il piano di query per l'istruzione CREATE INDEX , Query Optimizer potrebbe scegliere di analizzare un altro indice anziché eseguire un'analisi di tabella. L'operazione di ordinamento potrebbe essere eliminata in alcune situazioni. Nei computer multiprocessore è CREATE INDEX possibile usare il parallelismo per le operazioni di analisi e ordinamento associate alla creazione dell'indice, nello stesso modo in cui vengono eseguite altre query. Per altre informazioni, vedere Configurare le operazioni parallele sugli indici.

L'operazione CREATE INDEX potrebbe essere registrata al minimo se il modello di recupero del database è impostato su registrazione minima delle operazioni bulk o semplici.

È possibile creare indici per una tabella temporanea. Quando la tabella viene eliminata o esce dall'ambito, gli indici vengono eliminati.

Un indice cluster si basa su una variabile di tabella quando viene aggiunto un vincolo di chiave primaria. Analogamente, un indice non cluster si basa su una variabile di tabella quando viene aggiunto un vincolo univoco. Quando la variabile di tabella esce dall'ambito, gli indici vengono eliminati.

Gli indici supportano proprietà estese.

CREATE INDEX non è supportato in Microsoft Fabric.

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 altre informazioni sugli indici cluster, vedere Creare indici cluster e la guida all'architettura e alla progettazione degli indici di SQL Server.

Indici non cluster

A partire da SQL Server 2016 (13.x), nel database SQL di Azure e in Istanza gestita di SQL di Azure è possibile creare un indice non cluster in una tabella archiviata come indice columnstore cluster. Se si crea prima di tutto un indice non cluster in una tabella archiviata come heap o indice cluster, l'indice viene mantenuto se successivamente si converte la tabella in un indice columnstore cluster. Non è inoltre necessario eliminare l'indice non cluster quando si ricompila l'indice columnstore cluster.

L'opzione FILESTREAM_ON non è valida quando si crea un indice non cluster per una tabella archiviata come indice columnstore cluster.

Indici univoci

Quando esiste un indice univoco, il motore di database verifica la presenza di valori duplicati ogni volta che i dati vengono aggiunti o modificati. Viene eseguito il rollback delle operazioni che generano valori di chiave duplicati e il motore di database restituisce un messaggio di errore. Questo vale anche se l'operazione di aggiunta o modifica dei dati modifica molte righe, ma causa solo un duplicato. Se si tenta di inserire righe quando è presente un indice univoco con l'opzione IGNORE_DUP_KEY impostata su ON, le righe che violano l'indice univoco vengono ignorate.

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 usa lo stesso schema di partizione e la stessa colonna di partizionamento della tabella, l'indice viene allineato alla tabella.

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 altre informazioni sugli indici partizionati, vedere Tabelle e indici partizionati e la guida all'architettura e alla progettazione degli indici di SQL Server.

Quando un indice viene creato o ricompilato, la query ottimizza gli aggiornamenti delle statistiche sull'indice. Per un indice partizionato, Query Optimizer usa l'algoritmo di campionamento predefinito anziché analizzare tutte le righe della tabella per un indice non partizionato. Per ottenere statistiche sugli indici partizionati analizzando tutte le righe nella tabella, usare 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 SET opzioni nella colonna Valore obbligatorio sono necessarie ogni volta che si verifica una delle condizioni seguenti:

• Si crea un indice filtrato.

• Un'istruzione INSERT, UPDATE, DELETEo MERGE modifica i dati in un indice filtrato.

• L'indice filtrato viene usato da Query Optimizer per generare il piano di query.

  opzione SET	Valore obbligatorio	Valore server predefinito	Valori OLE DB e ODBC predefiniti	Valore DB-Library predefinito
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS 1	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

  ¹ L'impostazione ANSI_WARNINGS su ON imposta in modo implicito su ARITHABORTON quando il livello di compatibilità del database è impostato su 90 o superiore. Se il livello di compatibilità del database è impostato su 80 o versioni precedenti, l'opzione ARITHABORT deve essere impostata in modo esplicito su ON.

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

• La creazione dell'indice filtrato ha esito negativo.
• Il motore di database genera un errore ed esegue il rollback dell'istruzione INSERT, UPDATE, DELETEo MERGE che modifica i dati nell'indice.
• Query Optimizer esclude l'indice dal piano di esecuzione relativo alle istruzioni Transact-SQL.

Per altre informazioni sugli indici filtrati, vedere Creare indici filtrati e la guida all'architettura e alla progettazione degli indici di SQL Server.

Indici spaziali

Per informazioni sugli indici spaziali, vedere CENNI PRELIMINARI su CREATE SPATIAL INDEX e Indici spaziali.

Indici XML

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

Dimensione della chiave di indice

La dimensione massima per una chiave di indice è 900 byte per un indice cluster e 1700 byte per un indice non cluster. Prima di database SQL e SQL Server 2016 (13.x) il limite era sempre 900 byte. Gli indici sulle colonne varchar che superano il limite di byte possono essere creati se i dati esistenti nelle colonne non superano il limite al momento della creazione dell'indice; Tuttavia, le operazioni di inserimento o aggiornamento successive sulle colonne che causano un errore di dimensione totale maggiore del limite. 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 un indice cluster viene creato in una colonna varchar e i dati esistenti si trovano nell'unità IN_ROW_DATA di allocazione, le successive operazioni di inserimento o aggiornamento sulla colonna che eseguirebbero il push dei dati all'esterno della riga hanno esito negativo.

Gli indici non cluster possono includere colonne non chiave (incluse) nel livello foglia dell'indice. Queste colonne non vengono considerate dal motore di database durante il calcolo delle dimensioni della chiave di indice. Per altre informazioni, vedere Creare indici con colonne incluse e la guida all'architettura e alla progettazione degli indici di SQL Server.

Colonne calcolate

Gli indici possono essere creati su colonne calcolate. Inoltre, le colonne calcolate possono avere la proprietà PERSISTED. Questo significa che il motore di database archivia i valori calcolati nella tabella e li aggiorna quando vengono aggiornate altre colonne da cui dipende la colonna calcolata. Il motore di database usa 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. Tuttavia, l'uso della PERSISTED proprietà espande il tipo di colonne calcolate indicizzabili da includere:

• 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 nel motore di database, ma imprecise.

Per le colonne calcolate persistenti è necessario impostare le opzioni seguenti SET , come illustrato nella sezione precedente Opzioni SET necessarie per gli indici filtrati.

Il UNIQUE vincolo o PRIMARY KEY può contenere una colonna calcolata purché soddisfi tutte le condizioni per l'indicizzazione. In particolare, la colonna calcolata deve essere deterministica e precisa oppure deterministica e persistente. Per altre informazioni sul determinismo, 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 colonne chiave o colonne non chiave incluse purché il tipo di dati della colonna calcolata sia consentito come colonna chiave o colonna non chiave dell'indice. Ad esempio, non è possibile creare un indice XML primario per 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 in una colonna calcolata potrebbe causare l'errore di un'operazione di inserimento o aggiornamento eseguita in precedenza. Un errore di questo tipo può verificarsi quando la colonna calcolata genera un errore aritmetico.

Nella tabella seguente, ad esempio, anche se l'espressione della colonna c calcolata sembra generare un errore aritmetico quando la riga viene inserita, l'istruzione INSERT funziona.

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

Tuttavia, se si crea un indice nella colonna ccalcolata , la stessa INSERT istruzione ha 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 altre informazioni, vedere Indici per le colonne calcolate.

Colonne incluse negli 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 Query Optimizer può ottenere tutte le informazioni necessarie da un'analisi o una ricerca di indici non cluster; non è possibile accedere ai dati della tabella o dell'indice cluster. Per altre informazioni, vedere Creare indici con colonne incluse e la guida all'architettura e alla progettazione degli indici di SQL Server.

Impostazione di opzioni per gli indici

SQL Server 2005 (9.x) ha introdotto nuove opzioni di indice e modificato anche il modo in cui vengono specificate le opzioni. Nella sintassi WITH option_name compatibile con le versioni precedenti equivale a WITH (option_name = ON). Quando si impostano opzioni per gli indici, è necessario rispettare le regole seguenti:

• È possibile specificare nuove opzioni per gli indici solo usando WITH (<option_name> = <ON | OFF>).
• Non è possibile specificare opzioni usando 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 WITH (<option_name> = <ON | OFF>).

DROP_EXISTING - clausola

È possibile usare 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 PRIMARY KEY vincolo o UNIQUE e la definizione di indice non viene modificata in alcun modo, l'indice viene 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 PRIMARY KEY vincolo o UNIQUE , eliminare il vincolo e aggiungere un vincolo con la nuova definizione.

DROP_EXISTING consente un miglioramento delle prestazioni quando viene creato un indice cluster, con un set di chiavi identico oppure diverso, per una tabella che include anche indici non cluster. 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 se gli indici non cluster precedentemente allineati con l'indice cluster, potrebbero non essere più allineati con esso. Per altre informazioni sull'allineamento degli indici partizionati, vedere Tabelle e indici partizionati.

La DROP_EXISTING clausola non ordina nuovamente i dati se le stesse colonne chiave di indice vengono usate nello stesso ordine e con lo stesso ordine crescente o decrescente, a meno che l'istruzione index non specifichi un indice non cluster e che l'opzione ONLINE sia impostata su OFF. Se l'indice cluster è disabilitato, l'operazione CREATE INDEX WITH DROP_EXISTING deve essere eseguita con ONLINE impostato 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 ONLINE impostato su OFF o ON.

ONLINE - opzione

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.
• L'opzione WAIT_AT_LOW_PRIORITY dell'argomento consente di decidere come procede l'operazione sull'indice quando attende un Sch-M blocco. Per altre informazioni, vedere WAIT_AT_LOW_PRIORITY

Per altre informazioni, vedere Eseguire operazioni sugli indici online.

Operazioni sull'indice ripristinabili

Si applica a: SQL Server 2019 (15.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

È possibile rendere ripristinabile un'operazione di creazione di indici online. Ciò significa che la compilazione dell'indice può essere arrestata e riavviata in un secondo momento dal punto in cui è stata arrestata. Per eseguire una compilazione di indice come ripristinabile, specificare l'opzione RESUMABLE = ON .

Le linee guida seguenti si applicano alle operazioni di indice ripristinabili:

• Per usare l'opzione RESUMABLE è necessario usare anche l'opzione ONLINE.
• L'opzione RESUMABLE non viene salvata in modo permanente nei metadati per un determinato indice e si applica solo alla durata dell'istruzione DDL corrente. Per abilitare la funzione di ripristino, è necessario quindi che la clausola RESUMABLE = ON sia specificata in modo esplicito.
• L'opzione MAX_DURATION può essere specificata in due contesti:
  • MAX_DURATION per l'opzione RESUMABLE specifica l'intervallo di tempo per la ricompilazione di un indice. Dopo questo intervallo di tempo e, se la ricompilazione dell'indice è ancora in esecuzione, viene sospesa. Si decide quando è possibile riprendere la ricompilazione per un indice sospeso. Il tempo in minuti per MAX_DURATION deve essere maggiore di 0 minuti e minore o uguale a una settimana (7 * 24 * 60 = 10080 minuti). Una pausa prolungata in un'operazione sull'indice potrebbe influire notevolmente sulle prestazioni DML in una tabella specifica, nonché sulla capacità del disco del database, poiché sia l'indice originale che l'indice appena creato richiedono spazio su disco e devono essere aggiornati dalle operazioni DML. Se MAX_DURATION'opzione viene omessa, l'operazione sull'indice continua fino al completamento o fino a quando non si verifica un errore.
  • MAX_DURATION per l'opzione WAIT_AT_LOW_PRIORITY specifica il tempo di attesa tramite blocchi con priorità bassa se l'operazione sull'indice è bloccata, prima di eseguire un'azione. Per altre informazioni, vedere WAIT_AT_LOW_PRIORITY con operazioni sugli indici online.
• Per sospendere immediatamente l'operazione sull'indice, è possibile eseguire il comando ALTER INDEX PAUSE oppure eseguire il comando KILL <session_id>.
• L'esecuzione dell'istruzione originale CREATE INDEX con gli stessi parametri riprende un'operazione di compilazione dell'indice sospesa. È anche possibile riprendere un'operazione di compilazione dell'indice sospesa eseguendo l'istruzione ALTER INDEX RESUME .
• Il comando ABORT termina la sessione che esegue una compilazione dell'indice e annulla l'operazione sull'indice. Non è possibile riprendere un'operazione di indice interrotta.

Un'operazione di indice ripristinabile viene eseguita fino a quando non viene completata, sospesa o non riesce. Nel caso in cui l'operazione venga sospesa, viene generato un errore che indica che l'operazione è stata sospesa e che la creazione dell'indice non è stata completata. Nel caso in cui l'operazione non riesca, viene generato anche un errore.

Per verificare se un'operazione sull'indice viene eseguita come operazione ripristinabile e per controllarne lo stato di esecuzione corrente, usare la vista del catalogo sys.index_resumable_operations.

Risorse

Per le operazioni di indice ripristinabili sono necessarie le risorse seguenti:

• Spazio aggiuntivo necessario per mantenere l'indice compilato, incluso il momento in cui la compilazione viene sospesa.
• Velocità effettiva superiore del log delle transazioni nella fase di ordinamento. L'utilizzo complessivo dello spazio nel log delle transazioni è minore rispetto alla normale creazione di indici online e consente il troncamento del log durante questa operazione.
• Le istruzioni DDL che tentano di modificare la tabella associata all'indice creato mentre l'operazione sull'indice è sospesa non sono consentite.
• La pulizia fantasma è bloccata nell'indice della compilazione per la durata dell'operazione, sia in pausa che mentre l'operazione è in esecuzione.
• Se la tabella contiene colonne LOB, una compilazione di indice cluster ripristinabile richiede una modifica dello schema (Sch-M) blocco all'inizio dell'operazione.

Limitazioni funzionali attuali

Le operazioni di creazione di indici ripristinabili presentano le limitazioni seguenti:

• Dopo la sospensione di un'operazione di creazione dell'indice online ripristinabile, il valore iniziale di MAXDOP non può essere modificato.
• L'opzione SORT_IN_TEMPDB = ON non è supportata per le operazioni di indice ripristinabili.
• Il comando DDL con RESUMABLE = ON non può essere eseguito all'interno di una transazione esplicita.
• Non è possibile creare un indice ripristinabile che contiene:
  • Colonne calcolate o timestamp (rowversion) come colonne chiave.
  • Colonna LOB come colonna inclusa.
• Le operazioni di indice ripristinabili non sono supportate per:
  • Il comando ALTER INDEX REBUILD ALL
  • Il comando ALTER TABLE REBUILD
  • Indici Columnstore
  • Indici filtrati
  • Indici disabilitati

WAIT_AT_LOW_PRIORITY con operazioni sull'indice online

si applica a: SQL Server 2022 (16.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

Quando non si usa l'opzione WAIT_AT_LOW_PRIORITY , tutte le transazioni di blocco attive che contengono blocchi nella tabella o nell'indice devono essere completate affinché l'operazione di creazione dell'indice venga avviata e completata. Quando l'operazione sull'indice online viene avviata e prima del completamento, deve acquisire un blocco condiviso (S) o una modifica dello schema (Sch-M) sulla tabella e tenerla in attesa per un breve periodo di tempo. Anche se il blocco viene mantenuto solo per un breve periodo di tempo, potrebbe influire significativamente sulla velocità effettiva del carico di lavoro, aumentare la latenza delle query o causare timeout di esecuzione.

Per evitare questi problemi, l'opzione WAIT_AT_LOW_PRIORITY consente di gestire il comportamento di S o Sch-M blocchi necessari per l'avvio e il completamento di un'operazione sull'indice online, selezionando da tre opzioni. In tutti i casi, se durante il tempo di attesa specificato da MAX_DURATION = n [minutes] non esiste alcun blocco che implica l'operazione sull'indice, l'operazione sull'indice procede immediatamente.

WAIT_AT_LOW_PRIORITY fa attendere l'attesa dell'operazione sull'indice online usando blocchi con priorità bassa, consentendo ad altre operazioni di usare blocchi di priorità normali per procedere nel frattempo. L'omissione dell'opzione WAIT_AT_LOW_PRIORITY equivale a WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = tempo [MINUTES]

Tempo di attesa (valore intero specificato in minuti) che l'operazione sull'indice online attende usando blocchi con priorità bassa. Se l'operazione viene bloccata per il MAX_DURATION tempo, viene eseguita l'azione specificata ABORT_AFTER_WAIT . MAX_DURATION il tempo è sempre espresso in minuti e la parola MINUTES può essere omessa.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

• NONE: continuare ad attendere il blocco con priorità normale.
• SELF: uscire dall'operazione sull'indice online attualmente in esecuzione, senza eseguire alcuna azione. L'opzione SELF non può essere usata quando MAX_DURATION è 0.
• BLOCKERS: terminare tutte le transazioni utente che bloccano l'operazione sull'indice online in modo che l'operazione possa continuare. L'opzione BLOCKERS richiede che l'entità che esegue l'istruzione CREATE INDEX o ALTER INDEX disponga dell'autorizzazione ALTER ANY CONNECTION.

È possibile usare gli eventi estesi seguenti per monitorare le operazioni sugli indici che attendono blocchi con priorità bassa:

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Opzioni per blocchi di riga e di pagina

Se ALLOW_ROW_LOCKS = ON e ALLOW_PAGE_LOCK = ON, i blocchi a livello di riga, pagina e tabella sono consentiti quando si accede all'indice. Il 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, è consentito solo un blocco a livello di tabella quando si accede all'indice.

Chiavi sequenziali

Si applica a: SQL Server 2019 (15.x) e versioni successive, nel database SQL di Azure e in Istanza gestita di SQL di Azure.

La contesa di inserimento dell'ultima pagina è un problema di prestazioni comune che si verifica quando un numero elevato di thread simultanei tenta di inserire righe in un indice con una chiave sequenziale. Un indice viene considerato sequenziale quando la colonna chiave iniziale contiene valori che sono sempre crescenti o decrescenti, ad esempio una colonna Identity o una data che per impostazione predefinita è la data/ora corrente. Poiché le chiavi inserite sono sequenziali, tutte le nuove righe vengono inserite alla fine della struttura dell'indice, ovvero nella stessa pagina. Ciò porta a contesa per la pagina in memoria che può essere osservata come diversi thread in attesa di acquisire un latch per la pagina in questione. Il tipo di attesa corrispondente è PAGELATCH_EX.

Abilitando l'opzione per gli indici OPTIMIZE_FOR_SEQUENTIAL_KEY viene attivata un'ottimizzazione all'interno del motore di database che contribuisce a migliorare la velocità effettiva per gli inserimenti nell'indice con un elevato grado di concorrenza. Questa opzione è destinata agli indici che dispongono di una chiave sequenziale e che pertanto sono soggetti a contesa di inserimento dell'ultima pagina, ma può essere utile anche per gli indici contenenti aree sensibili in altre aree della struttura dell'indice albero B.

Compressione dei dati

Per altre informazioni sulla compressione dei dati, vedere Compressione dei dati.

Di seguito sono riportati i punti chiave da considerare nel contesto delle operazioni di compilazione degli indici quando viene usata la compressione dei dati:

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

Per valutare in che modo la modifica dello stato di compressione influisce sull'utilizzo dello spazio da una tabella, da un indice o da una partizione, utilizzare la stored procedure sp_estimate_data_compression_savings .

Compressione XML

Si applica a: SQL Server 2022 (16.x) e versioni successive Database SQL di Azure e Istanza gestita di SQL di Azure

Molte delle considerazioni sulla compressione dei dati si applicano alla compressione XML. È consigliabile tenere presenti le considerazioni seguenti:

• Quando è specificato un elenco di partizioni, la compressione XML può essere abilitata nelle singole partizioni. Se l'elenco di partizioni non è specificato, tutte le partizioni vengono impostate in modo da usare la compressione XML. Quando vengono creati una tabella o un indice, la compressione dei dati XML è disabilitata a meno che non sia specificato diversamente. Quando una tabella viene modificata, viene mantenuta la compressione esistente se non specificato diversamente.
• Se si specifica un elenco di partizioni o una partizione non compresa nell'intervallo, viene generato un errore.
• Quando un indice cluster viene creato in un heap, tale indice eredita lo stato di compressione XML, a meno che non venga specificata un'opzione di compressione alternativa.
• Per modificare l'impostazione di compressione XML di un heap, è necessario ricompilare tutti gli indici non cluster della tabella in modo che dispongano di puntatori verso i nuovi percorsi delle righe nell'heap.
• È possibile abilitare o disabilitare l'impostazione di compressione XML online oppure offline. L'abilitazione della compressione in un heap è un'operazione a thread singolo se eseguita online.
• Per determinare lo stato di compressione XML delle partizioni in una tabella partizionata, utilizzare la xml_compression colonna della vista del sys.partitions catalogo.

Statistiche sugli indici

Quando viene creato un indice rowstore, il motore di database crea anche statistiche sulle colonne chiave dell'indice. Il nome dell'oggetto statistiche nella vista del catalogo sys.stats corrisponde al nome dell'indice. Per un indice non partizionato, le statistiche vengono compilate usando un'analisi completa dei dati. Per un indice partizionato, le statistiche vengono compilate usando l'algoritmo di campionamento predefinito.

Quando viene creato un indice columnstore, il motore di database crea anche un oggetto statistiche in sys.stats . Questo oggetto statistiche non contiene dati statistici, ad esempio l'istogramma e il vettore di densità. Viene usato durante la creazione di un clone di database eseguendo lo script del database. In quel momento, i DBCC SHOW_STATISTICS comandi e UPDATE STATISTICS ... WITH STATS_STREAM vengono usati per ottenere metadati columnstore, ad esempio segmento, dizionario e dimensioni dell'archivio differenziale e aggiungerli alle statistiche sull'indice columnstore. Questi metadati vengono ottenuti in modo dinamico in fase di compilazione delle query per un database normale, ma vengono forniti dall'oggetto statistiche per un clone di database. Il comando UPDATE STATISTICS non è supportato per l'oggetto statistiche in un indice columnstore in qualsiasi altro scenario.

Autorizzazioni

È richiesta l'autorizzazione ALTER per la tabella o la vista o l'appartenenza al ruolo predefinito del db_ddladmin database.

Limitazioni e restrizioni

In Azure Synapse Analytics e nella piattaforma di strumenti analitici non è possibile creare:

• Un indice rowstore cluster o non cluster per una tabella di data warehouse se esiste già un indice columnstore. Questo comportamento è diverso rispetto a SMP SQL Server, che consente la coesistenza di indici rowstore e columnstore nella stessa tabella.
• Non è possibile creare un indice in una vista.

Metadati UFX

Per visualizzare informazioni sugli indici esistenti, è possibile eseguire una query sulla vista del catalogo sys.indexes.

Note sulla versione

• Il database SQL di Azure non supporta filegroup diversi da PRIMARY.
• Il database SQL di Azure e l'istanza gestita di SQL di Azure non supportano le opzioni FILESTREAM.
• Gli indici columnstore non sono disponibili prima di SQL Server 2012 (11.x).
• Le operazioni sugli indici ripristinabili sono disponibili a partire da SQL Server 2017 (14.x), nel database SQL di Azure e in Istanza gestita di SQL di Azure.

Esempi: tutte le versioni. Usa il database AdventureWorks.

R. Creare un indice rowstore non cluster semplice

Negli esempi che seguono viene creato un indice non cluster per la colonna VendorID della tabella Purchasing.ProductVendor.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B. Creare un indice rowstore composto non cluster semplice

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

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. Creare un indice per una tabella in un altro database

Nell'esempio seguente viene creato un indice cluster per la colonna VendorID della tabella ProductVendor nel database Purchasing.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D. Aggiungere una colonna a un indice

Nell'esempio seguente viene creato l'indice IX_FF con due colonne della tabella dbo.FactFinance. L'istruzione successiva ricompila l'indice con un'ulteriore colonna e mantiene il nome esistente.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

Esempi: SQL Server, database SQL di Azure

E. Creare un indice non cluster univoco

Nell'esempio seguente viene creato un indice non cluster univoco sulla colonna Name della tabella Production.UnitMeasure nel database AdventureWorks2022. Questo indice impone l'univocità dei dati inseriti nella colonna Name.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

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.

F. Uso 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.

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.

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.

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

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

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

H. Creare un indice per una vista

Nell'esempio seguente vengono creati una vista e un indice per tale vista, quindi vengono eseguite due query in cui viene usata la vista indicizzata.

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

I. Creare 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 Visualizza piano di esecuzione effettivo prima di eseguire la query.

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

J. Creare un indice partizionato

Nell'esempio seguente viene creato un indice partizionato non cluster nello schema di partizione esistente TransactionsPS1 nel database AdventureWorks2022. In questo esempio si presuppone che sia stato installato l'esempio di indice partizionato.

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K. Creazione di un indice filtrato

Nell'esempio seguente viene creato un indice filtrato nella tabella Production.BillOfMaterials nel database AdventureWorks2022. 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.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

.L Creare 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

M. Creare un indice con compressione XML

Si applica a: SQL Server 2022 (16.x) e versioni successive Database SQL di Azure e Istanza gestita di SQL di Azure

Nell'esempio seguente viene creato un indice in una tabella non partizionata usando la compressione XML. Almeno una colonna nell'indice deve essere il tipo di dati xml.

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

Nell'esempio seguente viene creato un indice in una tabella partizionata usando la compressione XML in tutte le partizioni dell'indice.

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

N. Creare, riprendere, sospendere e interrompere operazioni sull'indice ripristinabili

Si applica a: SQL Server 2019 (15.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

.O CREATE INDEX con diverse opzioni di blocco con priorità bassa

Gli esempi seguenti usano l'opzione WAIT_AT_LOW_PRIORITY per specificare diverse strategie per la gestione del blocco.

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

L'esempio seguente usa l'opzione RESUMABLE e specifica due valori MAX_DURATION, il primo applicabile all'opzione ABORT_AFTER_WAIT, il secondo all'opzione RESUMABLE.

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)

P. Sintassi di base

Creare, riprendere, sospendere e interrompere operazioni sull'indice ripristinabili

Si applica a: SQL Server 2019 (15.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

D. Creare un indice non cluster in una tabella nel database corrente

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

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

.R Creare un indice cluster per una tabella in un altro database

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

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S. Creare un indice cluster ordinato in una tabella

L'esempio seguente crea un indice cluster ordinato nelle colonne c1 e c2 della tabella T1 nel database MyDB.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

T. Convertire un indice columnstore cluster in un indice cluster ordinato in una tabella

L'esempio seguente converte l'indice columnstore cluster esistente in un indice columnstore cluster ordinato denominato MyOrderedCCI nelle colonne c1 e c2 della tabella T2 del database MyDB.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

Contenuto correlato

• Architettura e guida per la progettazione degli indici di SQL Server
• Eseguire operazioni online sugli indici
• Indici e ALTER TABLE
• ALTER INDEX
• CREATE PARTITION FUNCTION
• CREATE PARTITION SCHEME
• CREATE SPATIAL INDEX
• CREATE STATISTICS
• CREATE TABLE
• CREATE XML INDEX
• Tipi di dati
• DBCC SHOW_STATISTICS
• DROP INDEX
• Indici XML (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_indexes
• EVENTDATA