CREATE XML INDEX (Transact-SQL)

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

[!NOTA]

Per creare un indice relazionale, vedere CREATE INDEX (Transact-SQL). Per informazioni sulla creazione di un indice spaziale, vedere CREATE SPATIAL INDEX (Transact-SQL).

Convenzioni della 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 ] . | schema_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
}

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.

  • Gli indici XML devono essere creati su una singola colonna xml. Non è possibile creare un indice XML su una colonna non xml né creare un indice relazionale su una colonna xml.

  • Non è possibile creare un indice XML, primario o secondario, su una colonna xml di una vista, su una variabile 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 ulteriori informazioni, vedere Opzioni SET che hanno effetto sui risultati.

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

• index_name
  Nome dell'indice. I nomi di indice devono essere univoci all'interno di una tabella, ma non all'interno di un database. Devono inoltre essere conformi alle regole per gli 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 utilizzare per la creazione di un indice XML secondario.

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

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

  • PATH
    Crea un indice XML secondario su colonne con i valori di percorso e i valori di nodo dell'indice XML primario. Nell'indice secondario PATH i valori di nodo e di percorso sono colonne chiave che consentono di eseguire le ricerche 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 qualificato che si desidera indicizzare.

• database_name
  Nome del database.

• schema_name
  Nome dello schema a cui appartiene la tabella.

• table_name
  Nome della tabella da indicizzare.

<xml_index_option> ::=

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

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

  • ON
    La percentuale di spazio disponibile specificata in fillfactor viene applicata alle pagine di livello intermedio dell'indice.

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

  L'opzione PAD_INDEX risulta utile solo quando si specifica FILLFACTOR, in quanto PAD_INDEX utilizza la percentuale specificata in FILLFACTOR. Se la percentuale specificata in FILLFACTOR non consente l'inserimento di una riga, Motore di database sostituisce internamente tale percentuale in modo da rendere disponibile lo spazio minimo necessario. Il numero di righe di una pagina di indice intermedia 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 applicato da Motore di database durante la creazione o la ricostruzione dell'indice. fillfactor deve essere un intero compreso tra 1 e 100. Il valore predefinito è 0. Se fillfactor è 100 o 0, tramite Motore di database vengono creati indici con pagine foglia riempite fino alla capacità massima.

  [!NOTA]

  I valori 0 e 100 relativi al fattore di riempimento sono equivalenti.

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

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

  Per ulteriori informazioni, vedere Fattore di riempimento.

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

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

  • OFF
    I risultati intermedi dell'ordinamento vengono archiviati nello stesso database dell'indice.

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

• 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 altrimenti viene generato un errore.

• DROP_EXISTING = { ON | OFF }
  Viene specificato che è necessario eliminare e quindi ricostruire l'indice XML denominato preesistente. Il valore predefinito è OFF.

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

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

  L'opzione DROP_EXISTING non può essere utilizzata per modificare il tipo di indice. 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 creazione di indici in linea 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 non in linea sull'indice che crea, ricostruisce o elimina un indice XML acquisisce un blocco di modifica dello schema (SCH-M) per la tabella. Tale blocco impedisce agli utenti di accedere alla tabella sottostante per la durata dell'operazione.

  [!NOTA]

  Le operazioni sugli indici in linea sono disponibili solo nelle edizioni Enterprise, Developer ed Evaluation di SQL Server.

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

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

  • OFF
    I blocchi a livello di riga non vengono utilizzati.

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

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

  • OFF
    I blocchi a livello di pagina non vengono utilizzati.

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

  Importante
  Sebbene l'opzione MAXDOP sia supportata a livello di sintassi per tutti gli indici XML, per un indice XML primario CREATE XML INDEX utilizza solo un processore singolo.

  I possibili valori di max_degree_of_parallelism sono i seguenti:

  • 1
    Disattiva la generazione di piani paralleli.

  • >1
    Limita il numero massimo di processori utilizzati in un'operazione parallela sugli indici al valore specificato o a un valore più basso in base al carico di lavoro corrente del sistema.

  • 0 (predefinito)
    Utilizza il numero effettivo di processori o un numero inferiore in base al carico di lavoro corrente del sistema.

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

  [!NOTA]

  Le operazioni parallele sugli indici sono disponibili solo nelle edizioni Enterprise, Developer ed Evaluation di SQL Server.

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, utilizzare la vista del catalogo sys.xml_indexes.

Per ulteriori informazioni sugli indici XML, vedere Indici su colonne con tipo di dati XML.

Osservazioni aggiuntive sulla creazione dell'indice

Per ulteriori informazioni sulla creazione dell'indice, vedere la sezione "Osservazioni" in CREATE INDEX (Transact-SQL).

Esempi

A. Creazione di un indice XML primario

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

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

B. Creazione di un indice XML secondario

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

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

Vedere anche

Riferimento

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)

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.xml_indexes (Transact-SQL)

EVENTDATA (Transact-SQL)

Concetti

Linee guida generali per la progettazione di indici

Indici su colonne con tipo di dati XML

Indici su colonne con tipo di dati XML

Altre risorse

Individuazione dei requisiti di spazio su disco per gli indici

Architettura delle strutture di dati di indici e tabelle