Dans mon dernier article, j’ai décrit grossièrement comment SQL standard diffère de T-SQL et qui devrait apprendre lequel. J’aimerais maintenant me concentrer sur les différences de syntaxe et illustrer ces différences par des exemples. Si vous pensez que T-SQL est une extension implémentant toutes les fonctionnalités de SQL standard, vous avez tort. Cependant, dans SQL Server, vous trouverez presque toutes les fonctionnalités du standard SQL. Dans cet article, vous trouverez des exemples de certaines différences de syntaxe entre SQL standard et Transact-SQL.
#1 Noms des objets de la base de données
Dans les systèmes de bases de données relationnelles, nous nommons les tables, les vues et les colonnes, mais parfois nous devons utiliser le même nom comme mot-clé ou utiliser des caractères spéciaux. En SQL standard, vous pouvez placer ce type de nom entre guillemets (« »), mais en T-SQL, vous pouvez également le placer entre parenthèses (). Regardez ces exemples pour le nom d’une table en T-SQL :
CREATE TABLE dbo.test."first name" ( Id INT, Name VARCHAR(100));CREATE TABLE dbo.test. ( Id INT, Name VARCHAR(100));Seul le premier délimiteur (les guillemets) pour le nom spécial fait également partie de la norme SQL.
Qu’est-ce qui est différent dans une instruction SELECT ?
La norme SQL n’a pas de syntaxe pour une requête renvoyant des valeurs ou des valeurs provenant d’expressions sans faire référence à aucune colonne d’une table, mais MS SQL Server permet ce type d’expression. Comment ? Vous pouvez utiliser une instruction SELECT seule avec une expression ou avec d’autres valeurs ne provenant pas de colonnes de la table. En T-SQL, cela ressemble à l’exemple ci-dessous :
SELECT 12/6 ;Dans cette expression, nous n’avons pas besoin d’une table pour évaluer 12 divisé par 6, donc, l’instruction FROM et le nom de la table peuvent être omis.
#3 Limitation des enregistrements dans un ensemble de résultats
Dans le standard SQL, vous pouvez limiter le nombre d’enregistrements dans les résultats en utilisant la syntaxe illustrée ci-dessous :
SELECT * FROM tab FETCH FIRST 10 ROWS ONLYT-SQL met en œuvre cette syntaxe d’une manière différente. L’exemple ci-dessous montre la syntaxe de MS SQL Server:
SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;Comme vous le remarquez, ceci utilise une clause ORDER BY. Une autre façon de sélectionner des lignes, mais sans ORDER BY, est d’utiliser la clause TOP en T-SQL:
SELECT TOP 10 * FROM tab;#4 Génération automatique de valeurs
Le standard SQL vous permet de créer des colonnes avec des valeurs générées automatiquement. La syntaxe pour ce faire est présentée ci-dessous:
CREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY);En T-SQL, nous pouvons également générer automatiquement des valeurs, mais de cette manière:
CREATE TABLE tab (id INTEGER IDENTITY);#5 Fonctions mathématiques
Plusieurs fonctions mathématiques courantes font partie de la norme SQL. L’une de ces fonctions mathématiques est CEIL(x), que nous ne trouvons pas dans T-SQL. Au lieu de cela, T-SQL fournit les fonctions non standard suivantes : SIGN(x), ROUND(x,) pour arrondir la valeur décimale x au nombre de décimales, TRUNC(x) pour tronquer à un nombre donné de décimales, LOG(x) pour retourner le logarithme naturel pour une valeur x, et RANDOM() pour générer des nombres aléatoires. Le nombre le plus élevé ou le plus bas d’une liste dans le standard SQL est retourné par les fonctions MAX(list) et MIN(list), mais dans Transact-SQL, on utilise les fonctions GREATEST(list) et LEAST(list).
T-SQL function ROUND:SELECT ROUND(col) FROM tab;#6 Aggregate Functions
Nous trouvons une autre différence de syntaxe avec les fonctions agrégées. Les fonctions COUNT, SUM et AVG prennent toutes un argument lié à un comptage. T-SQL permet l’utilisation de DISTINCT devant ces valeurs d’argument afin que les lignes ne soient comptées que si les valeurs sont différentes des autres lignes. La norme SQL ne permet pas l’utilisation de DISTINCT dans ces fonctions.
Standard SQL:SELECT COUNT(col) FROM tab;T-SQL:SELECT COUNT(col) FROM tab;SELECT COUNT(DISTINCT col) FROM tab;Mais dans T-SQL, nous ne trouvons pas de fonction de covariance de population : COVAR_POP(x,y), qui est définie dans la norme SQL.
#7 Récupération de parties de dates et d’heures
La plupart des systèmes de bases de données relationnelles livrent de nombreuses fonctions pour opérer sur les dates et les heures.
Dans le langage SQL standard, la fonction EXTRACT(YEAR FROM x) et les fonctions similaires pour sélectionner des parties de dates sont différentes des fonctions T-SQL comme YEAR(x) ou DATEPART(year, x).
Il existe également une différence pour obtenir la date et l’heure actuelles. Le SQL standard vous permet d’obtenir la date actuelle avec la fonction CURRENT_DATE, mais dans MS SQL Server, il n’y a pas de fonction similaire, nous devons donc utiliser la fonction GETDATE comme argument dans la fonction CAST pour convertir en un type de données DATE.
#8 Operating on Strings
L’utilisation de fonctions pour opérer sur des chaînes de caractères est également différente entre le SQL standard et T-SQL. La principale différence se trouve dans la suppression des espaces de queue et de tête d’une chaîne de caractères. En SQL standard, il existe la fonction TRIM, mais en T-SQL, il existe plusieurs fonctions connexes : TRIM (suppression des espaces de fin et de début), LTRIM (suppression des espaces de début) et RTRIM (suppression des espaces de fin).
Une autre fonction de chaîne de caractères très souvent utilisée est SUBSTRING.
La syntaxe SQL standard de la fonction SUBSTRING ressemble à :
SUBSTRING(str FROM start ) mais en T-SQL, la syntaxe de cette fonction ressemble à :
SUBSTRING(str, start, length)Il y a parfois des raisons d’ajouter des valeurs provenant d’autres colonnes et/ou de chaînes supplémentaires. SQL standard permet la syntaxe suivante pour le faire:
Comme vous pouvez le voir, cette syntaxe fait appel à l’opérateur || pour ajouter une chaîne à une autre.
Mais l’opérateur équivalent en T-SQL est le caractère signe plus. Regardez cet exemple:
SELECT col1 + col2 FROM tab;En SQL Server, nous avons également la possibilité d’utiliser la fonction CONCAT concatène une liste de chaînes de caractères:
SELECT CONCAT(col1, str1, col2, ...) FROM tab;Nous pouvons également répéter un caractère plusieurs fois. Le langage SQL standard définit la fonction REPEAT(str, n) pour ce faire. Transact-SQL fournit la fonction REPLICATE. Par exemple :
SELECT REPLICATE(str, x); où x indique combien de fois répéter la chaîne ou le caractère.
#9 Opérateur d’inégalité
Lors du filtrage des enregistrements dans une instruction SELECT, nous devons parfois utiliser un opérateur d’inégalité. SQL standard définit <> comme cet opérateur, tandis que T-SQL permet à la fois l’opérateur standard et l’opérateur !=:
SELECT col3 FROM tab WHERE col1 != col2;#10 Fonction ISNULL
En T-SQL, nous avons la possibilité de remplacer les valeurs NULL provenant d’une colonne en utilisant la fonction ISNULL. Il s’agit d’une fonction spécifique à T-SQL qui ne figure pas dans la norme SQL.
SELECT ISNULL(col1) FROM tab;Quelles parties de la syntaxe DML sont différentes?
En T-SQL, la syntaxe de base des requêtes DELETE, UPDATE et INSERT est la même que celle du standard SQL, mais des différences apparaissent dans les requêtes plus avancées. Examinons-les.
#11 Mot-clé OUTPUT
Le mot-clé OUTPUT apparaît dans les instructions DELETE, UPDATE et INSERT. Il n’est pas défini dans le SQL standard.
En utilisant T-SQL, nous pouvons voir des informations supplémentaires retournées par une requête. Elle renvoie les anciennes et les nouvelles valeurs dans UPDATE ou les valeurs ajoutées avec INSERT ou supprimées avec DELETE. Pour voir ces informations, nous devons utiliser des préfixes dans INSERT, UPDATE et DELETE.
UPDATE tab SET col='new value' OUTPUT Deleted.col, Inserted.col;Nous voyons le résultat de la modification des enregistrements avec les valeurs précédentes et nouvelles dans une colonne mise à jour. La norme SQL ne prend pas en charge cette fonctionnalité.
#12 Syntaxe pour INSERT INTO…. SELECT
Une autre structure d’une requête INSERT est INSERT INTO … SELECT. T-SQL vous permet d’insérer des données d’une autre table dans une table de destination. Regardez cette requête :
INSERT INTO tab SELECT col1,col2,... FROM tab_source;Ce n’est pas une fonctionnalité standard mais une fonctionnalité caractéristique de SQL Server.
#13 Clause FROM dans DELETE et UPDATE
SQL Server fournit une syntaxe étendue des clauses UPDATE et DELETE avec FROM. Vous pouvez utiliser DELETE with FROM pour utiliser les lignes d’une table afin de supprimer les lignes correspondantes dans une autre table en faisant référence à une clé primaire et à une clé étrangère. De même, vous pouvez utiliser UPDATE with FROM pour mettre à jour les lignes d’une table en faisant référence aux lignes d’une autre table en utilisant des valeurs communes (clé primaire dans une table et clé étrangère dans la seconde, par exemple le même nom de ville). Voici un exemple:
DELETE FROM BookFROM AuthorWHERE Author.Id=Book.AuthorId AND Author.Name IS NULL;UPDATE BookSET Book.Price=Book.Price*0.2FROM AuthorWHERE Book.AuthorId=Author.Id AND Author.Id=12;La norme SQL ne fournit pas cette syntaxe.
#14 INSERT, UPDATE, et DELETE avec JOIN
Vous pouvez également utiliser INSERT, UPDATE, et DELETE en utilisant JOIN pour vous connecter à une autre table. En voici un exemple :
DELETE ItemOrder FROM ItemOrderJOIN Item ON ItemOrder.ItemId=Item.IdWHERE YEAR(Item.DeliveredDate) <= 2017;Cette fonctionnalité ne figure pas dans la norme SQL.
Summary
Cet article ne couvre pas toutes les questions relatives aux différences de syntaxe entre la norme SQL et T-SQL utilisant le système MS SQL Server. Cependant, ce guide permet d’indiquer certaines fonctionnalités de base caractéristiques uniquement de Transact-SQL et quelle syntaxe standard SQL n’est pas implémentée par MS SQL Server.