p. 1

[close]

p. 2

sql server 2008 sql transact sql jérôme gabillaud résumé ce livre sur sql server s adresse aussi bien aux étudiants en informatique désirant apprendre le sql avec sql server qu aux informaticiens qui souhaitent actualiser leurs compétences sur sql server 2008 pour comprendre et maîtriser les fonctionnalités qui enrichissent cette nouvelle version le livre permet de détailler l ensemble des instructions nécessaires à la définition des tables ainsi qu à la manipulation des données les différentes instructions sql et transact sql sont présentées et illustrées afin de bien comprendre l intérêt des différentes fonctionnalités exposées les apports de sql server 2008 au niveau du développement comme le type filestream ou les structures hiérarchiques sont développés les principales fonctionnalités qui permettent d obtenir une gestion des données plus efficace sont également détaillées comme le xml ou l intégration du code clr common langange runtime la base de données qui est construite au fur et à mesure de la lecture du livre est en téléchargement sur cette page jérôme gabillaud est reconnu microsoft mvp most valuable professional sur sql server retrouvez sur le site de l´auteur jérôme gabillaud des articles relatifs à sql server www.apsql.com l auteur ingénieur en informatique pour l industrie consultant jérôme gabillaud est également responsable pédagogique dans un grand centre de formation informatique spécialiste des systèmes d accès aux données microsoft ou oracle il est qualifié mvp most valuable professional sur sql server jérôme gabillaud est déjà auteur de nombreux ouvrages sur l administration ou la mise en oeuvre d une base de données reconnus pour leurs qualités techniques et pédagogiques ce livre numérique a été conçu et est diffusé dans le respect des droits d auteur toutes les marques citées ont été déposées par leur éditeur respectif la loi du 11 mars 1957 n autorisant aux termes des alinéas 2 et 3 de l article 41 d une part que les copies ou reproductions strictement réservées à l usage privé du copiste et non destinées à une utilisation collective et d autre part que les analyses et les courtes citations dans un but d exemple et d illustration toute représentation ou reproduction intégrale ou partielle faite sans le consentement de l auteur ou de ses ayants droit ou ayant cause est illicite alinéa 1er de l article 40 cette représentation ou reproduction par quelque procédé que ce soit constituerait donc une contrefaçon sanctionnée par les articles 425 et suivants du code pénal copyright editions eni © eni editions all rigths reserved 1-

[close]

p. 3

préambule ce livre s adresse principalement aux développeurs d applications ou à ceux qui souhaitent le devenir dans un premier temps l accent est mis sur le modèle relationnel et l algèbre relationnelle cette algèbre est à l origine du langage sql comprendre cette algèbre permet par la suite de travailler efficacement avec le langage sql et ceci quel que soit le serveur de base de données choisi ensuite la partie gestion de la structure ou sql ddl est abordée puis la gestion des données avec le sql dml enfin le transact sql langage de programmation sur sql server est détaillé les chapitres suivants sont consacrés à la découverte et l apprentissage de différentes fonctionnalités offertes par sql server au développeur d applications pour lui faciliter le travail intégration de code .net dans sql server gestion des données de type xml l ensemble des tâches relatives à l administration du serveur comme la gestion de l espace disque la gestion de la sécurité les sauvegardes la restauration la réplication sont développées dans un autre livre aux Éditions eni sql server 2008 représente une évolution conséquente du gestionnaire de base de données de microsoft au niveau du développement sql server propose un ensemble d outils et de fonctionnalités qui permettent de coder encore plus vite sql server permet également de réduire l écart qui existe encore entre l aspect développement et l administration de la base de données et du serveur l objectif recherché est bien sûr d améliorer les performances mais aussi de permettre la gestion de toutes les données présentes dans l entreprise et avec lesquelles les utilisateurs ont l habitude de travailler sql server 2008 introduit donc de nouveaux types de données pour la gestion des données de type date et heure afin d améliorer la pertinence des données il introduit un type de données hiérarchique fin de gagner en facilité de gestion et le type filestream afin d autoriser la gestion de données non structurées depuis une base relationnelle © eni editions all rigths reserved 1-

