Partager via

CREATE COLUMNSTORE INDEX (Transact-SQL)

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Convertit une table rowstore en index columnstore cluster, ou crée un index columnstore non-cluster. Utilisez un index columnstore pour effectuer efficacement une analytique opérationnelle en temps réel sur une charge de travail OLTP, ou pour améliorer la compression des données et les performances des requêtes des charges de travail de stockage de données.

Consultez régulièrement la page Nouveautés des index columnstore pour connaître les dernières améliorations apportées à cette fonctionnalité.

  • Les index columnstore en cluster ordonné ont été introduits dans SQL Server 2022 (16.x). Pour plus d’informations, consultez CREATE COLUMNSTORE INDEX.

  • Depuis SQL Server 2016 (13.x), vous pouvez créer la table en tant qu’index cluster columnstore. Il n’est plus nécessaire de créer d’abord une table rowstore, puis de la convertir en index columnstore cluster.

  • Pour obtenir des instructions sur la conception des index columnstore, consultez Index columnstore - Guide de conception.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe pour SQL Server et Azure SQL Database :

-- 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 )

Syntaxe pour Azure Synapse Analytics, Parallel Data Warehouse, SQL Server 2022 (16.x) et versions ultérieures :

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
[;]

Arguments

Certaines options ne sont pas disponibles dans toutes les versions du moteur de base de données. Le tableau suivant présente les versions quand les options sont introduites dans les index CLUSTERED COLUMNSTORE et NONCLUSTERED COLUMNSTORE :

Option 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)
WHERE (clause) N/A SQL Server 2016 (13.x)

Toutes les options sont disponibles dans Azure SQL Database.

CREATE CLUSTERED COLUMNSTORE INDEX

Crée un index cluster columnstore dans lequel toutes les données sont compressées et stockées en colonne. L'index inclut toutes les colonnes de la table et stocke la table entière. Si la table existante est un segment de mémoire ou un index cluster, elle est convertie en index columnstore cluster. Si la table est déjà stockée en tant qu’index cluster columnstore, puis l’index existant est supprimé et recréé.

index_name

Spécifie le nom du nouvel index.

Si la table a déjà un index cluster columnstore, vous pouvez spécifier le même nom que l’index existant, ou vous pouvez utiliser l’option DROP EXISTING pour spécifier un nouveau nom.

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

Spécifie le nom à une, deux ou trois parties de la table à stocker en tant qu'index columnstore cluster. Si la table est un segment de mémoire ou si elle a un index cluster, elle est convertie de table rowstore en table columnstore. Si la table est déjà un index columnstore, cette instruction reconstruit l’index cluster columnstore.

ORDER pour columnstore en cluster

S’applique à Azure Synapse Analytics, analytics Platform System (PDW), Azure SQL Database, Azure SQL Managed Instance avec la stratégie de mise à jour toujours à jour et SQL Server 2022 (16.x) et versions ultérieures

Utilisez la column_store_order_ordinal colonne dans sys.index_columns pour déterminer l’ordre des colonnes d’un index columnstore cluster. L’ordre columnstore aide à éliminer les segments, en particulier avec les données de chaîne. Pour plus d’informations, consultez l’optimisation des performances avec l’index columnstore cluster ordonné et les index Columnstore - Conseils de conception.

Pour effectuer une conversion en index columnstore cluster ordonné, l’index existant doit être un index columnstore cluster. Utilisez l'option DROP_EXISTING.

Les types de données métier (les types de données de longueur (max)) ne peuvent pas être la clé d’un index columnstore cluster ordonné.

Lors de la création d’un index columnstore cluster ordonné, utilisez-le OPTION(MAXDOP = 1) pour le tri de qualité la plus élevée, en échange d’une durée considérablement plus longue de l’instruction CREATE INDEX . Pour créer l’index aussi rapidement que possible, ne limitez pas MAXDOP. La qualité optimale de compression et de tri peut faciliter les requêtes sur l’index columnstore.

Options WITH

DROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON spécifie de supprimer l’index existant et de créer un index columnstore.

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

La valeur par défaut, DROP_EXISTING = OFF, s’attend à ce que le nom de l’index soit le même que le nom existant. Une erreur se produit si le nom d’index spécifié existe déjà.

MAXDOP = max_degree_of_parallelism

Cette option peut remplacer l’option de configuration de serveur relative au degré maximal de parallélisme durant 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 égal à 64.

Les valeurs de max_degree_of_parallelism peuvent être :

  • 1, ce qui signifie la suppression de la génération de plans parallèles.
  • >1, ce qui signifie que le nombre maximal de processeurs utilisés dans une opération d’index parallèle est limité au nombre spécifié, ou à un nombre inférieur, en fonction de la charge de travail actuelle du système. Par exemple, si MAXDOP = 4, le nombre de processeurs utilisés est inférieur ou égal à 4.
  • 0 (par défaut), ce qui signifie que le nombre réel de processeurs, ou un nombre inférieur, doit être utilisé en fonction de la charge de travail actuelle du système.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (MAXDOP = 2);

Pour plus d’informations, consultez Configurer le degré maximal de parallélisme (option de configuration du serveur) et Configurer les opérations d’index parallèles.

COMPRESSION_DELAY = 0 | delay [ MINUTES ]

Pour une table sur disque, délai spécifie le nombre minimal de minutes pendant lesquelles un rowgroup delta à l’état fermé doit rester dans le rowgroup delta. SQL Server peut ensuite le compresser dans le rowgroup compressé. Dans la mesure où les tables sur disque n’effectuent pas le suivi des durées d’insertion et de mise à jour sur chaque ligne, SQL Server applique le délai aux rowgroups delta à l’état fermé.

La valeur par défaut est 0 minute.

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

Pour obtenir des recommandations sur l’utilisation de COMPRESSION_DELAY, consultez Bien démarrer avec columnstore pour l’analytique opérationnelle en temps réel.

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

Spécifie l'option de compression de données pour la table, le numéro de partition ou la plage de partitions spécifiés. Les options disponibles sont les suivantes :

  • COLUMNSTORE est la valeur par défaut et spécifie l’utilisation de la compression columnstore la plus performante. Cette option est le choix typique.
  • COLUMNSTORE_ARCHIVE compresse la partition ou la table en une taille encore plus petite. Utilisez cette option dans les situations où l’archivage nécessite une taille de stockage plus petite, et où la durée de stockage et de récupération peut être plus longue.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

Pour plus d’informations sur la compression, consultez Compression des données.

ONLINE = [ON | OFF]
  • ON spécifie que l’index columnstore reste en ligne et disponible pendant la création de la copie de l’index.
  • OFF spécifie que l’index ne peut pas être utilisé pendant la création de la copie.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( ONLINE = ON );

Options ON

Avec ces options, vous pouvez spécifier des options pour le stockage de données, par exemple un schéma de partition, un groupe de fichiers spécifique ou le groupe de fichiers par défaut. Si l’option ON n’est pas spécifiée, l’index utilise les paramètres de partition ou de groupe de fichiers de la table existante.

nom_schéma_partition ( nom_colonne ) spécifie le schéma de partition pour la table. Le schéma de partition doit déjà exister dans la base de données. Pour créer le schéma de partition, consultez CREATE PARTITION SCHEME.

column_name désigne la colonne sur laquelle un index partitionné est 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.

filegroup_name spécifie le groupe de fichiers pour le stockage de l’index columnstore cluster. Si aucun emplacement n’est spécifié et si la table n’est pas partitionnée, l’index utilise le même groupe de fichiers que la table ou vue sous-jacente. Le groupe de fichiers doit déjà exister.

Pour créer l’index sur le groupe de fichiers par défaut, utilisez "default" ou [default]. Si vous spécifiez "default", l’option QUOTED_IDENTIFIER doit être ON pour la session active. QUOTED_IDENTIFIER est ON par défaut. Pour plus d’informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

CREATE [NONCLUSTERED] COLUMNSTORE INDEX

Créez un index columnstore non-cluster sur une table rowstore stockée sous forme de segment de mémoire ou d’index cluster. L’index peut avoir une condition filtrée. Il n’a pas besoin d’inclure toutes les colonnes de la table sous-jacente. L’index columnstore nécessite suffisamment d’espace pour stocker une copie des données. Vous pouvez mettre à jour l’index, qui se mettra à jour à chaque changement de la table sous-jacente. L’index columnstore non-cluster sur un index cluster permet une analytique en temps réel.

index_name

Spécifie le nom de l'index. index_name doit être unique dans la table, mais pas nécessairement dans la base de données. Les noms d’index doivent se conformer aux règles régissant les identificateurs.

(colonne [ ,...n ])

