Mon bon ami Aaron Bertrand m’a inspiré pour écrire cet article. Il m’a rappelé à quel point nous prenons parfois les choses pour acquises quand elles nous semblent évidentes et que nous ne prenons pas toujours la peine de vérifier toute l’histoire derrière elles. La pertinence pour T-SQL est que parfois nous supposons que nous savons tout ce qu’il y a à savoir sur certaines fonctionnalités de T-SQL, et que nous ne prenons pas toujours la peine de vérifier la documentation pour voir s’il y en a plus. Dans cet article, je couvre un certain nombre de fonctionnalités T-SQL qui sont souvent entièrement négligées, ou qui prennent en charge des paramètres ou des capacités souvent négligés. Si vous avez des exemples de vos propres gemmes T-SQL qui sont souvent négligés, veuillez les partager dans la section commentaires de cet article.

Avant de commencer à lire cet article, demandez-vous ce que vous savez sur les fonctionnalités T-SQL suivantes : EOMONTH, TRANSLATE, TRIM, CONCAT et CONCAT_WS, LOG, variables de curseur et FUSIONNER avec la SORTIE.

Dans mes exemples, j’utiliserai un exemple de base de données appelée TSQLV5. Vous pouvez trouver le script qui crée et remplit cette base de données ici, et son diagramme ER ici.

EOMONTH a un deuxième paramètre

La fonction EOMONTH a été introduite dans SQL Server 2012. Beaucoup de gens pensent qu’il ne prend en charge qu’un seul paramètre contenant une date d’entrée et qu’il renvoie simplement la date de fin de mois qui correspond à la date d’entrée.

Considérez un besoin un peu plus sophistiqué de calculer la fin du mois précédent. Par exemple, supposons que vous deviez interroger les ventes.Tableau des commandes, et les commandes de retour qui ont été passées à la fin du mois précédent.

Une façon d’y parvenir consiste à appliquer la fonction EOMONTH à SYSDATETIME pour obtenir la date de fin de mois du mois en cours, puis à appliquer la fonction DATEADD pour soustraire un mois du résultat, comme ceci :

USE TSQLV5; SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(DATEADD(month, -1, SYSDATETIME()));

EOMONTH ( start_date )

SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(SYSDATETIME(), -1);

TRADUIRE est parfois plus simple que REMPLACER

Beaucoup de gens connaissent la fonction de REMPLACEMENT et son fonctionnement. Vous l’utilisez lorsque vous souhaitez remplacer toutes les occurrences d’une sous-chaîne par une autre dans une chaîne d’entrée. Parfois, cependant, lorsque vous avez plusieurs remplacements à appliquer, l’utilisation de REMPLACER est un peu délicate et entraîne des expressions alambiquées.

Par exemple, supposons qu’on vous donne une chaîne d’entrée @s qui contient un nombre avec une mise en forme espagnole. En Espagne, ils utilisent un point comme séparateur pour les groupes de milliers et une virgule comme séparateur décimal. Vous devez convertir l’entrée au format US, où une virgule est utilisée comme séparateur pour les groupes de milliers et un point comme séparateur décimal.

En utilisant un appel à la fonction REPLACE, vous ne pouvez remplacer que toutes les occurrences d’un caractère ou d’une sous-chaîne par une autre. Pour appliquer deux remplacements (périodes aux virgules et virgules aux périodes), vous devez imbriquer les appels de fonction. La partie délicate est que si vous utilisez REPLACE une fois pour changer les périodes en virgules, puis une deuxième fois contre le résultat pour changer les virgules en périodes, vous vous retrouvez avec seulement des périodes. Essayez-le :

DECLARE @s AS VARCHAR(20) = '123.456.789,00'; SELECT REPLACE(REPLACE(@s, '.', ','), ',', '.');

Vous obtenez la sortie suivante:

123.456.789.00

