Problèmes de conception T-SQL

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Base de données SQL dans Microsoft Fabric

Lorsque vous analysez le code T-SQL de votre projet de base de données, un ou plusieurs avertissements peuvent être classés comme des problèmes de conception. Vous devez résoudre les problèmes de conception pour éviter les situations suivantes :

• Les modifications ultérieures apportées à votre base de données risquent de compromettre les applications qui en dépendent.
• Le code peut ne pas produire le résultat attendu.
• Le code risque de ne pas fonctionner si vous l'exécutez avec des versions ultérieures de SQL Server.

En général, vous ne devez pas supprimer un problème de conception parce qu'il risque de compromettre votre application, aujourd'hui ou à l'avenir.

Les règles fournies identifient les problèmes de conception suivants :

• SR0001 : Éviter SELECT * dans les procédures stockées, les vues et les fonctions table
• SR0008 : envisager d'utiliser SCOPE_IDENTITY au lieu de @@IDENTITY
• SR0009 : éviter d’utiliser des types de longueur variable de taille 1 ou 2
• SR0010 : évitez d’utiliser la syntaxe déconseillée lorsque vous joignez des tables ou des vues
• SR0013 : Le paramètre de sortie (paramètre) n’est pas peuplé dans tous les chemins du code
• SR0014 : La perte de données peut se produire lors d’une projection de {Type1} vers {Type2}

SR0001 : Éviter SELECT * dans les procédures stockées, les vues et les fonctions table

Si vous utilisez un caractère générique dans une procédure stockée, une vue ou une fonction table pour sélectionner toutes les colonnes d'une table ou d'une vue, le nombre ou la forme des colonnes renvoyées peut changer si la table ou la vue sous-jacente change. La forme d’une colonne est une combinaison de son type et de sa taille. Cette variance peut poser des problèmes aux applications qui consomment la procédure stockée, la vue ou la fonction table, car ces consommateurs s'attendront à un nombre différent de colonnes.

Comment corriger les violations

Vous pouvez protéger les consommateurs de la procédure stockée, de la vue ou de la fonction table contre les modifications du schéma en remplaçant le caractère générique par une liste complète de noms de colonnes.

Exemple

L'exemple suivant définit d'abord une table nommée [Table2], puis deux procédures stockées. La première procédure contient un SELECT *, ce qui viole la règle SR0001. La deuxième procédure évite SELECT * et liste explicitement les colonnes dans l'instruction SELECT.

CREATE TABLE [dbo].[Table2]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL,
[Comment] NVARCHAR (50)
)
ON [PRIMARY]

CREATE PROCEDURE [dbo].[procWithWarning]
AS
BEGIN
-- Contains code that breaks rule SR0001
SELECT *
FROM [dbo].[Table2]
END

CREATE PROCEDURE [dbo].[procFixed]
AS
BEGIN
-- Explicitly lists the column names in a SELECT statement
SELECT [dbo].[Table2].[ID], [dbo].[Table2].[c1], [dbo].[Table2].[Comment]
FROM [dbo].[Table2]
END

SR0008 : envisager d'utiliser SCOPE_IDENTITY au lieu de @@IDENTITY

@@IDENTITY étant une valeur d'identité globale, elle a peut-être été mise à jour en dehors de l’étendue actuelle et a obtenu une valeur inattendue. Les déclencheurs, y compris les déclencheurs imbriqués utilisés par le réplica, peuvent mettre à jour @@IDENTITY en dehors de votre étendue actuelle.

Comment corriger les violations

Pour résoudre ce problème, vous devez remplacer les références à @@IDENTITY par SCOPE_IDENTITY, qui renvoie la valeur d'identité la plus récente dans l'étendue de l’instruction de l'utilisateur.

Exemple

Dans le premier exemple, @@IDENTITY est utilisé dans une procédure stockée qui insère des données dans une table. La table est ensuite publiée pour la réplication de fusion, qui ajoute des déclencheurs aux tables publiées. Par conséquent, @@IDENTITY peut renvoyer la valeur de l'opération d'insertion dans une table système de réplication au lieu de l'opération d'insertion dans une table utilisateur.

