SQL Server 2005 et T-SQL
Les nouvelles fonctions T-SQL dans SQL Server 2005
Introduction
Alors que la version finale de SQL Server 2008 se profile à l'horizon, de nombreuses fonctions de SQL Server 2005 restent méconnue des développeurs SQL. L'occasion pour moi de les (re)découvrir avec vous.
CTE
SQL Server 2005 a introduit de nombreuses améliorations dans les instructions de DML. Une des nouveautés les plus excitantes sont les Common Table Expressions (CTE), qui offrent une manière très élégante de réaliser des requêtes récursives.
Une requête CTE récursive se compose de trois parties distinctes. La première requête est baptisée "anchor query". Elle permet d'initialiser la requête récursive et d'éviter qu'elle s'appelle elle-même et tombe dans une boucle infinie.
Dans notre exemple, qui se base sur une table de catégories hiérachisées, nous commençons donc par sélectionner la catégorie racine, c'est-à-dire celle qui n'a pas de parent.
La seconde requête, derrière l'instruction UNION ALL, est la requête récursive qui va être exécutée plusieurs fois, pour chaque enregistrement ajouté successivement dans la CTE.
Enfin, la requête finale va nous retourner le contenu de la CTE. Dans cet exemple, j'ai utilisé l'option MAXRECURSION qui permet de définir le nombre maximal de récursions autorisé :
PIVOT
Les utilisateurs d'Excel et même d'Access ont depuis longtemps l'habitude de travailler avec des tables pivots. Avant SQL Server 2005, les programmeurs SQL devaient utiliser les instructions UNION ou CASE, voire écrire des requêtes SQL dynamiques.
L'instruction PIVOT transforme les valeurs d'une colonne spécifiée en noms de colonnes, effectuant ainsi une rotation des données. Vous pouvez utiliser l'instruction PIVOT en combinaison avec les fonctions d'aggrégation classiques comme SUM, COUNT, MIN, MAX, AVG...
Dans l'exemple ci-dessus, j'ai commencé par sélectionner tous les types de documents présents dans ma table à l'aide de leur extension.
La fonction PIVOT effectue ensuite un COUNT sur la colonne Ext et limite le résultat à quatre types de fichiers.
Ce qui nous donne :
TOP
La fonction TOP n'a guère évolué dans SQL Server 2005, mais elle autorise désormais des variables ou des ordres SQL comme paramètres.
La requêtes suivante équivaut à effectuer un TOP 50 PERCENT :
INTERSECT & EXCEPT
En plus des opérateurs UNION et UNION ALL, SQL Server 2005 a introduit deux nouveaux opérateurs d'ensemble. Leur fonction se passe de commentaire :
CROSS APPLY
Comme l'opérateur JOIN, l'opérateur CROSS APPLY prend les tables qui se trouvent des deux côtés de l'opérateur. Cet opérateur permet que toutes les colonnes de la table de gauche soient utilisées comme paramètres de la fonction "table-valued" (TVF) de droite.
Dans l'exemple ci-dessous, une fonction TVF retourne le carré et le cube du nombre entier qui lui est passé en argument :
VARCHAR(MAX)
Les types de données VARCHAR(MAX), NVARCHAR(MAX) et VARBINARY(MAX) remplacent complètement les types TEXT, NTEXT et IMAGE de SQL Server 2000. Ceux-ci sont appelés à disparaître dans les futures versions de SQL Server.
Contrairement aux types qu'ils remplacent, ces nouveaux venus supportent toutes les fonctions applicables aux types CHAR et VARCHAR normaux. Vous pouvez également utiliser la nouvelle méthode WRITE pour accélérer la mise à jour des colonnes de ce type :
Introduction
Alors que la version finale de SQL Server 2008 se profile à l'horizon, de nombreuses fonctions de SQL Server 2005 restent méconnue des développeurs SQL. L'occasion pour moi de les (re)découvrir avec vous.
CTE
SQL Server 2005 a introduit de nombreuses améliorations dans les instructions de DML. Une des nouveautés les plus excitantes sont les Common Table Expressions (CTE), qui offrent une manière très élégante de réaliser des requêtes récursives.
Une requête CTE récursive se compose de trois parties distinctes. La première requête est baptisée "anchor query". Elle permet d'initialiser la requête récursive et d'éviter qu'elle s'appelle elle-même et tombe dans une boucle infinie.
Dans notre exemple, qui se base sur une table de catégories hiérachisées, nous commençons donc par sélectionner la catégorie racine, c'est-à-dire celle qui n'a pas de parent.
WITH CategoryStructure AS
(
SELECT
CategoryId,
ParentCategoryId,
DisplayName
FROM
Category
WHERE
ParentCategoryID IS NULL
UNION ALL
SELECT
c.CategoryId,
c.ParentCategoryId,
c.DisplayName
FROM
Category c
INNER JOIN
CategoryStructure p
ON
c.ParentCategoryID = p.CategoryId
)
SELECT
DisplayName
FROM
CategoryStructure
OPTION (MAXRECURSION 10)
La seconde requête, derrière l'instruction UNION ALL, est la requête récursive qui va être exécutée plusieurs fois, pour chaque enregistrement ajouté successivement dans la CTE.
Enfin, la requête finale va nous retourner le contenu de la CTE. Dans cet exemple, j'ai utilisé l'option MAXRECURSION qui permet de définir le nombre maximal de récursions autorisé :
DisplayName
----------------------
Logiciels bureautiques
Traitements de texte
Présentation
Tableurs
Base de données
Paradox
Lotus 123
Excel
Ami Pro
Word
(10 row(s) affected)
PIVOT
Les utilisateurs d'Excel et même d'Access ont depuis longtemps l'habitude de travailler avec des tables pivots. Avant SQL Server 2005, les programmeurs SQL devaient utiliser les instructions UNION ou CASE, voire écrire des requêtes SQL dynamiques.
L'instruction PIVOT transforme les valeurs d'une colonne spécifiée en noms de colonnes, effectuant ainsi une rotation des données. Vous pouvez utiliser l'instruction PIVOT en combinaison avec les fonctions d'aggrégation classiques comme SUM, COUNT, MIN, MAX, AVG...
SELECT [doc],[pdf], [jpg],[gif]
FROM
(
SELECT
Ext
FROM
BinaryContent
) p
PIVOT
(
COUNT(Ext)
FOR Ext
IN ( [doc],[pdf],[jpg],[gif] )
) AS Pvt
Dans l'exemple ci-dessus, j'ai commencé par sélectionner tous les types de documents présents dans ma table à l'aide de leur extension.
La fonction PIVOT effectue ensuite un COUNT sur la colonne Ext et limite le résultat à quatre types de fichiers.
Ce qui nous donne :
doc pdf jpg gif
----------- ----------- ----------- -----------
10 4 21 5
(1 row(s) affected)
TOP
La fonction TOP n'a guère évolué dans SQL Server 2005, mais elle autorise désormais des variables ou des ordres SQL comme paramètres.
La requêtes suivante équivaut à effectuer un TOP 50 PERCENT :
SELECT
top (SELECT COUNT(*)/2 FROM category) DisplayName
FROM
Category
DisplayName
----------------------
Lotus 123
Traitements de texte
Présentation
Excel
Logiciels bureautiques
(5 row(s) affected)
INTERSECT & EXCEPT
En plus des opérateurs UNION et UNION ALL, SQL Server 2005 a introduit deux nouveaux opérateurs d'ensemble. Leur fonction se passe de commentaire :
SELECT
SmallIcon
FROM
MimeType
WHERE
MimeType LIKE 'application%'
EXCEPT
SELECT
SmallIcon
FROM
MimeType
WHERE
MimeType LIKE '%pdf%'
SmallIcon
--------------------------------------------------
../images/mimetypes/excel16.gif
../images/mimetypes/powerpoint16.gif
../images/mimetypes/word16.gif
../images/mimetypes/zip16.gif
(4 row(s) affected)
SELECT
SmallIcon
FROM
MimeType
WHERE
MimeType LIKE 'application%'
INTERSECT
SELECT
SmallIcon
FROM
MimeType
WHERE
MimeType like '%pdf%'
SmallIcon
--------------------------------------------------
../images/mimetypes/pdf16.gif
(1 row(s) affected)
CROSS APPLY
Comme l'opérateur JOIN, l'opérateur CROSS APPLY prend les tables qui se trouvent des deux côtés de l'opérateur. Cet opérateur permet que toutes les colonnes de la table de gauche soient utilisées comme paramètres de la fonction "table-valued" (TVF) de droite.
Dans l'exemple ci-dessous, une fonction TVF retourne le carré et le cube du nombre entier qui lui est passé en argument :
CREATE FUNCTION dbo.fnPowers (@Num INT)
RETURNS TABLE
AS
RETURN
(
SELECT @Num * @Num AS Squared_Result,
@Num * @Num * @Num AS Cubed_Result
);
GO
CREATE TABLE #Numbers (Number INT NOT NULL PRIMARY KEY);
INSERT INTO #Numbers (Number) VALUES (1);
INSERT INTO #Numbers (Number) VALUES (2);
INSERT INTO #Numbers (Number) VALUES (3);
SELECT n.Number,
s.Squared_Result,
s.Cubed_Result
FROM
#Numbers n
CROSS APPLY
dbo.fnPowers (n.Number) s;
GO
DROP FUNCTION dbo.fnPowers;
DROP TABLE #Numbers;
Number Squared_Result Cubed_Result
----------- -------------- ------------
1 1 1
2 4 8
3 9 27
(3 row(s) affected)
VARCHAR(MAX)
Les types de données VARCHAR(MAX), NVARCHAR(MAX) et VARBINARY(MAX) remplacent complètement les types TEXT, NTEXT et IMAGE de SQL Server 2000. Ceux-ci sont appelés à disparaître dans les futures versions de SQL Server.
Contrairement aux types qu'ils remplacent, ces nouveaux venus supportent toutes les fonctions applicables aux types CHAR et VARCHAR normaux. Vous pouvez également utiliser la nouvelle méthode WRITE pour accélérer la mise à jour des colonnes de ce type :
CREATE TABLE #Articles (
[Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Body] NVARCHAR(MAX) NOT NULL
);
INSERT INTO
#Articles (Body)
VALUES (N'Fabrice Kauffmann''s Blog');
SELECT
Body
FROM
#Articles
UPDATE
#Articles
SET
Body.Write(N' What''s new in SQL Server?',24,25);
SELECT
Body
FROM
#Articles;
DROP TABLE #Articles;
Body
--------------------------------------------------
Fabrice Kauffmann's Blog
(1 row(s) affected)
Body
--------------------------------------------------
Fabrice Kauffmann's Blog What's new in SQL Server?
(1 row(s) affected)
Commentaires