CREATE COLUMNSTORE INDEX (Transact-SQL)

  • Articolo

Consente di creare un indice columnstore in una tabella specificata. Un indice columnstore ottimizzato in memoria xVelocity è un tipo di indice non cluster compresso. Può essere presente un solo indice columnstore per ogni tabella. L'indice può essere creato prima dell'immissione dei dati nella tabella. Non è possibile aggiornare una tabella che include un indice columnstore. Per informazioni sull'utilizzo di indici columnstore, vedere Indici columnstore.

[!NOTA]

Per informazioni su come creare un indice relazionale, vedere CREATE INDEX (Transact-SQL). Per informazioni su come creare un indice XML, vedere CREATE XML INDEX (Transact-SQL). Per informazioni su come creare un indice spaziale, vedere CREATE SPATIAL INDEX (Transact-SQL).

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

Sintassi

CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON <object> ( column  [ ,...n ] )
    [ WITH ( <column_index_option> [ ,...n ] ) ]
    [ ON {
           { partition_scheme_name ( column_name ) } 
           | filegroup_name 
           | "default" 
         }
    ]
[ ; ]

<object> ::=
{
    [database_name. [schema_name ] . | schema_name . ]
     table_name
{

<column_index_option> ::=
{
      DROP_EXISTING = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
 }

Argomenti

  • NONCLUSTERED
    Consente di creare un indice columnstore per specificare l'ordinamento logico di una tabella. Gli indici columnstore cluster non sono supportati.

  • COLUMNSTORE
    Indica che l'indice sarà un indice columnstore.

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

  • column
    Una o più colonne su cui è basato l'indice. Un indice columnstore è limitato a 1024 colonne.

  • ON partition_scheme_name**(column_name)**
    Specifica lo schema di partizione che definisce i filegroup in cui verrà eseguito il mapping delle partizioni di un indice partizionato. È necessario includere lo schema di partizione all'interno del database eseguendo CREATE PARTITION SCHEME. column_name specifica la colonna in base alla quale verrà partizionato un indice partizionato. La colonna deve corrispondere all'argomento della funzione di partizione utilizzata da partition_scheme_name per tipo di dati, lunghezza e precisione. column_name non è limitato alle colonne nella definizione dell'indice. Quando si partiziona un indice columnstore, la colonna di partizionamento viene aggiunta dal Motore di database come colonna dell'indice, se non è già stata specificata.

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

    Per ulteriori informazioni sul partizionamento degli indici, vedere Tabelle e indici partizionati.

  • ON filegroup_name
    Crea l'indice specificato nel filegroup specificato. Se non viene specificata una posizione e la tabella o la vista non è partizionata, l'indice utilizzerà lo stesso filegroup della tabella o della vista sottostante. È necessario che il filegroup esista già.

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

    In questo contesto il termine default non rappresenta una parola chiave, ma un identificatore per il filegroup predefinito e pertanto deve essere delimitato, ad esempio ON "default" oppure ON [default]. Se si specifica "default", l'opzione QUOTED_IDENTIFIER deve essere impostata su ON per la sessione corrente. Impostazione predefinita. Per ulteriori informazioni, vedere SET QUOTED_IDENTIFIER (Transact-SQL).

<object>::=

Oggetto con nome completo o non completo che si desidera indicizzare.

  • database_name
    Nome del database.

  • schema_name
    Nome dello schema a cui appartiene la tabella.

  • table_name
    Nome della tabella da indicizzare.

<column_index_option>::=

Specifica le opzioni da utilizzare per la creazione dell'indice columnstore.

  • DROP_EXISTING
    Specifica l'eliminazione e la ricompilazione dell'indice preesistente denominato. Il valore predefinito è OFF.

    • ON
      L'indice esistente deve essere eliminato e ricompilato. Il nome di indice specificato deve corrispondere a quello dell'indice esistente, mentre la definizione dell'indice può essere modificata. È possibile, ad esempio, specificare colonne diverse oppure opzioni dell'indice.
    • OFF
      Se il nome di indice specificato esiste già, viene visualizzato un messaggio di errore. Il tipo di indice non può essere modificato tramite l'opzione DROP_EXISTING. Per quanto riguarda la sintassi compatibile con le versioni precedenti, WITH DROP_EXISTING equivale a WITH DROP_EXISTING = ON.

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

    I possibili valori di max_degree_of_parallelism sono i seguenti:

    • 1
      Disattiva la generazione di piani paralleli.

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

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

    Per ulteriori informazioni, vedere Configurazione di operazioni parallele sugli indici.

    [!NOTA]

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

Osservazioni

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

I tipi di dati aziendali comuni possono essere inclusi in un indice columnstore. In un indice columnstore è possibile includere i tipi di dati seguenti.

  • char e varchar

  • nchar e nvarchar (ad eccezione di varchar(max) e nvarchar(max))

  • decimal (e numeric), ad eccezione dei valori con precisione maggiore di 18 cifre.

  • int, bigint, smallint e tinyint

  • float (e real)

  • bit

  • money e smallmoney

  • Tutti i tipi di dati relativi a data e ora (ad eccezione di datetimeoffset con scala maggiore di 2)

I tipi di dati seguenti non possono essere inclusi in un indice columnstore.

  • binary e varbinary

  • ntext, text e image

  • varchar(max) e nvarchar(max)

  • uniqueidentifier

  • rowversion (e timestamp)

  • sql_variant

  • decimal (e numeric) con precisione maggiore di 18 cifre.

  • datetimeoffset con scala maggiore di 2

  • Tipi CLR (hierarchyid e tipi spaziali)

  • xml

Limitazioni di base

Un indice columnstore:

  • Non può includere più di 1024 colonne.

  • Non può essere cluster. Solo disponibili solo indici columnstore non cluster.

  • Non può essere un indice univoco.

  • Non può essere creato in una vista o in una vista indicizzata.

  • Non può includere una colonna di tipo sparse.

  • Non può fungere da chiave primaria o da chiave esterna.

  • Non può essere modificato tramite l'istruzione ALTER INDEX. Eliminare e ricreare invece l'indice columnstore. (È possibile utilizzare ALTER INDEX per disabilitare e ricompilare un indice columnstore.)

  • Non può essere creato tramite la parola chiave INCLUDE.

  • Non può includere le parole chiave ASC o DESC per l'ordinamento dell'indice. Gli indici columnstore vengono ordinati in base agli algoritmi di compressione. L'ordinamento comporta molti dei vantaggi a livello di prestazioni.

Non è possibile combinare indici columnstore con le funzionalità seguenti:

  • Compressione di riga e di pagina e formato di archiviazione vardecimal (un indice columnstore è già compresso in un formato diverso).

  • Replica

  • Rilevamento modifiche

  • Change Data Capture

  • Filestream

Per informazioni sui vantaggi a livello di prestazioni e sulle limitazioni degli indici columnstore, vedere Indici columnstore.

Autorizzazioni

È necessario disporre dell'autorizzazione ALTER per la tabella.

Esempi

A.Creazione di un indice non cluster semplice

Nell'esempio seguente vengono creati una tabella e un indice cluster semplici, quindi viene illustrata la sintassi della creazione di un indice columnstore.

CREATE TABLE SimpleTable
(ProductKey [int] NOT NULL, 
OrderDateKey [int] NOT NULL, 
DueDateKey [int] NOT NULL, 
ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON SimpleTable (ProductKey);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey);
GO

B.Creazione di un indice non cluster semplice tramite tutte le opzioni

Nell'esempio seguente vengono creati una tabella e un indice cluster semplici, quindi viene illustrata la sintassi della creazione di un indice columnstore.

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey)
WITH (DROP_EXISTING =  ON, 
    MAXDOP = 2)
ON "default"
GO

Per un esempio più complesso in cui vengono utilizzate tabella partizionate, vedere Indici columnstore.

Vedere anche

Riferimento

sys.column_store_dictionaries (Transact-SQL)

sys.column_store_segments (Transact-SQL)

ALTER INDEX (Transact-SQL)

CREATE PARTITION FUNCTION (Transact-SQL)

CREATE PARTITION SCHEME (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

Concetti

Indici columnstore

Indici columnstore