SQLServer : TransacSQL

1. SQLServer : TransacSQL Philippe Bancquart

2. Intégrité des données et tables Philippe Bancquart

3. Objets • Un objet d’une base porte un nom et occupe un espace • Les données sont organisées en tables. • Les tables contiennent des colonnes qui comportent des données qui ont des types, des règles et valeurs de défaut. • La BD peut contenir des vues, procédures et des triggers.

4. Types de données • Un type précise la nature, taille et format d’enregistrement des colonnes • Types systèmes. • Types utilisateurs. • Constitués d’un nom, type et propriété • Assure la cohérence entre les différentes colonnes. • Liens entre les règles

5. Types systèmes 1

6. Types de données 2

7. Création type utilisateur • sp_addtype nomType, datatype [, ’identity’ | nulltype] • Sp_droptype ‘nomType’ • Sp_addtype EtatType, ‘char(2)’,’not null’ • Sp_help nomType • Un type ne peut être supprimé s’il est déjà utilisé.

8. Propriétés d’une colonne • La propriété peut être nulle ou non • Si valeur Null alors elle est inconnu • Exp id integer not null • Identifiant ‘identity’ : attribution automatique de valeurs numériques séquentielles à une colonnes. • Id numeric(4,0) identity • Ne pas utiliser cette colonne dans un insert.

9. Création de tables Create table nom_table (nom_colonne datatype [identity|null|not null , (nom_colonne datatype [identity|null|not null , … ) • EXERCICES : create table VentesAuDetail (stor_id char(4) not null, title_id varchar(20) not null,quantite smallint not null,remise float null ) select * from VentesAuDetail

10. Fonction d’accès à une table

11. Tables temporaires Elles sont utilisées pour contenir un ensemble de résultats intermédiaires, elles commencent par le signe # . Possibilité d'utiliser la commande Select into., qui permet d’insérer directement des données à partir d’un select. • SQLServer supprime automatiquement les tables temporaires à la déconnexion mises dans tempdb • select title_id, total_orders = sum(qty) into #qty_table from sales group by title_id

12. Création Select Into from nom_ancienne select select_list into nom_nouvelle_table _table where condition Le select into permet de créer et copier la structure d’une table dans une autre select * into newtitlesX from titles where pubdate < ‘1993-06-01’’ Mettre l’option à vrai, sinon non autorisé, avoir les droits administrateur. sp_dboption NomBase , "select into" , TRUE.

13. Pratique Création table et type • Choix base : use Nom • Vérification : select db_name() • use pubs , select db_name. • Création table PRODUCTEUR-PB • Création d’un type pid, type char(4) et non null • N_pub_id comme identité • Pub_id de type pid • Pub_name 40 caractères • City 40 caractères • State 2 caractères

14. TP suite • Select * into titrePB from titles • Select * into magasinPB from stores • Select * into auteursPB from authors • Select * into remisePB from discounts • Select * into venteBisPB from vente • Consulter les scripts(3eme icône afficher plan exécution)

15. Création d’une vues • Est le stockage d’une instruction SELECT fonctionnant comme une table • Ne stocke pas les données • Peut effectuer des sélections à partir de plusieurs tables et être utilisée pour contenir des données partielles d’une ou plusieurs tables. • Si les données d’une table change alors la vue dynamique change.

16. Création de vues • Create view nom_vue (col1, coln, ..) as commande_sélection [with check option] create view ca_auteurPB as select au_id , au_lname , au_fname from auteurs where state = ‘CA’Ensuite visualiser notre vue : select * from ca_ auteur

17. Restrictions Une vue peut inclure • une fonction d'agrégat et regroupement • Jointure , Une autre vue , Une clause where Une vue ne peut inclure • Une clause order by , compute, select into • Sp_helptext nom_vue

18. Vues : check option • With check option limite la mise à jour de lignes sur la base de la clause where • Permet d’interdire la mise à jour de la BD si l’on ne vérifie pas le restriction. Create view vueAuteurCA as select au_id, au_lname, au_fname, phone, address,city,state,zip,contract from auteurs where state =‘CA’ with check option • Les insertions ne sont possibles que dans l’état CA Insert vueAuteurCA values (‘111-222-3333’, ’phil’,’banc’,’O328’ ,’paix’, ’losangeles’ ,’NY’,’789’ ,’1’) dans ce cas l’insert échoue Mettre CA vérifier

19. exo • Création d’une vue ‘pub-vue_PB’ contenant le pub_id, pub_name, city, state de la table ‘publishers’ ou l’état est ‘CA’. • Vérifier le contenu de votre vue et celui de la table avec même restriction

20. Contraintes Elles sont utilisées dans la commande ‘create table’ pour limiter les valeurs contenues dans les tables. S’applique au niveau colonne ou table. • Clause Default : met une valeur si aucune valeur n’est indiquée. State char(2) default ‘CA’ • Vérification : Précise une liste ou fourchette de valeurs, vérifier une condition. Pub_id like ’99[0-9][0-9]’ commence par 99xx Vérifie que la colonne vérifie la condition. Qty smallint not null check (qty <=3000)

21. Contraintes Contrainte de table. Exemple  à la table discounts vérifier que la quantité maxi soit supérieur quantité mini : constraint low_high_checkcheck (lowqty < highqty) • Create table table nom_table • (nom_colonne datatype[constraint nom_contrainte] check (search_condition) ..

22. Messages Pour être parlant on peut ajouter des messages. • Sp_addmessage 20002 « lowqty indiqué est supérieur à highqty » • Ensuite on affecte ce message à la contrainte. • Sp_bindmsglow_high_check 20002 • Si les données sont rejetées par la contrainte alors messages au client. • Ajouter la contrainte à la table discount alter table discounts add constraint low_high_check check (lowqty < highqty)

23. Index C'est une structure de stockage indépendante, créée en plus, c'est un objet distinct dans la BD La création d'un index améliore les performances en réduisant les lectures de pages nécessaires pour retrouver des données. Garantit l'unicité. • ØIndex Clustérisés : Intéressant sur la jointure la plus courante, % élevé de duplication. Ils améliorent fortement les performances de la base, mais attention il ne peut y avoir qu'un seul index clusterisé par table (place en base). • ØIndex non clusterisés : ne reclassent pas les données et n'affectent pas les pages de données. Ils fournissent des pointeurs aux lignes de la table, 249 index maxi, ils sont plus longs en traitement.

24. Unicité Create table nom_table (non colonne type [ constraint non_contrainte] unique [clustered|nonclustered] Pour la création d’une table, on défini toujours une clé primaire. « Primary key » sur 1 ou n colonnes. Par défaut une primary key construit un index.

25. Alter table • Ajouter, modifier des colonnes. • Ajouter, éliminer des contraintes. • Modification, attention dans certain cas impossible sans perte du passé. • Alter table sales add constraint contr_unicite unique (stor_id, ord_num) • Alter table sales drop constraint contr_unicite