La table Sales.Customer possède une valeur d'identité maximale de 29483. Si vous insérez une ligne dans la table, @@IDENTITY et SCOPE_IDENTITY() renvoient des valeurs différentes. SCOPE_IDENTITY() renvoie la valeur de l'opération d'insertion dans la table utilisateur, mais @@IDENTITY renvoie la valeur de l'opération d'insertion dans la table système de réplication.

Le deuxième exemple montre comment vous pouvez utiliser SCOPE_IDENTITY() pour accéder à la valeur d'identité insérée et résoudre l'avertissement.

CREATE PROCEDURE [dbo].[ProcedureWithWarning]
@param1 INT,
@param2 NCHAR(1),
@Param3 INT OUTPUT
AS
BEGIN
INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (@param1,@param2);

SELECT @Param3 = @@IDENTITY
END

CREATE PROCEDURE [dbo].[ProcedureFixed]
@param1 INT,
@param2 NCHAR(1),
@param3 INT OUTPUT
AS
BEGIN
INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (@param1,@param2);

SELECT @Param3 = SCOPE_IDENTITY()
END

SR0009 : éviter d’utiliser des types de longueur variable de taille 1 ou 2

Lorsque vous utilisez des types de données de longueur variable tels que VARCHAR, NVARCHAR et VARBINARY, vous devez supporter un coût de stockage supplémentaire pour suivre la longueur de la valeur stockée dans le type de données. En outre, les colonnes de longueur variable sont stockées après toutes les colonnes de longueur fixe, ce qui peut avoir des conséquences sur les performances. Vous recevrez également un avertissement si vous déclarez un type de longueur variable, tel que VARCHAR, mais que vous ne spécifiez aucune longueur. Cet avertissement se produit parce que, si elle n'est pas spécifiée, la longueur par défaut est 1.

Comment corriger les violations

Si la longueur du type est très petite (taille 1 ou 2) et cohérente, déclarez-les comme un type de longueur fixe, tel que CHAR, NCHAR et BINARY.

Exemple

Cet exemple présente les définitions de deux tableaux. La première table déclare qu'une chaîne de caractères de longueur variable est de longueur 2. La deuxième table déclare plutôt une chaîne de longueur fixe, ce qui permet d'éviter l'avertissement.

CREATE TABLE [dbo].[TableWithWarning]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL PRIMARY KEY,
[c2] INT,
[c3] INT,
[SmallString] VARCHAR(2)
)
ON [PRIMARY]

CREATE TABLE [dbo].[FixedTable]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL PRIMARY KEY,
[c2] INT,
[c3] INT,
[SmallString] CHAR(2)
)
ON [PRIMARY]

Les données des types de longueur variable sont physiquement stockées après les données des types de longueur fixe. Par conséquent, vous déclencherez un déplacement des données si vous faites passer une colonne d'une longueur variable à une longueur fixe dans une table qui n'est pas vide.

SR0010 : évitez d’utiliser la syntaxe déconseillée lorsque vous joignez des tables ou des vues

Les jointures qui utilisent la syntaxe déconseillée se répartissent en deux catégories :

• Jointure interne : dans le cas d'une jointure interne, les valeurs des colonnes jointes sont comparées à l'aide d'un opérateur de comparaison tel que =, <, >=, etc. Les jointures internes ne renvoient des lignes que si au moins une ligne de chaque table correspond à la condition de jointure.
• Jointure externe : les jointures externes renvoient toutes les lignes d'au moins une des tables ou vues spécifiées dans la clause FROM, pour autant que ces lignes remplissent une condition de recherche WHERE ou HAVING. Si vous utilisez = ou = pour spécifier une jointure externe, vous utilisez la syntaxe déconseillée.

Comment corriger les violations

Pour corriger une violation dans une jointure interne, utilisez la syntaxe INNER JOIN.

Pour corriger une violation dans une jointure externe, utilisez la syntaxe appropriée OUTER JOIN. Les options suivantes s’offrent à vous :

• JOINTURE EXTERNE GAUCHE OU JOINTURE GAUCHE
• JOINTURE EXTERNE DROITE OU JOINTURE DROITE

Des exemples de la syntaxe déconseillée et de la syntaxe mise à jour sont fournis ci-après. De plus amples informations sur les jointures sont disponibles via la section Jointures.

Exemples

Les six exemples illustrent les options suivantes :

