CREATE INDEX (Transact-SQL)

Si applica a:yes SQL Server (tutte le versioni supportate) YesDatabase YesSQL di Azure Istanza yesgestita di SQL di Azure Azure Synapse Analytics Analytics yesPlatform System (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.

Nota

La documentazione di SQL Server usa in genere il termine albero B in riferimento agli indici. Negli indici rowstore, SQL Server implementa un albero B+. Ciò non si applica agli indici columnstore o agli archivi dati in memoria. Per informazioni dettagliate, vedere Architettura e progettazione degli indici di SQL Server .

Azure Synapse Analytics e il sistema di piattaforma di analisi (PDW) attualmente non supportano vincoli univoci. Tutti gli esempi che fanno riferimento a vincoli univoci sono applicabili solo a SQL Server e al database SQL.

Per informazioni sulle linee guida di progettazione degli indici, vedere Guida per la progettazione di indici di SQL Server.

Esempi:

  1. Creare un indice non cluster in una tabella o 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) e dal database SQL, è 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:

Topic link iconConvenzioni della sintassi Transact-SQL

Sintassi

Sintassi per SQL Server e per il database 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

Importante

La struttura della sintassi dell'indice relazionale compatibile con le versioni precedenti verrà rimossa in una versione futura di SQL Server. Evitare di utilizzare questa struttura della sintassi e pianificare la modifica delle applicazioni che ne fanno uso. Usare invece la struttura della sintassi specificata in <relational_index_option> .

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 } )
[;]

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 e versioni precedenti, vedere Documentazione delle versioni precedenti.

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.

Il motore di database non consente di creare 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. 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 altre informazioni, vedere Creare 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 CLUSTERED non viene specificato, viene creato un indice non cluster.

Nota

Poiché il livello foglia di un indice cluster e le pagine di dati sono uguali per definizione, la creazione di un indice cluster e l'uso della ON partition_scheme_name clausola or ON filegroup_name sposta effettivamente una tabella dal filegroup in cui è stata creata la tabella nel nuovo schema di partizione o nel 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 altre informazioni, vedere Abilitare indici e vincoli e Disabilitare 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.

Ogni tabella può avere fino a 999 indici non cluster, indipendentemente dalla modalità di creazione degli indici: in modo implicito con vincoli PRIMARY KEY 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 diversamente specificato, il tipo di indice predefinito è non cluster.

index_name

Nome dell'indice. I nomi degli indici devono essere univoci all'interno di una tabella o di una vista, ma non devono essere univoci all'interno di un database. Devono essere anche conformi alle regole degli identificatori.

column

Colonna o colonne su cui si basa 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 al database SQL e a SQL Server 2016 (13.x).

Le colonne che sono dei tipi di dati LOB (Large Object) ntext, text, varchar(max), nvarchar(max), varbinary(max), xml o image non possono essere specificate come colonne chiave per un indice. Inoltre, una definizione di visualizzazione non può includere colonne 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. È anche possibile creare indici su colonne calcolate definite come chiamate al metodo da una colonna di tipo definita dall'utente, purché i metodi siano contrassegnati come deterministici e non eseguano operazioni di accesso ai dati. Per altre 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 usati contemporaneamente come colonne chiave e non chiave. Gli indici non cluster contengono sempre 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) e dal database 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 in colonne non chiave 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 UDT, a una colonna del tipo di dati spaziale o a una colonna di tipo di dati hierarchyID. I confronti che usano NULL valori letterali non sono consentiti con gli operatori di confronto. Usare invece gli IS NULL operatori 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 agli indici 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 eseguendo CREATE PARTITION SCHEME o ALTER PARTITION SCHEME. column_name specifica la colonna in base alla quale verrà eseguita la partizione di un indice partizionato. 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 della tabella di base tranne quando si esegue la partizione di un indice UNIQUE. In questo caso il valore column_name deve essere scelto tra quelli usati come chiave univoca. Questa restrizione consente al motore di database di verificare l'univocità dei valori chiave all'interno di una singola partizione.

Nota

Quando si partiziona un indice non univoco, cluster, il motore di database aggiunge per impostazione predefinita la colonna di partizionamento all'elenco di chiavi di indice cluster, se non è già specificato. Quando si partiziona un indice non univoco e non cluster, il motore di database aggiunge la colonna di partizionamento come colonna non chiave (inclusa) dell'indice, se non è già specificata.

