L’automatisation à  la loupe

par Alex Glauberzon et Martin Wiseman - Mis en ligne le 28/06/2006 - Publié en Avril 2005

Prenez le temps de repenser à la dernière fois où vous avez dû mettre à jour une table contenant un grand nombre de colonnes. Imaginez maintenant que cette table est en cours de construction et que vous êtes chargé d’ajouter ou de supprimer des colonnes, ou encore de changer des spécifications de données de colonnes. Naturellement, cette situation se produit alors que votre emploi du temps est des plus serrés et que vous avez d’autres problèmes plus importants à résoudre. Quel casse-tête ! En fait, vous avez la possibilité d’automatiser une solution à ce problème en combinant les possibilités élémentaires des procédures stockées et le dictionnaire de données sous-jacent de SQL Server.Une procédure stockée est un groupe d’instructions T-SQL compilées dans un plan d’exécution. L’utilisation des procédures stockées procure un certain nombre d’avantages. Premièrement, elles permettent à un développeur de réutiliser efficacement le code. Une fois que vous avez créé une procédure stockée et que vous l’avez stockée dans la base de données, toute personne qui peut y accéder est en mesure de l’appeler à volonté, de sorte que vous n’avez pas besoin de réécrire constamment le même code. Le développeur peut ainsi utiliser de manière plus productive le temps dont il dispose. Deuxièmement, les procédures stockées accélèrent l’exécution des requêtes. Dans la mesure où ce type d’élément est précompilé et où SQL Server réutilise le plan d’exécution résultant, cela se traduit par des économies substantielles en termes de temps et d’utilisation globale du processeur. Si une application appelle constamment les mêmes procédures stockées, elle conserve ces dernières en mémoire, d’où un gain de temps supplémentaire, en particulier par rapport au temps nécessaire pour appeler à plusieurs reprises le code T-SQL équivalent. Troisièmement, le recours aux procédures stockées réduit le trafic réseau global. Au lieu de transmettre de nombreuses lignes de code T-SQL, SQL Server transmet uniquement le nom de la procédure appelée, ce qui optimise l’utilisation de la bande passante réseau. Enfin, les procédures stockées améliorent les contrôles de sécurité. Même si les utilisateurs n’ont pas le droit d’accéder à des tables spécifiques, vous pouvez leur attribuer l’autorisation d’exécuter une procédure stockée qui référence les tables en question. Cette méthode de limitation des accès aux tables peut soulager le travail de l’administrateur.

Même si les avantages de l’utilisation des procédures stockées sont appréciables, vous devez malgré tout consacrer le temps et l’énergie nécessaires à leur création. L’écriture de procédures stockées pour les opérations UPDATE, INSERT et DELETE prend du temps et le processus fournit pas mal d’occasions d’introduire des erreurs. Par exemple, une procédure stockée type pour une opération UPDATE contient plusieurs parties :
une instruction CREATE PROCEDURE; une liste de paramètres, qui peut être longue en fonction du nombre de colonnes dans la table ; une instruction UPDATE ; une liste SET, qui identifie la liste de colonnes mises à jour par la procédure et une clause WHERE énumérant les critères spécifiant les lignes mises à jour. De même, une procédure stockée servant à introduire des lignes dans une table comporte les parties suivantes : une instruction

CREATE PROCEDURE ;
une liste de paramètres ;
une instruction INSERT;
une liste de colonnes qui identifie les colonnes insérées par la procédure et une clause VALUES énumérant les critères qui spécifient les lignes insérées. La création d’une procédure stockée de suppression de lignes d’une table est relativement simple : elle requiert uniquement des critères de sélection en tant que paramètres et une clause WHERE afin de limiter le nombr