1. L’exemple 1 illustre la syntaxe déconseillée d’une jointure interne.
2. L’exemple 2 illustre comment mettre à jour l’exemple 1 pour utiliser la syntaxe actuelle.
3. L’exemple 3 illustre la syntaxe déconseillée d’une jointure externe gauche.
4. L’exemple 4 illustre comment mettre à jour l’exemple 2 pour utiliser la syntaxe actuelle.
5. L’exemple 5 illustre la syntaxe déconseillée d’une jointure externe droite.
6. L’exemple 6 illustre comment mettre à jour l’exemple 5 pour utiliser la syntaxe actuelle.

-- Example 1: Deprecated syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] = [T2].[ID]

-- Example 2: Current syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
INNER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

-- Example 3: Deprecated syntax for a left outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] *= [T2].[ID]

-- Example 4: Fixed syntax for a left outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
LEFT OUTER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

-- Example 5: Deprecated syntax for a right outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] =* [T2].[ID]

-- Example 6: Fixed syntax for a right outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
RIGHT OUTER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

SR0013 : Le paramètre de sortie (paramètre) n’est pas peuplé dans tous les chemins du code

Cette règle identifie le code dans lequel le paramètre de sortie n'est pas défini sur une valeur dans un ou plusieurs chemins de code à travers la procédure stockée ou la fonction. Cette règle n'identifie pas les chemins d'accès dans lesquels le paramètre de sortie doit être défini. Si plusieurs paramètres de sortie présentent ce problème, un avertissement s’affiche pour chaque paramètre.

Comment corriger les violations

Vous pouvez corriger ce problème de deux manières. Vous pouvez corriger ce problème plus facilement en initialisant les paramètres de sortie à une valeur par défaut au début du corps de la procédure. Vous pouvez également définir le paramètre de sortie sur une valeur dans les chemins de code spécifiques dans lesquels le paramètre n'est pas défini. Cependant, il se peut que vous négligiez un chemin de code peu commun dans une procédure complexe.

Important

La spécification d'une valeur dans la déclaration de procédure, telle que CREATE PROC MyProcedure (@param1 INT = 10 OUTPUT), ne résoudra pas le problème. Vous devez attribuer une valeur au paramètre de sortie dans le corps de la procédure.

Exemple

L’exemple suivant illustre deux procédures simples. La première procédure ne définit pas la valeur du paramètre de sortie, @Sum. La deuxième procédure initialise le paramètre @Sum au début de la procédure, ce qui garantit que la valeur sera définie dans tous les chemins de code.

CREATE PROCEDURE [dbo].[procedureHasWarning]
(
@Value1 BIGINT,
@Value2 INT,
@Value3 INT,
@Sum INT OUTPUT
)
AS
BEGIN
-- No initialization of the output parameter
--
-- Additional statements here.
--
RETURN 0;
END
--
CREATE PROCEDURE [dbo].[procedureFixed]
(
@Value1 BIGINT,
@Value2 INT,
@Value3 INT,
@Sum INT OUTPUT
)
AS
BEGIN
-- Initialize the out parameter
SET @Sum = 0;
--
-- Additional statements here
--
RETURN 0;
END

SR0014 : La perte de données peut se produire lors d’une projection de {Type1} vers {Type2}

Si des types de données sont attribués de manière incohérente à des colonnes, des variables ou des paramètres, ils sont implicitement convertis lors de l'exécution du code Transact-SQL qui contient ces objets. Ce type de conversion réduit non seulement les performances, mais entraîne aussi, dans certains cas, une perte subtile de données. Par exemple, une analyse de table peut être exécutée si chaque colonne d'une clause WHERE doit être convertie. Pire, des données peuvent être perdues si une chaîne Unicode est convertie en une chaîne ASCII qui utilise une page de code différente.

Cette règle :

• ne vérifie pas le type d'une colonne calculée car le type n'est connu qu'au moment de l'exécution.
• n'analyse rien à l'intérieur d'une instruction CASE. n'analyse pas la valeur de retour d'une instruction CASE.
• n'analyse pas les paramètres d'entrée ou la valeur de retour d'un appel à ISNULL.

Cette table résume les vérifications couvertes par la règle SR0014 :