[close]

p. 4

introduction la mise en oe uvre ou implémentation des bases de données consiste en un certain nombre de responsabilités spécifiques à distinguer de celles de l administration les différents rôles de l implémenteur sont q conception du schéma logique des données passage au modèle physique de données conception et mise en oe uvre des contraintes d intégrité programmation du serveur des données q q q © eni editions all rigths reserved 1-

[close]

p. 5

rappels sur le stockage des données le stockage des données représente un problème aussi vieux que l informatique au fur et à mesure de l évolution des capacités techniques du matériel et du volume des données manipulées la façon de stocker et d organiser les données a lui aussi évolué dans le cadre d une application de gestion toutes les catégories de données ne sont pas concernées de la même façon par ces problèmes d organisation 1 les différentes catégories de données dans un premier temps il convient de définir la catégorie des données cette catégorisation est issue de quelques questions simples q À quoi servent les données combien de temps estil nécessaire de conserver ces données q a les données de base ce type de données est au coe ur de tout système d information il s agit des données à partir desquelles il est possible de travailler ainsi dans le cadre d une gestion commerciale les données de bases seront les informations sur les clients et sur les produits les données de ce type sont aussi volumineuses que possible et bien entendu elles ont une durée de vie très longue comme ce sont des données de base elles devront être accessibles facilement et rapidement b les données de mouvement ces données sont générées à partir des données de base contrairement à ces dernières leur durée de vie sera limitée mais leur volume sera beaucoup plus important par exemple toujours dans le cadre d une gestion commerciale les informations relatives à chaque commande sont considérées comme des données de mouvement le volume est important car l entreprise compte bien que chaque client passe plusieurs commandes au cours d une même année comptable par contre la durée de vie de ces informations est bien moindre en effet il n est pas nécessaire de conserver ce type d informations plusieurs années en ligne mais plutôt sur un support d archivage autre et moins coûteux c les données de travail il s agit de données générées dans un but précis avec un volume parfois important mais une durée de vie très courte dès que le travail est réalisé il n est pas nécessaire de conserver ces données ainsi par exemple les données extraites de la base et qui vont servir à la réalisation de graphiques sont à ranger dans cette catégorie dès que les graphiques sont réalisés il n est plus nécessaire de conserver les données extraites de la base qui ont permis de les obtenir d les données d archive il s agit de données très volumineuses et avec une durée de vie très longue mais qui présentent la caractéristique de ne pas être directement accessibles lorsqu elles le sont c est uniquement en lecture par exemple dans le cadre d une application de gestion commerciale il peut s agir des données relatives aux années comptables passées 2 l organisation des données a directe cette organisation est sans doute la plus simple à utiliser les données sont enregistrées les unes à la suite des autres dans un fichier chaque ensemble de données possède une longueur fixe et les enregistrements sont stockés les uns derrière les autres ainsi la connaissance de la longueur d un enregistrement permet par simple calcul d accéder directement au 10è m e enregistrement © eni editions all rigths reserved 1-

[close]

p. 6

ce type d organisation est coûteux en espace disque et ne permet pas d extraire facilement les informations sur des critères autres que leur position dans l ordre d enregistrement b séquentielle avec l organisation séquentielle les données sont enregistrées les unes à la suite des autres un caractère spécial est utilisé pour marquer la séparation entre les différents champs tandis qu un autre est utilisé pour marquer la fin de chaque enregistrement les caractères retenus sont couramment la virgule et la fin de ligne cr les fichiers qui retiennent ces séparateurs sont alors décrits comme des fichiers csv comma separated values ce type d organisation permet d optimiser l espace de stockage utilisé et résoud ainsi l un des problèmes majeurs des fichiers avec un accès direct par contre comme pour l organisation directe lorsque l on recherche des données répondant à des critères de sélection bien précis il est nécessaire de parcourir l ensemble des données ce qui s avère d autant plus long que le volume de données nombre d enregistrements est important c séquentielle indexée les données sont toujours stockées au format séquentiel mais afin de permettre un accès plus rapide aux informations des index peuvent être définis pour chaque fichier À l intérieur de ces index les données sont triées par ordre alphanumérique le parcours de l index est réalisé de façon séquentielle et permet un accès direct aux informations stockées dans le fichier de données le parcours de l index bien que séquentiel est rapide car le volume de données manipulé est faible de plus comme les données sont triées il n est pas nécessaire de lire la totalité de l index enfin il est possible de définir plusieurs index sur un même fichier de données par exemple sur un fichier stockant des informations relatives aux clients il est possible de définir un index sur les noms et un autre sur les villes avec ce type d organisation la difficulté consiste à maintenir à jour les index lors des opérations d ajout de suppression et de mise à jour de plus comme avec les organisations directe et séquentielle les fichiers ne sont pas liés les uns aux autres et il n existe pas de contexte de sécurité au niveau des données par exemple rien ne s oppose au niveau des données à la suppression d un client même s il possède des commandes en cours de même toute personne en mesure de travailler avec les données peut accéder à la totalité des données en lecture et en écriture ces inconvénients posent plus de problèmes avec l organisation séquentielle indexée car des volumes de données important peuvent être gérés ainsi avec de nombreux utilisateurs connectés cette solution séquentielle indexée a été adoptée de façon massive pour des applications petites à moyennes car afin de faciliter les développements de nombreux langages de programmation proposaient un moteur de gestion de ce type d organisation d base de données hiérarchique avec ces bases de données les problèmes de sécurité d accès aux données ainsi que la liaison entre les données ont été résolus par contre chaque moteur a été développé de façon indépendante par les différents éditeurs l apprentissage du moteur est donc à recommencer à chaque fois que l on développe avec un nouveau moteur le langage d interrogation l api d accès aux données ce à quoi il faut ajouter une organisation complexe des données ces solutions hautement propriétaires sont souvent très coûteuses pour l entreprise qui les choisit e base de données relationnelle fondée sur une représentation logique des données en respectant le modèle relationnel les bases de données relationnelles ont su s imposer car elles s appuient toutes sur le même langage standardisé et normalisé qu est le sql 3 la normalisation du schéma relationnel lorsque le schéma relationnel est défini afin de répondre à tous les besoins des utilisateurs il est nécessaire de le normaliser afin d éviter toute redondance d information ainsi que toute structure non conforme avec le modèle relationnel lorsque cette opération est réalisée le schéma pourra alors être dénormalisé bien que cette opération soit rarement la meilleure si le développeur dénormalise le schéma il doit également mettre en place l ensemble du mécanisme qui permet de maintenir la cohérence des données en effet le modèle relationnel et donc les sgbdr système de gestion de base de données relationnelle ne peuvent garantir la cohérence des données que sur des modèles normalisés les formes normales permettent de s assurer que le schéma est bien conforme au modèle relationnel il existe de façon théorique cinq formes normales mais dans la pratique seules les trois premières sont appliquées 2 © eni editions all rigths reserved

[close]

p. 7

l application des formes normales nécessite de bien maîtriser le concept de dépendance fonctionnelle une donnée dépend fonctionnellement d une autre lorsque la connaissance de la seconde permet de déterminer la valeur de la première par exemple il est possible de dire que dans une application de gestion commerciale il existe une dépendance fonctionnelle entre un code tva et le taux de tva ou bien entre la référence d un article et sa désignation première forme normale une table est dite en première forme normale lorsque toutes les colonnes contiennent des valeurs simples par exemple si une table des clients contient un champ telephones dans lequel les différents numéros de téléphone d un client son stockés alors cette table n est pas en première forme normale il est alors nécessaire de définir les colonnes bureau et mobile afin de mieux structurer les données clients numero nom prenom telephones 01 02 03 04 05 06 07 08 09 10 01 02 03 04 05 06 07 08 09 10 01 03 05 07 09 commande du 1 dupont jean 1350 01/01/2008 1 dupont jean 1352 15/01/2008 2 durand pauline 1351 02/01/2008 la table présentée cidessus ne respecte pas la première forme normale clients numero 1 nom dupont prenom jean bureau 01 02 03 04 05 01 02 03 04 05 01 03 05 07 09 mobile 06 07 08 09 10 06 07 08 09 10 commande 1350 du 01/01/2008 1 dupont jean 1352 15/01/2008 2 durand pauline 1351 02/01/2008 cette table respecte la première forme normale deuxième forme normale une table est dite en deuxième forme normale si elle est en première forme normale et si toutes les colonnes non clés dépendent fonctionnellement de la clé primaire en reprenant l exemple présenté cidessus il est possible d admettre dans un premier temps que la clé de la table des clients est composée des colonnes numero et commande dans ce cas les valeurs des colonnes nom prenom bureau et mobile dépendent uniquement du numéro tandis que la colonne du est liée au numéro de la commande la table n est donc pas en seconde forme normale il est donc nécessaire de définir deux tables clients et commandes client numero 1 2 nom dupont durand prenom jean pauline bureau 01 02 03 04 05 01 03 05 07 09 mobile 06 07 08 09 10 commande numero 1350 1352 1351 du 01/01/2008 15/01/2008 02/01/2008 client 1 1 2 les deux tables présentées cidessus respectent la deuxième forme normale troisième forme normale une table est dite en troisième forme normale si elle est en deuxième forme normale et s il © eni editions all rigths reserved 3-

[close]

p. 8

n existe pas de dépendance fonctionnelle entre deux colonnes non clé par exemple si dans la table des clients les colonnes civilite et sexe sont ajoutées de la façon suivante client numero nom prenom bureau 01 02 03 04 05 01 03 05 07 09 mobile 06 07 08 09 10 civilite sexe 1 dupont jean m m 2 durand pauline mlle f il est alors possible de dire qu il existe une dépendance fonctionnelle entre le sexe et la civilité en effet le fait de connaître la civilité mlle mme ou m permet de déduire le sexe la table des clients ne respecte donc pas la troisième forme normale la table des civilités est définie de façon à obtenir le schéma suivant client numero nom prenom bureau 01 02 03 04 05 01 03 05 07 09 mobile 06 07 08 09 10 civilite 1 dupont jean m 2 durand pauline mlle civilite valeur mlle mme m sexe f f m les deux tables présentées cidessus respectent la troisième forme normale 4 © eni editions all rigths reserved

[close]

p. 9

le modèle relationnel l organisation des données au sein des systèmes de gestion des bases de données relationnelles sgbdr ou rdbms en anglais repose entièrement sur le modèle relationnel ce modèle fut mis au point par edgar franck codd sous l impulsion d ibm au cours des années 70 en plus de ce modèle une algèbre l algèbre relationnelle fut également créée afin d extraire les données stockées dans ce modèle la compréhension de ce modèle et de son algèbre permet d aborder l apprentissage du sql de façon sereine car il ne s agit plus alors que de transposer les concepts théoriques en lignes de commandes ce travail permettra de plus de s adapter beaucoup plus facilement aux différents perfectionnements que le sql peut subir au fur et à mesure des versions l algèbre relationnelle a conduit à la mise au point du sql qui est devenu le standard en ce qui concerne la gestion des données le fait que les sgbdr respectent le modèle relationnel conduit à travailler avec une structure logique d organisation des données tables vues index qui est indépendante de la structure physique fichiers c est le rôle de chaque sgbdr que de fournir une vue logique à l utilisateur tout en assurant un stockage physique des informations cette contrainte est également la force des sgbdr car la gestion des données d un point de vue logique est d une grande simplicité d utilisation ainsi des utilisateurs peu ou pas habitués à développer des applications peuvent s initier sans difficultés au sql 1 concepts et définitions le modèle relationnel repose sur des concepts de base simples domaine relation attribut auxquels s appliquent des règles précises.la mise en oe uvre de la base est facilitée par un langage assertionnel non procédural simple basé sur une logique ensembliste domaine c est un ensemble de valeurs caractérisé par un nom cardinal c est le nombre d éléments d un domaine exemple le dictionnaire des données de l analyse d une gestion commerciale peut comporter entre autres des spécifications sur la gestion des états de commande ou des numéros d ordre à afficher le modèle relationnel les traduira de la manière suivante États des commandes ec li fa so cardinal 4 numéros d ordre {n 1 n 9999 cardinal 9999 produit cartésien le produit cartésien p entre plusieurs domaines d1 d2 dn noté p d1 x d2 x x dn est l ensemble des nuplets tuples d1 d2 dn où chaque di est un élément du domaine di exemple si on veut gérer deux domaines codes et taux on pourra obtenir des 2uplets composés d un code et d un taux codes {1,2,3,4 taux de tva {0,5.5,19.6 codes x taux de tva 1,0 1,5.5 1,19.6 2,0 2,5.5 2,19.6 3,0 3,5.5 3,19.6 4,0 4,5.5 4,19.6 relation une relation définie sur les domaines d1 d2 dn est un sousensemble du produit cartésien de ces domaines caractérisé par un nom attribut c est une colonne d une relation caractérisée par un nom © eni editions all rigths reserved 1-

[close]

p. 10

degré c est le nombre d attributs d une relation exemple pour associer un seul taux par code seuls trois 2uplets doivent être concernés relation tva 1,0 2,5.5 3,19.6 représentation elle se fait sous forme de tableau table en extension ou en compréhension tva code:codes valeur:taux de tva ou tva code valeur 2 principales règles le modèle relationnel gère donc un objet principal la relation associée aux concepts de domaine et d attribut des règles s appliquent à cette relation afin de respecter les contraintes liées à l analyse quelquesunes de ces règles sont cohérence toute valeur prise par un attribut doit appartenir au domaine sur lequel il est défini unicité tous les éléments d une relation doivent être distincts identifiant attribut ou ensemble d attributs permettant de caractériser de manière unique chaque élément de la relation clé primaire identifiant minimum d une relation clés secondaires autres identifiants de la relation intégrité référentielle cette règle impose qu un attribut ou ensemble d attributs d une relation apparaisse comme clé primaire dans une autre relation clé étrangère attribut ou ensemble d attributs vérifiant la règle d intégrité référentielle exemple l analyse d une gestion commerciale nous impose de gérer des clients ayant des caractéristiques nom adresse et des commandes que passent ces clients on pourra proposer le modèle suivant 2 © eni editions all rigths reserved

[close]

p. 11

clients numerocli,nomcli,adressecli numerocli identifiant clé primaire de clients nomcli,adressecli identifiant clé secondaire de clients commandes numerocde,datecde,numerocli,etatcde numerocde identifiant clé primaire de commandes numerocli clé étrangère de commandes référençant numerocli de clients valeur nulle dans le modèle relationnel la notion de nullité est admise c est une valeur représentant une information inconnue ou inapplicable dans une colonne elle est notée ou null contrainte d entité toute valeur participant à une clé primaire doit être non null exemple dans la relation article on admet que le prix ou le code tva peuvent être inconnus mais la référence de l article clé primaire doit être renseignée © eni editions all rigths reserved 3-

[close]

p. 12

l algèbre relationnelle c est une méthode d extraction permettant la manipulation des tables et des colonnes son principe repose sur la création de nouvelles tables tables résultantes à partir des tables existantes ces nouvelles tables devenant des objets utilisables immédiatement les opérateurs de l algèbre relationnelle permettant de créer les tables résultantes sont basés sur la théorie des ensembles la syntaxe et les éléments de notations retenus ici sont les plus couramment utilisés 1 opérateurs union l union entre deux relations de même structure degré et domaines donne une table résultante de même structure ayant comme éléments l ensemble des éléments distincts des deux relations initiales notation rx r1 r2 exemples soient les tables cliouest et clicentre clients des deux régions clients=cliouest clicentre intersection l intersection entre deux relations de même structure degré et domaines donne une table résultante de même structure ayant comme éléments l ensemble des éléments communs aux deux relations initiales notation rx r1 r2 exemple clients communs aux deux régions clicommun=cliouest clicentre différence la différence entre deux relations de même structure degré et domaines donne une table résultante de même structure ayant comme éléments l ensemble des éléments de la première relation qui ne sont pas dans la deuxième notation rx r1 r2 exemple clients gérés uniquement par la région ouest © eni editions all rigths reserved 1-

[close]

p. 13

cliouestseul=cliouest clicentre division la division entre deux relations est possible à condition que la relation diviseur soit totalement incluse dans la relation dividende le quotient de la division correspond à l information qui présente dans le dividende n est pas présente dans le diviseur il est également possible de définir la division de la façon suivante :soit r1 et r2 des relations telles que r2 soit totalement inclus dans r1.le quotient r1÷r2 est constitué des tuples t tels que pour tous tuples t définis sur r2 il existe le tuple t.t défini sur r1 notation rx=r1÷r2 exemple soient les relations personnes qui contiennent des informations relatives à des individus soit la relation cliouest la division entre les deux relations permet d isoler l information complémentaire aux clients et présente dans la relation individu restriction la restriction repose sur une condition elle produit à partir d une relation une relation de même schéma n ayant que les éléments de la relation initiale qui répondent à la condition notation rx condition r1 la condition s exprime sous la forme [non attribut opérateur valeur et/ou}condition opérateur un opérateur de comparaison valeur une constante ou un autre attribut exemples clients de nantes cli44 adresse nantes cliouest articles de la famille ab art1 refart ab et refart ac articles 2 © eni editions all rigths reserved

[close]

p. 14

tapis dont le prix est inférieur à 1000 art2 prix 1000art1 projection la projection d une relation sur un groupe d attributs donne une relation résultante ayant comme schéma uniquement ces attributs et comme éléments les nuplets distincts composés par les valeurs associées de ces attributs notation rx r a1 a2 an exemple commandes et états de commande cde commandesnumerocde,numerocli,etatcde clients ayant des commandes clicde1 commandesnumerocli clients et états de commande clide2 commandesnumerocli,etatcde produit cartésien le produit cartésien entre deux relations produit une relation ayant comme schéma tous les attributs des deux relations existantes et comme éléments l association de chaque ligne de la première table avec chaque ligne de la deuxième notation rx s1 x s2 exemple soient les tables © eni editions all rigths reserved 3-

[close]

p. 15

inventaire depots x art2 jointures la jointure entre deux relations est produite par la restriction sur le produit cartésien notation rx s1 join condition s2 exemple soient les tables ligcdeec cdeec join cdeec.numerocde lignescde.nocde lignescde les différents types de jointures sont thetajointure la condition est une comparaison entre deux attributs equijointure la condition porte sur l égalité entre deux attributs jointure naturelle Équijointure entre les attributs portant le même nom calculs élémentaires projection sur une relation associée à un calcul portant sur chaque ligne pour créer un ou plusieurs nouveaux attributs notation rx s a1 n1 expression calculée l expression calculée peut être q une opération arithmétique une fonction mathématique une fonction portant sur une chaîne q q exemple on veut obtenir le montant d une ligne de commande prix quantité 4 © eni editions all rigths reserved

[close]