Se partition_scheme_name o filegroup non è specificato e la tabella viene partizionata, l'indice viene inserito nello stesso schema di partizione usando la stessa colonna di partizionamento, come la 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 altre informazioni sul partizionamento degli indici, vedere Tabelle e indici partizionati.

ON filegroup_name

Crea l'indice specificato nel filegroup specificato. Se non viene specificata alcuna posizione e la tabella o la visualizzazione non viene partizionata, l'indice usa lo stesso filegroup della tabella o della vista sottostante. Il filegroup deve essere già esistente.

ON "default"

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

Il termine predefinito, in questo contesto, non è una parola chiave. Si tratta di un identificatore del filegroup predefinito e deve essere delimitato, ad esempio ON "default" o ON [default]. Se si specifica "default", l'opzione QUOTED_IDENTIFIER deve essere impostata su ON per la sessione corrente. Si tratta dell'impostazione predefinita. Per altre informazioni, vedere SET QUOTED_IDENTIFIER.

Nota

"default" non indica il filegroup predefinito del database nel contesto di CREATE INDEX. Ciò è diverso da CREATE TABLE, dove "default" individua la tabella nel filegroup predefinito del database.

[ 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 FILESTREAM_ON clausola consente lo spostamento dei dati FILESTREAM in un filegroup o uno schema di partizione FILESTREAM diverso.

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.

FILESTREAM_ON NULL può essere specificato in un'istruzione se viene creato un CREATE INDEX indice cluster e la tabella non contiene una colonna FILESTREAM.

Per altre informazioni, vedere FILESTREAM (SQL Server).

<object>::=

Oggetto completo o non qualificato 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 da 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.

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 a tre parti database_name. [schema_name]. object_name quando l'database_name è il database corrente o il database_name è tempdb e il object_name inizia con #.

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

ON
La percentuale di spazio disponibile specificata da 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 è utile solo quando FILLFACTOR viene specificato, perché PAD_INDEX usa la percentuale specificata da FILLFACTOR. Se la percentuale specificata per FILLFACTOR non è sufficiente per consentire una riga, il motore di database esegue internamente l'override della percentuale per consentire il minimo. Il numero di righe di una pagina intermedia dell'indice non è mai minore di due, indipendentemente dal valore 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 per 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 per capacità.

L'impostazione FILLFACTOR si applica solo quando l'indice viene creato o ricompilato. Il motore di database non mantiene dinamicamente la percentuale specificata di spazio vuoto nelle pagine.

Per visualizzare l'impostazione del fattore di riempimento, usare fill_factor in sys.indexes.

Importante

La creazione di un indice cluster con meno FILLFACTOR di 100 influisce sulla quantità di spazio di archiviazione occupata dai dati perché il motore di database ridistribuisce i dati quando crea l'indice cluster.

Per altre 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 è DISATTIVATo , ad eccezione di Hyperscale del database SQL di Azure. Per tutte le operazioni di compilazione degli indici in Hyperscale, è sempre ON, SORT_IN_TEMPDB indipendentemente dall'opzione specificata a meno che non venga usata la ricompilazione dell'indice ripristinabile.

ON
I risultati intermedi dell'ordinamento usati per la compilazione dell'indice vengono archiviati in tempdb. In questo modo si può ridurre il tempo necessario per creare un indice se tempdb si trova in un set di dischi diverso rispetto al database utente. 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 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 si applica solo alle operazioni di inserimento 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.

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 IGNORE_DUP_KEY, usare 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 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 su STATISTICS_NORECOMPUTE OFF o eseguire UPDATE STATISTICS senza la NORECOMPUTE clausola .

Importante

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.

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 (a partire da SQL Server 2014 (12.x)) e database SQL di Azure

Se è specificato ON, le statistiche create sono statistiche per 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 per i seguenti tipi di statistiche:

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

ON
Specifica in DROP e REBUILD nell'indice esistente, che deve avere lo stesso nome del parametro index_name.

OFF
Specifica di non specificare DROP e REBUILD l'indice esistente. 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_EXISTINGnon è 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.

Importante

Le operazioni sugli indici online non sono disponibili in ogni edizione di MicrosoftSQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere Edizioni e funzionalità supportate per SQL Server 2016.

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 di tempo, viene acquisito un blocco S (Condiviso) nell'origine se viene creato un indice non cluster. Un blocco Sch-M (modifica dello schema) viene acquisito quando un indice cluster viene creato o eliminato online e quando viene ricompilato un indice cluster o non cluster. L'opzione ONLINE non può essere impostata su ON quando viene creato un indice per una tabella temporanea locale.

Nota

La creazione di indici online può impostare le low_priority_lock_wait opzioni, 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 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. Tale blocco impedisce l'aggiornamento della tabella sottostante ma consente operazioni di lettura, ad esempio l'esecuzione di istruzioni SELECT.

Per altre informazioni, vedere Perform Index Operations 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
  • 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)) e nel database SQL di Azure, quando una tabella contiene colonne varchar(max) o varbinary(max), è possibile creare o ricompilare un indice cluster contenente altre colonne usando l'opzione ONLINE .

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

