Condividi tramite

CREATE COLUMNSTORE INDEX (Transact-SQL)

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

Convertire una tabella rowstore in un indice columnstore cluster o creare un indice columnstore non cluster. Usare un indice columnstore per eseguire in modo efficiente un'analisi operativa in tempo reale nell'ambito di un carico di lavoro OLTP o per migliorare la compressione dei dati e le prestazioni delle query per i carichi di lavoro di data warehousing.

Per i miglioramenti più recenti a questa funzionalità, vedere Novità degli indici columnstore.

  • Gli indici columnstore cluster ordinati sono stati introdotti in SQL Server 2022 (16.x). Per altre informazioni, vedere CREATE COLUMNSTORE INDEX. Per la disponibilità degli indici columnstore ordinati, vedere Indici columnstore: Panoramica.

  • A partire da SQL Server 2016 (13.x), è possibile creare la tabella come indice columnstore cluster. Non è più necessario creare prima una tabella rowstore e, quindi, convertirla in un indice columnstore cluster.

  • Per informazioni sulle linee guida per la progettazione degli indici columnstore, vedere Indicazioni sulla progettazione degli indici columnstore.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per SQL Server e database SQL di Azure:

-- Create a clustered columnstore index on disk-based table.
CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER (column [ , ...n ] ) ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

-- Create a nonclustered columnstore index on a disk-based table.
CREATE [ NONCLUSTERED ]  COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
        ( column  [ , ...n ] )
    [ ORDER (column [ , ...n ] ) ]
    [ WHERE <filter_expression> [ AND <filter_expression> ] ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

<with_option> ::=
      DROP_EXISTING = { ON | OFF } -- default is OFF
    | MAXDOP = max_degree_of_parallelism
    | ONLINE = { ON | OFF }
    | COMPRESSION_DELAY  = { 0 | delay [ MINUTES ] }
    | DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { partition_number_expression | range } [ , ...n ] ) ]

<on_option>::=
      partition_scheme_name ( column_name )
    | filegroup_name
    | "default"

<filter_expression> ::=
      column_name IN ( constant [ , ...n ]
    | column_name { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< } constant )

Sintassi per Azure Synapse Analytics, Parallel Data Warehouse, SQL Server 2022 (16.x) e versioni successive:

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER ( column [ , ...n ] ) ]
    [ WITH ( DROP_EXISTING = { ON | OFF } ) ] -- default is OFF
[;]

Argomenti

In determinate versioni del motore di database, alcune opzioni non sono disponibili. La tabella seguente elenca le versioni in cui le opzioni vengono introdotte negli indici COLUMNSTORE CLUSTERED e COLUMNSTORE NONCLUSTERED:

Opzione CLUSTERED NONCLUSTERED
COMPRESSION_DELAY SQL Server 2016 (13.x) SQL Server 2016 (13.x)
DATA_COMPRESSION SQL Server 2016 (13.x) SQL Server 2016 (13.x)
ONLINE SQL Server 2019 (15.x) SQL Server 2017 (14.x)
Clausola WHERE N/D SQL Server 2016 (13.x)

Tutte le opzioni sono disponibili nel database SQL di Azure.

CREATE CLUSTERED COLUMNSTORE INDEX

Creare un indice columnstore cluster in cui tutti i dati vengono compressi e archiviati per colonna. L'indice include tutte le colonne della tabella e archivia l'intera tabella. Se la tabella esistente è un heap o un indice cluster, viene convertita in un indice columnstore cluster. Se la tabella è già stata archiviata come indice columnstore cluster, l'indice esistente verrà eliminato e ricompilato.

index_name

Specifica il nome del nuovo indice.

Se la tabella ha già un indice columnstore cluster, è possibile specificare lo stesso nome dell'indice esistente oppure usare l'opzione DROP_EXISTING per specificare un nuovo nome.

ON [ database_name. [ schema_name ]. | schema_name . ] table_name

Specifica il nome composto da una, due o tre parti della tabella da archiviare come indice columnstore cluster. Se la tabella è un heap o un indice cluster, verrà convertita dal tipo rowstore al tipo columnstore. Se la tabella è già un columnstore, questa istruzione consente di ricompilare l'indice columnstore cluster.

ORDER per columnstore cluster

Usare la column_store_order_ordinal colonna in sys.index_columns per determinare l'ordine delle colonne per un indice columnstore cluster. L'ordinamento columnstore facilita l'eliminazione dei segmenti, in particolare con i dati stringa. Per altre informazioni, vedere Ottimizzazione delle prestazioni con indici columnstore cluster ordinati e indici Columnstore - Linee guida per la progettazione.

Per eseguire la conversione in un indice columnstore cluster ordinato, l'indice esistente deve essere un indice columnstore cluster. Usare l'opzione DROP_EXISTING.

I tipi di dati LOB (tipi di dati di lunghezza massima) non possono essere la chiave di un indice columnstore cluster ordinato.

Quando si crea un indice columnstore cluster ordinato, usare OPTION(MAXDOP = 1) per l'ordinamento di qualità più elevata, in cambio di una durata significativamente più lunga dell'istruzione CREATE INDEX . Per creare l'indice il più velocemente possibile, non limitare MAXDOP. La massima qualità di compressione e ordinamento può aiutare le query sull'indice columnstore.

Per la disponibilità degli indici columnstore ordinati, vedere Indici columnstore: Panoramica.

Opzioni WITH

DROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON specifica di eliminare l'indice esistente e di creare un nuovo indice columnstore.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (DROP_EXISTING = ON);

L'impostazione predefinita, ovvero DROP_EXISTING = OFF, prevede che il nome dell'indice sia uguale al nome esistente. Se il nome dell'indice specificato è già esistente, si verifica un errore.

MAXDOP = max_degree_of_parallelism

Questa opzione consente l'override della configurazione server del massimo grado di parallelismo già esistente per tutta la durata dell'operazione sugli indici. Utilizzare MAXDOP per limitare il numero di processori utilizzati durante l'esecuzione di un piano parallelo. Il valore massimo è 64 processori.

I valori per max_degree_of_parallelism possono essere:

  • 1, che disattiva la generazione di piani paralleli.
  • >1, che limita il numero massimo di processori usati in un'operazione parallela sugli indici a un valore specificato o a un valore più basso, in base al carico di lavoro corrente del sistema. Ad esempio, se MAXDOP = 4, i processori usati sono al massimo 4.
  • 0 (valore predefinito), che usa il numero di processori effettivo o un numero inferiore, in base al carico di lavoro corrente del sistema.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (MAXDOP = 2);

Per altre informazioni, vedere Configurazione del server: max degree of parallelism e Configure Parallel Index Operations.For more information, see Server configuration: max degree of parallelism, and Configure Parallel Index Operations.

COMPRESSION_DELAY = 0 | delay [ MINUTES ]

Per una tabella basata su disco, il valore di delay indica il numero minimo di minuti in cui un rowgroup differenziale contrassegnato come chiuso deve rimanere nel rowgroup differenziale. Allo scadere dei minuti di ritardo, SQL Server potrà comprimere tale rowgroup nel rowgroup compresso. Poiché le tabelle basate su disco non tengono traccia dell'ora di inserimento o aggiornamento nelle singole righe, SQL Server applica il ritardo ai rowgroup differenziali contrassegnati come chiusi.

Il valore predefinito è 0 minuti.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( COMPRESSION_DELAY = 10 MINUTES );

Per indicazioni su quando usare COMPRESSION_DELAY, vedere Introduzione a columnstore per l'analisi operativa in tempo reale.

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

Specifica l'opzione di compressione dei dati per la tabella, il numero di partizione o l'intervallo di partizioni specificato. Le opzioni sono le seguenti:

  • COLUMNSTORE è l'impostazione predefinita e specifica di eseguire la compressione con il columnstore che offre le prestazioni migliori. Questa opzione rappresenta la scelta tipica.
  • COLUMNSTORE_ARCHIVE comprime la tabella o la partizione in una dimensione ancora inferiore. È ad esempio opportuno usare questa opzione nel caso di un'archiviazione per la quale sono sufficienti risorse più limitate ed è disponibile più tempo per l'archiviazione e il recupero.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

Per altre informazioni sulla compressione, vedere Compressione dei dati.

ONLINE = [ON | OFF]
  • ON specifica che l'indice columnstore rimarrà online e risulterà disponibile durante la compilazione della nuova copia dell'indice.
  • OFF specifica che l'indice non rimarrà disponibile per l'uso mentre viene compilata la nuova copia.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( ONLINE = ON );

Opzioni ON

Tramite queste opzioni, è possibile specificare alcune impostazioni per l'archiviazione dati, ad esempio uno schema di partizione, un filegroup specifico o il filegroup predefinito. Se l'opzione ON non è specificata, per l'indice verranno usate le impostazioni della partizione o del filegroup della tabella esistente.

partition_scheme_name ( column_name ) specifica lo schema di partizione per la tabella. Lo schema di partizione deve essere già presente nel database. Per creare lo schema di partizione, vedere CREATE PARTITION SCHEME (Transact-SQL).

column_name specifica la colonna in base alla quale viene 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.

filegroup_name specifica il filegroup per l'archiviazione dell'indice columnstore cluster. Se non viene specificato alcun percorso e la tabella non è partizionata, per l'indice verrà usato lo stesso filegroup della tabella o vista sottostante. Il filegroup deve essere già esistente.

Per creare l'indice nel filegroup predefinito, usare "default" o [default]. Se si specifica "default", l'opzione QUOTED_IDENTIFIER deve essere ON per la sessione corrente. QUOTED_IDENTIFIER è ON per impostazione predefinita. Per altre informazioni, vedere SET QUOTED_IDENTIFIER (Transact-SQL).

CREATE [NONCLUSTERED] COLUMNSTORE INDEX

Creare un indice columnstore non cluster in una tabella rowstore archiviata in memoria come heap o indice cluster. L'indice può essere associato a una condizione di filtro e non è necessario che includa tutte le colonne della tabella sottostante. L'indice columnstore richiede spazio sufficiente per archiviare una copia dei dati. È possibile aggiornare l'indice. L'aggiornamento verrà eseguito man mano che viene modificata la tabella sottostante. L'indice columnstore non cluster per un indice cluster consente l'esecuzione di analisi in tempo reale.