Spécifie les colonnes à stocker. Un index columnstore non cluster est limité à 1 024 colonnes. Chaque colonne doit appartenir à un type de données pris en charge pour les index columnstore. Pour obtenir la liste des types de données pris en charge, consultez Limitations et Restrictions.

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

Spécifie un nom en une, deux ou trois parties pour la table qui contient l’index.

ORDER pour columnstore non cluster

S’applique à Azure SQL Database et à Azure SQL Managed Instance avec la stratégie de mise à jour toujours à jour

Les colonnes spécifiées dans la ORDER clause d’un index columnstore non cluster doivent être un sous-ensemble des colonnes clés de l’index.

Utilisez la column_store_order_ordinal colonne dans sys.index_columns pour déterminer l’ordre des colonnes d’un index columnstore non cluster. L’ordre columnstore aide à éliminer les segments, en particulier avec les données de chaîne. Pour plus d’informations, consultez l’optimisation des performances avec l’index columnstore cluster ordonné et les index Columnstore - Conseils de conception. Les considérations relatives à la conception et aux performances de ces articles s’appliquent généralement aux index columnstore cluster et non cluster.

Les types de données métier (les types de données de longueur maximale) ne peuvent pas être la clé d’un index columnstore non cluster ordonné.

Lors de la création d’un index columnstore non cluster ordonné, utilisez-le OPTION(MAXDOP = 1) pour le tri de qualité la plus élevée, en échange d’une durée beaucoup plus longue de l’instruction CREATE INDEX . Pour créer l’index aussi rapidement que possible, ne limitez pas MAXDOP. La qualité optimale de compression et de tri peut faciliter les requêtes sur l’index columnstore.

Options WITH

DROP_EXISTING = [OFF] | ON

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

DROP_EXISTING = 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 le degré maximal de parallélisme (option de configuration du serveur) pendant 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 égal à 64.

Les valeurs de max_degree_of_parallelism peuvent être :

  • 1, ce qui signifie la suppression de la génération de plans parallèles.
  • >1, ce qui signifie que le nombre maximal de processeurs utilisés dans une opération d’index parallèle est limité au nombre spécifié, ou à un nombre inférieur, en fonction de la charge de travail actuelle du système. Par exemple, si MAXDOP = 4, le nombre de processeurs utilisés est inférieur ou égal à 4.
  • 0 (par défaut), ce qui signifie que le nombre réel de processeurs, ou un nombre inférieur, doit être utilisé en fonction de la charge de travail actuelle du système.

Pour plus d’informations, consultez Configurer des opérations d’index parallèles.

Notes

Les opérations d’index parallèles ne sont pas disponibles dans toutes les éditions de Microsoft SQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

ONLINE = [ON | OFF]
  • ON spécifie que l’index columnstore reste en ligne et disponible pendant la création de la copie de l’index.
  • OFF spécifie que l’index ne peut pas être utilisé pendant la création de la copie. Dans un index non-cluster, la table de base reste disponible. Seul l’index columnstore non-cluster n’est pas utilisé pour répondre aux requêtes tant que le nouvel index n’est pas terminé.
CREATE COLUMNSTORE INDEX ncci ON Sales.OrderLines (StockItemID, Quantity, UnitPrice, TaxRate)
WITH ( ONLINE = ON );
COMPRESSION_DELAY = 0 | delay [ MINUTES ]

Spécifie une durée minimale pendant laquelle une ligne doit rester dans un rowgroup delta, avant d’être éligible à la migration vers un rowgroup compressé. Par exemple, vous pouvez décider que si une ligne reste inchangée pendant 120 minutes, elle est éligible à la compression dans un format de stockage en colonnes.

Pour un index columnstore de tables sur disque, l’heure à laquelle une ligne a été insérée ou mise à jour ne fait pas l’objet d’un suivi. À la place, l’heure de fermeture du rowgroup delta est utilisée en tant que proxy pour la ligne. La durée par défaut est de 0 minute. Une ligne est migrée vers le stockage en colonnes après 1 million de lignes cumulées dans le rowgroup delta, et elle est marquée comme fermée.

DATA_COMPRESSION

Spécifie l'option de compression de données pour la table, le numéro de partition ou la plage de partitions spécifiés. S’applique uniquement aux index columnstore, notamment les index cluster et non-cluster. Les options disponibles sont les suivantes :

  • COLUMNSTORE est la valeur par défaut et spécifie l’utilisation de la compression columnstore la plus performante. Cette option est le choix typique.
  • COLUMNSTORE_ARCHIVE compresse la partition ou la table en une taille encore plus petite. Vous pouvez utiliser cette option à des fins d’archivage ou dans d’autres situations qui nécessitent une taille de stockage plus petite, et où la durée de stockage et de récupération peut être plus longue.

Pour plus d’informations sur la compression, consultez Compression des données.

WHERE <filter_expression> [ AND <filter_expression> ]

Appelé prédicat de filtre, cette option spécifie les lignes à inclure dans l’index. SQL Server crée des statistiques filtrées sur les lignes de données de l’index filtré.

Le prédicat de filtre utilise une logique de comparaison simple. Les comparaisons qui utilisent NULL des littéraux ne sont pas autorisées avec les opérateurs de comparaison. L’utilisation des opérateurs IS NULL et IS NOT NULL est préférable.

Voici quelques exemples de prédicats de filtre pour la table Production.BillOfMaterials :

  • WHERE StartDate > '20000101' AND EndDate <= '20000630'
  • WHERE ComponentID IN (533, 324, 753)
  • WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Pour obtenir des conseils d’aide sur les index filtrés, consultez Créer des index filtrés.

Options ON

Les options suivantes spécifient les groupes de fichiers sur lesquels l’index est créé.

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é sont 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 sur laquelle un index partitionné est 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 n’est pas limité aux colonnes de la définition d’index. Durant le 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’est pas déjà spécifiée.

Si la table est partitionnée et si partition_scheme_name ou filegroup ne sont pas spécifiés, l’index est placé dans le même schéma de partition et utilise la même colonne de partitionnement que la table sous-jacente.

Un index columnstore sur une table partitionnée doit être aligné sur les partitions. Pour plus d’informations sur le partitionnement des index, consultez Tables et index partitionnés.

filegroup_name

Spécifie un nom de groupe de fichiers sur lequel créer l'index. Si filegroup_name n’est pas spécifié et si la table n’est pas partitionnée, l’index utilise le même groupe de fichiers que la table sous-jacente. Le groupe de fichiers doit déjà exister.

« valeur par défaut »

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, qui est le paramètre par défaut. Pour plus d’informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

Autorisations

Requiert une autorisation ALTER sur la table.

Notes

Vous pouvez créer un index columnstore sur une table temporaire. Lorsque la table est supprimée ou que la session prend fin, l'index est également supprimé.

Index filtrés

Un index filtré est un index non-cluster optimisé, adapté aux requêtes qui sélectionnent un petit pourcentage de lignes dans une table. Il utilise un prédicat de filtre pour indexer une partie des données de la table. Un index filtré bien conçu peut améliorer les performances des requêtes, réduire les coûts de stockage et réduire les coûts de maintenance.

Options SET requises pour les index filtrés

Les options SET de la colonne Valeur obligatoire sont nécessaires chaque fois que l’une des conditions suivantes est vérifiée :

  • Vous créez un index filtré.
  • Une opération INSERT, UPDATE, DELETE ou MERGE modifie les données dans un index filtré.
  • L’optimiseur de requête utilise l’index filtré pour produire le plan de requête.
Options définies Valeur requise Valeur de serveur par défaut Valeur OLE DB et ODBC par défaut Valeur DB-Library par défaut
ANSI_NULLS ACTIVÉ ACTIVÉ ACTIVÉ OFF
ANSI_PADDING ACTIVÉ ACTIVÉ ACTIVÉ OFF
ANSI_WARNINGS 1 ACTIVÉ ACTIVÉ ACTIVÉ OFF
ARITHABORT ACTIVÉ ACTIVÉ OFF OFF
CONCAT_NULL_YIELDS_NULL ACTIVÉ ACTIVÉ ACTIVÉ OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER ACTIVÉ ACTIVÉ ACTIVÉ OFF

1 L’affectation de la valeur ON à ANSI_WARNINGS affecte de manière implicite la valeur ON à ARITHABORT, quand le niveau de compatibilité de la base de données est défini au minimum sur 90. Si le niveau de compatibilité de la base de données est défini au maximum à 80, vous devez explicitement affecter la valeur ON à l’option ARITHABORT.

Si les options SET sont incorrectes, les conditions suivantes peuvent se vérifier :

  • L'index filtré n'est pas créé.

  • Le Moteur de base de données génère une erreur et restaure les instructions INSERT, UPDATE, DELETE ou MERGE qui changent les données dans l’index.

  • L’optimiseur de requête ne prend en compte l’index dans le plan d’exécution pour aucune instruction Transact-SQL.