RESUMABLE = { ON | OFF }

Si applica a: SQL Server (a partire da SQL Server 2019 (15.x)) e database SQL di Azure

Specifica se un'operazione sull'indice online è ripristinabile.

ON
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 (a partire da SQL Server 2019 (15.x)) e database SQL di Azure

Indica il tempo (un valore intero specificato in minuti) di esecuzione di un'operazione sull'indice online ripristinabile prima di essere sospesa.

Importante

Per informazioni più dettagliate sulle operazioni di indice eseguibili online, vedere Linee guida per operazioni di indice online.

Nota

Negli indici columnstore non sono supportate le ricompilazioni degli indici online ripristinabili.

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. Il motore di database determina quando vengono utilizzati 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 vengono utilizzati blocchi di pagina.

OFF
I blocchi a livello di pagina non vengono utilizzati.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

Si applica a: SQL Server (a partire da SQL Server 2019 (15.x)) e database SQL di Azure

Specifica se eseguire o meno l'ottimizzazione per la contesa di inserimento dell'ultima pagina. Il valore predefinito è OFF. Per altre informazioni, vedere le sezione Chiavi sequenziali.

MAXDOP = max_degree_of_parallelism

Sostituisce l'opzione di configurazione max degree of parallelism per la durata dell'operazione sull'indice. Per altre informazioni, vedere Configurare l'opzione di configurazione del server max degree of parallelism. Utilizzare MAXDOP per limitare il numero di processori utilizzati durante l'esecuzione di un piano parallelo. Il valore massimo è 64 processori.

max_degree_of_parallelism può essere:

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 altre informazioni, vedere Configurazione di operazioni parallele sugli indici.

Nota

Le operazioni sugli indici paralleli non sono disponibili in ogni edizione di MicrosoftSQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere Edizioni e funzionalità supportate per SQL Server 2016 ed edizioni e funzionalità supportate per SQL Server 2017.

DATA_COMPRESSION

Specifica l'opzione di compressione dei dati per l'indice, il numero di partizione o l'intervallo di partizioni specificato. descritte di seguito:

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 altre informazioni sulla compressione, vedere Compressione dei dati.

XML_COMPRESSION

Si applica a: ANTEPRIMA di SQL Server 2022 (16.x) e versioni successive e anteprima del database SQL di Azure.

Specifica l'opzione di compressione XML per l'indice specificato che contiene una o più colonne del tipo di dati xml . descritte di seguito:

ON
Gli indici o le partizioni specificate vengono compressi utilizzando la compressione XML.

OFF
L'indice o le partizioni specificate non vengono compressi.

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

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

Nota

XML_COMPRESSION è disponibile solo a partire dall'anteprima di SQL Server 2022 (16.x) e dall'anteprima del database SQL di Azure.

<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 che 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)
);

È anche possibile specificare l'opzione XML_COMPRESSION più volte, 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)
);

Commenti

L'istruzione CREATE INDEX è 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 CREATE INDEX multiprocessore possono usare più processori per eseguire le operazioni di analisi e ordinamento associate alla creazione dell'indice, allo stesso modo delle altre query. Per altre informazioni, vedere Configurazione di operazioni parallele sugli indici.

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

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

Un indice cluster può essere compilato in base a una variabile di tabella quando viene creata una chiave primaria. Quando la query viene completata o la sessione termina, l'indice viene eliminato.

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

Indici non cluster

A partire da SQL Server 2016 (13.x) e nel database SQL di Azure, è possibile creare un indice non cluster in una tabella archiviata come indice columnstore cluster. Se si crea prima un indice non cluster per una tabella archiviata come heap o indice cluster, l'indice verrà conservato se in un secondo tempo la tabella viene convertita in un indice columnstore cluster. Non è inoltre necessario eliminare l'indice non cluster quando si ricompila l'indice columnstore cluster.