index_name

Specifica il nome dell'indice. I valori di index_name devono essere univoci all'interno della tabella, ma non all'interno del database. Devono essere anche conformi alle regole degli identificatori.

( column [ ,...n ] )

Specifica le colonne da archiviare. Un indice columnstore non cluster è limitato a 1.024 colonne.

Ogni colonna deve essere di un tipo di dati supportato per gli indici columnstore. Per un elenco dei tipi di dati supportati, vedere Limitazioni e restrizioni.

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.

ORDER per columnstore non cluster

Le colonne specificate nella ORDER clausola per un indice columnstore non cluster devono essere un subset delle colonne chiave per l'indice.

Utilizzare la column_store_order_ordinal colonna in sys.index_columns per determinare l'ordine delle colonne per un indice columnstore non cluster. L'ordinamento columnstore facilita l'eliminazione dei segmenti, in particolare con i dati stringa. Per altre informazioni, vedere Ottimizzazione delle prestazioni con indici columnstore cluster ordinati e indici Columnstore - Linee guida per la progettazione. Le considerazioni sulla progettazione e sulle prestazioni in questi articoli si applicano in genere agli indici columnstore cluster e non cluster.

I tipi di dati LOB (i tipi di dati di lunghezza massima) non possono essere la chiave di un indice columnstore non cluster ordinato.

Quando si crea un indice columnstore non cluster ordinato, usare OPTION(MAXDOP = 1) per l'ordinamento di qualità più elevata, in cambio di una durata significativamente più lunga dell'istruzione CREATE INDEX . Per creare l'indice il più velocemente possibile, non limitare MAXDOP. La massima qualità di compressione e ordinamento può aiutare le query sull'indice columnstore.

Per la disponibilità dell'indice columnstore ordinato, vedere Disponibilità dell'indice di colonna ordinata.

Opzioni WITH

DROP_EXISTING = [OFF] | ON

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

DROP_EXISTING = OFF
Se il nome dell'indice specificato è già esistente, viene visualizzato un errore. Il tipo di indice non può essere modificato tramite DROP_EXISTING. Nella sintassi compatibile con le versioni precedenti, WITH DROP_EXISTING equivale a WITH DROP_EXISTING = ON.

MAXDOP = max_degree_of_parallelism

Esegue l'override dell'opzione di configurazione Server: max degree of parallelism durante l'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 valori per max_degree_of_parallelism possono essere:

  • 1, che disattiva la generazione di piani paralleli.
  • >1, che limita il numero massimo di processori usati in un'operazione parallela sugli indici a un valore specificato o a un valore più basso, in base al carico di lavoro corrente del sistema. Ad esempio, se MAXDOP = 4, i processori usati sono al massimo 4.
  • 0 (valore predefinito) determina l'uso del numero effettivo dei processori o di 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 parallele sugli indici non sono disponibili in ogni edizione di Microsoft SQL Server. Per un elenco delle funzionalità supportate dalle varie edizioni di SQL Server, vedere Edizioni e funzionalità supportate di SQL Server 2017.

ONLINE = [ON | OFF]
  • ON specifica che l'indice columnstore rimarrà online e risulterà disponibile durante la compilazione della nuova copia dell'indice.
  • OFF specifica che l'indice non rimarrà disponibile per l'uso mentre viene compilata la nuova copia. In un indice non cluster la tabella di base rimane disponibile. L'indice columnstore non cluster non viene usato ai fini delle query finché non viene completato il nuovo indice.
CREATE COLUMNSTORE INDEX ncci ON Sales.OrderLines (StockItemID, Quantity, UnitPrice, TaxRate)
WITH ( ONLINE = ON );
COMPRESSION_DELAY = 0 | delay [ MINUTES ]

Consente di specificare un periodo di tempo minimo in cui una riga deve rimanere nel rowgroup differenziale prima che sia idonea per la migrazione in un rowgroup compresso. È ad esempio possibile indicare che una riga diventa idonea per la compressione in un formato di archiviazione a colonne se rimane invariata per 120 minuti.

Per un indice columnstore di tabelle basate su disco, l'ora in cui una riga è stata inserita o aggiornata non viene rilevata. Di contro, l'ora di chiusura del rowgroup differenziale viene usata come proxy per la riga. La durata predefinita è 0 minuti. Viene eseguita la migrazione di una riga all'archiviazione a colonne dopo un accumulo di 1 milione di righe nel rowgroup differenziale e viene contrassegnata come chiusa.

DATA_COMPRESSION

Specifica l'opzione di compressione dei dati per la tabella, il numero di partizione o l'intervallo di partizioni specificato. Si applica solo agli indici columnstore, sia cluster sia non cluster. Le opzioni sono le seguenti:

  • COLUMNSTORE è l'impostazione predefinita e specifica di eseguire la compressione con il columnstore che offre le prestazioni migliori. Questa opzione rappresenta la scelta tipica.
  • COLUMNSTORE_ARCHIVE comprime la tabella o la partizione in una dimensione ancora inferiore. È possibile usare questa opzione per un'archiviazione o per altre situazioni in cui sono richieste dimensioni di archiviazione inferiori ed è disponibile più tempo per l'archiviazione e il recupero.

Per altre informazioni sulla compressione, vedere Compressione dei dati.

WHERE <filter_expression> [ AND <filter_expression> ]

Questa opzione rappresenta un predicato di filtro e consente di specificare le righe da includere nell'indice. SQL Server crea statistiche filtrate per le righe di dati nell'indice filtrato.

Il predicato del filtro usa una logica di confronto semplice. I confronti che usano NULL valori letterali non sono consentiti con gli operatori di confronto. Usare invece gli operatori IS NULL 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

Per le indicazioni relative agli indici filtrati, vedere Creare indici filtrati.

Opzioni ON

Le opzioni che seguono consentono di specificare i filegroup nei quali viene creato l'indice.

partition_scheme_name ( column_name )

Specifica lo schema di partizione che definisce i filegroup a cui vengono mappate le 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 viene 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. Quando si partiziona un indice columnstore, il motore di database aggiunge la colonna di partizionamento come colonna dell'indice, se non è già stata specificata.

Se la tabella viene partizionata senza che sia stato specificato il valore di partition_scheme_name o filegroup, l'indice viene posizionato nello stesso schema di partizione e usa la stessa colonna di partizionamento della tabella sottostante.

Un indice columnstore su una tabella partizionata deve essere allineato con il partizionamento. Per altre informazioni sul partizionamento degli indici, vedere Tabelle e indici partizionati.

filegroup_name

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

"default"

Crea l'indice specificato nel filegroup predefinito.

In questo contesto "default" non rappresenta una parola chiave. Si tratta di un identificatore per il filegroup predefinito e deve essere delimitato, come in ON "default" o ON [default]. Se "default" viene specificato, l'opzione QUOTED_IDENTIFIER deve essere IMPOSTATA per la sessione corrente, ovvero l'impostazione predefinita. Per altre informazioni, vedere SET QUOTED_IDENTIFIER.

Autorizzazioni

È necessario disporre dell'autorizzazione ALTER per la tabella.

Osservazioni:

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

Indici filtrati

Un indice filtrato è un indice non cluster ottimizzato, adatto per le query che prevedono la selezione di 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 per la colonna dei valori obbligatori devono essere specificate ogni qual volta si verifichi una qualsiasi delle condizioni seguenti:

  • Si crea un indice filtrato.
  • I dati di un indice filtrato vengono modificati da un'operazione INSERT, UPDATE, DELETE o MERGE.
  • L'indice filtrato viene usato da Query Optimizer per generare il piano di query.
Opzioni SET Valore obbligatorio Valore server predefinito Valori OLE DB e ODBC predefiniti Valore DB-Library predefinito
ANSI_NULLS In... In... In... OFF
ANSI_PADDING In... In... In... OFF
ANSI_WARNINGS 1 In... In... In... OFF
ARITHABORT In... In... OFF OFF
CONCAT_NULL_YIELDS_NULL In... In... In... OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER In... In... In... OFF

1 L'impostazione di ANSI_WARNINGS su ON imposta in modo implicito ARITHABORT su ON quando il livello di compatibilità del database è impostato su 90 o versioni successive. 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 delle istruzioni INSERT, UPDATE, DELETE o MERGE tramite le quali vengono modificati i dati dell'indice.

  • Query Optimizer esclude l'indice dal piano di esecuzione relativo alle istruzioni Transact-SQL.

Per altre informazioni sugli indici filtrati, vedere Creare indici filtrati.

Limitazioni e restrizioni

Ogni colonna di un indice columnstore deve contenere dati di business di uno dei seguenti tipi comuni:

  • datetimeoffset [ ( n ) ]
  • datetime2 [ ( n ) ]
  • datetime
  • smalldatetime
  • date
  • time [ ( n ) ]
  • float [ ( n ) ]
  • real [ ( n ) ]
  • decimal [ ( precision [ , scale ] ) ]
  • numeric [ ( precision [ , scale ] ) ]
  • money
  • smallmoney
  • bigint
  • int
  • smallint
  • tinyint
  • bit
  • nvarchar [ ( n ) ]
  • nvarchar(max) 1
  • nchar [ ( n ) ]
  • varchar [ ( n ) ]
  • varchar(max) 1
  • char [ ( n ) ]
  • varbinary [ ( n ) ]
  • varbinary(max) 1
  • binary [ ( n ) ]
  • uniqueidentifier 2

1 Si applica a SQL Server 2017 (14.x) e database SQL di Azure al livello Premium, al livello Standard (S3 e versioni successive) e a tutti i livelli di offerte vCore, solo negli indici columnstore cluster.

2 Si applica a SQL Server 2014 (12.x) e versioni successive.

Se la tabella sottostante contiene una colonna di un tipo di dati non supportato per gli indici columnstore, è necessario omettere tale colonna dall'indice columnstore non cluster.

I dati LOB (Large Object) superiori a 8 kilobyte vengono memorizzati nell'archiviazione LOB, all'esterno di righe, solo con un puntatore alla posizione fisica archiviata all'interno del segmento di colonna. Le dimensioni dei dati archiviati non vengono riportate in sys.column_store_segments, né in sys.column_store_dictionaries o in sys.dm_db_column_store_row_group_physical_stats.

