CREATE XML INDEX (Transact-SQL)

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure

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

Convenzioni di sintassi Transact-SQL

Sintassi

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

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

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

Argomenti

[PRIMARY] XML

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

• È necessario che esista un indice cluster sulla chiave primaria della tabella utente.

• La chiave di clustering della tabella utente può includere al massimo 15 colonne.

• Per ogni colonna xml di una tabella è possibile creare un indice XML primario e più indici XML secondari.

• Prima di poter creare un indice XML secondario su una colonna xml, è necessario che per tale colonna esista un indice XML primario.

• È possibile creare un indice XML solo su una singola colonna xml. Non è possibile creare un indice XML su una colonna non xml né creare un indice relazionale su una colonna xml.

• Non è possibile creare un indice XML, primario o secondario, su una colonna xml di una vista, su una variabile con valori di tabella con colonne xml oppure su variabili di tipo xml.

• Non è possibile creare un indice XML primario su una colonna xml calcolata.

• Le impostazioni delle opzioni SET devono corrispondere a quelle necessarie per le viste indicizzate e gli indici su colonne calcolate. In particolare, l'opzione ARITHABORT deve essere impostata su ON quando viene creato un indice XML e quando vengono inseriti, eliminati o aggiornati valori nella colonna xml.

Per altre informazioni, vedere Indici XML (SQL Server).

index_name

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

I nomi di indici XML primari non possono iniziare con i caratteri seguenti: #, ##, @ o @@.

xml_column_name

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

USING XML INDEX xml_index_name

Specifica l'indice XML primario da usare per la creazione di un indice XML secondario.

FOR { VALUE | PATH | PROPERTY }

Specifica il tipo di indice XML secondario.

VALORE
Crea un indice XML secondario su colonne con le colonne chiave (valore di nodo e percorso) dell'indice XML primario.

PATH
Crea un indice XML secondario su colonne compilate in base ai valori di percorso e i valori di nodo dell'indice XML primario. Nell'indice secondario PATH i valori di nodo e di percorso sono colonne chiave che consentono di eseguire le ricerche dei percorsi in modo più efficiente.

PROPERTY
Crea un indice XML secondario su colonne (valore di chiave primaria, percorso e nodo) dell'indice XML primario, dove il valore di chiave primaria è la chiave primaria della tabella di base.

<object>::=

Oggetto con nome completo o non completo da indicizzare.

database_name
Nome del database.

schema_name
Nome del processo a cui appartiene la tabella.

table_name
Nome della tabella da indicizzare.

<xml_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 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 risulta utile solo quando si specifica FILLFACTOR, in quanto PAD_INDEX usano la percentuale specificata in FILLFACTOR. Se la percentuale specificata in FILLFACTOR non consente l'inserimento di una riga, il motore di database sostituisce internamente tale percentuale in modo da rendere disponibile lo spazio minimo necessario. Il numero di righe di una pagina intermedia dell'indice non è mai minore di due, indipendentemente dal valore di fillfactor.

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. fillfactor deve essere un valore intero compreso tra 1 e 100. Il valore predefinito è 0. Se fillfactor è 100 o 0, il motore di database crea indici con pagine foglia riempite fino alla capacità massima.

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

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

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

IGNORE_DUP_KEY = OFF

Non ha effetto per gli indici XML perché il tipo di indice non è mai univoco. Non impostare questa opzione su ON. In caso contrario, viene generato un errore.

DROP_EXISTING = { ON | OFF }

Specifica che è necessario eliminare e quindi ricompilare l'indice XML denominato preesistente. Il valore predefinito è OFF.

In...
L'indice esistente deve essere eliminato e ricompilato. Il nome di indice specificato deve corrispondere a quello dell'indice esistente, mentre la definizione dell'indice può essere modificata. È possibile, ad esempio, specificare valori diversi per le colonne, il tipo di ordinamento, lo schema di partizione o le opzioni dell'indice.

OFF
Se il nome di indice specificato esiste già, viene visualizzato un messaggio di errore.

Il tipo di indice non può essere modificato tramite DROP_EXISTING. Non è inoltre possibile ridefinire un indice XML primario come indice XML secondario o viceversa.

ONLINE = OFF

Specifica che le tabelle sottostanti e gli indici associati non sono disponibili per le query e la modifica dei dati durante l'operazione sull'indice. In questa versione di SQL Server, le operazioni di compilazione di indici online non sono supportate per gli indici XML. Se questa opzione è impostata su ON per un indice XML, viene generato un errore. Omettere l'opzione ONLINE o impostare ONLINE su OFF.

Un'operazione offline sull'indice che crea, ricompila o elimina un indice XML acquisisce un blocco di modifica dello schema (SCH-M) per la tabella. Il blocco impedisce a tutti gli utenti di accedere alla tabella sottostante durante l'operazione.

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

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 di pagina non vengono usati.

MAXDOP = max_degree_of_parallelism

Esegue l'override dell'opzione di configurazione server del massimo grado di parallelismo durante l'operazione sugli indici. 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.

Osservazioni:

Le colonne calcolate che derivano da tipi di dati xml possono essere indicizzate come colonna chiave o come colonna non chiave inclusa a condizione che il tipo di dati della colonna calcolata sia supportato come colonna chiave o colonna non chiave dell'indice. Non è possibile creare un indice XML primario su una colonna xml calcolata.

Per visualizzare informazioni relative agli indici XML, usare la vista del catalogo sys.xml_indexes.

Per altre informazioni sugli indici XML, vedere Indici XML (SQL Server).

Compressione XML

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

• Gli indici XML non ereditano la proprietà di compressione della tabella. Per comprimere gli indici, è necessario abilitare in modo esplicito la compressione XML sugli indici XML.
• Gli indici XML secondari non ereditano la proprietà di compressione dell'indice XML primario.
• Per impostazione predefinita, al momento della creazione dell'indice XML la compressione XML è impostata su OFF.

Osservazioni aggiuntive sulla creazione degli indici

Per altre informazioni sulla creazione degli indici, vedere la sezione "Osservazioni" in CREATE INDEX (Transact-SQL).

Esempi

Questo articolo richiede il AdventureWorks2022 database di esempio, che è possibile scaricare dalla home page degli esempi di Microsoft SQL Server e dei progetti della community.

R. Creazione di un indice XML primario

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

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

B. Creazione di un indice XML primario 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 XML primario sulla colonna CatalogDescription della tabella Production.ProductModel.

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

C. Creazione di un indice XML secondario

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

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

D. Creazione di un indice XML secondario 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 XML secondario sulla colonna CatalogDescription della tabella Production.ProductModel.

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

Vedi anche

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