Limitazioni e restrizioni:

  • L'opzione FILESTREAM_ON non è valida quando si crea un indice non cluster in 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 dalle operazioni di inserimento. Viene eseguito il rollback delle operazioni di inserimento che generano valori di chiave duplicati e il motore di database visualizza 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 è presente un indice univoco e la IGNORE_DUP_KEY clausola è impostata su ON, solo le righe che violano l'indice UNIQUE hanno esito negativo.

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 avere un indice partizionato in una tabella che non è partizionato ed è possibile disporre di un indice non partizionato in una tabella partizionata.

Se si crea un indice in una tabella partizionata e non si specifica un filegroup in cui inserire l'indice, l'indice viene partizionato nello stesso modo della 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.

Avviso

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 il motore di database aggiunge tutte le colonne di partizionamento all'elenco di chiavi di indice cluster, se non è già specificato.

È 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 Architettura e guida per la progettazione degli indici di SQL Server.

In SQL Server le statistiche non vengono create analizzando tutte le righe della tabella quando 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, 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 opzioni SET nella colonna Valore obbligatorio sono richieste ogni volta che si verifica una qualsiasi 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 usato da Query Optimizer per generare il piano di query.

    Opzioni SET Valore richiesto Valore server predefinito Predefinito

    OLE DB e ODBC predefinito
    Predefinito

    DB-Library predefinito
    ANSI_NULLS ON ON ON OFF
    ANSI_PADDING ON ON ON OFF
    ANSI_WARNINGS* ON ON ON OFF
    ARITHABORT ON ON OFF OFF
    CONCAT_NULL_YIELDS_NULL ON ON ON OFF
    NUMERIC_ROUNDABORT OFF OFF OFF OFF
    QUOTED_IDENTIFIER ON ON ON OFF
    • 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.
  • Il motore di database genera un errore ed esegue il rollback di istruzioni INSERT, UPDATE, DELETE o MERGE che modificano i dati nell'indice.
  • Query Optimizer non considera l'indice nel piano di esecuzione per le istruzioni Transact-SQL.

Per altre informazioni sugli indici filtrati, vedere Creare indici filtrati e Architettura e guida per la progettazione degli indici di SQL Server.

Indici spaziali

Per informazioni sugli indici spaziali, vedere CREATE SPATIAL INDEX e Panoramica degli indici spaziali.

Indici XML

Per informazioni sugli indici XML, vedere CREATE XML INDEX and XML Indexes (SQL Server).

Dimensioni 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 del database SQL e di SQL Server 2016 (13.x) il limite era sempre di 900 byte. È possibile creare indici in colonne varchar che superano il limite di byte se i dati esistenti nelle colonne non superano il limite al momento della creazione dell'indice; Tuttavia, le azioni di inserimento o aggiornamento successive sulle colonne che causano l'esito negativo delle dimensioni totali superano il limite. La chiave di indice di un indice cluster non può contenere colonne 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 delle righe avranno esito negativo.

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 delle dimensioni della chiave di indice. Per altre informazioni, vedere Creare indici con colonne incluse e Architettura e guida per la progettazione degli indici di SQL Server.

Nota

Quando le tabelle vengono partizionate, se le colonne chiave di partizionamento non sono già presenti in un indice cluster non univoco, vengono aggiunte all'indice dal motore di database. 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. Ciò significa che il motore di database archivia i valori calcolati nella tabella e li aggiorna quando vengono aggiornate tutte le altre colonne da cui dipende la colonna calcolata. Il motore di database usa questi valori persistenti quando crea un indice nella colonna e quando viene fatto riferimento all'indice in una query.

Per indicizzare una colonna calcolata, la colonna calcolata deve essere 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 di tipo CLR definiti dall'utente contrassegnati come deterministici dall'utente.
  • Colonne calcolate basate su espressioni deterministiche definite dal motore di database, ma imprecise.

Le colonne calcolate persistenti richiedono l'impostazione delle seguenti opzioni SET come mostrato nella sezione precedente Opzioni SET necessarie per gli indici filtrati.

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 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 in 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 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. Ciò consente all'utilità di ottimizzazione query di individuare tutte le informazioni necessarie da un'analisi dell'indice; i dati dell'indice cluster o della tabella non sono accessibili. Per altre informazioni, vedere Creare indici con colonne incluse e Architettura e guida per la progettazione degli indici di SQL Server.

Specifica delle opzioni di indice

SQL Server 2005 (9.x) ha introdotto nuove opzioni di indice e modifica anche il modo in cui sono specificate le opzioni. Nella 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:

  • È possibile specificare nuove opzioni di indice solo usando WITH (<option_name> = <ON | OFF>).
  • Le opzioni non possono essere specificate usando sia la sintassi compatibile con le versioni precedenti che la nuova sintassi nella stessa istruzione. Ad esempio, specificando WITH (DROP_EXISTING, ONLINE = ON) l'istruzione ha esito negativo.
  • Quando si crea un indice XML, è necessario specificare le opzioni usando WITH (<option_name> = <ON | OFF>).

DROP_EXISTING - clausola

È possibile usare la DROP_EXISTING clausola per ricompilare l'indice, aggiungere o eliminare colonne, modificare le opzioni, modificare l'ordine di ordinamento delle colonne o modificare lo schema di partizione o il filegroup.

Se l'indice applica un vincolo PRIMARY KEY 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 vincolo PRIMARY KEY o UNIQUE, eliminare il vincolo e aggiungere un vincolo con la nuova definizione.

DROP_EXISTING migliora le prestazioni quando si crea nuovamente un indice cluster, con lo stesso set o diverso di chiavi, in una tabella con indici non cluster. DROP_EXISTING sostituisce l'esecuzione di un'istruzione nell'indice cluster precedente seguito dall'esecuzione di un'istruzione DROP INDEXCREATE 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 DROP_EXISTING clausola non ricompila gli indici non cluster quando la definizione dell'indice ha lo stesso nome di indice, colonne chiave e partizione, attributo di univocità e ordinamento come indice originale.

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 altre informazioni sull'allineamento degli indici partizionati, vedere Tabelle e indici partizionati.

La DROP_EXISTING clausola non ordina di nuovo 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 indice non specifica un indice non cluster e l'opzione ONLINE è impostata su OFF. Se l'indice cluster è disabilitato, l'operazione CREATE INDEX WITH DROP_EXISTING deve essere eseguita con 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 ONLINE impostata su OFF o ON.

Nota

Quando gli indici con 128 extent o più vengono eliminati o ricompilati, il motore di database deferisce le deallozioni di pagina effettive e i relativi blocchi associati, fino a quando non viene eseguito il commit della transazione.

ONLINE - opzione

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

  • La tabella sottostante non può essere modificata, troncata o eliminata mentre un'operazione di indice online è in corso.
  • 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 low_priority_lock_wait dell'argomento consente di decidere in che modo l'operazione di indice può procedere quando viene bloccata nel blocco Sch-M. Questa funzionalità è attualmente supportata solo nel database SQL di Azure e nell'istanza gestita di SQL di Azure.

Per altre informazioni, vedere Perform Index Operations Online.

Risorse

Sono necessarie le risorse seguenti per un'operazione di creazione dell'indice online ripristinabile:

  • Uno spazio aggiuntivo necessario per mantenere l'indice compilato, incluso il tempo di sospensione dell'indice
  • 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.
  • Uno stato DDL per impedire eventuali modifiche DDL
  • La pulizia fantasma è bloccata nell'indice della compilazione per la durata dell'operazione, sia in pausa che mentre l'operazione è in esecuzione.

Limitazioni funzionali attuali

Le funzionalità seguenti sono disabilitate per le operazioni di creazione dell'indice ripristinabili:

  • Dopo aver sospeso un'operazione di creazione dell'indice online ripristinabile, il valore iniziale di MAXDOP non può essere modificato

  • Creare un indice che contenga:

    • Colonne calcolate o colonne TIMESTAMP come colonne chiave
    • Colonna LOB come colonna inclusa per la creazione indice ripristinabile
    • Indice filtrato

Operazioni sull'indice ripristinabili

Si applica a: SQL Server (a partire da SQL Server 2019 (15.x)) e database SQL di Azure

