La clause VALUES n’est-elle réservée qu’à l’ordre INSERT ?

Publié le par

Si je pose la question, c’est que la réponse est … non, et ce depuis SQL Server 2008 !

En effet l’utilisation la plus commune de la clause VALUES se fait lors de la définition d’un ordre INSERT, et permettait jusqu’à SQL Server 2005 inclus d’insérer seulement une ligne dans une table.

Cela a changé lors de la sortie de SQL Server 2008, qui permet de réaliser un INSERT d’au plus 1000 lignes dont les valeurs sont codées « en dur » dans un script. Voyons les autres utilisations possible de cette clause …

En fait, cette clause est utilisable pour toute instruction DML, pour peu qu’on la précise dans la clause FROM d’une instruction SELECT, INSERT, UPDATE ou DELETE, et dans la clause USING de l’instruction MERGE.

Voici quelques exemples simples d’utilisation, qui éveilleront votre imagination dès que vous retournerez à vos claviers.
Commençons par appréhender la clause avec une requête triviale :

1
2
3
4
SELECT  *
FROM    (
                VALUES (-15), (0), (1), (2), (3)
        ) AS V(i)

Ce qui nous retourne le résultat suivant :

Si nous regardons le plan d’exécution à l’aide de SQL Sentry Plan Explorer, on voit que l’estimation de cardinalité se fait proprement :

Ceci évite d’écrire une requête avec de nombreux UNION ALL, comme suit :

1
2
3
4
5
6
7
8
SELECT  *
FROM    (
                SELECT -15
                UNION ALL SELECT 0
                UNION ALL SELECT 1
                UNION ALL SELECT 2
                UNION ALL SELECT 3
        ) AS V(i)

Même si l’estimation de cardinalité se fait de la même manière.

On peut donc spécifier un INSERT de plusieurs lignes à l’aide de la clause VALUES :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
DECLARE @pays_capitale TABLE
(
        pays varchar(15) NOT NULL
        , capitale varchar(32) NOT NULL
)

INSERT  INTO @pays_capitale
(
        pays
        , capitale
)
VALUES  ('France', 'Paris')
        , ('Thaïlande', 'Bangkok')
        , ('Bouthan', 'Thimphou')
        , ('Vanuatu', 'Port-Vila')
        , ('Singapour', 'Singapour')
        , ('Viêt Nam', 'Hanoï')
        , ('Cambodge', 'Phnom Penh')
        , ('Laos', 'Vientiane')
        , ('Malaisie', 'Kuala Lumpur')
        , ('Brunei', 'Bandar Seri Begawan')
        , ('Philippines', 'Manille')
        , ('Indonésie', 'Jakarta')
        , ('Birmanie', 'Naypyidaw')

SELECT  *
FROM    @pays_capitale

Nous obtenons le résultat suivant :

La démonstration des exemples suivants, nous utilisons le jeu de données défini par le lot de requêtes ci-dessous :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE TABLE capteur
(
        capteur_id tinyint NOT NULL IDENTITY (0, 1)
                CONSTRAINT PK_capteur PRIMARY KEY
        , capteur_nom varchar(15) NOT NULL
                CONSTRAINT UQ_capteur UNIQUE
        , capteur_date_ajout datetime
                NOT NULL CONSTRAINT DF_capteur__capteur_date_ajout DEFAULT (GETDATE())
)
GO

CREATE TABLE mesure
(
        mesure_id int NOT NULL IDENTITY(-2147483648, 1)
                CONSTRAINT PK_mesure PRIMARY KEY
        , capteur_id tinyint NOT NULL
                CONSTRAINT FK_mesure__capteur_id
                FOREIGN KEY (capteur_id) REFERENCES dbo.capteur
        , mesure_date_time datetime NOT NULL
                CONSTRAINT DF_mesure_mesure_date_time DEFAULT (GETDATE())
        , valeur tinyint NOT NULL
)
GO

INSERT INTO dbo.capteur
(
        capteur_nom
)
VALUES ('Capteur 1'), ('Capteur 2'), ('Capteur 3')
GO

INSERT INTO dbo.mesure
(
        capteur_id
        , valeur
)
SELECT  ABS(CHECKSUM(NEWID())) % 3
        , ABS(CHECKSUM(NEWID())) % 256
FROM    master.dbo.spt_values
WHERE   type = 'P'
GO 50