Si vous souhaitez utiliser la fonction REMPLACER, vous avez besoin de trois appels de fonction. Un pour remplacer les points par un caractère neutre que vous connaissez et qui ne peut normalement pas apparaître dans les données (par exemple, ~). Un autre contre le résultat pour remplacer toutes les virgules par des points. Un autre contre le résultat pour remplacer toutes les occurrences du caractère temporaire (~ dans notre exemple) par des virgules. Voici l’expression complète:

DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT REPLACE(REPLACE(REPLACE(@s, '.', '~'), ',', '.'), '~', ',');

Cette fois, vous obtenez la bonne sortie:

123,456,789.00

C’est un peu faisable, mais cela donne une expression longue et alambiquée. Et si vous aviez plus de remplacements à postuler?

Beaucoup de gens ne savent pas que SQL Server 2017 a introduit une nouvelle fonction appelée TRANSLATE qui simplifie beaucoup ces remplacements. Voici la syntaxe de la fonction:

TRANSLATE ( inputString, characters, translations )

La deuxième entrée (caractères) est une chaîne avec la liste des caractères individuels que vous souhaitez remplacer, et la troisième entrée (traductions) est une chaîne avec la liste des caractères correspondants avec lesquels vous souhaitez remplacer les caractères source. Cela signifie naturellement que les deuxième et troisième paramètres doivent avoir le même nombre de caractères. Ce qui est important à propos de la fonction, c’est qu’elle ne fait pas de passes séparées pour chacun des remplacements. Si c’était le cas, cela aurait potentiellement entraîné le même bogue que dans le premier exemple que j’ai montré en utilisant les deux appels à la fonction DE REMPLACEMENT. Par conséquent, la gestion de notre tâche devient une évidence :

DECLARE @s AS VARCHAR(20) = '123.456.789,00';SELECT TRANSLATE(@s, '.,', ',.');

Ce code génère la sortie souhaitée:

123,456,789.00

C’est assez soigné!

TRIM est plus que LTRIM(RTRIM())

SQL Server 2017 a introduit le support de la fonction TRIM. Beaucoup de gens, y compris moi-même, supposent au départ que ce n’est qu’un simple raccourci vers LTRIM(RTRIM(input)). Cependant, si vous consultez la documentation, vous vous rendez compte qu’elle est en fait plus puissante que cela.

Avant d’entrer dans les détails, considérez la tâche suivante: étant donné une chaîne d’entrée @s, supprimez les barres obliques de début et de fin (arrière et avant). Par exemple, supposons que @s contienne la chaîne suivante:

//\\ remove leading and trailing backward (\) and forward (/) slashes \\//

La sortie souhaitée est :

 remove leading and trailing backward (\) and forward (/) slashes 

Notez que la sortie doit conserver les espaces de début et de fin.

Si vous ne connaissiez pas toutes les capacités de TRIM, voici une façon de résoudre la tâche :

DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRANSLATE(TRIM(TRANSLATE(TRIM(TRANSLATE(@s, ' /', '~ ')), ' \', '^ ')), ' ^~', '\/ ') AS outputstring;

La solution commence par utiliser TRANSLATE to remplacez tous les espaces par un caractère neutre (~) et des barres obliques avec des espaces, puis utilisez TRIM pour couper les espaces de début et de fin du résultat. Cette étape coupe essentiellement les barres obliques avant et arrière, en utilisant temporairement ~ au lieu des espaces d’origine. Voici le résultat de cette étape:

\\~remove~leading~and~trailing~backward~(\)~and~forward~( )~slashes~\\

La deuxième étape utilise ensuite TRANSLATE pour remplacer tous les espaces par un autre caractère neutre (^) et des barres obliques vers l’arrière avec des espaces, puis en utilisant TRIM pour couper les espaces de début et de fin du résultat. Cette étape coupe essentiellement les barres obliques avant et arrière, en utilisant temporairement ^ au lieu d’espaces intermédiaires. Voici le résultat de cette étape:

~remove~leading~and~trailing~backward~( )~and~forward~(^)~slashes~

La dernière étape utilise TRANSLATE pour remplacer les espaces par des barres obliques arrière, ^ par des barres obliques avant et ~ par des espaces, générant la sortie souhaitée:

 remove leading and trailing backward (\) and forward (/) slashes 

Comme exercice, essayez de résoudre cette tâche avec une solution compatible pre-SQL Server 2017 où vous ne pouvez pas utiliser TRIM et TRANSLATE.

De retour à SQL Server 2017 et au-dessus, si vous aviez pris la peine de vérifier la documentation, vous auriez découvert que TRIM est plus sophistiqué que ce que vous pensiez au départ. Voici la syntaxe de la fonction:

TRIM ( string )

Les caractères optionnels DE part vous permettent de spécifier un ou plusieurs caractères que vous souhaitez rogner depuis le début et la fin de la chaîne d’entrée. Dans notre cas, tout ce que vous devez faire est de spécifier ‘/\’ comme cette partie, comme ceci:

DECLARE @s AS VARCHAR(100) = '//\\ remove leading and trailing backward (\) and forward (/) slashes \\//'; SELECT TRIM( '/\' FROM @s) AS outputstring;

C’est une amélioration assez significative par rapport à la solution précédente!

CONCAT et CONCAT_WS

Si vous travaillez avec T-SQL depuis un certain temps, vous savez à quel point il est difficile de traiter les valeurs nulles lorsque vous devez concaténer des chaînes. Par exemple, considérons les données de localisation enregistrées pour les employés dans la table HR.Employees :

SELECT empid, country, region, cityFROM HR.Employees;

Cette requête génère la sortie suivante :

empid country region city----------- --------------- --------------- ---------------1 USA WA Seattle2 USA WA Tacoma3 USA WA Kirkland4 USA WA Redmond5 UK NULL London6 UK NULL London7 UK NULL London8 USA WA Seattle9 UK NULL London

Notez que pour certains employés, la partie région n’est pas pertinente et qu’une région non pertinente est représentée par un NULL. Supposons que vous deviez concaténer les parties d’emplacement (pays, région et ville), en utilisant une virgule comme séparateur, mais en ignorant les régions NULLES. Lorsque la région est pertinente, vous souhaitez que le résultat ait la forme <coutry>,<region>,<city> et lorsque la région n’est pas pertinente, vous souhaitez que le résultat ait la forme <country>,<city>. Normalement, la concaténation de quelque chose avec un NULL produit un résultat NULL. Vous pouvez modifier ce comportement en désactivant l’option de session CONCAT_NULL_YIELDS_NULL, mais je ne recommanderais pas d’activer le comportement non standard.

Si vous n’avez pas connaissance de l’existence de la méthode CONCAT et CONCAT_WS fonctions, vous avez probablement utilisé ISNULL ou FUSIONNENT pour remplacer NULL par une chaîne vide, comme suit:

SELECT empid, country + ISNULL(',' + region, '') + ',' + city AS locationFROM HR.Employees;

Voici le résultat de cette requête:

empid location----------- -----------------------------------------------1 USA,WA,Seattle2 USA,WA,Tacoma3 USA,WA,Kirkland4 USA,WA,Redmond5 UK,London6 UK,London7 UK,London8 USA,WA,Seattle9 UK,London

SQL Server 2012 a introduit la fonction CONCAT. Cette fonction accepte une liste d’entrées de chaînes de caractères et les concatène, et ce faisant, elle ignore les valeurs nulles. Donc, en utilisant CONCAT, vous pouvez simplifier la solution comme ceci:

SELECT empid, CONCAT(country, ',' + region, ',', city) AS locationFROM HR.Employees;

Cependant, vous devez spécifier explicitement les séparateurs dans le cadre des entrées de la fonction. Pour nous rendre la vie encore plus facile, SQL Server 2017 a introduit une fonction similaire appelée CONCAT_WS où vous commencez par indiquer le séparateur, suivi des éléments que vous souhaitez concaténer. Avec cette fonction, la solution est encore simplifiée comme suit:

SELECT empid, CONCAT_WS(',', country, region, city) AS locationFROM HR.Employees;

L’étape suivante est bien sûr mindreading. Le 1er avril 2020, Microsoft prévoit de publier CONCAT_MR. La fonction acceptera une entrée vide et déterminera automatiquement les éléments que vous souhaitez qu’elle concatène en lisant votre esprit. La requête ressemblera alors à ceci:

SELECT empid, CONCAT_MR() AS locationFROM HR.Employees;

Le JOURNAL a un deuxième paramètre

Similaire à la fonction EOMONTH, beaucoup de gens ne réalisent pas que commencer déjà avec SQL Serveur 2012, la fonction LOG prend en charge un deuxième paramètre qui vous permet d’indiquer la base du logarithme. Avant cela, T-SQL prenait en charge la fonction LOG (input) qui renvoie le logarithme naturel de l’entrée (en utilisant la constante e comme base), et LOG10 (input) qui utilise 10 comme base.

N’étant pas au courant de l’existence du deuxième paramètre de la fonction LOG, lorsque les gens voulaient calculer Logb(x), où b est une base autre que e et 10, ils l’ont souvent fait à long terme. Vous pouvez vous fier à l’équation suivante :

Par exemple, pour calculer Log2(8), vous vous fiez à l’équation suivante :

Traduit en T-SQL, vous appliquez le calcul suivant:

DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x) / LOG(@b);

Une fois que vous réalisez que LOG prend en charge un deuxième paramètre où vous indiquez la base, le calcul devient simplement:

DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x, @b);

Variable de curseur

Si vous travaillez avec T-SQL depuis un moment, vous avez probablement eu beaucoup de chances de travailler avec des curseurs. Comme vous le savez, lorsque vous travaillez avec un curseur, vous utilisez généralement les étapes suivantes:

• Déclarer le curseur
• Ouvrir le curseur
• Parcourir les enregistrements du curseur
• Fermer le curseur
• Désallouer le curseur

Par exemple, supposons que vous deviez effectuer une tâche par base de données dans votre instance. En utilisant un curseur, vous utiliserez normalement un code similaire à ce qui suit :

DECLARE @dbname AS sysname; DECLARE C CURSOR FORWARD_ONLY STATIC READ_ONLY FOR SELECT name FROM sys.databases; OPEN C; FETCH NEXT FROM C INTO @dbname; WHILE @@FETCH_STATUS = 0BEGIN PRINT N'Handling database ' + QUOTENAME(@dbname) + N'...'; /* ... do your thing here ... */ FETCH NEXT FROM C INTO @dbname;END; CLOSE C;DEALLOCATE C;

La commande CLOSE libère le jeu de résultats actuel et libère les verrous. La commande DE DÉSALLOCATION supprime une référence de curseur, et lorsque la dernière référence est désallouée, libère les structures de données comprenant le curseur. Si vous essayez d’exécuter le code ci-dessus deux fois sans les commandes CLOSE et DEALLOCATE, vous obtiendrez l’erreur suivante :

Msg 16915, Level 16, State 1, Line 4A cursor with the name 'C' already exists.Msg 16905, Level 16, State 1, Line 6The cursor is already open.

Assurez-vous d’exécuter les commandes CLOSE et DEALLOCATE avant de continuer.

Beaucoup de gens ne réalisent pas que lorsqu’ils ont besoin de travailler avec un curseur dans un seul lot, ce qui est le cas le plus courant, au lieu d’utiliser un curseur ordinaire, vous pouvez travailler avec une variable de curseur. Comme toute variable, la portée d’une variable curseur est uniquement le lot où elle a été déclarée. Cela signifie que dès qu’un lot se termine, toutes les variables expirent. À l’aide d’une variable curseur, une fois un lot terminé, SQL Server le ferme et le désalloue automatiquement, ce qui vous évite d’exécuter explicitement la commande FERMER et DÉSALLOUER.

Voici le code révisé en utilisant une variable de curseur cette fois:

DECLARE @dbname AS sysname, @C AS CURSOR; SET @C = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR SELECT name FROM sys.databases; OPEN @C; FETCH NEXT FROM @C INTO @dbname; WHILE @@FETCH_STATUS = 0BEGIN PRINT N'Handling database ' + QUOTENAME(@dbname) + N'...'; /* ... do your thing here ... */ FETCH NEXT FROM @C INTO @dbname;END;

N’hésitez pas à l’exécuter plusieurs fois et notez que cette fois, vous n’obtenez aucune erreur. C’est juste plus propre, et vous n’avez pas à vous soucier de conserver les ressources du curseur si vous avez oublié de fermer et de désallouer le curseur.

FUSIONNER avec OUTPUT

Depuis la création de la clause OUTPUT pour les instructions de modification dans SQL Server 2005, il s’est avéré être un outil très pratique chaque fois que vous vouliez renvoyer des données à partir de lignes modifiées. Les gens utilisent cette fonctionnalité régulièrement à des fins telles que l’archivage, l’audit et de nombreux autres cas d’utilisation. L’une des choses ennuyeuses de cette fonctionnalité, cependant, est que si vous l’utilisez avec des instructions INSERT, vous n’êtes autorisé à renvoyer que des données à partir des lignes insérées, en préfixant les colonnes de sortie avec inserted. Vous n’avez pas accès aux colonnes de la table source, même si vous devez parfois renvoyer des colonnes de la source à côté des colonnes de la cible.

À titre d’exemple, considérons les tables T1 et T2, que vous créez et remplissez en exécutant le code suivant:

DROP TABLE IF EXISTS dbo.T1, dbo.T2;GO CREATE TABLE dbo.T1(keycol INT NOT NULL IDENTITY PRIMARY KEY, datacol VARCHAR(10) NOT NULL); CREATE TABLE dbo.T2(keycol INT NOT NULL IDENTITY PRIMARY KEY, datacol VARCHAR(10) NOT NULL); INSERT INTO dbo.T1(datacol) VALUES('A'),('B'),('C'),('D'),('E'),('F');

Notez qu’une propriété identity est utilisée pour générer les clés dans les deux tables.

Supposons que vous devez copier certaines lignes de T1 à T2; disons, celles où keycol%2 = 1. Vous souhaitez utiliser la clause OUTPUT pour renvoyer les clés nouvellement générées dans T2, mais vous souhaitez également renvoyer à côté de ces clés les clés source respectives de T1. L’attente intuitive est d’utiliser l’instruction INSERT suivante:

INSERT INTO dbo.T2(datacol) OUTPUT T1.keycol AS T1_keycol, inserted.keycol AS T2_keycol SELECT datacol FROM dbo.T1 WHERE keycol % 2 = 1;

Malheureusement, comme mentionné, la clause de SORTIE ne vous permet pas de faire référence aux colonnes de la table source, vous obtenez donc l’erreur suivante:

Beaucoup de gens ne réalisent pas que curieusement cette limitation ne s’applique pas à l’instruction MERGE. Donc, même si c’est un peu gênant, vous pouvez convertir votre instruction INSERT en une instruction MERGE, mais pour ce faire, vous devez que le prédicat de FUSION soit toujours faux. Cela activera la clause WHEN NOT MATCHED et y appliquera la seule action d’INSERTION prise en charge. Vous pouvez utiliser une fausse condition factice telle que 1 = 2. Voici le code converti complet:

MERGE INTO dbo.T2 AS TGTUSING (SELECT keycol, datacol FROM dbo.T1 WHERE keycol % 2 = 1) AS SRC ON 1 = 2WHEN NOT MATCHED THEN INSERT(datacol) VALUES(SRC.datacol)OUTPUT SRC.keycol AS T1_keycol, inserted.keycol AS T2_keycol;

Cette fois, le code s’exécute avec succès, produisant la sortie suivante:

T1_keycol T2_keycol----------- -----------1 13 25 3