CREATE COLUMNSTORE INDEX (Transact-SQL)

Article
08/20/2013

Crée un index columnstore sur une table spécifiée. Composant des technologies xVelocity en mémoire, un index columnstore est un type d'index non cluster compressé. Il existe une limite d'un index columnstore par table. Un index peut être créé avant que la table soit remplie de données. Une table comportant un index columnstore ne peut pas être mise à jour. Pour plus d'informations sur l'utilisation d'index columnstore, consultez Index columnstore. Pour plus d'informations sur la structure des index columnstore, consultez Index columnstore.

[!REMARQUE]

Pour plus d'informations sur la création d'un index relationnel, consultez CREATE INDEX (Transact-SQL). Pour plus d'informations sur la création d'un index XML, consultez CREATE XML INDEX (Transact-SQL). Pour plus d'informations sur la création d'un index spatial, consultez CREATE SPATIAL INDEX (Transact-SQL).

Icône Lien de rubrique Conventions de la syntaxe Transact-SQL

Syntaxe

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
 }

Arguments

NONCLUSTERED
Crée un index columnstore qui spécifie l'ordre logique d'une table. Les index cluster columnstore ne sont pas pris en charge.
COLUMNSTORE
Indique que l'index sera un index columnstore.
index_name
Nom de l'index. Les noms d'index doivent être uniques dans une table ou une vue, mais ne doivent pas être nécessairement uniques dans une base de données. Les noms d'index doivent se conformer aux règles régissant les identificateurs.
column
Colonnes sur lesquelles l'index est basé. Un index columnstore est limité à 1 024 colonnes.
ON partition_scheme_name**(column_name)**
Spécifie le schéma de partition qui définit les groupes de fichiers auxquels les partitions d'un index partitionné seront mappées. Le schéma de partition doit exister dans la base de données en exécutant CREATE PARTITION SCHEME. column_name désigne la colonne selon laquelle un index partitionné sera partitionné. Cette colonne doit correspondre au type de données, à la longueur et à la précision de l'argument de la fonction de partition que partition_scheme_name utilise. column_name ne se limite pas aux colonnes dans la définition de l'index. Lors du partitionnement d'un index columnstore, le moteur de base de données ajoute la colonne de partitionnement en tant que colonne de l'index, si elle n'a pas déjà été spécifiée.

Si partition_scheme_name ou filegroup n'est pas spécifié et que la table est partitionnée, l'index est placé dans le même schéma de partition que la table sous-jacente, en utilisant la même colonne de partitionnement.

Pour plus d'informations sur le partitionnement des index, consultez Tables et index partitionnés.
ON filegroup_name
Crée l'index spécifié dans le groupe de fichiers spécifié. Si aucun emplacement n'est défini et que la table ou la vue n'est pas partitionnée, l'index utilise le même groupe de fichiers que la table ou la vue sous-jacente. Le groupe de fichiers doit déjà exister.
ON "default"
Crée l'index spécifié sur le groupe de fichiers par défaut.

Le terme « default », dans ce contexte, n'est pas un mot clé. Il s'agit de l'identificateur du groupe de fichiers par défaut et il doit être délimité, comme dans ON "default" ou ON [default]. Si "default" est spécifié, l'option QUOTED_IDENTIFIER doit être activée (ON) pour la session active. Il s'agit du paramètre par défaut. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

<object>::=

Objet qualifié complet ou partiel à indexer.

database_name
Nom de la base de données.
schema_name
Nom du schéma auquel appartient la table.
table_name
Nom de la table à indexer.

<column_index_option>::=

Spécifie les options à utiliser lorsque vous créez l'index de magasin de colonnes.

DROP_EXISTING
Spécifie que l'index nommé préexistant est supprimé et reconstruit. La valeur par défaut est OFF.
- ON
  L'index existant est supprimé et recréé. Le nom d'index défini doit être identique à celui de l'index existant. Toutefois, la définition de l'index peut être modifiée. Par exemple, vous pouvez spécifier différentes colonnes, ou options d'index.
- OFF
  Une erreur s'affiche si le nom d'index spécifié existe déjà. Le type d'index ne peut pas être modifié à l'aide de DROP_EXISTING. Dans la syntaxe de compatibilité descendante, WITH DROP_EXISTING est équivalent à WITH DROP_EXISTING = ON.
MAXDOP = max_degree_of_parallelism
Remplace l'option de configuration Configurer l'option de configuration du serveur Degré maximal de parallélisme pendant la durée de l'opération d'index. Utilisez MAXDOP pour limiter le nombre de processeurs utilisés dans une exécution de plan parallèle. Le nombre maximal de processeurs est 64.

Valeurs possibles de max_degree_of_parallelism :
- 1
  Supprime la génération de plan parallèle.
- >1
  Limite le nombre maximal de processeurs utilisés dans l'indexation parallèle au nombre défini ou à un nombre inférieur en fonction de la charge de travail actuelle du système.
- 0 (valeur par défaut)
  Utilise le nombre réel de processeurs ou un nombre de processeurs inférieur en fonction de la charge de travail actuelle du système.
Pour plus d'informations, consultez Configurer des opérations d'index parallèles.

[!REMARQUE]

Les opérations d'index parallèles ne sont pas disponibles dans toutes les éditions de Microsoft SQL Server. Pour obtenir une liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Fonctionnalités prises en charge par les éditions de SQL Server 2012.

Notes

Vous pouvez créer des index sur une table temporaire. Lorsque la table est supprimée ou que la session prend fin, les index sont supprimés.

Les types de données métiers communs peuvent être inclus dans un index columnstore. Les types de données suivantes peuvent être inclus dans un index columnstore.

char et varchar
nchar et nvarchar (sauf varchar(max) et nvarchar(max))
decimal (et numeric) (sauf avec une précision supérieure à 18 chiffres.)
int, bigint, smallint et tinyint
float (et real)
bit
money et smallmoney
Tous les types de données de date et d'heure (sauf datetimeoffset avec une échelle supérieure à 2)

Les types de données suivants ne peuvent pas être inclus dans un index columnstore.

binary et varbinary
ntext, text et image
varchar(max) et nvarchar(max)
uniqueidentifier
rowversion (et timestamp)
sql_variant
decimal (et numeric) avec une précision supérieure à 18 chiffres
datetimeoffset avec une échelle supérieure à 2
Types CLR (hierarchyid et types spatiaux)
xml

Restrictions de base

Un index columnstore :

Ne peut pas avoir plus de 1 024 colonnes.
Ne peut pas être un index cluster. Seuls les index columnstore non cluster sont disponibles.
Ne peut pas être un index unique.
Ne peut pas être créé sur une vue ou une vue indexée.
Ne peut pas inclure de colonne éparse.
Ne peut pas jouer le rôle d'une clé primaire ou d'une clé étrangère.
Ne peut pas être modifié à l'aide de l'instruction ALTER INDEX. Supprimez et recréez plutôt l'index columnstore. (Vous pouvez utiliser ALTER INDEX pour désactiver et reconstruire un index columnstore.)
Ne peut pas être créé par avec le mot clé INCLUDE.
Ne peut pas inclure les mots clés ASC ou DESC pour le tri de l'index. Les index columnstore sont triés en fonction des algorithmes de compression. Le tri éliminerait beaucoup des avantages en termes de performances.

Les index columnstore ne peuvent pas être combinés avec les fonctionnalités suivantes :

La compression de pages et de lignes et le format de stockage vardecimal (un index columnstore est déjà compressé dans un format différent).
Réplication
Suivi des modifications
Capture des données modifiées
Filestream

Pour plus d'informations sur les avantages en termes de performances et sur les limitations d'index columnstore, consultez Index columnstore.

Autorisations

Requiert une autorisation ALTER sur la table.

Exemples

A.Création d'un index non cluster simple

L'exemple suivant crée une table simple et un index cluster, puis illustre la syntaxe de création d'un index 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.Création d'un index non cluster simple à l'aide de toutes les options

L'exemple suivant crée une table simple et un index cluster, puis illustre la syntaxe de création d'un index columnstore.

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

Pour obtenir un exemple plus complexe à l'aide des tables partitionnées, consultez Index columnstore.