CREATE COLUMNSTORE INDEX (Transact-SQL)

Crea un indice columnstore in memoria non cluster per una tabella di SQL Server. Utilizzare un indice columnstore non cluster per sfruttare la compressione columnstore e migliorare significativamente i tempi di esecuzione delle query sui dati di sola lettura.

Per creare un indice columnstore cluster, vedere CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL).

Per ulteriori informazioni, vedere gli argomenti seguenti:

• Descrizione degli indici columnstore

• Utilizzo di indici columnstore non cluster

• Utilizzo di indici columnstore cluster

Si applica a: SQL Server (da SQL Server 2012 alla versione corrente). )

Convenzioni della sintassi Transact-SQL

Sintassi

Create a non-clustered columnstore index.
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON [database_name. [schema_name ] . | schema_name . ] table_name  
        ( column  [ ,...n ] )
    [ WITH ( <columnstore_index_option> [ ,...n ] ) ]
    [ ON {
        partition_scheme_name ( column_name ) 
        | filegroup_name 
        | "default" 
        } 
    ]
[ ; ]

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

Argomenti

• index_name
  Specifica il nome dell'indice. index_name deve essere univoco all'interno della tabella, ma non all'interno del database. I nomi di indice devono essere conformi alle regole per gli identificatori.

  Per un indice columnstore non cluster,

• ( column [ ,...n ] )
  Specifica le colonne da archiviare. Un indice columnstore non cluster è limitato a 1024 colonne.

  Ogni colonna deve essere di un tipo di dati supportato per gli indici columnstore. Vedere Limitazioni e restrizioniper un elenco di tipi di dati supportati.

• ON [database_name. [schema_name ] . | schema_name . ] table_name
  Specifica il nome composto da una, due o tre parti della tabella che contiene l'indice.

• ON
  Queste opzioni consentono di specificare i filegroup in cui l'indice verrà creato.

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

    Un indice columnstore su una tabella partizionata deve essere allineato con il partizionamento.

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

  • filegroup_name
    Specifica il nome di un filegroup in cui creare l'indice. Se filegroup_name non viene specificato e la tabella non è partizionata, l'indice utilizza lo stesso filegroup della tabella sottostante. Il filegroup deve essere già esistente.

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

  • 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_parallelismsono i seguenti.

    • 1 - Disattiva la generazione di piani paralleli.

    • >1 - Limita 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. Ad esempio, se MAXDOP = 4, il numero di processori utilizzati sarà 4 o inferiore.

    • 0 (valore 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 2014.

Autorizzazioni

È richiesta l'autorizzazione ALTER per la tabella.

Osservazioni generali

È possibile creare un indice columnstore per una tabella temporanea. Quando si elimina la tabella o termina la sessione, viene eliminato anche l'indice.

Se una colonna dispone di un tipo di dati non supportato per gli indici columnstore, è necessario omettere la colonna dall'indice columnstore.

Limitazioni e restrizioni

Indici columnstore non cluster:

• Non può includere più di 1024 colonne.

• Una tabella con un indice columnstore non cluster può presentare vincoli univoci, vincoli di chiave primaria o vincoli di chiave esterna, ma i vincoli non possono essere inclusi nell'indice columnstore non cluster.

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

• Non può includere una colonna di tipo sparse.

• Non può essere modificato tramite l'istruzione ALTER INDEX. Per modificare l'indice non cluster, è invece necessario eliminare e ricreare 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.

Ogni colonna in un indice columnstore deve essere di uno dei comuni tipi di dati aziendali seguenti.

• datetimeoffset [ ( n ) ]

• datetime2 [ ( n ) ]

• datetime

• smalldatetime

• date

• time [ ( n ) ]

• float [ ( n ) ]

• real [ ( n ) ]

• decimal [ ( precision [ , scale ] ) ]

• money

• smallmoney

• bigint

• int

• smallint

• tinyint

• bit

• nvarchar [ ( n ) ] tranne nvarchar (max) che non è supportato

• nchar [ ( n ) ]

• varchar [ ( n ) ]

• char [ ( n ) ]

• varbinary [ ( n ) ] tranne varbinary (max) che non è supportato

• binary [ ( n ) ]

Si applica a: SQL Server (da SQL Server 2014 alla versione corrente).

• uniqueidentifier

Le colonne che utilizzano uno qualsiasi dei tipi di dati seguenti non possono essere incluse in un indice columnstore.

• ntext, testo e immagine

• varchar(max) e nvarchar(max)

• rowversion (e timestamp)

• sql_variant

• Tipi CLR (tipi spaziali e hierarchyid)

• xml

Si applica a: SQL Server 2012.

• uniqueidentifier

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 Descrizione degli indici columnstore.

Metadati

Tutte le colonne di un indice columnstore vengono archiviate nei metadati come colonne incluse. L'indice columnstore non contiene colonne chiave. Nelle seguenti viste di sistema sono fornite informazioni sugli indici columnstore.

• sys.indexes (Transact-SQL)

• sys.index_columns (Transact-SQL)

• sys.partitions (Transact-SQL)

• sys.column_store_segments (Transact-SQL)

• sys.column_store_dictionaries (Transact-SQL)

• sys.column_store_row_groups (Transact-SQL)

[INIZIO PAGINA]

Esempi

A.Creare un indice columnstore 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 non cluster.

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.Creare un indice non cluster semplice utilizzando tutte le opzioni

Nell'esempio seguente viene illustrata la sintassi della creazione di un indice columnstore non cluster utilizzando tutte le opzioni.

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 relativo all'utilizzo delle tabelle partizionate, vedere Descrizione degli indici columnstore.

Modificare i dati in un indice columnstore non cluster

Dopo aver creato un indice columnstore non cluster in una tabella, non è possibile modificare i dati direttamente nella tabella. Se si esegue una query con INSERT, UPDATE, DELETE o MERGE viene restituito un messaggio di errore. Per aggiungere o modificare i dati nella tabella, è possibile effettuare una delle operazioni seguenti:

• Disabilitare o eliminare l'indice columnstore. È possibile aggiornare i dati nella tabella. Se si disabilita l'indice columnstore, è possibile ricompilare l'indice columnstore al termine dell'aggiornamento dei dati. Ad esempio,

  ALTER INDEX mycolumnstoreindex ON mytable DISABLE;
  -- update mytable --
  ALTER INDEX mycolumnstoreindex on mytable REBUILD
  

• Caricare i dati in una tabella di gestione temporanea che non dispone di un indice columnstore. Compilare un indice columnstore nella tabella di gestione temporanea. Passare la tabella di gestione temporanea in una partizione vuota della tabella principale.

• Passare una partizione della tabella con l'indice columnstore in una tabella di gestione temporanea vuota. Se è presente un indice columnstore nella tabella di gestione temporanea, disabilitare l'indice columnstore. Eseguire gli aggiornamenti. Compilare o ricompilare l'indice columnstore. Passare la tabella di gestione temporanea nuovamente nella partizione (ora vuota) della tabella principale.

[INIZIO PAGINA]