Pour plus d’informations sur les index filtrés, consultez Créer des index filtrés.

Limitations et restrictions

Chaque colonne dans un index columnstore doit appartenir à l’un des types de données métier courants suivants :

  • 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 S’applique à SQL Server 2017 (14.x) et Azure SQL Database au niveau Premium, au niveau Standard (S3 et ultérieur) ainsi qu’à tous les niveaux d’offres vCore, dans les index columnstore cluster uniquement.

2 S’applique à SQL Server 2014 (12.x) et versions ultérieures.

Si la table sous-jacente a une colonne d’un type de données non pris en charge pour les index columnstore, vous devez omettre cette colonne de l’index columnstore non-cluster.

Les données LOB (Large Object) supérieures à 8 kilo-octets (Ko) sont stockées dans un stockage LOB hors ligne, avec simplement un pointeur vers l’emplacement physique stocké au sein du segment de colonne. La taille des données stockées n’est pas signalée dans sys.column_store_segments, sys.column_store_dictionaries ou sys.dm_db_column_store_row_group_physical_stats.

Les colonnes qui utilisent l’un des types de données suivants ne peuvent pas être incluses dans un index columnstore :

  • ntext, text et image
  • nvarchar(max), varchar(max), et varbinary(max) 1
  • rowversion (et timestamp)
  • sql_variant
  • Types CLR (hierarchyid et types spatiaux)
  • xml
  • uniqueidentifier 2

1 S’applique à SQL Server 2016 (13.x) et versions antérieures, ainsi qu’aux index columnstore non-cluster.

2 S’applique à SQL Server 2012 (11.x).