In un indice columnstore non è possibile includere colonne che usano uno dei tipi di dati seguenti:

  • ntext, text e image
  • nvarchar(max), varchar(max)e varbinary(max) 1
  • rowversion (e timestamp)
  • sql_variant
  • Tipi CLR (hierarchyid e tipi spaziali)
  • xml
  • uniqueidentifier 2

1 Si applica a SQL Server 2016 (13.x) e alle versioni precedenti e agli indici columnstore non cluster.

2 Si applica a SQL Server 2012 (11.x).

Indici columnstore non cluster:

  • Non può contenere più di 1.024 colonne.
  • Non possono essere creati come indici basati su vincoli. Per una tabella con un indice columnstore possono essere presenti vincoli univoci, vincoli di chiave primaria o vincoli di chiave esterna. I vincoli vengono applicati sempre con un indice rowstore. I vincoli non possono essere applicati con un indice columnstore (cluster o non cluster).
  • Non possono includere una colonna di tipo sparse.
  • Non possono essere modificati tramite l'istruzione ALTER INDEX. Per modificare l'indice non cluster, è invece necessario eliminare e ricreare l'indice columnstore. È possibile usare ALTER INDEX per disabilitare e ricompilare un indice columnstore.
  • Non possono essere creati tramite la parola chiave INCLUDE.
  • Non possono includere la parola chiave ASC o la parola chiave 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. In Azure Synapse Analytics e a partire da SQL Server 2022 (16.x), è possibile specificare un ordine per le colonne in un indice columnstore. Per altre informazioni, vedere Ottimizzazione delle prestazioni con indici columnstore cluster ordinati.
  • Non è possibile includere colonne LOB di tipo nvarchar(max), varchar(max)e varbinary(max) in indici columnstore non cluster. Solo gli indici columnstore cluster supportano i tipi LOB, a partire dalla versione di SQL Server 2017 (14.x), database SQL di Azure (configurati a livello Premium, livello Standard (S3 e versioni successive) e tutti i livelli di offerte vCore. Le versioni precedenti non supportano i tipi LOB negli indici columnstore cluster e non cluster.
  • A partire da SQL Server 2016 (13.x), è possibile creare un indice columnstore non cluster in una vista indicizzata.

Le combinazioni tra gli indici columnstore e le funzionalità elencate di seguito non sono consentite:

  • Colonne calcolate. A partire da SQL Server 2017 (14.x), un indice columnstore cluster può contenere una colonna calcolata non persistente. Tuttavia, in SQL Server 2017 (14.x), gli indici columnstore cluster non possono contenere colonne calcolate persistenti e non è possibile creare indici non cluster in colonne calcolate.
  • Compressione di pagine e righe e formato di archiviazione vardecimal . (un indice columnstore è già compresso in un formato diverso).
  • Replica con indici columnstore cluster. Gli indici columnstore non cluster sono supportati. Per altre informazioni, vedere sp_addarticle.
  • FileStream.

Non è possibile usare cursori o trigger in una tabella con un indice columnstore cluster. Questa restrizione non si applica agli indici columnstore non cluster. È possibile usare cursori e trigger in una tabella con un indice columnstore cluster.

Limitazioni specifiche relative a SQL Server 2014 (12.x):

Le limitazioni indicate di seguito sono valide solo per SQL Server 2014 (12.x). In questa versione sono stati introdotti indici columnstore cluster aggiornabili. Gli indici columnstore non cluster sono ancora di sola lettura.

  • Change Tracking. Non è possibile usare il rilevamento delle modifiche con gli indici columnstore.
  • Change Data Capture. Non è possibile abilitare questa funzionalità nelle tabelle con un indice columnstore cluster. A partire da SQL Server 2016 (13.x), Change Data Capture può essere abilitato nelle tabelle con un indice columnstore non cluster.
  • Secondario leggibile. Non è possibile accedere a un indice columnstore cluster (CCI) da una replica secondaria leggibile di un gruppo di disponibilità Always On leggibile. È possibile accedere a un indice columnstore non cluster (NCCI) da una replica secondaria leggibile.
  • MARS (Multiple Active Result Sets). In SQL Server 2014 (12.x) questa funzionalità viene usata per le connessioni di sola lettura a tabelle con indice columnstore. Tuttavia, SQL Server 2014 (12x) non supporta la funzionalità MARS per operazioni DML (Data Manipulation Language) simultanee in una tabella con indice columnstore. Se si tenta di usare questa funzionalità per eseguire tale tipo di operazioni, SQL Server interromperà le connessioni e annullerà le transazioni.
  • Non è possibile creare indici columnstore non cluster in una vista o in una vista indicizzata.

Per informazioni sui vantaggi e sulle limitazioni delle prestazioni degli indici columnstore, vedere Indici columnstore: Panoramica.

Metadati UFX

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 disponibili informazioni sugli indici columnstore:

Esempi: convertire una tabella da rowstore a columnstore

R. Convertire un heap in un indice columnstore cluster

In questo esempio viene creata una tabella come heap e viene quindi eseguita la conversione di tale tabella in un indice columnstore cluster denominato cci_Simple. La creazione dell'indice columnstore cluster modifica l'archiviazione per l'intera tabella da rowstore a columnstore.

CREATE TABLE dbo.SimpleTable(
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON dbo.SimpleTable;
GO

B. Convertire un indice cluster in un indice columnstore cluster con lo stesso nome

In questo esempio viene creato una tabella con un indice cluster, quindi viene illustrata la sintassi della conversione dell'indice cluster in un indice columnstore cluster. La creazione dell'indice columnstore cluster modifica l'archiviazione per l'intera tabella da rowstore a columnstore.

CREATE TABLE dbo.SimpleTable2 (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable2 (ProductKey);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cl_simple ON dbo.SimpleTable2
WITH (DROP_EXISTING = ON);
GO

C. Gestire gli indici non cluster durante la conversione di una tabella rowstore in un indice columnstore

In questo esempio viene indicato come gestire gli indici non cluster durante la conversione di una tabella rowstore in un indice columnstore. A partire da SQL Server 2016 (13.x), non è necessaria alcuna azione speciale. SQL Server definisce e ricompila automaticamente gli indici non cluster nel nuovo indice columnstore cluster.

Per eliminare gli indici non cluster, usare l'istruzione DROP INDEX prima di creare l'indice columnstore. L'opzione DROP_EXISTING elimina solo l'indice cluster che viene convertito. Non elimina gli indici non cluster.

In SQL Server 2012 (11.x) e SQL Server 2014 (12.x), non è possibile creare un indice non cluster in un indice columnstore.

--Create the table for use with this example.
CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
  
--Create two nonclustered indexes for use with this example
CREATE INDEX nc1_simple ON dbo.SimpleTable (OrderDateKey);
CREATE INDEX nc2_simple ON dbo.SimpleTable (DueDateKey);
GO

Solo per SQL Server 2012 (11.x) e SQL Server 2014 (12.x), è necessario eliminare gli indici non cluster per creare l'indice columnstore.

DROP INDEX dbo.SimpleTable.nc1_simple;
DROP INDEX dbo.SimpleTable.nc2_simple;
  
--Convert the rowstore table to a columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_simple ON dbo.SimpleTable;
GO

D. Convertire una tabella dei fatti di grandi dimensioni da rowstore a columnstore

In questo esempio viene illustrato come convertire una tabella dei fatti di grandi dimensioni da una tabella rowstore in una tabella columnstore.

  1. Creare una tabella di piccole dimensioni da usare nell'esempio.

    --Create a rowstore table with a clustered index and a nonclustered index.
CREATE TABLE dbo.MyFactTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL
INDEX IDX_CL_MyFactTable CLUSTERED  ( ProductKey )
);

--Add a nonclustered index.
CREATE INDEX my_index ON dbo.MyFactTable ( ProductKey, OrderDateKey );

  2. Eliminare tutti gli indici non cluster della tabella rowstore. È possibile creare lo script degli indici per ricrearli in un secondo momento.

    --Drop all nonclustered indexes
DROP INDEX my_index ON dbo.MyFactTable;

  3. Convertire la tabella rowstore in una tabella columnstore con un indice columnstore cluster.

    Cercare innanzitutto il nome dell'indice rowstore cluster esistente. Nel passaggio 1 il nome dell'indice è stato impostato su IDX_CL_MyFactTable. Se il nome dell'indice non è specificato, all'indice viene assegnato un nome univoco generato automaticamente. È possibile recuperare il nome generato automaticamente tramite la seguente query di esempio:

    SELECT i.object_id, i.name, t.object_id, t.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'MyFactTable';

    Opzione 1: eliminare l'indice cluster esistenteIDX_CL_MyFactTable e convertire MyFactTable in columnstore. Modificare il nome del nuovo indice columnstore cluster.

    --Drop the clustered rowstore index.
DROP INDEX [IDX_CL_MyFactTable]
ON dbo.MyFactTable;
GO
--Create a new clustered columnstore index with the name MyCCI.
CREATE CLUSTERED COLUMNSTORE
INDEX IDX_CCL_MyFactTable ON dbo.MyFactTable;
GO

    Opzione 2: eseguire la conversione in columnstore e riutilizzare il nome dell'indice rowstore cluster esistente.

    --Create the clustered columnstore index,
--replacing the existing rowstore clustered index of the same name
CREATE CLUSTERED COLUMNSTORE
INDEX [IDX_CL_MyFactTable]
ON dbo.MyFactTable
WITH (DROP_EXISTING = ON);

E. Convertire una tabella columnstore in una tabella rowstore con un indice cluster

Per convertire una tabella columnstore in una tabella rowstore con un indice cluster, usare l'istruzione CREATE INDEX con l'opzione DROP_EXISTING.

CREATE CLUSTERED INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable] ( ProductKey )
WITH ( DROP_EXISTING = ON );

F. Convertire una tabella columnstore in un heap rowstore

Per convertire una tabella columnstore in un heap rowstore, eliminare l'indice columnstore cluster. In genere, questa operazione non è opportuna, ma può risultare utile in alcuni specifici casi. Per altre informazioni sugli heap, vedere Heap (tabelle senza indici cluster).

DROP INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable];