Construction de langage	Qu’est-ce qui est vérifié	Exemple
Valeur par défaut des paramètres	Type de données de paramètre	CREATE PROCEDURE p1(@p1 INT = 1) AS BEGIN END
Prédicat CREATE INDEX	Le prédicat est booléen	CREATE INDEX index1 ON table1 (column1) WHERE column1 > 10
Arguments des fonctions LEFT ou RIGHT	Type et longueur des arguments de type chaîne	SET @v = LEFT('abc', 2)
Arguments des fonctions CAST et CONVERT	Les expressions et les types sont valides	SET @v = CAST('abc' AS CHAR(10))
SET, instruction	Le côté gauche et le côté droit ont des types compatibles	SET @v1 = 'xyz' SELECT @v1 = c1 FROM t1
Prédicat d’instruction IF	Le prédicat est booléen	IF (@v > 10)
Prédicat d’instruction WHILE	Le prédicat est booléen	WHILE (@v > 10)
Instruction INSERT	Les valeurs et les colonnes sont correctes	INSERT INTO t1(c1, c2) VALUES (99, 'xyz') INSERT INTO t1 SELECT c1 FROM t2.
Prédicat SELECT WHERE	Le prédicat est booléen	SELECT * FROM t1 WHERE c1 > 10
Expression SELECT TOP	L'expression est de type entier ou flottant	SELECT TOP 4 * FROM t1 SELECT TOP 1.5 PERCENT * FROM t1
Instruction UPDATE	Les expressions et les colonnes ont des types compatibles	UPDATE t1 SET c1 = 100
Prédicat UPDATE	Le prédicat est booléen	UPDATE t1 SET c1 = 100 WHERE c1 > 100
Expression UPDATE TOP	L'expression est de type entier ou flottant	UPDATE TOP 4 table1
Prédicat DELETE	Le prédicat est booléen	DELETE t1 WHERE c1 > 10
Expression DELETE TOP	L'expression est de type entier ou flottant	DELETE TOP 2 FROM t1
Déclaration de variable DECLARE	La valeur initiale et le type de données sont compatibles	DECLARE @v INT = 10
Arguments d’instruction EXECUTE et type de retour	Paramètres et arguments	CREATE PROCEDURE p1 (@p1 INT) AS GO EXECUTE p1 100 EXECUTE @v1 = p1 100
Instruction Return	L’expression RETURN a un type de données compatible	CREATE FUNCTION f1() RETURNS INT AS BEGIN RETURN 100 END
Conditions de l’instruction MERGE	La condition est booléenne	MERGE t1 USING t2 ON t1.c1 = t2.c2 WHEN t1.c1 > 10 THEN DELETE

Comment corriger les violations

Vous pouvez éviter ces problèmes ou les résoudre en attribuant des types de données de manière cohérente et en convertissant explicitement les types lorsqu'ils sont nécessaires. Pour plus d'informations sur la conversion explicite des types de données, consultez cette page sur le site Web de Microsoft : CAST et CONVERT (Transact-SQL).

Exemple

Cet exemple illustre deux procédures stockées qui insèrent des données dans une table. La première procédure, procWithWarning, déclenche une conversion implicite d'un type de données. La deuxième procédure, procFixed, montre comment ajouter une conversion explicite pour maximiser les performances et conserver toutes les données.

CREATE TABLE [dbo].[Table2]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL,
[c2] INT NOT NULL,
[c3] BIGINT NOT NULL,
[Comment] VARCHAR (25)
)
ON [PRIMARY]

CREATE PROCEDURE [dbo].[procWithWarning]
(
@Value1 INT,
@Value2 INT,
@Value3 BIGINT,
@Comment CHAR(30)
)
AS
BEGIN
INSERT INTO [Table2] ([c1], [c2], [c3], Comment)
VALUES (@Value1, @Value2, @Value3, @Comment)

END

CREATE PROCEDURE [dbo].[procFixed]
(
@Value1 INT,
@Value2 INT,
@Value3 BIGINT,
@Comment CHAR(10)
)
AS
BEGIN
INSERT INTO [Table2] ([c1], [c2], [c3], Comment)
VALUES (@Value1, @Value2, @Value3, CAST(@Comment AS VARCHAR(25)))

END

Contenu connexe

• Analyse du code SQL pour améliorer la qualité du code
• Problèmes de dénomination T-SQL
• Problèmes de performances T-SQL
• Analyser le code T-SQL pour trouver des défauts