Lorsque vous travaillez avec SQL Server, il est important de comprendre les différents types d’index et comment ils peuvent affecter les performances. Dans cet article, nous explorerons un scénario spécifique impliquant des index de colonnes et des index btree clusterisés.

Récemment, lors de la mise à niveau vers SQL Server 2016 pour notre entrepôt de données, j’ai rencontré un problème intéressant. J’avais quelques tables avec des index de colonnes et un index btree clusterisé. Lorsque j’ai essayé de supprimer et de recréer l’index de colonnes, j’ai rencontré une erreur indiquant que l’optimiseur ne pouvait pas créer le plan d’exécution.

Au départ, je pensais que l’erreur était due à des contraintes de mémoire. Pour tester cette théorie, j’ai augmenté la mémoire sur le serveur, mais le problème persistait. Après une enquête plus approfondie, j’ai découvert que le problème était lié à la clé sur laquelle je créais l’index btree clusterisé. Cette clé incluait une colonne calculée.

Une fois que j’ai supprimé la colonne calculée de la clé, j’ai pu créer avec succès à la fois l’index de colonnes et l’index btree clusterisé sans aucun problème. Alternativement, je pouvais choisir de faire de l’index de colonnes l’index clusterisé sur SQL Server 2016. Cependant, si je voulais conserver la colonne calculée comme partie de la clé, je devrais créer l’index clusterisé en tant qu’index de colonnes, puis utiliser un index btree non clusterisé si nécessaire.

Examinons les étapes pour recréer l’erreur:

CREATE TABLE [dbo].[BillDate](
   [DateID]  AS (isnull((datepart(year,[date])*(10000)+datepart(month,[date])*(100))+datepart(day,[date]),(-1))) PERSISTED NOT NULL,
   [Date] [date] NULL,
   [DayOfWeekNumber] [int] NULL,
   [DayOfWeekName] [varchar](20) NULL
CONSTRAINT [PK_Date] PRIMARY KEY NONCLUSTERED
(
   [DateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCS_Billdate] ON [dbo].[Billdate]
(
   [Date],
   [DayOfWeekNumber],
   [DayOfWeekName]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]

CREATE CLUSTERED INDEX [pk_Billdate] ON BillDate(DateID)

Les étapes 2 ou 3 peuvent être inversées et de toute façon – la dernière étape donne une erreur comme ci-dessous:

Ce problème peut être résolu en:

1. Ne pas utiliser un champ calculé dans la clé primaire de l’index btree clusterisé ou de l’index de colonnes (les index de colonnes ne peuvent de toute façon pas inclure de colonnes calculées).
2. Créer l’index btree clusterisé en utilisant la colonne calculée et d’autres colonnes, en évitant complètement les index de colonnes.
3. Créer un index de colonnes non clusterisé sans inclure la colonne calculée et un index btree non clusterisé sur la colonne calculée.

En suivant ces étapes, vous pouvez éviter l’erreur et vous assurer de la création réussie de vos index.

Comprendre les subtilités des index SQL Server est crucial pour optimiser les performances de la base de données. En étant conscient des problèmes potentiels, tels que celui discuté dans cet article, vous pouvez prendre des décisions éclairées lors de la conception et de la gestion de votre base de données.