La vue master.dbo.spt_values retourne 2048 lignes qui ont pour type « P » : la table dbo.mesure contient donc 2048 * 50 = 102.400 lignes.

Étant donné que la clause se comporte de la même façon qu’une table dérivée, on peut tout à fait l’utiliser pour réaliser une jointure.
Essayons de classer les mesures par fourchettes :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT          BUCKET.low_bound
                , BUCKET.high_bound
                , COUNT(*) AS occurences
FROM            dbo.mesure AS M
INNER JOIN      (
                        VALUES (0, 5), (5, 10), (10, 15), (15, 20), (20, 30)
                                , (30, 40), (40, 50), (50, 100), (100, 200)
                                , (200, 1000)
                ) AS BUCKET(low_bound, high_bound)
                        ON M.valeur >= BUCKET.low_bound
                        AND M.valeur < BUCKET.high_bound
GROUP BY        BUCKET.low_bound, BUCKET.high_bound
ORDER BY        BUCKET.low_bound, BUCKET.high_bound

Ce qui nous retourne :

Voyons le cas d’un UPDATE :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH
        BUCKET (low_bound, high_bound) AS
        (
                SELECT  *
                FROM    (
                                VALUES (0, 5), (5, 10)
                                , (10, 15), (15, 20), (20, 30)
                        ) AS V(low_bound, high_bound)
        )
UPDATE          dbo.mesure
SET             valeur *= 2 -- multiplie par deux la valeur mesurée
FROM            dbo.mesure AS M
INNER JOIN      BUCKET AS B
                        ON B.low_bound  M.valeur

Notez que rien ici n’oblige à la construction d’une expression de table commune : on peut tout à fait spécifier le constructeur de valeurs de table dans la clause INNER JOIN, comme nous l’avons fait avec la requête précédente.

Si nous ré-exécutons la requête de classement par fourchettes, nous obtenons maintenant :

l’instruction DELETE se décrit de manière similaire :

1
2
3
4
5
6
7
8
DELETE FROM     dbo.mesure
FROM            dbo.mesure AS M
INNER JOIN      (
                        VALUES  (30, 40), (40, 50), (50, 100)
                                , (100, 200), (200, 1000)
                ) AS BUCKET (low_bound, high_bound)
                        ON M.valeur >= BUCKET.low_bound
                        AND M.valeur < BUCKET.high_bound

Après ré-exécution de la requête de distribution des valeurs par plages, on voit que la table des mesures contient maintenant :

Terminons par l’ordre MERGE, avec lequel nous allons ajouter des capteurs, ou marquer ceux qui existent déjà à la date courante :

1
2
3
4
5
6
7
8
9
10
11
12
MERGE   dbo.capteur AS TGT
USING   (
                VALUES ('Capteur 1'), ('Capteur 2'), ('Capteur 3')
                        , ('Capteur 4'), ('Capteur 5'), ('Capteur 6')
        ) AS SRC (capteur_nom)
                ON TGT.capteur_nom = SRC.capteur_nom
WHEN    MATCHED THEN UPDATE SET capteur_date_ajout = GETDATE()
WHEN    NOT MATCHED THEN INSERT (capteur_nom) VALUES (SRC.capteur_nom)
OUTPUT  $ACTION, DELETED.capteur_nom, INSERTED.capteur_nom;

SELECT  *
FROM    dbo.capteur

On voit bien les capteurs 4, 5 et 6 ajoutés, et les capteurs 1, 2 et 3, dont les lignes existaient déjà, modifiés. Tous les capteurs portent la même date d’ajout, transaction oblige.

Enfin on peut calculer le maximum d’un tuple de valeurs sans avoir à écrire une fonction pour ce faire. Soit la variable de type TABLE suivante :

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE @series TABLE
(
        id tinyint identity
        , a tinyint
        , b tinyint
        , c tinyint
)

INSERT  INTO @series
VALUES  (1, 2, 3)
        , (4, 5, 6)
        , (7, 8, 9)

Nous souhaitons obtenir la plus grande valeur des trois colonnes a, b et c, pour chaque ligne. On peut exprimer ce besoin comme suit :

1
2
3
4
5
6
7
8
SELECT  id
        , (
                SELECT  MAX(n)
                FROM    (
                                VALUES (a), (b), (c)
                        ) AS V(n)
        )
FROM    @series

Ce qui amène le résultat suivant :

Amusez-vous avec VALUES !

Laisser un commentaire