G. Eseguire la deframmentazione tramite la riorganizzazione dell'indice columnstore

Sono disponibili due modi per gestire l'indice columnstore cluster. A partire da SQL Server 2016 (13.x), è possibile usare ALTER INDEX...REORGANIZE anziché REBUILD. Per altre informazioni, vedere Rowgroup degli indici columnstore. Nelle versioni precedenti di SQL Server, è possibile usare CREATE CLUSTERED COLUMNSTORE INDEX con DROP_EXISTING=ON o ALTER INDEX (Transact-SQL) e l'opzione REBUILD. I due metodi forniscono gli stessi risultati.

Iniziare determinando il nome dell'indice columnstore cluster in MyFactTable.

SELECT i.object_id, i.name, t.object_id, t.name
FROM sys.indexes i
INNER JOIN sys.tables t on i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED COLUMNSTORE'
AND t.name = 'MyFactTable';

Rimuovere la frammentazione eseguendo un comando REORGANIZE nell'indice columnstore.

--Rebuild the entire index by using ALTER INDEX and the REBUILD option.
ALTER INDEX IDX_CL_MyFactTable
ON dbo.[MyFactTable]
REORGANIZE;

Esempi per gli indici columnstore non cluster

R. Creare un indice columnstore come indice secondario per una tabella rowstore

