juin
2012
Assigner des variables et renvoyer un résultat en une seule opération.
Il vous arrive parfois de faire deux opérations SELECT équivalentes, la première servant à assigner une ou plusieurs valeurs à des variables (ou à une table) et la seconde à renvoyer un résultat ?
Voici une technique (SQL Server 2008 et +) pour faire ces deux choses en un seul query.
Situation :
Il n’est pas rare, lors d’une opération SELECT, de souhaiter pouvoir en exploiter le résultat pour en conserver certaines valeurs (dans des variables ou une table) et simultanément en renvoyer certaines valeurs (autres et/ou identiques).
Par exemple, si nous avons un query dont le résultat contient un nombre important d’éléments identiques provenant d’une seule (voire quelques) table seulement et afin de réduire la quantité de donnée renvoyée, il devient souhaitable de ne pas directement inclure ces éléments (sinon une clé pour les identifiés) dans notre query mais plutôt dans une nouvelle étape, un second query (ce principe est analogue au principe de la normalisation).
Moins de données mais plus de complexité.
En rédigeant le second query et afin de n’y récupérer que les éléments nécessaire, il peut devenir nécessaire d’y inclure un nombre conséquent de jointures, filtres, … qui étaient déjà présents dans le premier query. Nous amènerons donc probablement le second query à refaire tout un travail (ou partie de travail) qui vient pourtant d’être fait.
Solution :
La solution à ce problème est (encore) l’utilisation de MERGE et de la clause OUTPUT. L’idée est simple, MERGE va insérer dans une variable table les données à réutiliser dans une seconde étapes (les clés de certaines lignes par exemple) tout en renvoyant via sa clause OUTPUT la première partie du résultat dont nous avons besoin.
La solution
Un exemple solutionné :
Prenons un exemple qui par soucis de lisibilité sera volontairement trop simple que pour ne pas être trivial. Il n’y sera par exemple pas question d’exploiter des clés et des jointures. Je laisse à votre esprit créatif le soin d’extrapoler cet exemple et sa solution à des scénarios plus complexes et pertinents.
Dans cet exemple, nous allons d’abord renvoyer un ensemble de prénoms, métiers et âge tout en conservant ces prénoms (dans une table variable) pour ensuite renvoyer les prénoms ainsi conservés.
Note : Ces instructions ci-dessous n’ont besoin d’aucune table pour fonctionner, vous pouvez donc les exécuter directement.
DECLARE @tmp TABLE (
FirstName NVARCHAR(100)
)
MERGE INTO @tmp
USING (
VALUES
('Bob', 'Camioneur', 42)
, ('Roger', 'Sénateur', 62)
, ('Françoise', 'Alterophile', 18)
) AS Src (FirstName, Job, Age)
ON ( 1 = 2 )
WHEN NOT MATCHED THEN
INSERT ( FirstName )
VALUES ( Src.FirstName )
OUTPUT
Src.*
;
SELECT FirstName
FROM @tmp
Je ne pense pas présumer de vos capacités en concluant que vous aurez maintenant compris la solution général.
Conslusion :
Assigner des variables et renvoyer un résultat en une seule opération n’a rien de sorcier, il faut juste avoir à l’esprit que SELECT n’est pas la seule opération qui peut renvoyer un résultat et que MERGE qui non seulement le permet aussi, est justement un outil servant à conserver (insert/update) des données.
1 Commentaire + Ajouter un commentaire
Commentaires récents
- Et si on se passait des clés étrangères ? dans
- Quand les fonctions tables surpassent les vues et CTE. dans
- Quelques choses à savoir sur les espaces en fin de chaîne dans
- Assigner des variables et renvoyer un résultat en une seule opération dans
- Quelques choses à savoir sur les espaces en fin de chaîne dans
Voici un exemple plus « viril » qui peut vous aider à cerner les gains de performance que cette approche peut amener: