Avant SQL Server 2008, écrire une requête comportant un prédicat sur une date bien précise sans notion d’heures était plutôt fastidieux pour des requêtes sur des dates de transactions de stock par exemple où l’on désire un résultat à la journée . (Je prends l’exemple simple WHERE date =  ‘20091001’). Dans ce cas là il fallait procéder à une transformation de la colonne de type DATETIME. Cependant, le nettoyage ou suppression des heures de cette colonne pouvait poser un inconvénient majeur car cela nécessitait un transtypage (je pense aux fonctions CAST et FLOOR qui donnent le résultat 20090101 00:00:00 par exemple). Hors le seul fait de transtyper une valeur de colonne empêche l’optimiseur de  requêtes d’utiliser un index associé à cette même colonne. Cela obligeait par conséquent à écrire cette même requête avec un prédicat de date par intervalles (avec BETWEEN date AND date .).

Heureusement des nouveaux types de données DATE et TIME ont fait leur apparition avec SQL Server 2008 et il est maintenant possible de transtyper un type de données DATETIME en un autre type de données DATE en extrayant la valeur de date sans les heures. Ce nouveau transtypage est d’autant plus intéressant car dans les prédicats de requêtes où l’on doit exploiter seulement la partie date d’une colonne DATETIME l’optimiseur de requêtes peut maintenant profiter d’un index candidat associé à cette même colonne et ceci malgré la présence du transtypage.

Vérifions ceci par un exemple :

Une table de transactions de stock avec un type de mouvement (entrée, sortie), le code article concerné, la quantité et sa date de la transaction.

РCr̩ation table des transactions de stocks
CREATE TABLE dbo.transaction_stocks
(
id INT IDENTITY(1,1) NOT NULL,
mvt CHAR(1) NOT NULL,
code_piece VARCHAR(10) NOT NULL,
qte INT NOT NULL,
date_tran DATETIME NOT NULL
);

– Population de la table des transactions de stocks
Рavec des donn̩es
DECLARE @i INT;
SET @i = 0;

WHILE @i < 10000
BEGIN
IF @i < 5000
  INSERT dbo.transaction_stocks (mvt,code_piece,qte,date_tran) VALUES (‘E’,’ME1001′,CASE WHEN @i – RAND() * 1000 < 0 THEN 0 ELSE @i – RAND() * 1000 END,DATEADD(mm,- FLOOR(RAND() * 100),GETDATE()));
ELSE
  INSERT dbo.transaction_stocks (mvt,code_piece,qte,date_tran) VALUES (‘S’,’ME1001′,CASE WHEN RAND() * 1000 – @i > 0 THEN 0 ELSE RAND() * 1000 – @i END,DATEADD(mm,- FLOOR(RAND() * 100),GETDATE()));

SET @i = @i + 1;
END;

SET @i = 0;

WHILE @i < 200
BEGIN
UPDATE dbo.transaction_stocks
SET date_tran = DATEADD(hh,- RAND() * 10,’20090101 23:59:00′) 
WHERE id = @i;
SET @i = @i + 1;
END;

– Création d’un index cluster sur la date de transaction
CREATE CLUSTERED INDEX IDX_date_tran
ON dbo.transaction_stocks
(
date_tran
);

Maintenant écrivons la requête qui permet de récupérer les transactions de stocks pour la journée du 1er janvier janvier 2009 (ce qui sous entend toutes les transactions entre le 29 janvier 2009 00:00:00 et le 29 janvier 2009 23:59:00 inclus).

1ère solution : Suppression des heures de la colonne date_tran avec les fonctions CAST et FLOOR

SELECT * FROM dbo.transaction_stocks
WHERE CAST(FLOOR(CAST(date_tran AS DECIMAL(15,4))) AS DATETIME) = ‘2009-01-01′

Le plan d’exécution est le suivant :

On voit que l’optimiseur de requêtes ne profite pas de l’index poser sur la table dbo.transaction_stocks du fait du transtypage présent dans le prédicat de la requête.

2ème solution : Ecriture dut prédicat en se servant de la notion d’intervalle avec l’opérateur BETWEEN

SELECT * FROM dbo.transaction_stocks
WHERE date_tran BETWEEN ‘2009-01-01′ AND ‘2009-01-01 23:59:59′

Le plan d’exécution est le suivant :

Ici la réécriture de la requête avec l’opérateur BETWEEN permet à l’optimiseur de requêtes de profiter de l’index associée à la colonne date_tran.

Dernière solution : Avec le nouveau transtypage DATETIME en DATE

SELECT * FROM dbo.transaction_stocks
WHERE CAST(date_tran AS DATE) = ‘2009-01-01′

Le plan d’exécution est le suivant :

On voit ici que la requête utilise également une recherche d’index. On constate également que d’autres opérateurs de plan ont fait leur apparition (Opérateurs Constant Scan et Compute Scalar). Pour pouvoir utiliser l’index sur la colonne date_tran, SQL Server effectue d’abord une conversion de la valeur de notre argument ‘2009-01-01′ en intervalle de date ‘2009-01-01 00:00:00′ et ‘2009-01-02 00:00:00′ et associe à cet intervalle une valeur de clé pour pouvoir faire une recherche dans l’index par la suite.

Le plan d’exécution en mode texte le confirme : On voit la définition des valeurs de l’intervalle avec l’opérateur computer scalar et la recherche par intervalle de dates avec ces valeurs au niveau de la recherche par index (Clustered Index seek …)

|–Nested Loops(Inner Join, OUTER REFERENCES:([Expr1007], [Expr1008], [Expr1006]))

|–Compute Scalar(DEFINE:(([Expr1007],[Expr1008],[Expr1006])=GetRangeThroughConvert(CONVERT_IMPLICIT(date,[@1],0),CONVERT_IMPLICIT(date,[@1],0),(62))))

|    |-Constant Scan

|–Clustered Index Seek(OBJECT:([TK432].[dbo].[transaction_stocks].[IDX_date_tran]), SEEK:([TK432].[dbo].[transaction_stocks].[date_tran] > [Expr1007] AND [TK432].[dbo].[transaction_stocks].[date_tran] < [Expr1008]),  WHERE:(CONVERT(date,[TK432].[dbo].[transaction_stocks].[date_tran],0)=CONVERT_IMPLICIT(date,[@1],0)) ORDERED FORWARD)

En d’autres termes cela revient à exécuter la requête suivante :

SELECT * FROM dbo.transaction_stocks
WHERE date_tran > ‘2009-01-01′ AND date_tran < ‘2009-01-02 00:00:00′

. qui reste une requête par intervalle de dates.

Pour résumé, avec SQL Server 2008 vous pouvez rendre votre requête « sargable » même avec un transtypage d’un type DATETIME en DATE sur une recherche pour une date donnée, ce qui constitue une avancée au niveau de l’écriture de la requête même si on peut relativiser .

Bonne recherche !!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon