Éléments fondamentaux de l`architecture de SQL Server
publicité
Éléments fondamentaux de l'architecture de SQL Server Microsoft® SQL Server™ est une base de données relationnelle client/serveur basée sur Structured Query Language (SQL). Chacun de ces terms decrit une partie fondamentale de l'architecture de SQL Server. Base de données Une base de donnée est similaire à un fichier de données en ce qu'il s'agit d'un emplacement de stockage de données. De la même façon qu'un fichier de données, une base de données ne présentent pas directement les informations à un utilisateur; ces derniers exécutent une application qui accède aux données de la base de données et les présentent à l'utilisateur sous un format compréhensible. Les systèmes de base de données sont plus puissants que les fichiers de données. Les données sont beaucoup plus organisées. Dans une base de données bien conçue, il n'y a pas d'ensembles de données dupliqués que l'utilisateur ou l'application aient à mettre à jour en même temps. Les ensembles de données associées sont regroupés dans une seule structure ou enregistrement, et des relations peuvent être définies entre ces structures et ces enregistrements. Pour travailler avec des fichiers de données, une application doit être programmée afin de pouvoir travailler avec la structure spécifique de chaque fichier de données. Au contraire, une base de données contient un catalogue que les applications utilisent pour déterminer comment sont organisées les données. Les applications de base de données génériques utilisent le catalogue pour présenter de façon dynamique aux utilisateurs les données en provenance de différentes bases de données, sans être associées à un format de données spécifique. Généralement, une base de données possède deux constituants: les fichiers contenant la base de données physique et le programme de gestion de base de données (SGBD) que les applications utilisent pour accéder aux données. Le SGBD est responsable du respect de la structure de la base de données, c'est-à-dire : de maintenir les relations entre les données dans la base de données. de s'assurer que les données sont correctement conservées et que les règles définissant les relations entre les données ne sont pas violées. de récupérer toutes les données à partir d'un point de cohérence connu en cas de défaillances du système. Base de données relationnelle Il existe différentes façons d'organiser des données dans une base de données, mais les bases de données relationnelles font partie des plus efficaces. Les systèmes de bases de données relationnelles sont une application au problème de l'organisation efficace des données de la théorie mathématique des ensembles. Dans une base de données relationnelle, les données sont regroupées dans des tables (appelées relations dans la théorie relationnelle). Une table représente une classe d'objets qui sont importants pour une organisation. Par exemple, une entreprise peut avoir une base de données comprenant une table des employés, une autre table pour les clients et une dernière pour ses stocks. Chaque table comporte des colonnes et des lignes (attributs et nuplets dans la théorie relationnelle). Chaque colonne représente un attribut de l'objet dont la table est une représentation. Par exemple, une table Employés se compose typiquement de colonnes pour le nom, le prénom, l'identification de l'employé, le service, l'échelon salarial et l'intitulé de poste. Chaque ligne représente une instance de l'objet dont la table est une représentation. Par exemple, un ligne de la table Employés représente l'employé dont le numéro d'identification est ID 12345. Los de l'organisation des données à l'intérieur de tables, il est possible de trouver de nombreuses façons permettant de définir les tables. La théorie des bases de données relationnelles définit le processus normalisation, qui permet de s'assurer que l'ensemble de tables que vous avez défini organisera efficacement vos données. Client/Serveur Dans un système client/serveur, le serveur est un ordinateur relativement important situé en position centrale qui gère des ressources utilisées par de nombreuses personnes. Quand des personnes ont besoin d'utiliser les ressources, elles se connectent par l'intermédiaire du réseau à partir de leurs ordinateurs, ou clients, au serveur. Exemples de serveurs: Serveurs d'impression Gèrent les imprimantes utilisées par une équipe ou un service. Serveurs de fichiers Stockent de gros fichiers utilisés par une unité ou un service qui utilisent d'importants lecteurs. Serveurs de messagerie électronique Exécutent le système de messagerie électronique d'une société. Dans l'architecture d'une base de données client/serveur, les fichiers de la base de données et le logiciel de SGBD sont installés sur un serveur. Un composant de communications est fourni, permettant aux applications de s'exécuter sur des clients distincts et de communiquer avec le serveur de base de données par l'intermédiaire d'un réseau. Le composant de communications de SQL Server autorise également les communications entre une application qui s'exécute sur le serveur et SQL Server. Les applications de Server sont en général capables de travailler avec plusieurs clients en même temps. SQL Server peut travailler simultanément avec des milliers d'applications clientes. Le serveur possède des caractéristiques lui permettant de prévenir les problèmes logiques susceptibles de survenir si un utilisateur essaie de lire ou de modifier des données en cours d'utilisation par d'autres. Bien que SQL Server ait été conçu pour travailler comme serveur à l'intérieur d'un réseau client/serveur, il est également capable de fonctionner comme base de données isolée directement sur un ordinateur client. L'évolutivité et la facilité d'emploi de SQL Server lui permettent de fonctionner efficacement sur un ordinateur client sans consommer trop de ressources. Langage de requêtes structurées (SQL) Pour travailler sur les données d'une base de données, il faut utiliser un jeu de commandes et d'instructions (langage) défini par le logiciel de SGBD. Les bases de données relationnelles peuvent utiliser plusieurs langages différents, le plus courant est SQL. Les standards pour SQL ont été définis par l'American National Standards Institute (ANSI) et l'International Standards Organization (ISO). La plupart des SGBD modernes prennent en charge au moins le Niceau d'entrée de SQL-92, le dernier standard SQL (publié en 1992). Caractéristiques de SQL Server Microsoft® SQL Server™ met en œuvre un ensemble de caractéristiques qui présentent les avantages suivants : Facilité d'installation, de déploiement, et d'utilisation. SQL Server possède un ensemble d'outils de développement et d'administration qui améliorent votre capacité à installer, déployer, gérer et utiliser SQL Server à travers plusieurs sites. Evolutivité Le même moteur de base de données peut être utilisé sur plusieurs plates-formes, allant des ordinateurs portables qui exécutent Microsoft Windows® 95/98 jusqu'aux gros serveurs multiprocesseurs fonctionnant sous Microsoft Windows NT®, Édition Entreprise. Data warehousing SQL Server possède des outils permettant d'extraire et d'analyser des données synthétiques en vue de leur traitement analytique en ligne (OLAP). SQL Server possède aussi des outils pour concevoir visuellement des bases de données et analyser les données à l'aide de questions posées en anglais. Intégration du système avec d'autres logiciels de serveur SQL Server intègre la messagerie électronique, Internet et Windows. Architecture Client/Serveur Microsoft® SQL Server™ est conçu pour fonctionner de manière efficace dans divers environnements : En tant que système de bases de données client/serveur à deux ou plusieurs niveaux. En tant que système de bases de données de bureau. Systèmes de bases de données client/serveur Les systèmes client/serveur sont conçus de telle manière que la base de données puisse résider dans un ordinateur central qui occupe alors la fonction de serveur et puisse être partagée par plusieurs utilisateurs. Les utilisateurs accèdent au serveur par l'intermédiaire d'une application client ou serveur : Dans un système client/serveur à deux niveaux, les utilisateurs exécutent une application en local sur leur ordinateur (lequel est le poste client) qui les connecte, en passant par le réseau, au serveur sur lequel s'exécute SQL Server. L'application client, qui exécute la logique d'entreprise et le code permettant d'afficher le résultat à l'utilisateur, est également appelée client lourd. Dans un système client/serveur à plusieurs niveaux, la logique de l'application client s'exécute en deux endroits : Le client léger s'exécute en local sur l'ordinateur de l'utilisateur et vise à afficher les résultats à l'utilisateur. La logique d'entreprise se retrouve dans les applications serveur qui s'exécutent sur un serveur. Les clients légers demandent des fonctions à l'application serveur, qui est elle-même une application parallélisée capable de fonctionner simultanément avec plusieurs utilisateurs. C'est l'application serveur qui ouvre les connexions au serveur de base de données, et elle peut s'exécuter sur le même serveur que la base de données. Elle peut également se connecter, en passant par le réseau, à un autre serveur qui fonctionne en tant que serveur de base de données. Ceci est le scénario typique d'une application Internet. Par exemple, une application serveur peut s'exécuter sous Microsoft Internet Information Services (IIS) et servir des milliers de clients légers qui s'exécutent sur Internet ou sur un intranet. L'application serveur utilise un ensemble de connexions pour communiquer avec une copie de SQL Server. SQL Server peut s'installer sur le même ordinateur que IIS ou sur un autre serveur du réseau. Le fait d'avoir des données stockées et gérées à un endroit centralisé présente plusieurs avantages : Chaque élément de données est stocké en un point central, accessible à tous les utilisateurs. Les différentes copies des données ne sont pas stockées sur les postes clients, ce qui élimine, par exemple, les problèmes avec les utilisateurs qui ont à s'assurer qu'ils travaillent bien avec les mêmes informations. Les règles relatives à l'entreprise et à la sécurité doivent être définies une fois pour toutes sur le serveur, et contraignent tous les utilisateurs au même niveau et de manière identique. Ceci est possible dans une base de données en utilisant les contraintes, les procédures stockées et les déclencheurs. C'est également possible dans une application serveur. Un serveur de base de données relationnelle permet d'optimiser le trafic sur le réseau, en ne renvoyant que les données nécessaires à chaque application. Par exemple, si une application utilisant un fichier de serveur a besoin d'afficher la liste des noms de tous les vendeurs (Sales Representative) de l'Orégon (OR), elle doit normalement récupérer le fichier des employés (employee) dans sa totalité. Au contraire, si cette application communique avec un serveur de base de données relationnelle, il lui suffit d'exécuter la commande suivante : SELECT first_name, last_name FROM employees WHERE emp_title = 'Sales Representative' AND emp_state = 'OR' La base de données relationnelle renverra uniquement les noms de tous les vendeurs de l'Oregon, et non l'intégralité des informations relatives à tous les employés. Les coûts matériels peuvent ainsi être minimisés. Étant donné que les données ne sont pas stockées sur chaque poste client, il n'est pas nécessaire pour l'utilisateur, de réserver de l'espace disque pour le stockage de ces données. Il n'est donc pas nécessaire pour les postes clients de disposer de capacité de traitement pour gérer les données en local. Quant au serveur, il est dispensé de mobiliser sa puissance de traitement pour afficher les données. Le serveur peut être configuré en vue d'optimiser les capacités d'E/S du disque afin de rechercher les données, tandis que les postes clients peuvent en revanche être configurés en vue d'optimiser les opérations de mise en forme et d'affichage des données récupérées sur le serveur. Le serveur doit être stocké dans un endroit relativement bien sécurisé et être équipé en périphériques tels qu'un onduleur, qui est plus économique qu'un dispositif général de protection de tous les postes clients. Les tâches de maintenance telles que la sauvegarde et la restauration des données, sont donc plus simples, puisqu'elles ne concernent que le serveur central. Au sein d'un important système client/serveur, des milliers d'utilisateurs peuvent être connectés simultanément au serveur qui exécute SQL Server. SQL Server est muni d'un dispositif complet de protection pour ce type d'environnements avec, par exemple, des sécurités évitant des problèmes tels que le fait d'avoir plusieurs utilisateurs qui essayent simultanément de mettre à jour les mêmes données. SQL Server est capable d'allouer efficacement à plusieurs utilisateurs les ressources disponibles telles que la mémoire, la largeur de la bande réseau ou encore les E/S disque. Des applications SQL Server peuvent être exécutées sur l'ordinateur même où s'exécute SQL Server. L'application se connecte à SQL Server en utilisant les composants de communication interprocessus (IPC) de Windows (telle que la mémoire partagée), au lieu de passer par un réseau. Cela permet à SQL Server de pouvoir être utilisé avec de petits systèmes sur lesquels, par exemple, une application donnée a besoin de stocker ses données localement. Systèmes de bases de données de bureau Efficace en tant que serveur, SQL Server peut également être utilisé avec des applications qui ont besoin d'utiliser une base de données autonome stockée localement sur le poste client. SQL Server peut, de façon dynamique, se configurer lui-même, afin de s'exécuter avec les ressources disponibles sur un poste client, sans qu'il soit pour cela besoin de dédier à chaque poste client un administrateur de base de données. Les fournisseurs d'applications peuvent également intégrer SQL Server en tant que composant de stockage de données dans leurs applications. Lorsque les clients utilisent des bases de données locales SQL Server, une copie du moteur de base de données SQL Server s'exécute sur le poste client et gère toutes les bases de données SQL Server qui y sont installées. Les applications se connectent au moteur de base de données de la même manière que lorsqu'elles se connectent, en passant par le réseau, à un moteur de base de données qui s'exécute sur un serveur distant. Architecture de base de données Les données de Microsoft® SQL Server™ sont stockées dans des bases de données et sont organisées en composants logiques visibles par les utilisateurs. Une base de données est également implémentée physiquement sous forme de deux fichiers ou plus sur disque. Lorsque vous utilisez une base de données, vous commencez par manipuler les composants logiques tels que les tables, les vues, les procédures et les utilisateurs. L'implémentation physique des fichiers est en grande partie transparente. Cette tâche revient par défaut à l'administrateur de la base de données. Chaque installation SQL Server possède plusieurs bases de données. SQL Server dispose de quatre bases de données système (master, model, tempdb et msdb), chaque installation SQL Server possédant une ou plusieurs bases de données utilisateur. Certaines entreprises ne disposent que d'une base de données utilisateur unique qui regroupe toutes leurs données. D'autres, en revanche, possèdent des bases de données propres à une division de leur entreprise et, parfois, d'une base de données réservée à une seule application. Ainsi, une entreprise peut disposer d'une base de données spécifique aux ventes, aux fiches de paie, à une application de gestion de la documentation, etc. Il arrive aussi qu'une application fasse appel à une ou plusieurs bases de données. Il n'est pas nécessaire d'exécuter plusieurs copies de SQL Server pour permettre à plusieurs utilisateurs d'accéder aux bases de données qui sont installées sur le serveur. SQL Server est capable de prendre en charge des centaines d'utilisateurs travaillant simultanément sur plusieurs bases de données d'un même site. Celles-ci sont accessibles à tous les utilisateurs qui se connectent au serveur, à condition que ces derniers disposent des autorisations d'accès nécessaires. Lorsque vous vous connectez à SQL Server, la connexion est associée à une base de données spécifique appelée la base de données en cours. En principe, il s'agit de la base de données par défaut définie par l'administrateur système, bien que vous puissiez en spécifier une autre grâce aux options de connexion proposées dans les API de la base de données. Vous pouvez passer d'une base de données à une autre à l'aide de l'instruction Transact-SQL USE nom_base_de_données ou utiliser une fonction d'API modifiant le contexte de la base de données en cours. La version 7.0 de SQL Server vous permet de détacher des bases de données d'un serveur, de les réattacher à un autre serveur ou au serveur d'origine. Si vous disposez d'un fichier de base de données SQL Server, vous pouvez indiquer à SQL Server, au moment de la connexion, que vous souhaitez attacher ce fichier à une base de données spécifique. Composants de la base de données logique Les données d'une base de données Microsoft® SQL Server™ sont organisées en plusieurs objets, visibles par un utilisateur lorsqu'il se connecte à la base de données. Dans SQL Server, les composants suivants sont définis en tant qu'objets : Les contraintes Les tables Les valeurs par défaut Les déclencheurs Les types de données définis par Les index l'utilisateur Les clés Les vues Les procédures stockées Types de données et structures de table Toutes les données des bases de données Microsoft® SQL Server™ sont contenues dans des objets appelés tables, chacune d'entre elles représentant un type d'objet significatif pour les utilisateurs. Dans la base de données d'une école, par exemple, il est logique de trouver des tables relatives au cours, au professeur et à l'élève. Les tables SQL Server sont constituées de deux composants principaux : Colonnes Chaque colonne représente un attribut quelconque de l'objet modélisé par la table, comme une table de pièces détachées comportant des colonnes réservées à l'ID, à la couleur et à l'épaisseur. Lignes Chaque ligne représente une seule occurrence de l'objet modélisé par la table. Par exemple, la table de pièces détachées pourrait comporter une ligne pour chaque pièce vendue par l'entreprise. Étant donné que chaque colonne représente un attribut d'un objet, les données de chaque occurrence de la colonne sont similaires. L'une des propriétés d'une colonne s'appelle le type de données. SQL Server possède plusieurs types de données de base : binary bit char datetime decimal float image int money nchar ntext nvarchar numeric real smalldatetime smallint smallmoney sysname text timestamp tinyint varbinary varchar uniqueidentifier Les utilisateurs peuvent également créer leurs propres types de données, par exemple : -- Create a birthday data type that allows nulls. EXEC sp_addtype birthday, datetime, 'NULL' GO -- Create a table using the new data type. CREATE TABLE employee (emp_id char(5), emp_first_name char(30), emp_last_name char(40), emp_birthday birthday) Un type de données défini par l'utilisateur facilite la lisibilité de la structure de la table et garantit que les colonnes contenant des catégories de données similaires possèdent le même type de données de base. Un domaine désigne l'ensemble de toutes les valeurs autorisées dans une colonne. Il comprend le type de données et les valeurs autorisées dans les colonnes. Ainsi, un domaine couleur de pièce comprendrait le type de données char(6) ainsi que les chaînes de caractères autorisées dans la colonne telles que Rouge, Bleu, Vert, Jaune, Marron, Noir, Blanc, Vert foncé, Gris et Argent. Les valeurs de domaine peuvent être imposées par l'intermédiaire de mécanismes comme les contraintes CHECK et les déclencheurs. Les colonnes peuvent accepter ou rejeter les valeurs NULL. NULL est un concept spécifique aux bases de données désignant une valeur de type inconnu, contrairement au blanc ou au zéro (0), qui représentent respectivement un caractère et un nombre reconnus. NULL se distingue également d'une chaîne de longueur nulle. Si une définition de colonne contient la clause NOT NULL, cela signifie que vous ne pouvez pas insérer de ligne ayant la valeur NULL. Si la définition de colonne comporte uniquement le mot clé NULL, vous pouvez insérer des valeurs NULL. L'autorisation des valeurs NULL dans une colonne peut augmenter la complexité de toutes les comparaisons logiques utilisant cette colonne. Selon la norme SQL-92, toute comparaison effectuée à l'aide d'une valeur NULL ne prend pas la valeur TRUE ou FALSE mais la valeur UNKNOWN. Cela se traduit donc par l'introduction de trois opérateurs de comparaison logiques pouvant entraîner des problèmes de gestion. SQL Server stocke les données définissant la configuration du serveur et toutes ses tables dans un ensemble de tables spécifique appelé tables système. Il est impossible de les consulter ou de les modifier directement. Seul SQL Server peut les référencer en réponse aux commandes d'administration lancées par les utilisateurs. Les tables système pouvant changer d'une version à l'autre, il s'avère parfois nécessaire de réécrire les applications qui les référencent directement avant de passer à une nouvelle version de SQL Server utilisant une autre version des tables système. SQL Server prend en charge les tables temporaires dont le nom commence par le signe dièse (#). Si une table temporaire n'est pas supprimée lors de la déconnexion de l'utilisateur, elle l'est automatiquement par SQL Server. Les tables temporaires ne sont pas stockées dans la base de données en cours, mais dans la base de données système tempdb. Il en existe deux types : Les tables temporaires locales possèdent un seul signe dièse (#) au début de leur nom. Elles ne sont visibles que de la connexion qui les a créées. Les tables temporaires globales possèdent un double signe dièse (##) au début de leur nom. Elles sont visibles par toutes les connexions. Si elles ne sont pas supprimées explicitement avant la fin de la connexion qui les a créées, elles le sont dès que toutes les autres tâches ont cessé de les référencer. Aucune nouvelle tâche ne peut référencer une table temporaire globale une fois la déconnexion effectuée. Par ailleurs, l'association entre une tâche et une table étant toujours supprimée une fois l'instruction en cours terminée, les tables temporaires globales sont en général supprimées peu après la fin de la connexion qui les a créées. Les utilisateurs manipulent les données dans les tables à l'aide des instructions du langage de manipulation des données (DML) SQL : -- Get a list of all employees named Smith: SELECT emp_first_name, emp_last_name FROM employee WHERE emp_last_name = 'Smith' -- Delete an employee who quit: DELETE employee WHERE emp_id = 'OP123' -- Add a new employee: INSERT INTO employee VALUES ( 'OP456', 'Dean', 'Straight', '01/01/1960') -- Change an employee name: UPDATE employee SET emp_last_name = 'Smith' WHERE emp_id = 'OP456' Vues SQL Une vue peut être représentée comme une table virtuelle ou une requête stockée. Les données accessibles par l'intermédiaire d'une vue ne sont pas stockées dans la base de données sous forme d'objet distinct. C'est une instruction SELECT qui est stockée, le jeu de résultats de cette instruction constituant la table virtuelle renvoyée par la vue. Un utilisateur peut l'utiliser en référençant le nom de la vue dans les instructions Transact-SQL de la même façon qu'une table. Une vue permet d'exécuter tout ou une partie des fonctions suivantes : Limiter les droits d'accès d'un utilisateur à certaines lignes d'une table. Vous pouvez, par exemple, accorder à un employé l'autorisation de visualiser uniquement les lignes concernant ses heures de travail dans la table de suivi de la main-d'œuvre. Limiter les droits d'accès d'un utilisateur à certaines colonnes. Vous pouvez, par exemple accorder aux employés qui ne travaillent pas dans le service de la paye, l'autorisation en lecture des colonnes Nom, Bureau, Téléphone professionnel et Division de la table des employés, mais leur refuser l'accès aux informations d'ordre privé ou financier. Relier les colonnes issues de plusieurs tables de façon à leur conférer l'aspect d'une table unique. Rassembler des informations au lieu de les détailler. Vous pouvez, par exemple, présenter le total d'une colonne, ou encore sa valeur minimale ou maximale. Pour créer une vue, il convient de définir l'instruction SELECT qui récupère les données à présenter dans la vue. Les tables de données référencées par l'instruction SELECT sont appelées les tables de base de la vue. titleview dans la base de données pubs est un exemple de vue qui sélectionne les données issues de trois tables de base, pour présenter une table virtuelle des données les plus fréquentes. CREATE VIEW titleview AS SELECT title, au_ord, au_lname, price, ytd_sales, pub_id FROM authors AS a JOIN titleauthor AS ta ON (a.au_id = ta.au_id) JOIN titles AS t ON (t.title_id = ta.title_id) Vous pouvez ensuite référencer titleview dans les instructions de la même façon qu'une table. SELECT * FROM titleview Une vue peut en référencer une autre. Ainsi, titleview présente des informations utiles aux directeurs mais, en général, une société n'a besoin que des chiffres cumulés jusqu'à ce jour dans ses états financiers annuels ou trimestriels. Il est possible de construire une vue sélectionnant toutes les colonnes de titleview à l'exception de au_ord et de ytd_sales. Les clients peuvent s'en servir pour obtenir les listes des catalogues disponibles sans accéder aux informations financières : CREATE VIEW Cust_titleview AS SELECT title, au_lname, price, pub_id FROM titleview Les vues de Microsoft® SQL Server™ peuvent être mises à jour (elles peuvent être la cible des instructions UPDATE, DELETE ou INSERT) tant que la modification n'affecte que l'une des tables de base référencées par la vue. -- Increase the prices for publisher '0736' by 10%. UPDATE titleview SET price = price * 1.10 WHERE pub_id = '0736' GO Procédures stockées SQL Une procédure stockée est un groupe d'instructions Transact-SQL compilées en un plan d'exécution unique. Les procédures stockées de Microsoft® SQL Server™ renvoient les données de quatre façons différentes : Les paramètres en sortie, qui renvoient des données (telles qu'un entier ou un caractère) ou une variable de curseur (les curseurs sont des jeux de résultats pouvant être extraits une ligne à la fois). Les codes renvoyés, qui sont toujours un entier. Un jeu de résultats pour chaque instruction SELECT contenue dans la procédure stockée ou toute autre procédure stockée appelée par la procédure stockée. Un curseur global qui peut être référencé en dehors de la procédure stockée. Les procédures stockées contribuent à mettre en œuvre une logique cohérente dans les applications. Les instructions SQL et la logique nécessaires à l'exécution d'une tâche fréquente peuvent être créées, codées et testées une seule fois dans une procédure stockée. Il suffit ensuite à chaque application devant effectuer la tâche d'exécuter la procédure stockée. Le codage de la logique de gestion en une seule procédure offre aussi un point de contrôle unique permettant de vérifier que les règles d'entreprise sont bien respectées. Les procédures stockées peuvent également améliorer les performances. De nombreuses tâches sont mises en œuvre sous forme de séquences d'instructions SQL. La logique conditionnelle appliquée aux résultats des premières instructions SQL détermine les instructions suivantes à exécuter. Si ces instructions SQL et la logique conditionnelle sont écrites dans une procédure stockée, elles deviennent partie intégrante d'un plan d'exécution unique sur le serveur. Les résultats n'ont pas besoin d'être renvoyés au client pour que la logique conditionnelle soit appliquée, car tout le travail est réalisé sur le serveur. Dans cet exemple, l'instruction IF montre l'imbrication d'une logique conditionnelle dans une procédure afin d'empêcher l'envoi d'un ensemble de résultats à l'application : IF (@QuantityOrdered < (SELECT QuantityOnHand FROM Inventory WHERE PartID = @PartOrdered) ) BEGIN -- SQL statements to update tables and process order. END ELSE BEGIN -- SELECT statement to retrieve the IDs of alternate items -- to suggest as replacements to the customer. END Les applications n'ont pas besoin de transmettre l'intégralité des instructions SQL dans la procédure : elles n'ont à transmettre que les instructions EXECUTE ou CALL qui contiennent le nom de la procédure et les valeurs des paramètres. Les procédures stockées évitent aussi aux utilisateurs d'avoir à connaître les détails des tables de la base de données. Si un ensemble de procédures stockées prend en charge toutes les fonctions de gestion nécessaires aux utilisateurs, ceux-ci n'ont pas besoin d'accéder directement aux tables ; il leur suffit d'exécuter les procédures stockées qui modélisent les processus avec lesquels ils ont l'habitude de travailler. Les procédures stockées du système SQL Server évitant aux utilisateurs d'accéder aux tables système en sont un exemple. SQL Server comprend un ensemble de procédures stockées système dont les noms commencent en général par sp_. Ces procédures prennent en charge toutes les tâches administratives nécessaires à l'exécution d'un système SQL Server. Vous pouvez administrer un système SQL Server à l'aide des instructions Transact-SQL associées à l'administration (telles que CREATE TABLE) ou des procédures stockées du système, sans jamais avoir à mettre à jour directement les tables système. Dans les versions antérieures de SQL Server, les procédures stockées constituaient une façon de précompiler partiellement un plan d'exécution. Au moment où la procédure stockée était créée, un plan d'exécution partiellement compilé était enregistré dans une table système. L'exécution d'une procédure stockée était plus efficace que celle d'une instruction SQL, car SQL Server n'avait pas besoin de compiler complètement un plan d'exécution ; il n'avait qu'à finir d'optimiser le plan stocké pour la procédure. De même, la compilation complète d'un plan d'exécution pour la procédure stockée a été conservée dans le cache de procédure de SQL Server, ce qui signifie que les exécutions ultérieures de la procédure stockée pourront utiliser le plan d'exécution précompilé. SQL Server version 7.0 apporte de nombreuses modifications au traitement des instructions ce qui permet d'étendre à toutes les instructions SQL la plupart des améliorations des performances dues aux procédures stockées. Au moment de la création de procédures stockées, SQL Server 7.0 n'enregistre pas un plan partiellement compilé. Une procédure stockée est compilée au moment de son exécution comme n'importe laquelle des instructions Transact-SQL. SQL Server 7.0 conserve les plans d'exécution de toutes les instructions SQL dans le cache de procédure, et pas uniquement les plans d'exécution des procédures stockées. Un algorithme performant compare les nouvelles instructions Transact-SQL à celles des plans d'exécution existants. Si une correspondance est trouvée, SQL Server 7.0 réutilise le plan d'exécution. Ceci réduit le bénéfice de performances relatif de la précompilation des procédures stockées en étendant la réutilisation des plans d'exécution à toutes les instructions SQL. SQL Server 7.0 offre une nouvelle alternative au traitement des instructions SQL. Pour plus d'information, voir Architecture du processeur de requêtes SQL Server prend également en charge les procédures stockées temporaires qui, à l'instar des tables temporaires, sont automatiquement supprimées à la déconnexion. Les procédures stockées temporaires sont stockées dans tempdb et elles sont utiles en cas de connexion avec les versions antérieures de SQL Server. Vous pouvez les utiliser lorsqu'une application construit des instructions Transact-SQL dynamiques exécutées plusieurs fois. Au lieu d'avoir à recompiler à chaque fois ces instructions, vous pouvez créer une procédure stockée temporaire qui est compilée à la première exécution, puis exécuter le plan précompilé plusieurs fois. Cependant, une utilisation intensive des procédures stockées temporaires, peut conduire à un encombrement des tables système dans tempdb. Les deux fonctions suivantes de SQL Server 7.0 réduisent l'utilisation des procédures stockées temporaires : SQL Server 7.0 est capable de réutiliser les plans d'exécution issus d'instructions SQL précédentes. Ceci est particulièrement efficace en cas de couplage avec l'utilisation de la nouvelle procédure stockée système sp_executesql. SQL Server 7.0 gère le modèle de préparation/exécution d'OLE DB et d'ODBC en mode natif sans utiliser de procédure stockée. Pour plus d'information sur les alternatives à l'utilisation des procédures stockées temporaires, voir Mise en mémoire cache et réutilisation du plan d'exécution Cette simple procédure stockée illustre les trois manières selon lesquelles les procédures stockées renvoient des données : 1. La procédure lance d'abord une instruction SELECT qui renvoie un jeu de résultats regroupant l'activité de commandes des magasins dans la table sales. 2. Elle lance ensuite une instruction SELECT qui complète un paramètre en sortie. 3. Enfin, elle comporte une instruction RETURN accompagnée d'une instruction SELECT qui renvoie un entier. Les codes renvoyés permettent en général de retransmettre des informations de contrôle d'erreur. Comme cette procédure s'exécute sans erreur, elle renvoie une autre valeur pour montrer comment les codes renvoyés sont complétés. USE Northwind GO DROP PROCEDURE OrderSummary GO CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS -- SELECT to return a result set summarizing -- employee sales. SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity) FROM Orders AS Ord JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID) GROUP BY Ord.EmployeeID ORDER BY Ord.EmployeeID -- SELECT to fill the output parameter with the -- maximum quantity from Order Details. SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details] -- Return the number of all items ordered. RETURN (SELECT SUM(Quantity) FROM [Order Details]) GO -- Test the stored procedure. -- DECLARE variables to hold the return code -- and output parameter. DECLARE @OrderSum INT DECLARE @LargestOrder INT -- Execute the procedure, which returns -- the result set from the first SELECT. EXEC @OrderSum = OrderSummary @MaxQuantity = @LargestOrder OUTPUT -- Use the return code and output parameter. PRINT 'The size of the largest single order was: ' + CONVERT(CHAR(6), @LargestOrder) PRINT 'The sum of the quantities ordered was: ' + CONVERT(CHAR(6), @OrderSum) GO Le résultat généré par l'exécution de cet exemple est le suivant : EmployeeID SummSales ----------- -------------------------1 202,143.71 2 177,749.26 3 213,051.30 4 250,187.45 5 75,567.75 6 78,198.10 7 141,295.99 8 133,301.03 9 82,964.00 The size of the largest single order was: 130 The sum of the quantities ordered was: 51317 Contraintes, règles, valeurs par défaut et déclencheurs Les colonnes des tables possèdent des propriétés autres que le type et la taille des données. Ces propriétés constituent une composante importante du mécanisme assurant l'intégrité des données d'une base de données. L'intégrité des données se rapporte à chaque occurrence d'une colonne possédant une valeur de donnée correcte. Par valeur de donnée correcte, on entend un type de données et un domaine valides. L'intégrité référentielle indique que les relations existant entre les tables sont correctes. Les données d'une table doivent pointer uniquement sur les lignes existantes d'une autre table, pas sur des lignes inexistantes. Les objets utilisés pour mettre à jour les deux types d'intégrité sont : Les contraintes Les règles Les valeurs par défaut Les déclencheurs Contraintes Les contraintes sont un moyen permettant à Microsoft® SQL Server™ d'assurer automatiquement l'intégrité d'une base de données. Les contraintes définissent des règles relatives aux valeurs autorisées dans les colonnes et elles constituent le mécanisme standard de mise en application de l'intégrité plutôt que les déclencheurs, les règles ou les valeurs par défaut. L'optimiseur de requêtes s'en sert aussi pour améliorer les performances liées à l'évaluation de la sélectivité, au calcul des coûts et à la réécriture des requêtes. Il existe cinq catégories de contraintes. NOT NULL indique que la colonne n'accepte pas les valeurs NULL. Les contraintes CHECK assurent l'intégrité du domaine en limitant les valeurs placées dans une colonne. Une contrainte CHECK indique une condition de recherche de type booléen (valeurs TRUE ou FALSE) appliquée à toutes les valeurs saisies dans la colonne ; toutes les valeurs ne prenant pas la valeur TRUE sont rejetées. Vous pouvez spécifier plusieurs contraintes CHECK pour chaque colonne. Cet exemple montre la création d'une contrainte nommée, chk_id, qui vérifie par la suite le domaine de la clé primaire en veillant à ce que seuls les nombres compris dans la plage spécifiée soient saisis pour la clé. CREATE TABLE cust_sample ( cust_id int PRIMARY KEY, cust_name char(50), cust_address char(50), cust_credit_limit money, CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 ) ) Les contraintes UNIQUE garantissent le caractère unique des valeurs dans un ensemble de colonnes. Dans une contrainte UNIQUE, deux lignes d'une table ne peuvent pas avoir la même valeur non NULL dans les colonnes. Les clés primaires mettent aussi en application l'unicité des valeurs, mais elles n'autorisent pas les valeurs NULL. Une contrainte UNIQUE est préférable à un index unique. Les contraintes PRIMARY KEY identifient une colonne ou un ensemble de colonnes dont les valeurs identifient de manière unique chaque ligne d'une table. Deux lignes d'une table ne peuvent pas avoir la même valeur de clé primaire. Dans une clé primaire, il est impossible d'entrer une valeur NULL dans une colonne. NULL est une valeur spécifique aux bases de données qui représente une valeur inconnue, distincte d'un blanc ou de la valeur 0. L'utilisation d'une petite colonne d'entiers comme clé primaire est recommandée. Chaque table devrait avoir une clé primaire. Une table peut être constituée de plusieurs combinaisons de colonnes identifiant de manière unique les lignes d'une table ; chaque combinaison est une clé candidate. L'administrateur de la base de données sélectionne l'une des clés candidates pour en faire la clé primaire. Dans la table part_sample, par exemple, part_nmbr et part_name pourraient être des clés candidates, mais seule part_nmbr est choisie comme clé primaire. CREATE TABLE part_sample (part_nmbr int PRIMARY KEY, part_name char(30), part_weight decimal(6,2), part_color char(15) ) Les contraintes FOREIGN KEY identifient les relations entre les tables. Une clé étrangère dans une table pointe sur une clé candidate dans une autre table. Vous ne pouvez pas insérer de ligne ayant une valeur de clé étrangère (à l'exception de NULL) s'il n'existe pas de clé candidate possédant cette valeur. Vous ne pouvez pas supprimer de ligne dans la table référencée s'il existe une valeur de clé étrangère référençant cette clé candidate. Dans l'exemple ci-dessous, la table order_part crée une clé étrangère référençant la table part_sample définie précédemment. En principe, order_part devrait avoir aussi une clé étrangère correspondante dans une table de commandes, mais il s'agit ici d'un exemple simple. CREATE TABLE order_part (order_nmbr int, part_nmbr int FOREIGN KEY REFERENCES part_sample(part_nmbr), qty_ordered int) GO Les contraintes peuvent porter sur une colonne ou une table : Une contrainte de colonne est déclarée comme faisant partie de la définition de la colonne et ne s'applique qu'à celle-ci (c'est le cas des contraintes des exemples précédents). Une contrainte de table est déclarée indépendamment de la définition de la colonne et s'applique à plusieurs colonnes d'une table. Les contraintes de table sont obligatoires lorsque plusieurs colonnes doivent être incluses dans une contrainte. Si, par exemple, une table dispose de deux colonnes ou plus dans la clé primaire, vous devez utiliser une contrainte de table pour inclure les deux colonnes dans la clé primaire. Prenons l'exemple d'une table enregistrant les événements survenant sur une machine dans une usine. Supposons que des événements de plusieurs types puissent se produire au même moment, mais qu'aucun des deux événements survenant au même moment ne soit du même type. Pour reproduire ce cas de figure dans une table, vous pouvez inclure les colonnes type et time dans une clé primaire à deux colonnes. CREATE TABLE factory_process (event_type int, event_time datetime, event_site char(50), event_desc char(1024), CONSTRAINT event_key PRIMARY KEY (event_type, event_time) ) Règles Les règles sont une fonction de compatibilité ascendante exécutant certaines des fonctions effectuées par les contraintes CHECK. Les contraintes CHECK sont le moyen standard et le plus efficace de limiter les valeurs d'une colonne. Elles sont également plus concises que les règles. Il ne peut y avoir qu'une seule règle appliquée à une colonne, contrairement aux contraintes CHECK qui peuvent être multiples. Les contraintes CHECK font partie intégrante de l'instruction CREATE TABLE, alors que les règles sont créées sous forme d'objets séparés, liés ensuite à la colonne. Cet exemple crée une règle exécutant la même fonction que la contrainte CHECK citée en exemple dans la rubrique précédente. La contrainte CHECK est la méthode recommandée dans Microsoft® SQL Server™. CREATE RULE id_chk AS @id BETWEEN 0 and 10000 GO CREATE TABLE cust_sample ( cust_id int PRIMARY KEY, cust_name char(50), cust_address char(50), cust_credit_limit money, ) GO sp_bindrule id_chk, 'cust_sample.cust_id' GO Valeurs par défaut Les valeurs par défaut sont utilisées dans une colonne si vous n'en indiquez pas d'autres lors de l'insertion d'une ligne. Il peut s'agir de toute valeur prenant la valeur d'une constante : Constante Fonction intégrée Expression mathématique Il existe deux moyens d'appliquer les valeurs par défaut : Créer une définition par défaut à l'aide du mot clé DEFAULT dans l'instruction CREATE TABLE pour affecter une expression de constante comme valeur par défaut à une colonne. Cette méthode est recommandée. Elle est également la plus concise. Créer un objet par défaut à l'aide de l'instruction CREATE DEFAULT et le lier aux colonnes à l'aide de la procédure stockée du système sp_bindefault. Il s'agit d'une fonction de compatibilité ascendante. Cet exemple crée une table à l'aide de chaque type de valeur par défaut. Il crée ensuite un objet par défaut pour affecter une valeur par défaut à une colonne et lie cet objet à la colonne. Puis il réalise un test d'insertion sans indiquer de valeur pour les colonnes comportant des valeurs par défaut et extrait la ligne de test pour vérifier que les valeurs par défaut ont bien été appliquées. USE pubs GO CREATE TABLE test_defaults (keycol smallint, process_id smallint DEFAULT @@SPID, --Preferred default definition date_ins datetime DEFAULT getdate(), --Preferred default definition mathcol smallint DEFAULT 10 * 2, --Preferred default definition char1 char(3), char2 char(3) DEFAULT 'xyz') --Preferred default definition GO /* Illustration only, use DEFAULT definitions instead.*/ CREATE DEFAULT abc_const AS 'abc' GO sp_bindefault abc_const, 'test_defaults.char1' GO INSERT INTO test_defaults(keycol) VALUES (1) GO SELECT * FROM test_defaults GO Le résultat de cet exemple est le suivant : Default bound to column. (1 row(s) affected) keycol process_id date_ins mathcol char1 char2 ------ ---------- --------------------------- ------- ----- ---1 7 Oct 16 1997 8:34PM 20 abc xyz (1 row(s) affected) Déclencheurs Les déclencheurs constituent une catégorie particulière de procédures stockées ; ils sont définis pour s'exécuter automatiquement lors du lancement sur une table d'une instruction UPDATE, INSERT ou DELETE. Ils constituent un outil puissant qui permet à chaque site de mettre automatiquement en application ses règles de gestion quand des données sont modifiées. Les déclencheurs peuvent étendre la logique de contrôle d'intégrité des contraintes, des valeurs par défaut et des règles de Microsoft® SQL Server™, même si l'utilisation des contraintes et des valeurs par défaut est préférable à chaque fois, bien que ces dernières fournissent toutes les fonctionnalités nécessaires. Les tables peuvent comporter plusieurs déclencheurs. L'instruction CREATE TRIGGER peut être définie avec les clauses FOR UPDATE, FOR INSERT ou FOR DELETE pour affecter un déclencheur à une catégorie spécifique d'actions de modification des données. Lorsque la clause FOR UPDATE est spécifiée, la clause IF UPDATE (nom_colonne) affecte un déclencheur aux mises à jour portant sur une colonne spécifique. SQL Server permet d'attribuer plusieurs déclencheurs à une action spécifique (UPDATE, INSERT ou DELETE) dans une table unique. Les déclencheurs permettent aussi d'automatiser certaines tâches dans une entreprise. Dans un système de gestion de stock, les déclencheurs de mise à jour peuvent détecter le seuil de réapprovisionnement et générer automatiquement une commande au fournisseur. Dans une base de données enregistrant les procédés de fabrication d'une usine, les déclencheurs peuvent prévenir les opérateurs par radiomessagerie ou par courrier électronique qu'un procédé dépasse les limites de sécurité autorisées. Le déclencheur suivant génère un courrier électronique à chaque fois qu'un nouveau titre est ajouté à la base de données pubs. CREATE TRIGGER reminder ON titles FOR INSERT AS EXEC master..xp_sendmail 'MaryM', 'New title, mention in the next report to distributors.' Les déclencheurs contiennent des instructions Transact-SQL, à l'instar des procédures stockées. Ils renvoient l'ensemble des résultats généré par toute instruction SELECT au déclencheur. Il est déconseillé d'inclure des instructions SELECT dans les déclencheurs, à l'exception de celles qui ne remplissent que des paramètres, car les utilisateurs ne s'attendent pas à voir apparaître des jeux de résultats à la suite d'une instruction UPDATE, INSERT ou DELETE. Les déclencheurs s'exécutent une fois terminée l'instruction qui les a lancés. Si l'instruction échoue du fait d'une erreur, telle qu'une violation de contrainte ou une erreur de syntaxe, le déclencheur n'est pas exécuté. Bases de données et données système Les systèmes Microsoft® SQL Server™ possèdent quatre bases de données système : master La base de données master enregistre l'intégralité des informations système relatives à un système SQL Server. Tous les comptes de connexion et les paramètres de configuration du système y sont consignés. La base de données master enregistre l'existence de toutes les autres bases de données et l'emplacement des fichiers primaires contenant les informations d'initialisation des bases de données utilisateur. Elle enregistre les informations d'initialisation de SQL Server, de sorte qu'une sauvegarde récente de la base est toujours disponible. tempdb tempdb contient toutes les tables et les procédures stockées temporaires. Elle répond également à tout autre besoin de stockage temporaire tel que les tables de travail générées par SQL Server. tempdb est une ressource globale ; les tables temporaires et les procédures stockées de tous les utilisateurs connectés au système y sont stockées. tempdb est recréée à chaque fois que SQL Server est lancé de façon que le système démarre avec une copie propre de la base de données. Les tables et les procédures stockées temporaires sont automatiquement supprimées à la déconnexion et aucune connexion n'est active à l'arrêt du système. De ce fait, tempdb ne contient jamais rien qui doive être sauvegardé d'une session SQL Server à une autre. tempdb se développe automatiquement au fur et à mesure des besoins. À chaque redémarrage du système, tempdb reprend sa taille par défaut. Vous pouvez éviter la surchage liée à l'extension automatique de tempdb en utilisant l'instruction ALTER TABLE pour augmenter la taille de tempdb. model La base de données model fait office de modèle pour toutes les bases de données créées sur un système. Lors de l'émission d'une instruction CREATE DATABASE, la première partie de la nouvelle base de données est créée par copie du contenu de la base de données model, le reste étant constitué de pages vides. La base de données tempdb étant créée à chaque démarrage de SQL Server, la base de données model doit toujours exister sur un système SQL Server. msdb La base de données msdb permet à SQL Server Agent de planifier les alertes et les travaux et d'enregistrer les opérateurs. Dans la version 7.0 de SQL Server, chaque base de données (base de données système comprises), possède son propre jeu de fichiers qui n'est pas partagé avec les autres bases de données. L'emplacement par défaut de ces fichiers est le répertoire C:\Mssql7\Data : Fichier de base de Nom de fichier Taille par défaut, données physique. installation standard données primaires de master.mdf 7.5 Mo master journal de master mastlog.ldf 1.0 Mo données primaires de tempdb.mdf 8.0 Mo tempdb journal de tempdb templog.ldf 0.5 Mo données primaires de model.mdf 0,75 Mo model journal de model modellog.ldf 0,75 Mo données primaires de msdbdata.mdf 3.5 Mo msdb journal de msdb msdblog.ldf 0,75 Mo Dans les versions précédentes de SQL Server, les bases de données système master et model se trouvaient dans un fichier unique qui portait le nom de périphérique maître. Les deux premiers Mo affectés à la base de données tempdb se trouvaient aussi dans le ce fichier périphérique maître et, parfois, dans la base de données exemple pubs. Cette concentration de bases de données sur un seul et même fichier entraînait parfois des problèmes de place dans les bases de données master et model. Dans SQL Server 7.0, toutes ces bases de données possèdent leur propre jeu de fichiers qui peuvent ainsi se développer de façon indépendante. Chaque base de données SQL Server contient des tables système enregistrant les données nécessaires aux composants SQL Server. Le bon fonctionnement de SQL Server dépendant de l'intégrité des informations présentes dans les tables système, Microsoft ne prend pas directement en charge les utilisateurs mettant à jour les informations des tables système. Microsoft fournit un jeu complet d'outils administratifs permettant aux utilisateurs d'administrer complètement leur système et de gérer tous les utilisateurs et les objets d'une base de données. Les utilisateurs peuvent utiliser les outils d'administration tels que SQL Server Enterprise Manager. Les développeurs peuvent recourir à l'API SQL-DMO pour intégrer les fonctionnalités d'administration complètes de SQL Server dans leurs applications. Ceux qui développent des scripts Transact-SQL et des procédures stockées peuvent utiliser les procédures stockées système et les instructions DDL Transact-SQL pour gérer toutes les fonctions administratives. Une autre fonctionnalité importante de SQL-DMO, des procédures stockées système et des instructions DDL consiste à protéger les applications des modifications effectuées dans les tables système. Microsoft a parfois besoin de modifier les tables système des nouvelles versions de SQL Server pour prendre en charge les nouvelles fonctionnalités ajoutées à la version. Les applications qui lancent des instructions SELECT référençant directement les tables système dépendent souvent de l'ancien format des tables système. Certains sites peuvent être dans l'impossibilité de migrer vers la nouvelle version de SQL Server tant qu'ils n'ont pas réécrit les applications effectuant des sélections à partir de ces tables système. Microsoft prend en compte les procédures stockées système, les interfaces de publication DDL et SQL-DMO et fait en sorte de maintenir la compatibilité descendante de ces interfaces. Microsoft ne prend pas en charge les déclencheurs définis sur les tables système car ils pourraient perturber le bon fonctionnement du système. Un autre outil très important d'interrogation du catalogue SQL Server est le jeu de vues du schéma d'informations (Information Schema Views). Ces vues sont compatibles avec le schéma d'informations défini dans la norme SQL-92. Elles fournissent aux applications un composant de recherche du catalogue SQL basé sur des standards.
