Les colonnes calculées persistantes... Une bonne idée ?

Bonsoir !

Les colonnes calculées sont supportées par SQL Server depuis la version 7... En revanche, SQL Server 2005 permet désormais de rendre ces colonnes persistantes.

En effet, jusque là, les colonnes calculées étaient des colonnes virtuelles, c'est à dire que ces colonnes n'étaient pas écrites sur le disque. Le fait de pouvoir rendre une colonne calculée persistante, c'est le fait de rendre cette colonne physique, et donc de stocker les données inhérentes à cette colonne sur le disque de données.

SQL Server 2000 avait déjà apporté la possibilité de créer des index sur les colonnes calculées, permettant ainsi d'améliorer grandement les performances de certaines applications, en particulier dans le domaine de la consolidation de données. L'exemple type qui reste gravé dans la mémoire collective est la consolidation du chiffre d'affaire mois par mois.
En effet, examinons la table suivante :

CREATE TABLE dbo.Vente
(
VenteID INT IDENTITY (1, 1) NOT NULL
CONSTRAINT PK_Vente PRIMARY KEY CLUSTERED,
DateVente SMALLDATETIME NOT NULL,
MontantVente MONEY NOT NULL
)

Une requête pour consolider ces résultats sur le mois de janvier serait :

SELECT
SUM(MontantVente)
FROM
dbo.Vente
WHERE
DATEPART(MONTH, DateVente) = 1

Cependant, si la volumétrie est importante, cette requête peut rapidement s'avérer très coûteuse. Si la solution d'indexer la colonne DateVente n'apporte rien dans notre cas, il peut être très utile d'employer une colonne calculée :

ALTER TABLE dbo.Vente
ADD MoisVente AS DATEPART(MONTH, DateVente)

Cette colonne n'utilisant qu'une fonction déterministe, il est possible d'y ajouter un index. A cet effet, un certain nombre d'options doivent être correctement positionnées :

SET ANSI_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF

Puis, il est possible de créer l'index :

CREATE NONCLUSTERED INDEX IX_MoisVente ON dbo.Vente
(
MoisVente
)

Avant d'effectuer notre requête optimisée :

SELECT
SUM(MontantVente)
FROM
dbo.Vente
WHERE
MoisVente = 1

Le résultat est assez probant, et peut s'avérer extrêmement intéressant.
La question qui se pose donc est ce que peut bien apporter la persistance d'une telle colonne calculée. En effet, l'argument régulièrement évoqué de l'indexation de la colonne semble bien tomber à l'eau... Quant aux conditions d'utilisation des index sur les colonnes calculées, elles s'avèrent identiques que la valeur soit persistée ou non, à l'exception du déterminisme de la fonction employée...

Pourquoi donc encombrer les disques de données visiblement inutiles ?
La première piste serait donc l'utilisation de fonctions non déterministes, ou dont il serait difficile de savoir si elles le sont, en particulier en ce qui concerne les fonctions CLR.

Mais il faut également se tourner vers des cas plus gourmands en calculs : la persistance prend tout son intérêt lors de calculs complexes nécessitant des ressources CPU importantes, en particulier lorsque la volumétrie de modifications est faible.
Dans ces cas là, le calcul à la volée de notre colonne entraine nécessairement un ralentissement du système, et justifie la persistance de ce calcul.

Soit, voici donc les applications de ces colonnes calculées persistantes...