Voici une liste de quelques best practices pour la création de tables, d’instructions et de procédures stockées rédigée à partir de quelques articles glanés au fil du temps. Il s’agit davantage d’un aide mémoire que d’une liste d’éléments rigides à respecter ; il faut mettre en perspective plusieurs facteurs (fréquence d’utilisation de la requête, quantité de lignes de données à la source, jeu d’enregistrement retourné) lorsque l’on rédige des instructions SQL afin d’évaluer l’importance de respecter ces pratiques.
Conception de tables / tables temporaires :
- Utiliser le plus petit type de données possible.
Si possible, préférer un type tinyint à un int et ainsi, préférer un int à un bigint. Il en va de même pour les colonnes de type texte, il est plus efficace de définir la longueur d’un champ char ou varchar selon le nombre de caractères du terme le plus long qu’elle contiendra. - Utiliser des clés primaires de type entier (int) ou de peu de caractères (ex. char(3)).
Éviter les types « float », « real » et « datetime » pour vos clés primaires. - Éviter le type « TEXT ».
Par souci de performance, si le champ doit contenir moins de 8000 caractères, utiliser le type VARCHAR. - Éviter les types « NCHAR » et « NVARCHAR » si non requis.
Ces types sont les équivalents Unicode des types CHAR et VARCHAR mais utilisent le double de l’espace utilisé par ces derniers (CHAR(1) = 1 byte ; NCHAR(1) = 2 bytes). - Choisir entre « CHAR » et « VARCHAR ».
Si la longueur des valeurs contenues dans le champ est assez constante, choisir le type CHAR ; si la longueur des valeurs varie beaucoup, choisir le type VARCHAR. L’espace utilisé par les valeurs contenues dans une colonne CHAR est constant et est le maximum autorisé par la colonne. Comme le type CHAR est de longueur fixe, le traitement de ce type de champ est plus rapide que les colonnes de type VARCHAR. - Définir un « CLUSTERED INDEX » sur chaque table.
- Éviter l’utilisation de tables temporaires.
Il est préférable d’utiliser une variable de type table (stockée en mémoire) plutôt qu’une table temporaire (stockée dans la base de données temp sur le disque) lorsque le jeu d’enregistrement n’est pas trop gros. - Ne pas utiliser « SELECT INTO » pour créer une table temporaire.
Créez manuellement la table avec l’instruction CREATE TABLE. Le SELECT INTO verrouille la base de données temp ; ce qui peut amener les autres processus à subir des blocages.
Conception de requêtes :
- Commentez votre code.
Les commentaires permettront aux autres programmeurs / développeurs et à vous-même de vous comprendre lorsque vous relirez votre code. Il n’y a aucune raison de ne pas inscrire de commentaires, ils n’affectent en rien le code SQL. - Inscrivez les mots clés SQL en lettres majuscules.
Afin d’améliorer la lisibilité de votre code. - Spécifier les colonnes dans les instructions « INSERT ».
Afin d’éviter les problèmes si la structure des tables constituant la requête change. - Ne pas utiliser « SELECT * ».
Spécifiez seulement les colonnes dont vous avez besoin. En plus de retourner des résultats inutiles l’utilisation d’un SELECT * peut empêcher l’utilisation d’un index couvrant. - Toujours utiliser une clause « WHERE ».
Afin de limiter le nombre d’enregistrements renvoyés et de retourner seulement les enregistrements nécessaires. - Éviter d’utiliser la clause « DISTINCT ».
Il est préférable d’utiliser une clause GROUP BY car elle est exécutée avant la clause DISTINCT dans le plan d’exécution d’une requête. - Éviter d’utiliser des curseurs.
Les curseurs utilisent énormément de ressources, il est préférable d’utiliser une approche ensembliste (set based) plutôt qu’une approche procédurale telle un curseur. Si un curseur est inévitable, préférer une boucle « WHILE ». - Éviter les « ORDER BY ».
Effectuer les tris (order by) au niveau du client ; si ce n’est pas possible, spécifier la colonne plutôt qu’utiliser son numéro. - Éviter les expressions suivantes qui ne sont pas sargable (qui ne profitent pas de l’utilisation d’un index et qui résultent en scan d’indexes ou de tables) :
- L’utilisation du JOKER au début d’une expression dans une recherche WHERE […] LIKE.
- Les opérateurs « n’égale pas » (<> et NOT).
- Les clauses IN et NOT IN, EXISTS et NOT EXISTS (bien que ces derniers soient préférables aux IN / NOT IN).
- NOT LIKE
- IS NULL
- Utiliser des « Stored procedures ».
Dès qu’une application cliente doit exécuter des requêtes TSQL , il est préférable d’utiliser des procédures stockées (SPROC) plutôt que des scripts dans l’application. Voici quelques avantages :- Réduire le trafic sur le réseau.
- Les plans d’exécution des SPROC peuvent être réutilisés par l’optimiseur de requêtes, ce qui aide à réduire la charge sur le serveur.
- Permet d’encapsuler la logique d’affaire dans la base de données.
- Débuter toutes les procédures stockées par « SET NOCOUNT ON ».
Cette commande permet de réduire le trafic sur le réseau et devrait débuter toutes les SPROC. Elle évite à SQL Server de calculer le nombre de lignes concernées par une requête ou une procédure stockée. - Ne pas préfixer les procédures stockées par « sp_ ».
Ce préfixe est réservé pour les procédures stockées systèmes. Lorsqu’une procédure débutant par « sp_ » est rencontrée, SQL essaie de la localiser premièrement dans la base de données master. - Éviter les longues requêtes.
Il est préférable de séparer les longues requêtes en plusieurs petites requêtes. Écrivez des requêtes claires et aussi courtes que possible.
Permissions :
- Donner les permissions adéquates aux objets.
Ne pas donner des permissions aux usagers d’application dont ils n’ont pas besoin. Permettez seulement les opérations requises (SELECT, INSERT, DELETE, UPDATE, EXECUTE) par l’application sur les objets de la base de données. Dans le cas de tables contenant des données sensibles, spécifiez des permissions précises sur les colonnes plutôt que sur la table. - Ne jamais utiliser le login « SA » pour l’accès aux données pour une application!
Références :