Per l'esecuzione di operazioni sull'indice ripristinabili, è necessario attenersi alle indicazioni seguenti:

  • La creazione dell'indice online viene specificata come ripristinabile usando l'opzione RESUMABLE = ON.
  • L'opzione RESUMABLE non viene mantenuta nei metadati per un determinato indice e si applica solo alla durata di un'istruzione DDL corrente. Per abilitare la funzione di ripristino, è necessario quindi che la clausola RESUMABLE = ON sia specificata in modo esplicito.
  • MAX_DURATION l'opzione è supportata solo per RESUMABLE = ON l'opzione.
  • MAX_DURATION per l'opzione RESUMABLE specifica l'intervallo di tempo per la compilazione di un indice. Trascorso questo tempo, la compilazione dell'indice viene sospesa oppure viene completata. È l'utente a decidere quando riprendere la compilazione di un indice che è stata sospesa. 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 sospensione prolungata di un'operazione sull'indice può compromettere le prestazioni DML su una tabella specifica, nonché la capacità del disco del database poiché entrambi gli indici, quello originale e quello appena creato, richiedono spazio su disco e devono essere aggiornati durante le operazioni DML. Se MAX_DURATION l'opzione viene omessa, l'operazione di indice continuerà fino al completamento o fino a quando non si verifica un errore.
  • Per sospendere immediatamente l'operazione sull'indice, è possibile arrestare il comando in corso (CTRL+C) oppure eseguire il comando ALTER INDEX PAUSE o il comando KILL <session_id>. Quando il comando viene sospeso, è possibile riprenderlo usando il comando ALTER INDEX.
  • Eseguire nuovamente l'istruzione originale CREATE INDEX per l'indice ripristinabile, riprende automaticamente un'operazione di creazione dell'indice sospesa.
  • L'opzione SORT_IN_TEMPDB = ON non è supportata per l'indice ripristinabile.
  • Il comando DDL con RESUMABLE = ON non può essere eseguito all'interno di una transazione esplicita (non può far parte del blocco di inizio TRAN ... COMMIT ).
  • Per riprendere/interrompere la creazione o la ricompilazione di un indice, usare la sintassi T-SQL ALTER INDEX

Nota

Il comando DDL viene eseguito fin tanto che è completato, sospeso o non riuscito. Nel caso in cui il comando sia sospeso, verrà generato un errore a indicare che l'operazione è stata sospesa e che la creazione dell'indice non è stata completata. Altre informazioni sullo stato corrente dell'indice sono disponibili in sys.index_resumable_operations. Come in precedenza, in caso di errore verrà generato un errore.

Per indicare che la creazione di un indice viene eseguita come operazione ripristinabile e per verificarne lo stato di esecuzione corrente, vedere index_resumable_operations.

WAIT_AT_LOW_PRIORITY con operazioni sull'indice online

Si applica a: questa sintassi per CREATE INDEX attualmente si applica solo al database SQL di Azure e all'istanza gestita di SQL di Azure. Per ALTER INDEX, questa sintassi si applica a SQL Server (a partire da SQL Server 2014 (12.x)) e al database SQL di Azure. Per altre informazioni, vedere ALTER INDEX.

La low_priority_lock_wait sintassi consente di specificare WAIT_AT_LOW_PRIORITY il comportamento. WAIT_AT_LOW_PRIORITY può essere usato solo con ONLINE=ON .

L'opzione WAIT_AT_LOW_PRIORITY consente ai database di gestire i blocchi Sch-S e Sch-M necessari per la creazione di indici online e consente loro di selezionare una delle 3 opzioni. In tutti i 3 casi, se durante il tempo MAX_DURATION = n [minutes]di attesa , non sono presenti attività di blocco, la ricompilazione dell'indice online viene eseguita immediatamente senza attendere e l'istruzione DDL viene completata.

WAIT_AT_LOW_PRIORITY indica che l'operazione di creazione dell'indice online attenderà blocchi con priorità bassa, consentendo ad altre operazioni di procedere mentre l'operazione di compilazione dell'indice online è in attesa. L'omettere l'opzione WAIT AT LOW PRIORITY equivale a WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES]

Tempo di attesa (valore intero specificato in minuti) che l'indice online crea blocchi attenderà con priorità bassa durante l'esecuzione del comando DDL. Se l'operazione viene bloccata per il MAX_DURATION tempo, verrà eseguita l'azione specificata ABORT_AFTER_WAIT . MAX_DURATION il tempo è sempre in minuti e la parola MINUTES può essere omessa.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

NONE: continua ad attendere il blocco con priorità normale (regolare).