Questo esempio consente di creare un indice columnstore non cluster in una tabella rowstore. In questa situazione può essere creato solo un indice columnstore. L'indice columnstore richiede memoria aggiuntiva perché contiene una copia dei dati nella tabella rowstore. In questo esempio vengono creati una tabella e un indice cluster semplici, quindi viene illustrata la sintassi di creazione di un indice columnstore non cluster.

CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO

CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable (ProductKey);
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON dbo.SimpleTable (OrderDateKey, DueDateKey, ShipDateKey);
GO

B. Creare un indice columnstore non cluster di base usando tutte le opzioni

Nell'esempio seguente viene illustrata la sintassi per creare un indice columnstore non cluster nel filegroup DEFAULT con l'opzione dei gradi massimi di parallelismo (MAXDOP) impostata su 2.

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

C. Creare un indice columnstore non cluster con un predicato filtrato

Nell'esempio seguente viene creato un indice columnstore non cluster filtrato per la tabella Production.BillOfMaterials inclusa nel database di esempio AdventureWorks2022. Il predicato del filtro può includere colonne che non sono colonne chiave nell'indice filtrato. Il predicato usato in questo esempio determina la selezione delle solo righe per le quali EndDate è impostato su un valore diverso da NULL.

IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

D. Modificare i dati in un indice columnstore non cluster

Si applica a: da SQL Server 2012 (11.x) fino SQL Server 2014 (12.x).

In SQL Server 2014 (12.x) e versioni precedenti, dopo aver creato un indice columnstore non cluster in una tabella, non è possibile modificare direttamente i dati in tale tabella. Una query eseguita con INSERT, UPDATE, DELETE o MERGE ha esito negativo e restituisce un messaggio di errore. Di seguito sono indicate le opzioni che è possibile usare per aggiungere o modificare i dati nella tabella:

  • 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 dbo.mytable DISABLE;
-- update the data in mytable as necessary
ALTER INDEX mycolumnstoreindex on dbo.mytable REBUILD;

  • Caricare i dati in una tabella di staging senza indice columnstore. Compilare un indice columnstore nella tabella di staging. Passare la tabella di staging in una partizione vuota della tabella principale.

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

Esempi: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

R. Modificare un indice cluster in un indice columnstore cluster

Usando l'istruzione CREATE CLUSTERED COLUMNSTORE INDEX con DROP_EXISTING = ON, è possibile:

  • Trasformare un indice cluster in un indice columnstore cluster.

  • Ricompilare un indice columnstore cluster.

In questo esempio viene creata la tabella xDimProduct come tabella rowstore con un indice cluster. La tabella rowstore viene quindi convertita in una tabella columnstore tramite CREATE CLUSTERED COLUMNSTORE INDEX.

-- Uses AdventureWorks
  
IF EXISTS (SELECT name FROM sys.tables
    WHERE name = N'xDimProduct'
    AND object_id = OBJECT_ID (N'xDimProduct'))
DROP TABLE xDimProduct;
  
--Create a distributed table with a clustered index.
CREATE TABLE xDimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey)
WITH ( DISTRIBUTION = HASH(ProductKey),
    CLUSTERED INDEX (ProductKey) )
AS SELECT ProductKey, ProductAlternateKey, ProductSubcategoryKey FROM DimProduct;

Cercare il nome dell'indice cluster creato automaticamente per la nuova tabella nei metadati di sistema usando sys.indexes. Ad esempio:

SELECT i.object_id, i.name, t.object_id, t.name, i.type_desc
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'xdimProduct';

È ora possibile scegliere di:

  1. Eliminare l'indice columnstore cluster esistente con un nome creato automaticamente, quindi creare un nuovo indice columnstore cluster con un nome definito dall'utente.
  2. Eliminare e sostituire l'indice esistente con un indice columnstore cluster, mantenendo lo stesso nome generato dal sistema, ad esempio ClusteredIndex_1bd8af8797f7453182903cc68df48541.

Ad esempio:

--1. DROP the existing clustered columnstore index with an automatically-created name, for example:
DROP INDEX ClusteredIndex_1bd8af8797f7453182903cc68df48541 on xdimProduct;
GO
CREATE CLUSTERED COLUMNSTORE INDEX [<new_index_name>]
ON xdimProduct;
GO

--Or,
--2. Change the existing clustered index to a clustered columnstore index with the same name.
CREATE CLUSTERED COLUMNSTORE INDEX [ClusteredIndex_1bd8af8797f7453182903cc68df48541]
ON xdimProduct
WITH ( DROP_EXISTING = ON );
GO

B. Ricompilare un indice columnstore cluster

Sulla base di quello precedente, questo esempio usa CREATE CLUSTERED COLUMNSTORE INDEX per ricompilare l'indice columnstore cluster esistente denominato cci_xDimProduct.

--Rebuild the existing clustered columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = ON );

C. Modificare il nome di un indice columnstore cluster

Per modificare il nome di un indice columnstore cluster, eliminare l'indice columnstore cluster esistente e ricreare l'indice con un nuovo nome.

È consigliabile limitare questa operazione a una tabella piccola o vuota. La procedura necessaria per eliminare un indice columnstore cluster di grandi dimensioni e ricompilarlo con un altro nome richiede molto tempo.

In questo esempio si fa riferimento all'indice columnstore cluster cci_xDimProduct dell'esempio precedente. Innanzitutto viene eliminato l'indice columnstore cluster cci_xDimProduct, quindi viene ricreato l'indice columnstore cluster denominato mycci_xDimProduct.

--For illustration purposes, drop the clustered columnstore index.
--The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xDimProduct;
  
--Create a clustered index with a new name, mycci_xDimProduct.
CREATE CLUSTERED COLUMNSTORE INDEX mycci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = OFF );

D. Convertire una tabella columnstore in una tabella rowstore con un indice cluster

In alcuni casi, potrebbe essere opportuno rimuovere un indice columnstore cluster e creare un indice cluster. Quando si rimuove un indice columnstore cluster, la tabella viene modificata nel formato rowstore. Questo esempio converte una tabella columnstore in una tabella rowstore con un indice cluster con lo stesso nome. Non vengono persi dati. Tutti i dati vengono inseriti nella tabella rowstore e le colonne elencate diventano le colonne chiave nell'indice cluster.

--Drop the clustered columnstore index and create a clustered rowstore index.
--All of the columns are stored in the rowstore clustered index.
--The columns listed are the included columns in the index.
CREATE CLUSTERED INDEX cci_xDimProduct
ON xdimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey, WeightUnitMeasureCode)
WITH ( DROP_EXISTING = ON);

E. Convertire una tabella columnstore di nuovo in un heap rowstore

Usare DROP INDEX per eliminare l'indice columnstore cluster e convertire la tabella in un heap rowstore. In questo esempio viene convertita in un heap rowstore la tabella cci_xDimProduct. La tabella continua a essere distribuita, ma viene archiviata come heap.

--Drop the clustered columnstore index. The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xdimProduct;

F. Creare un indice columnstore cluster ordinato in una tabella senza indice

Un indice columnstore non ordinato copre tutte le colonne per impostazione predefinita. Non è pertanto necessario specificare alcun elenco di colonne. Un indice columnstore ordinato consente di specificare l'ordine delle colonne. Non è necessario che l'elenco includa tutte le colonne.

Per altre informazioni, vedere Ottimizzazione delle prestazioni con indici columnstore cluster ordinati.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE);

G. Convertire un indice columnstore cluster in un indice columnstore cluster ordinato

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE)
WITH (DROP_EXISTING = ON);

H. Aggiungere una colonna all'ordinamento di un indice columnstore cluster ordinato

È possibile specificare un ordine per le colonne in un indice columnstore. L'indice columnstore cluster ordinato originale era ordinato solo nella colonna SHIPDATE. Nell'esempio seguente viene aggiunta all'ordinamento la colonna PRODUCTKEY. Per la disponibilità degli indici columnstore ordinati, vedere Indici columnstore: Panoramica.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE, PRODUCTKEY)
WITH (DROP_EXISTING = ON);

I. Modificare l'ordinamento delle colonne ordinate

L'indice columnstore cluster originale era ordinato in SHIPDATE, PRODUCTKEY. Nell'esempio seguente l'ordinamento viene modificato in PRODUCTKEY, SHIPDATE. Per la disponibilità degli indici columnstore ordinati, vedere Indici columnstore: Panoramica.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (PRODUCTKEY,SHIPDATE)
WITH (DROP_EXISTING = ON);

J. Creare un indice columnstore cluster ordinato

È possibile creare un indice columnstore cluster con chiavi di ordinamento. Quando si crea un indice columnstore cluster ordinato, è necessario applicare l'hint MAXDOP = 1 per la query per ottenere la massima qualità dell'ordinamento e la durata più breve. Per la disponibilità degli indici columnstore ordinati, vedere Indici columnstore: Panoramica.

CREATE CLUSTERED COLUMNSTORE INDEX [OrderedCCI] ON dbo.FactResellerSalesPartCategoryFull
ORDER (EnglishProductSubcategoryName, EnglishProductName)
WITH (MAXDOP = 1, DROP_EXISTING = ON);

Contenuto correlato