Génération d’un calendrier

Fabriquer des milliers de lignes sans en écrire une seule : la table de nombres.

≈ 3 min de lectureLecture

Sur cette page

Énoncé
Jeu de données
Procédural contre ensembliste
1. Procédural
2. Ensembliste
Résultat attendu
Pourquoi l'ensembliste est préférable
Pièges
Variantes
Vérifier l'équivalence
Versions de SQL Server

Énoncé#

Remplir dbo.Calendrier avec un jour par ligne, du 1^er janvier 2020 au 31 décembre 2029, avec l'année, le mois et le jour de la semaine (norme ISO).

Jeu de données#

CREATE TABLE dbo.Calendrier
(
    Jour    date     NOT NULL PRIMARY KEY,
    Annee   smallint NOT NULL,
    Mois    tinyint  NOT NULL,
    JourISO tinyint  NOT NULL      -- 1 = lundi … 7 = dimanche
);

Procédural contre ensembliste#

Procédural#

DECLARE @Jour date = '2020-01-01';

WHILE @Jour <= '2029-12-31'
BEGIN
    INSERT INTO dbo.Calendrier (Jour, Annee, Mois, JourISO)
    VALUES (@Jour,
            YEAR(@Jour),
            MONTH(@Jour),
            (DATEDIFF(DAY, CAST('19000101' AS date), @Jour) % 7) + 1);

    SET @Jour = DATEADD(DAY, 1, @Jour);
END;

Ensembliste#

WITH N AS
(
    SELECT TOP (3653)
           CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS int) AS n
    FROM   sys.all_columns AS a
    CROSS JOIN sys.all_columns AS b
)
INSERT INTO dbo.Calendrier (Jour, Annee, Mois, JourISO)
SELECT d.Jour,
       YEAR(d.Jour),
       MONTH(d.Jour),
       (DATEDIFF(DAY, CAST('19000101' AS date), d.Jour) % 7) + 1
FROM   N
CROSS APPLY (SELECT DATEADD(DAY, N.n, CAST('2020-01-01' AS date)) AS Jour) AS d;

Résultat attendu#

3 653 lignes, du 1^er janvier 2020 au 31 décembre 2029 (dix ans, dont trois années bissextiles : 2020, 2024, 2028).

Pourquoi l'ensembliste est préférable#

Une table de nombres obtenue par produit cartésien fournit les 3 653 entiers sans boucle ; CROSS APPLY transforme chacun en date.
La version procédurale exécute 3 653 insertions, chacune validée séparément si aucune transaction n'est ouverte.
Le calendrier est typiquement une table créée une fois : le gain absolu est faible ici, mais le motif sert à générer de grandes séries (jeux de test, plages horaires, trames).

Pièges#

Jour de la semaine : DATEPART(weekday, …) dépend de SET DATEFIRST et DATENAME de SET LANGUAGE. Le calcul par DATEDIFF depuis le 1^er janvier 1900 (un lundi) est indépendant des paramètres de session : (DATEDIFF(DAY, '19000101', Jour) % 7) + 1 donne 1 pour lundi.
Taille de la table de nombres : le produit cartésien de sys.all_columns avec lui-même fournit largement 3 653 lignes ; pour des séries plus grandes, ajouter un troisième CROSS JOIN.
Récursion : générer des dates avec une CTE récursive fonctionne, mais niveau par niveau et limitée par MAXRECURSION (valeur par défaut 100) ; ce n'est pas la meilleure option pour de grandes séries.

Variantes#

-- SQL Server 2022 et Azure SQL : GENERATE_SERIES
INSERT INTO dbo.Calendrier (Jour, Annee, Mois, JourISO)
SELECT d.Jour, YEAR(d.Jour), MONTH(d.Jour), (DATEDIFF(DAY, CAST('19000101' AS date), d.Jour) % 7) + 1
FROM   GENERATE_SERIES(0, 3652) AS g
CROSS APPLY (SELECT DATEADD(DAY, g.value, CAST('2020-01-01' AS date)) AS Jour) AS d;

Vérifier l'équivalence#

Un changement de méthode ne vaut que si le résultat est identique. Comparer dans les deux sens ; aucune ligne ne doit sortir.

SELECT COUNT(*) AS NbJours, MIN(Jour) AS Premier, MAX(Jour) AS Dernier FROM dbo.Calendrier;
-- Attendu : 3653, 2020-01-01, 2029-12-31

-- Aucun trou : chaque jour est exactement 1 jour après le précédent
SELECT Jour FROM (SELECT Jour, LAG(Jour) OVER (ORDER BY Jour) AS Prec FROM dbo.Calendrier) AS x
WHERE Prec IS NOT NULL AND DATEDIFF(DAY, Prec, Jour) <> 1;
-- Attendu : aucune ligne.

Versions de SQL Server#

Table de nombres par produit cartésien : toutes versions. GENERATE_SERIES : SQL Server 2022 et Azure SQL.