Index columnstore non-cluster :

  • Impossible d’avoir plus de 1 024 colonnes.
  • Ne peuvent pas être créés en tant qu’index basés sur des contraintes. Une table ayant un index columnstore peut avoir des contraintes uniques, des contraintes de clé primaire et des contraintes de clé étrangère. Les contraintes sont toujours appliquées avec un index rowstore. Les contraintes ne peuvent pas être appliquées avec un index columnstore (cluster ou non-cluster).
  • Ne peuvent pas inclure de colonne éparse.
  • Ne peuvent pas être changés à l’aide de l’instruction ALTER INDEX. Pour modifier l'index non cluster, vous devez plutôt supprimer et recréer l'index columnstore. Vous pouvez utiliser ALTER INDEX pour désactiver et reconstruire un index columnstore.
  • Ne peuvent pas être créés à l’aide du mot clé INCLUDE.
  • Ne peuvent 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. Dans Azure Synapse Analytics et à partir de SQL Server 2022 (16.x), vous pouvez spécifier un ordre pour les colonnes dans un index columnstore. Pour plus d’informations, consultez Optimisation des performances avec un index columnstore en cluster ordonné.
  • Impossible d’inclure des colonnes métier de type nvarchar(max), varchar(max) et varbinary(max) dans les index columnstore non cluster. Seuls les index columnstore cluster prennent en charge les types métier, à compter de la version de SQL Server 2017 (14.x), d’Azure SQL Database (configuré au niveau Premium, au niveau Standard (S3 et versions ultérieures) et de tous les niveaux d’offre vCore. Les versions antérieures ne prennent pas en charge les types métier dans les index columnstore cluster et non cluster.
  • À partir de SQL Server 2016 (13.x), vous pouvez créer un index columnstore non-cluster dans une vue indexée.

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

  • Colonnes calculées À compter de SQL Server 2017 (14.x), un index columnstore cluster peut contenir une colonne calculée non persistante. Toutefois, dans SQL Server 2017 (14.x), les index columnstore cluster ne peuvent pas contenir de colonnes calculées persistantes et vous ne pouvez pas créer d’index non cluster sur des colonnes calculées.
  • Compression de page et de ligne et format de stockage vardecimal . (Un index columnstore est déjà compressé dans un autre format.)
  • Réplication avec des index columnstore cluster. Les index columnstore non cluster sont pris en charge. Pour plus d’informations, consultez sp_addarticle.
  • Flux de fichier.

Vous ne pouvez pas utiliser de curseurs ou de déclencheurs sur une table avec un index columnstore cluster. Cette restriction ne s’applique pas aux index columnstore non-cluster. Vous ne pouvez pas utiliser de curseurs ni de déclencheurs sur une table avec un index columnstore non-cluster.

Limitations spécifiques à SQL Server 2014 (12.x) :

Les limitations suivantes s’appliquent uniquement à SQL Server 2014 (12.x). Dans cette version, vous pouvez utiliser des index columnstore cluster pouvant être mis à jour. Les index columnstore non-cluster sont toujours en lecture seule.

  • Suivi des modifications. Vous ne pouvez pas utiliser le suivi des changements avec les index columnstore.
  • Capture des modifications de données. Cette fonctionnalité ne peut pas être activée sur les tables ayant un index columnstore cluster. À compter de SQL Server 2016 (13.x), la capture de données modifiées peut être activée sur des tables avec un index columnstore non cluster.
  • Secondaire accessible en lecture. Vous ne pouvez pas accéder à un index columnstore cluster (CCI) à partir d’un secondaire accessible en lecture d’un groupe de disponibilité Always On lisible. Par contre, vous pouvez accéder à un index columnstore non-cluster (NCCI) à partir d’un secondaire accessible en lecture.
  • MARS (Multiple Active Result Sets). SQL Server 2014 (12.x) utilise cette fonctionnalité pour les connexions en lecture seule aux tables ayant un index columnstore. Toutefois, SQL Server 2014 (12.x) ne prend pas en charge cette fonctionnalité pour les opérations DML (langage de manipulation de données) simultanées sur une table ayant un index columnstore. Si vous essayez d’utiliser la fonctionnalité dans ce but, SQL Server met fin aux connexions et annule les transactions.
  • Les index columnstore non-cluster ne peuvent pas être créés dans une vue ou une vue indexée.

Pour plus d’informations sur les avantages en matière de performances et les limitations des index columnstore, consultez les index Columnstore : Vue d’ensemble.

Métadonnées

Toutes les colonnes dans un index columnstore sont stockées dans les métadonnées en tant que colonnes incluses. L'index columnstore n'a pas de colonnes clés. Les vues système suivantes fournissent des informations sur les index columnstore :

Exemples : Convertir une table rowstore en columnstore

R. Convertir un segment dans un index cluster columnstore

Cet exemple crée une table en tant que segment de mémoire, puis la convertit en index columnstore cluster nommé cci_Simple. La création de l’index columnstore cluster change le stockage de l’ensemble de la table de rowstore en 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. Convertir un index cluster en index columnstore cluster ayant le même nom

Cet exemple suivant crée une table avec un index cluster, puis illustre la syntaxe de conversion de l'index cluster en un index columnstore cluster. La création de l’index columnstore cluster change le stockage de l’ensemble de la table de rowstore en 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 Gérer les index non-cluster durant la conversion d’une table rowstore en index columnstore

Cet exemple montre comment gérer les index non-cluster quand vous convertissez une table rowstore en index columnstore. À partir de SQL Server 2016 (13.x), aucune action spéciale n’est nécessaire. SQL Server définit et recrée automatiquement les index non-cluster sur le nouvel index columnstore cluster.

Si vous souhaitez supprimer les index non cluster, utilisez l’instruction DROP INDEX avant de créer l’index columnstore. L’option DROP EXISTING supprime uniquement l’index cluster en cours de conversion. Les index non-cluster ne sont pas supprimés.

Dans SQL Server 2012 (11.x) et SQL Server 2014 (12.x), vous ne pouvez pas créer d’index non-cluster sur un index 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

Pour SQL Server 2012 (11.x) et SQL Server 2014 (12.x) uniquement, vous devez supprimer les index non-cluster afin de créer l’index 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. Convertir une table de faits volumineuse de rowstore en columnstore

Cet exemple explique comment convertir une table de faits volumineuse d'une table rowstore en une table columnstore.

  1. Créez une petite table pour cet exemple.

    --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. Supprimez tous les index non-cluster de la table rowstore. Vous pouvez être amené à écrire un script relatif aux index pour les recréer plus tard.

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

  3. Convertissez la table rowstore en une table columnstore avec un index columnstore cluster.

    Tout d’abord, recherchez le nom de l’index rowstore cluster existant. À l’étape 1, nous avons affecté au nom de l’index la valeur IDX_CL_MyFactTable. Si le nom d’index n’a pas été spécifié, un nom d’index unique généré est automatiquement attribué. Vous pouvez récupérer le nom généré automatiquement avec l’exemple de requête suivant :

    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';

    Option 1 : Supprimez l’index cluster existant IDX_CL_MyFactTable, et convertissez MyFactTable en columnstore. Changez le nom du nouvel index 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

    Option 2 : Effectuez la conversion en columnstore, et réutilisez le nom d’index cluster rowstore existant.

    --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. Convertir une table columnstore en une table rowstore avec un index cluster

Pour convertir une table columnstore en une table rowstore avec un index cluster, utilisez l'instruction CREATE INDEX avec l'option DROP_EXISTING.

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

F. Convertir une table columnstore en un segment de mémoire rowstore

Pour convertir une table columnstore en un segment de mémoire, supprimez l’index columnstore cluster. Cela n’est généralement pas recommandé, mais certains peuvent avoir des utilisations restreintes. Pour plus d’informations sur les segments de mémoire, consultez Segments de mémoire (tables sans index cluster).

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

G. Défragmenter en réorganisant l’index columnstore

Il existe deux façons de gérer l'index clustered columnstore. À partir de SQL Server 2016 (13.x), utilisez ALTER INDEX...REORGANIZEà la place de REBUILD. Pour plus d'informations, consultez Columnstore index rowgroup. Dans les versions précédentes de SQL Server, vous pouvez utiliser CREATE CLUSTERED COLUMNSTORE INDEX avec DROP_EXISTING=ON ou ALTER INDEX (Transact-SQL) et l’option REBUILD. Les deux méthodes ont permis d'obtenir le même résultat.

Commencez par déterminer le nom de l’index columnstore cluster dans 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';

Supprimez la fragmentation en effectuant une opération REORGANIZE sur l’index columnstore.

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

Exemples d’index columnstore non-cluster

R. Créer un index columnstore comme index secondaire sur une table rowstore

Cet exemple crée un index columnstore non-cluster sur une table rowstore. Un seul index columnstore peut être créé dans ce cas de figure. L’index columnstore nécessite un stockage supplémentaire, car il contient une copie des données dans la table rowstore. Cet exemple crée une table simple et un index cluster rowstore, puis montre la syntaxe de création d'un index 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. Créer un index columnstore non-cluster basique à l’aide de toutes les options

L’exemple suivant illustre la syntaxe de création d’un index columnstore non-cluster sur le groupe de fichiers DEFAULT, en spécifiant la valeur 2 pour le degré maximal de parallélisme (MAXDOP).

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

C. Créer un index columnstore non-cluster avec un prédicat filtré

L’exemple suivant crée un index columnstore non-cluster, filtré, sur la table Production.BillOfMaterials dans l’exemple de base de données AdventureWorks2022. Le prédicat de filtre peut inclure des colonnes qui ne sont pas des colonnes clés dans l’index filtré. Dans cet exemple, le prédicat sélectionne uniquement les lignes où EndDate n'est pas 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. Changer les données dans un index columnstore non-cluster

S’applique à : SQL Server 2012 (11.x) jusqu’à SQL Server 2014 (12.x).

Dans SQL Server 2014 (12.x) et les versions antérieures, après avoir créé un index columnstore non cluster sur une table, vous ne pouvez pas modifier directement les données de cette table. Une requête avec INSERT, UPDATE, DELETE ou MERGE échoue et retourne un message d’erreur. Voici les options que vous pouvez utiliser pour ajouter ou modifier les données dans la table :

  • Désactivez ou supprimez l'index columnstore. Vous pourrez ensuite mettre à jour les données de la table. Si vous désactivez l'index columnstore, vous pouvez le reconstruire lorsque vous avez fini de mettre à jour les données. Par exemple :

    ALTER INDEX mycolumnstoreindex ON dbo.mytable DISABLE;
-- update the data in mytable as necessary
ALTER INDEX mycolumnstoreindex on dbo.mytable REBUILD;

  • Chargez les données dans une table de mise en lots qui n’a pas d’index columnstore. Créez un index columnstore sur la table intermédiaire. Basculez la table intermédiaire dans une partition vide de la table principale.

  • Basculez une partition de la table avec l'index columnstore dans une table intermédiaire vide. S'il existe un index columnstore sur la table intermédiaire, désactivez-le. Effectuez toutes les mises à jour. Créez (ou reconstruisez) l'index columnstore. Rebasculez la table intermédiaire dans la partition (maintenant vide) de la table principale.

Exemples : Azure Synapse Analytics, Analytics Platform System (PDW)

R. Changer un index cluster en index cluster columnstore

En utilisant l’instruction CREATE CLUSTERED COLUMNSTORE INDEX avec DROP_EXISTING = ON, vous pouvez :

  • Changer un index cluster en index cluster columnstore.

  • Reconstruire un index cluster columnstore.

Cet exemple crée la table xDimProduct en tant que table rowstore avec un index cluster. L’exemple utilise ensuite CREATE CLUSTERED COLUMNSTORE INDEX pour changer la table rowstore en table columnstore.

-- 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;

Recherchez automatiquement le nom de l’index cluster créé pour la nouvelle table dans les métadonnées système, à l’aide de sys.indexes. Par exemple :

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';

Vous pouvez ensuite choisir l’une des options suivantes :

  1. Supprimez l’index columnstore cluster existant avec un nom créé automatiquement, puis créez un autre index columnstore cluster avec un nom défini par l’utilisateur.
  2. Supprimez et remplacez l’index existant par un index columnstore cluster, en gardant le même nom généré par le système, comme ClusteredIndex_1bd8af8797f7453182903cc68df48541.

Par exemple :

--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. Reconstruire un index cluster columnstore

S’appuyant sur l’exemple précédent, cet exemple utilise CREATE CLUSTERED COLUMNSTORE INDEX pour recréer l’index columnstore cluster existant, appelé cci_xDimProduct.

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

C. Changer le nom d’un index cluster columnstore

Pour changer le nom d’un index columnstore cluster, supprimez l’index columnstore cluster existant, puis recréez l’index avec un nouveau nom.

Nous vous recommandons de limiter cette opération à une petite table ou à une table vide. L’opération qui consiste à supprimer un index columnstore cluster volumineux, et à le recréer sous un autre nom est très longue.

Cet exemple référence l’index columnstore cluster cci_xDimProduct de l’exemple précédent. Cet exemple supprime l’index columnstore cluster cci_xDimProduct, puis recrée l’index columnstore cluster sous le nom 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. Convertir une table columnstore en une table rowstore avec un index cluster

Il peut arriver que vous souhaitiez supprimer un index columnstore cluster et créer un index cluster. Quand vous supprimez un index columnstore cluster, la table passe au format rowstore. Cet exemple convertit une table columnstore en table rowstore avec un index cluster du même nom. Aucune des données n’est perdue. Toutes les données sont envoyées à la table rowstore, et les colonnes répertoriées deviennent les colonnes clés de l’index 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. Reconvertir une table columnstore en segment rowstore

Utilisez DROP INDEX (SQL Server PDW) pour supprimer l’index columnstore cluster, puis convertir la table en segment de mémoire rowstore. Cet exemple convertit la table cci_xDimProduct en segment rowstore. La table continue d’être distribuée, mais est stockée en tant que segment.

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

F. Créer un index columnstore en cluster ordonné sur une table sans index

Un index columnstore non ordonné couvre toutes les colonnes par défaut, sans qu’il soit nécessaire de spécifier une liste de colonnes. Un index columnstore ordonné vous permet de spécifier l’ordre des colonnes. La liste n’a pas besoin d’inclure toutes les colonnes.

Les index columnstore ordonnés sont disponibles dans Azure Synapse Analytics, Analytics Platform System (PDW) et SQL Server 2022 (16.x). Pour plus d’informations, consultez Réglage des performances avec un index columnstore cluster ordonné.

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

G. Convertir un index columnstore en cluster en index columnstore en cluster ordonné

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

H. Ajouter une colonne au classement d’un index columnstore en cluster ordonné

Dans Azure Synapse Analytics, Analytics Platform System (PDW) et à compter de SQL Server 2022 (16.x), vous pouvez spécifier un ordre pour les colonnes dans un index columnstore. L’index columnstore cluster d’origine a été ordonné uniquement sur la colonne SHIPDATE. L’exemple suivant ajoute la PRODUCTKEY colonne à l’ordre.

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

I. Modifier l’ordinal de colonnes ordonnées

L’index columnstore cluster d’origine a été ordonné sur SHIPDATE, PRODUCTKEY. L’exemple suivant modifie l’ordre en PRODUCTKEY, SHIPDATE.

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

J. Créer un index columnstore en cluster ordonné

S’applique à : Azure Synapse Analytics et SQL Server 2022 (16.x)

Vous pouvez créer un index columnstore cluster avec des clés de tri. Lors de la création d’un index columnstore cluster ordonné, vous devez appliquer l’indicateur MAXDOP = 1 de requête pour une qualité maximale de tri et une durée la plus courte.

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

Contenu connexe