SELF Uscire dall'indice online per creare un'operazione DDL attualmente in esecuzione, senza eseguire alcuna azione. L'opzione SELF non può essere usata con un MAX_DURATION valore pari a 0.

BLOCKERS Termina tutte le transazioni utente che bloccano l'operazione DDL di ricompilazione dell'indice online in modo che l'operazione possa continuare. L'opzione BLOCKERS richiede che l'account di accesso disponga ALTER ANY CONNECTION dell'autorizzazione.

Opzioni di 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ò inoltrare il 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 (a partire da SQL Server 2019 (15.x)) e database 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 da inserire sono sequenziali, tutte le nuove righe verranno inserite alla fine della struttura dell'indice (in altre parole, nella stessa pagina). Questo comporta una contesa per la pagina in memoria, che si manifesta come diversi thread in attesa di PAGELATCH_EX per la pagina in questione.

L'abilitazione dell'opzione OPTIMIZE_FOR_SEQUENTIAL_KEY di indice abilita un'ottimizzazione all'interno del motore di database che consente di migliorare la velocità effettiva per gli inserimenti con concorrenza elevata nell'indice. 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.

Nota

La documentazione di SQL Server usa in genere il termine albero B in riferimento agli indici. Negli indici rowstore, SQL Server implementa un albero B+. Ciò non si applica agli indici columnstore o agli archivi dati in memoria. Per informazioni dettagliate, vedere Architettura e progettazione degli indici di SQL Server .

Visualizzazione delle informazioni sull'indice

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 l'archiviazione di più righe in una pagina, ma non modifica le dimensioni massime delle righe.
  • 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 ha una singola impostazione di compressione 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 ALTER INDEX <index> ... REBUILD PARTITION ... sintassi ricompila la partizione specificata dell'indice.
  • La ALTER INDEX <index> ... REBUILD WITH ... sintassi ricompila 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, usare la stored procedure sp_estimate_data_compression_savings .

Compressione XML

Si applica a: ANTEPRIMA di SQL Server 2022 (16.x) e versioni successive e anteprima del database SQL di Azure.

Molte delle stesse considerazioni per la compressione dei dati si applicano alla compressione XML. Tenere presenti anche le considerazioni seguenti:

  • Quando viene specificato un elenco di partizioni, è possibile abilitare la compressione XML in singole partizioni. Se l'elenco di partizioni non viene specificato, tutte le partizioni vengono impostate per l'uso della compressione XML. Quando viene creata una tabella o un indice, la compressione dei dati XML viene disabilitata se non diversamente specificato. 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 viene creato un indice cluster in un heap, l'indice cluster eredita lo stato di compressione XML dell'heap, a meno che non venga specificata un'opzione di compressione alternativa.
  • La modifica dell'impostazione di compressione XML di un heap richiede la ricompilazione di tutti gli indici non cluster nella tabella in modo che dispongano di puntatori alle nuove posizioni delle righe nell'heap.
  • È possibile abilitare o disabilitare la compressione XML online o 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, eseguire una query sulla xml_compression colonna della vista del sys.partitions catalogo.

Autorizzazioni

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

Limitazioni e restrizioni

In Azure Synapse Analytics and Analytics Platform System (PDW) 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 da SMP SQL Server che consente la coesistenza di indici rowstore e columnstore nella stessa tabella.
  • Non è possibile creare un indice in una vista.

Metadati

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

Note sulla versione

Il database SQL non supporta le opzioni filegroup e filestream.

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 AdventureWorks2012. 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 AdventureWorks2012 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 dall'utilità di ottimizzazione query, nel menu Query in 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 AdventureWorks2012. 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 AdventureWorks2012. 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: anteprima di SQL Server 2022 (16.x) e versioni successive e anteprima del database 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 (a partire da SQL Server 2019 (15.x)) e database 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 sia l'opzione RESUMABLE che specifica due MAX_DURATION valori, il primo si applica all'opzione, il secondo si applica all'opzione ABORT_AFTER_WAITRESUMABLE .

--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 Analytics Platform System (PDW)

P. Sintassi di base

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

Si applica a: SQL Server (a partire da SQL Server 2019 (15.x)) e database 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;

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

Nell'esempio seguente viene convertito l'indice columnstore cluster esistente in un indice columnstore cluster ordinato chiamato MyOrderedCCI sulle c1 colonne e c2 della T2 tabella nel MyDB database.

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

Vedere anche