Base de Données
On sait que les données des systèmes transactionnels sont stockées sous une forme purement relationnelle.
C’est d’ailleurs cette vision relationnelle que l’on a remplacée par une vision multidimensionnelle dans cette partie. La question qui se pose maintenant est : comment stocker des cubes ayant de nombreuses dimensions, elles-même `a plusieurs niveaux ?
16.1 Schéma relationnel de l’entrepˆot
La première idée est de stocker physiquement un objet multidimensionnel, c’est-`a-dire une hypermatrice avec plusieurs mesures par cellule et de stocker aussi les agrégats au bout de chaque ligne, chaque colonne, etc. et pour tous les niveaux. C’est la meilleure approche du point de vue des performances de consultation (puisque tous les résultats sont présents), mais ce stockage est bien souvent trop gourmand en place mémoire.
Notre soucis est donc maintenant d’économiser de l’espace. Or on connaˆit déj`a la fa¸con la plus économique de stocker des données, c’est l’approche relationnelle (dans laquelle on évite toute redondance).
Il se trouve justement que l’on peut voir un cube sous forme de tables et de relations.
16.1.1 Schéma en étoile
Si au sein de l’entrepˆot, nous nous concentrons sur un cube, alors on peut modéliser ses dimensions, leurs niveaux, leurs membres
et les cellules sous la forme d’un schéma relationnel étoilé.
Table de dimension
`A
chaque dimension on associe une table avec :
– une clé primaire non
composite ;
– et une colonne par niveau (pour y stocker les membres).
(a) (b)
Fig. 13 – Tables de dimension
Par exemple, `a la dimension geographie on associe une table (cf. figure 13(a)) ayant pour clé primaire
code geographie et comme autres colonnes : pays, region et ville.
16 STOCKAGE DES DONNéES 69
étant donné que les données sont issues de plusieurs bases de données ayant chacune leur clés primaires,
les clés primaires des tables de dimensions sont différentes car elles doivent assurer une unicité
plus générale. Ces clés de substitution (surrogate keys, par opposition aux clés naturelles utilisées dans
les bases de production) servent `a identifier un membre pendant tout sa durée de vie au sein de l’entrepˆot.
Dimension temporelle
Les dimensions temporelles ne rentrent pas dans ce cadre puisque qu’on ne s’amuse pas `a détailler les
années, les mois et les jours dans trois colonnes différentes, mais qu’on regroupe cette information dans
une seule colonne de type DATETIME.
Remarque : la dimension temporelle pose toujours un problème de conception notamment parce que
tous les acteurs d’une entreprise ne fo
nctionnent ni avec le mˆeme calendrier (civil, fiscal, scolaire, etc.) ni
avec la mˆeme horloge (surtout si l’entreprise est multinationale).
Table des faits
Ensuite, chaque cellule du cube :
– est identifiée par ses coordonnées (i.e. son code geographie, son code produits et sa date) ;
– et contient ses mesures.
L’ensemble de ces informations (coordonnées et mesures relatives `a une cellule) constitue ce que l’on
appelle un fait. Si maintenant on stocke chaque fait sous la forme d’une ligne dans une table, alors on
obtient un schéma relationnel étoilé par rapport `a cette table (autrement dit toutes les relations partent
de cette table, cf. figure 14).
Fig. 14 – Schéma en étoile d’un cube `a 5 dimensions et 2 mesures
Dans l’exemple du cube ventes, la table des faits n’aurait posséder que deux clés étrangères : code
geographie et code produit, une colonne pour la date ainsi que deux autres colonnes pour les mesures :
NbCommandes et [Montant total]. Pour mettre en évidence le caractère étoilé du schéma relationnel,
nous ajoutons deux dimensions au cube ventes : une relative aux clients et une relative aux vendeurs.
Remarque : la table des faits peut compter plusieurs millions de lignes et la taille des autres tables
est négligeable devant celle de la table des faits.
Dans le schéma en étoile (star scheme), il peut encore y avoir des redondances, pour deux types de
raisons :
– n’utiliser qu’une seule table pour définir une dimension qui possède plusieurs niveaux est presque
toujours en contradiction avec la troisième forme normale [?] (cf. figure 13(b)) ; on peut donc pousser
plus loin la décomposition relationnelle (cf. figure 15
– par ailleurs, on peut parfois factoriser certaines données (une seule table geographie pour la
géographie des ventes et des clients, par exemple).
Comme la table des faits n’est plus la seule `a étoiler les relations autour d’elle, dès lors qu’une dimension
est basée sur deux tables ou plus (cf. figure 16), on dit que le schéma est en flocon (snowflake scheme)
Remarque : dans cet exemple, la dimension produits présente trois hiérarchies (une selon les types,
une selon les paquetages et une selon les catégories).
Ce qu’il faut retenir de tout cela, c’est que :
– une dimension s’inscrit dans un schéma en étoile si elle n’est définie que sur une table du schéma
relationnel du cube (c’est le cas de la dimension geographie) ;
– dès qu’elle utilise plusieurs tables du schéma relationnel, la dimension s’inscrit dans un schéma en
flocon (c’est le cas des dimensions produits et clients).
Quand certaines dimensions sont en étoile et d’autre en flocon, on dit que le schéma du cube est en
starflake.
Dans Analysis Services, on est donc amené `a spécifier pour chaque dimension (non temporelle) :
– si elle s’inscrit dans un schéma en étoile ou en flocon ;
– la ou les tables concernées (elles doivent exister avant la création du cube).
Que choisir ?
Le schéma en étoile est, certes, plus redondant que le schéma en flocon mais :
– la redondance n’est pas un problème pour le décisionnel, puisqu’il n’y a que des requˆetes de sélection ;
– l’espace occupé par les tables de dimension est négligeable devant celui de la table des faits ;
– les requˆetes sont plus rapides sur un schéma en étoile ;
– l’ETL est plus simple avec un schéma en étoile.
Pour toutes ces raisons, le schéma en flocon est peu recommandé et doit se limiter `a :
– la factorisation, comme celle de la table geographie ;
– la représentation de hiérarchie multiple, comme celle de la dimension produits.
Ce qui exclut la décomposition `a outrance comme celle des catégories et sous-catégories.
16.1.3 Parent-enfant
D’habitude, les niveaux d’une relation sont de type ensembliste : dans la dimension geographie, un
pays est un ensemble de régions.
Prenons maintenant une hiérarchie de dimension qui ait une signification familiale (cf. figure 17).
Par exemple une dimension personnes avec un niveau GrandsParents, un niveau parents et un niveau
Fig. 17 – Hiérarchie parent-enfant
enfants. Le schéma relationnel du cube n’est alors plus le mˆeme, puisqu’il fait intervenir une autojointure
sur la table personnes (cf. figure 18).
Fig. 18 – Auto-jointure d’une table de dimension parent-enfant
C’est ce que l’on appelle une hiérarchie parent-enfant. Elle est valable également quand il y a une
relation employé-employeur.
Remarque : dans le cas d’une hiérarchie parent-enfant, les niveaux supérieurs possèdent des données
propres (non agrégées). Par exemple, le nom du directeur commercial n’est pas issus de l’agrégation des
noms de ses vendeurs...
16.1.4 Base décisionnelle
Un entrepˆot de données peut contenir plusieurs cubes (le cube ventes que l’on vient de voir, et le
cube production, par exemple). Le schéma relationnel de l’entrepˆot regroupe les schémas relationnels de ses cubes. La base de données qui correspond `a ce schéma relationnel global,s’appelle la base décisionnelle
(par opposition aux bases de production).
Elle doit ˆetre construite avant l’entrepˆot lui-mˆeme. Elle résulte d’un travail important de rétroconception
des bases de production et de normalisation `a l’échelle de l’entreprise.
Certaines dimensions sont communes `a plusieurs cubes (la dimension produits est commune aux
cubes ventes et production, par exemple). Leurs tables ne sont évidemment pas répétées dans le schéma
16 STOCKAGE DES DONNéES 73
relationnel de l’entrepˆot, mais utilisées par plusieurs tables des faits. C’est pourquoi Analysis Services
emploie le terme de dimensions partagées.
Il en va de mˆeme pour les mesures et toutes les autres colonnes utilisées pour définir le cube : elle
sont présentes une bonne fois pour toutes dans ce schéma relationnel global, puis utilisées dans le schéma
relationnel de chaque cube.
`A
l’instar des bases de production, le schéma relationnel de la base décisionnelle doit ˆetre con¸cu dans
une phase modélisation et doit coller aux besoins des utilisateurs. Par contre, cette modélisation OLAP
diffère sensiblement des modélisations relationnelles classiques pour les systèmes OLTP. Le schéma relationnel
doit ˆetre suffisamment bien con¸cu dès le départ, car c’est encore plus problématique de modifier
la base décisionnelle qu’une base de production.
Rappelons que cette base décisionnelle a pour but d’historiser les données et est mise-`a-jour, non
pas par de nombreuses transactions ACID (cf. §2.4 page 15), mais par des extractions périodiques des
systèmes OLTP sous-jacents (avant qu’ils ne soient purgés).
Dans Analysis Services, c’est cette base qui est la source de données `a connecter `a l’entrepˆot. Notons
qu’Analysis Services autorise plusieurs sources de données (et donc plusieurs bases décisionnelles) pour
un mˆeme entrepˆot.
16.2 Modes de stockage
Classiquement, il existe trois modes de stockage pour chaque partition d’un cube. Sans entrer dans le
détail, les données d’un cube peuvent ˆetre partitionnées (selon les années, généralement).
Généralement un cube présente :
– une partition pour l’année en cours ;
– une partition pour l’année précédente (ou les deux années précédentes) ;
– et une dernière partition pour les autres années.
Remarque : les partitions correspondent `a un éclatement de la table des faits.
16.2.1 Multidimensional OLAP (MOLAP)
En mode MOLAP, les données de la base décisionnelle sont copiées dans une structure (hyper-
)matricielle qui contient également les agrégats. Analysis Services compresse alors les données et n’alloue
aucun espace aux cellules vides 19, ce qui limite la taille de stockage.
Pourtant, ce type de stockage doit se limiter aux données les plus utilisées (celle de l’année en cours, par
exemple) afin que les réponses soient instantanées et que le volume de données MOLAP reste raisonnable.
16.2.2 Relational OLAP (ROLAP)
En mode ROLAP, la partition est vide. Toutes les données restent dans la base décisionnelle. Les
requˆetes MDX sur ce cube font donc appel `a des requˆetes SQL impliquant des jointures. C’est le type de stockage qui offre les temps de réponse les plus lents. Mais c’est aussi le plus économe.
Généralement, les données qui remontent `a deux ans et plus (elles sont nombreuses et peu consultées)
sont stockées en ROLAP.
Remarque : les requˆetes sont plus rapides sur un schéma en étoile (mˆeme redondant) que sur un
schéma en flocon (qui met en jeu davantage de jointures).
19. cela est heureux car, généralement, un cube OLAP est creux, c’est-`a-dire essentiellement constitué de cellules vides
16 STOCKAGE DES DONNéES 74
16.2.3 Hybrid OLAP (HOLAP)
En mode HOLAP, seuls les agrégats des niveaux supérieurs sont stockés sous forme matricielle (cf.
figure 19) et les données bas niveaux restent dans la base décisionnelle. Il s’agit d’une combinaison entre
l’approche MOLAP et l’approche ROLAP. Seule les requˆetes MDX qui utilisent directement les données
bas niveaux sont ralenties (drillthrough, par exemple).
Fig. 19 – Cube réduit pour les agrégats des niveaux supérieurs
Ce type de stockage convient bien aux données de l’année précédente (ou des deux années précédentes).
16.3 Niveau d’agrégation
Quelque soit le mode de stockage choisi, les agrégats ne sont pas forcément tous calculés et stockés.
Analysis Services peut déterminer quels agrégats stocker (en commen¸cant par les niveaux les plus bas)
en fonction de deux critères :
– la taille maximale allouée au cube (si on la connaˆit, autant la préciser) ;
– ou le gain de performance souhaité :
gain en pourcentage = 100
Tmax - T
Tmax - Tmin
o`u Tmax est le temps d’exécution d’une requˆete si aucun agrégat n’est stocké, Tmin est le temps
d’exécution de cette requˆete si tous les agrégats sont stockées et T est le temps d’exécution avec le
gain souhaité.
Stockage en Colonne Une base de données orientée colonnes est une base de données qui stocke les données par colonne et non par ligne. Elle permet de plus une compression par colonne, efficace lorsque les données de la colonne se ressemblent.
Index Un index est une fonctionnalité permettant d'effectuer des accès rapides aux enregistrements d'une table.
Impact PerformancesPourquoi ne pas indexer toutes les colonnes d’un table ?
On se sert d’un index pour accélérer la lecture de données mais cela pénalise par contre l’écriture (à chaque opération INSERT/UPDATE/DELETE le moteur doit mettre à jour les données de la table ET de l’index).
On se sert également des index pour assurer certaines contraintes d’intégrité (ex. grâce à un index UNIQUE on va vérifier qu’une colonne (ou un groupe de colonnes) ne contient que des valeurs distinctes.
Différence entre un index Cluster et non Cluster ?
Cluster impacte l’organisation physique des données
Un seul index cluster
Index MultiColonnes
Si un index est défini sur plusieurs colonnes, alors il ne sera utilisé que si le WHERE contient des conditions sur l’ensemble des colonnes impliquées et utilisées dans l’ordre utilisé par l’index.
Contraintes D’Intégrité C’est une assertion qui doit être vérifiée par des données à des instants déterminés : cela sert à assurer la précision et la logique des données dans une base de données.
Une base de données cohérente est une base de données dont l’ensemble des contraintes d’intégrité est respecté par les données de la base.
Gestion d’Erreur
Bonnes Pratiques
Validation en amont
RAISEERROR
Transactions
Une transaction est une suite de commandes SQL qui doivent être effectuées correctement et entièrement ou pas du tout. La validation explicite d’une transaction est réalisée via la commande COMMIT, l’annulation explicite via la commande ROLLBACK.
Syntaxe : BEGIN, COMMIT, ROLLBACK
Jointures
Définition
La jointure est l'opération permettant d’associer plusieurs tables ou vues de la base par le biais d’un lien logique de données entre les différentes tables ou vues, le lien étant vérifié par le biais d'un prédicat. Le résultat de l'opération est une nouvelle table.
Interne vs Externe
Interne
Une jointure interne permet de sélectionner les enregistrements ayant des correspondances entre deux tables joints.
Externe
Une jointure externe permet de sélectionner à la fois :
les enregistrements ayant des correspondances entre les deux tables jointes.
les enregistrements n'ayant pas de correspondance dans le premier, le second ou dans tous les tables jointes.
Types de Jointure
Jointure interne : Jointure n'affichant que les lignes ayant une correspondance dans les deux tables jointes.
Jointure externe gauche : La jointure inclut toutes les lignes de la première table citée (table la plus à gauche dans la clause JOIN).
Jointure externe droite La jointure inclut toutes les lignes de la deuxième table citée (table la plus à droite dans la clause JOIN).
Jointure externe entière Inclut toutes les lignes de toutes les tables jointes, qu'elles aient ou non une correspondance entre elles.
Jointure croisée Jointure dont l'ensemble des résultats inclut une ligne par paire de lignes constituée par correspondance entre les deux tables.
Allocation mémoire fixe ou variable
Différence entre CHAR et VARCHAR ?
Allocation mémoire fixe ou variable
Jeux de caractères
Différence entre VARCHAR et NVARCHAR ?
Unicode, 2 x plus de mémoire
Logs
Différence entre DELETE et TRUNCATE ?
DELETE : clause WHERE, suppression ligne à ligne, log, conservation des identifiants autogénérés
TRUNCATE : pas de clause where, supprime toute la table, plus rapide, moins de ressources
Union Différence entre UNION et UNION ALL ? UNION des valeurs différentes entre 2 jeux de données
UNION ALL combine toutes les valeurs
Performances Regarder le plan d’exécution
Mise en place d’index
Création tables temporaires
Faire attention aux jointures utilisées
Commande UPDATE_STATISTICS ?
Suite grosse opérations sur table, mise à jour des statistiques
What is the Difference between a HAVING clause and a WHERE clause?
HAVING travaille sur des agrégats
WHERE sur des lignes
Selection de Données
Sélection simple
Rappelons que la syntaxe pour effectuer une requête de sélection est :
SELECT colonnes
FROM table
WHERE condition1 AND condition2
Dans les conditions WHERE (reliées entre elles par OR ou AND) on peut utiliser
– =, <> et tous les opérateurs de comparaison :
WHERE clt nom <> ’Razibus’
– une plage de valeurs (bornes incluses) :
WHERE clt ca BETWEEN 10000 AND 100000
– une liste de valeurs :
WHERE clt nom IN (’Razibus’, ’Fricotin’, ’Mironton’)
– un filtre :
WHERE clt nom LIKE ’R%’ -- commencant par R
-- % remplace toute série de caractères
-- (y compris vide)
LIKE ’R zibus’ -- remplace un caractère
LIKE ’%[M-R]’ -- finissant par M, N, O, P, Q ou R
LIKE ’%[^FMR]%’ -- ne contenant ni F ni M ni R
Remarque : on dispose évidemment de
NOT BETWEEN ... AND ...
NOT IN ...
NOT LIKE ...
Par ailleurs, on peut
– intituler les colonnes (si l’intitulé contient des espaces ou des accents, le délimiter avec des crochets
[ ]) :
SELECT cmd num AS [numéro de commande], cmd date AS date
– trier les résultats :
SELECT ... FROM ... WHERE ...
ORDER BY cmd date DESC, cmd num ASC
c’est-`a-dire par dates décroissantes, puis (pour les commandes de la mˆeme date)
par numéro croissant
– n’afficher que des résultats distincts :
SELECT DISTINCT ...
– n’afficher que les premiers résultats :
SELECT TOP 50 ...
Mais malheureusement, on ne peut pas utiliser les alias définis dans la clause SELECT dans les autres
clauses (WHERE et ORDER BY notamment).
==== Jointures internes ====
Quand on utilise deux tables ou plus, on effectue en réalité des jointures entre ces tables. Donc
désormais on écrira plutˆot :
1 SELECT cmd_num, cmd_date
2 FROM commandes
3 JOIN clients ON cmd_clt = clt_num -- condition de jointure
4 WHERE clt_nom = ’Razibus’ -- condition de selection
Remarques :
– ceci permet de bien séparer les conditions de jointure des conditions de sélection ;
– une jointure est en quelque sorte un produit cartésien temporaire : la table (cmd num, cmd date,
clt nom) est provisoirement créée pour effectuer la sélection ;
– le moteur du SGBD se charge de trouver le moyen d’effectuer le moins d’opérations possible ;
– une jointure n’est pas seulement le rappel des relations entre deux tables, c’est une vraie condition
(qui peut utiliser <> et les autres opérateur de comparaison `a la place de = par exemple) et pas
forcément entre une clé étrangère et sa référence ;
– on peut effectuer plusieurs jointures successives :
FROM commandes
JOIN clients ON cmd clt = clt num
JOIN articles ON cmd art = art num
– pour une mˆeme jointure, on peut utiliser plusieurs conditions de jointures (reliées entre elles par
AND ou OR).
Pour ˆetre tout `a fait rigoureux, il faut toujours préciser la table `a laquelle appartiennent les colonnes
utilisées (en utilisant des alias). On écrira donc désormais :
1 SELECT a.cmd_num, a.cmd_date
2 FROM commandes AS a
3 JOIN clients AS b ON a.cmd_clt = b.clt_num
4 WHERE b.clt_nom = ’Razibus’
Exercice : sélectionner les clients ayant commandé le mˆeme jour (le résultat devra se présenter sous
forme de deux colonnes : un client et un autre qui a commandé le mˆeme jour).
Solution : avec les alias, l’auto-jointure devient possible
1 SELECT DISTINCT a.cmd_clt, b.cmd_clt
2 FROM commandes AS a
3 JOIN commandes AS b ON a.cmd_clt <> b.cmd_clt
4 -- la condition de jointure est que les deux clients ne sont pas les memes
5 WHERE a.cmd_date = b.cmd_date
6 -- parmi tous les couples de clients distincts on ne garde que ceux-la
==== Jointures externes ====
Imaginons maintenant que l’on dispose de la table clients plus qui contient les colonnes clt num,
clt adresse, clt email, clt telephone et que l’on veuille afficher la liste complète des clients ainsi que
leur renseignements complémentaires s’ils existent.
La première idée consiste `a effectuer la requˆete suivante :
1 SELECT a.clt_nom, b.clt_adresse, b.clt_email, b.clt_telephone
2 FROM clients AS a JOIN clients_plus AS b
3 ON a.clt_num = b.clt_num
Problème : ne s’affichent que les clients ayant des informations complémentaires. La solution consiste
`a rendre facultative la jointure avec la table de droite.
1 SELECT a.clt_nom, b.clt_adresse, b.clt_email, b.clt_telephone
2 FROM clients AS a LEFT JOIN clients_plus AS b
3 ON a.clt_num = b.clt_num
4 -- jointure facultative gauche
Attention : si c’est la table de droite qui est facultative, alors il s’agit d’une jointure externe gauche.
Autre cas : on veut la liste des adresses électroniques de la table clients plus mais parfois il n’y a
aucun client de rattaché.
1 SELECT b.clt_email, a.clt_nom
2 FROM clients AS a RIGHT JOIN clients_plus AS b
3 ON a.clt_num = b.clt_num
4 -- jointure facultative droite
Dernier cas : on veut la liste des clients dont on a soit le nom, soit l’e-mail.
1 SELECT a.clt_nom, b.clt_email
2 FROM clients AS a FULL OUTER JOIN clients_plus AS b
3 ON a.clt_num = b.clt_num
4 -- jointure facultative dans les deux sens
==== Union des sélections ====
On peut regrouper les résultats de plusieurs requˆetes de sélection.
Exemple : `a supposer que les tables commandes2000 et comandes2001 existent, on peut visualiser les
commandes d’un client au cours de ces deux années ainsi :
1 SELECT a.cmd_num, a.cmd_date
2 FROM commandes2000 AS a JOIN clients AS b
3 ON a.cmd_clt = b.clt_num
4 WHERE b.clt_nom = ’Razibus’
5
6 UNION
7
8 SELECT a.cmd_num, a.cmd_date
9 FROM commandes2001 AS a JOIN clients AS b
10 ON a.cmd_clt = b.clt_num
11 WHERE b.clt_nom = ’Razibus’
Remarques :
– l’utilisation de l’opérateur UNION suppose que les colonnes sélectionnées des les deux requˆetes sont
du mˆeme nombre, du mˆeme type et dans le mˆeme ordre (mais pas forcément les mˆemes) ;
– les doublons sont supprimés automatiquement (i.e. on ne retrouve pas deux fois la mˆeme ligne) `a
moins d’utiliser UNION ALL ;
– pour spécifier les intitulés de colonnes, préciser les alias AS dans la première clause SELECT.
Il est parfois possible de substituer un UNION par une jointure supplémentaire et plusieurs conditions
WHERE, mais c’est plus facile d’utiliser UNION. De plus, on peut parfois obtenir de meilleures performances
en décomposant une requˆete complexe en une série de SELECT combinés avec l’opérateur UNION.
==== Sous-requêtes ====
Les conditions de sélection de la clause WHERE peuvent utiliser le résultat d’une autre requˆete.
=== Sous-requête renvoyant une valeur ===
Lors que cette autre requˆete ne revoie qu’une valeur, on peut utiliser = et tous les autres opérateurs
de comparaison logique 7.
Exemple : pour afficher les commandes d’un client on peut utiliser une sous-requˆete.
1 SELECT cmd_num, cmd_date
2 FROM commandes
3 WHERE cmd_clt = ( SELECT clt_num
4 FROM clients
5 WHERE clt_nom = ’Razibus’ )
=== Sous-requˆete renvoyant une liste de valeurs ===
Les sous-requˆetes qui renvoie une liste de valeurs peuvent ˆetre naturellement utilisé par l’opérateur IN.
Exemple : on veut les commandes de tous les clients dont le nom commence par P.
1 SELECT cmd_num, cmd_date
2 FROM commandes
3 WHERE cmd_clt IN ( SELECT clt_num
4 FROM clients
5 WHERE clt_nom LIKE ’P%’ )
Le langage SQL offre d’autres mot-clé pour ce type de sous-requˆete, découvrons-les par l’exemple.Avec
la table articles qui comporte les colonnes art num, art nom, art prix et art couleur on veut successivement
:
– les articles dont le prix est supérieur `a tous les articles blancs :
1 SELECT art_nom
2 FROM articles
3 WHERE art_prix > ALL ( SELECT art_prix
4 FROM articles
5 WHERE art_couleur = ’blanc’ )
7. mais aussi BETWEEN ... AND ...
ceci est équivalent `a
1 SELECT art_nom
2 FROM articles
3 WHERE art_prix > ( SELECT MAX(art_prix)
4 FROM articles
5 WHERE art_couleur = ’blanc’ )
– les articles dont le prix est supérieur `a l’un des articles blancs :
1 SELECT art_nom
2 FROM articles
3 WHERE art_prix > ANY ( SELECT art_prix
4 FROM articles
5 WHERE art_couleur = ’blanc’ )
ceci est équivalent `a
1 SELECT art_nom
2 FROM articles
3 WHERE art_prix > ( SELECT MIN(prix)
4 FROM articles
5 WHERE art_couleur = ’blanc’ )
– tous les articles mais seulement s’il en existe un de blanc (pourquoi pas ?) :
1 SELECT art_nom
2 FROM articles
3 WHERE EXISTS ( SELECT art_num
4 FROM articles
5 WHERE art_couleur = ’blanc’ )
– tous les articles mais seulement s’il n’en existe pas de blanc (pourquoi pas non plus ?) :
1 SELECT art_nom
2 FROM articles
3 WHERE NOT EXISTS ( SELECT art_num
4 FROM articles
5 WHERE art_couleur = ’blanc’ )
=== Requˆetes correlées ===
Lorsqu’une sous-requˆete a besoin d’information de la requˆete parent, on dit qu’elle est corrélée. Il
suffit d’utiliser des alias AS pour lui passer les informations.
Exemple : quels sont les clients qui ont passé une commande d’un montant supérieur `a 1 % de leur
chiffre d’affaire ?
1 SELECT clt_nom
2 FROM clients AS a
3 WHERE (clt_ca / 100) < ANY ( SELECT cmd_montant
4 FROM commandes AS b
5 WHERE b.cmd_clt = a.clt_num )
Remarques :
– l’alias a est défini dans la requˆete appelante et est utilisable dans la sous-requˆete ;
– La sous-requˆete sera exécutée autant de fois qu’il y a de clients.
=== Requˆetes imbriquées vs. jointures ===
Souvent une sous-requˆete peut ˆetre remplacée par une jointure :
1 SELECT DISTINCT a.clt_nom
2 FROM clients AS a
3 JOIN commandes AS b ON b.cmd_clt = a.clt_num
4 WHERE (a.clt_ca / 100) < b.cmd_montant
Lorsqu’on emploie les requˆetes imbriquées, on précise `a SQL Server comment effectuer la requˆete ;
c’est une fa¸con procédurale de voir les choses. Tandis que quand on utilise des jointures c’est une forme
relationnelle et SQL Server se charge de faire pour le mieux.
Par contre, il n’y a parfois pas d’équivalence jointures `a une écriture en sous-requˆetes. Mais quand
on a un équivalent, il vaut mieux utiliser les jointures car la requˆete sera optimisée par l’interprète SQL.
Ceci dit, l’utilisation de sous-requˆete est plus lisible ...
=== Sous-requˆete renvoyant plusieurs colonnes ===
Une sous-requˆete renvoyant une ou plusieurs colonnes peut ˆetre utilisée comme table dans la clause
FROM. Cela peut servir par exemple `a ne sélectionner que les plus gros clients :
1 SELECT a.clt_nom
2 FROM ( SELECT TOP 10 *
3 FROM clients
4 ORDER BY clt_ca DESC ) AS a
5 JOIN commandes AS b ON b.cmd_clt = a.clt_num
6 WHERE (a.clt_ca / 100) < b.cmd_montant
Par contre, on ne peut pas utiliser ce type de sous-requˆete dans une clause WHERE (sauf avec EXISTS),
contrairement `a Oracle.
==== Requêtes multibases ====
A l’origine, SQL ne permet pas de faire de requˆetes qui portent sur les tables de plusieurs bases de données et encore moins gérées par différents SGBDR. Transact-SQL offre un mécanisme de dénomination
qui permet d’effectuer des jointures entre des tables issus de systèmes hétérogènes.
La syntaxe complète du nom d’une table est :
[nom du serveur] . [nom de la base] . [nom du propriétaire] . [nom de la table]
Le propriétaire est généralement dbo le database owner.
Cette syntaxe permet d’écrire une jointure portant sur les tables des deux bases de données différentes
(sur le mˆeme serveur) :
1 SELECT a.cmd_num, b.art_nom
2 FROM GestionCommerciale.dbo.commandes AS a
3 JOIN GestionProductique.dbo.articles AS b ON a.cmd_art = b.art_num
Ou une jointure portant sur deux bases de données gérées par des serveurs différents :
1 SELECT a.clt_nom AS [clients communs]
2 FROM ENTREPRISE1.GestionCommerciale.dbo.clients AS a
3 JOIN ENTREPRISE2.GestionCommerciale.dbo.clients AS b ON a.clt_nom = b.clt_nom
Cependant, la requˆete étant effectuée sur un serveur SQL Server (ENTREPRISE1 par exemple), il faut
que les autres serveurs utilisés (ENTREPRISE2 en l’occurrence) soient déclarés comme serveurs liés dans
le serveur SQL Server.
Remarque : ENTREPRISE2 n’est pas forcément un serveur SQL Server, mais peut ˆetre n’importe quel
SGBD reconnu par DTS (cf. 17 page 75), ce qui permet d’écrire des requêtes sur des données hétérogènes
(cf. [1]).
==== Quelques fonctions SQL ====
A tout moment dans une requête SELECT on peut utiliser de nombreuses fonctions, `a commencer par
la fonction suivante qui s’avère souvent très utile : ISNULL qui permet de remplacer NULL par une autre
valeur. Par exemple, pour remplacer une absence de chiffre d’affaire par un chiffre d’affaire nul :
1 SELECT clt_nom, ISNULL(clt_ca, 0)
2 FROM clients
=== Fonctions d’agrégat ===
COUNT dénombrement
SUM
AVG moyenne
VAR variance
STDEV écart-type
MIN
MAX
Exemple :
1 -- pour compter le nombre de client
2 SELECT COUNT(clt_num)
3 FROM clients
4
5 -- pour connaitre le chiffre d’affaire moyen des clients
6 SELECT AVG(clt_ca)
7 FROM clients
Remarque : toutes ces fonctions ignorent les valeurs NULL (surtout COUNT).
=== Opérateurs ===
C’est-`a-dire : +, -, *, /, % et le + de concaténation. Exemple :
1 -- pour afficher le chiffre d’affaire mensuel moyen de chaque client
2 SELECT clt_nom, clt_ca / 12 AS [ca mensuel]
3 FROM clients
4
5 -- pour concatener le nom et le prenom
6 SELECT clt_nom + ’ ’ + clt_prenom AS [Identité]
7 FROM clients
=== Fonctions sur les dates ===
Avec date de type DATETIME :
DATEADD(year, 4, date) ajoute 4 ans `a date
DATEADD(month, 4, date)
DATEADD(week, 4, date)
DATEADD(day, 4, date)
DATEADD(hour, 4, date)
DATEDIFF(minute, date debut, date fin) donne la différence en minutes entre
DATEDIFF(second, date debut, date fin) date fin et date debut
DATEPART(month, date) renvoie le numéro du mois de date
Remarque : DATEDIFF et DATEPART renvoient un entier.
Reprenons l’exemple de l’auto-jointure. Si on veut vraiment sélectionner les clients qui ont commandé
le mˆeme jour, il faut remplacer le test d’égalité entre les dates par :
1 SELECT DISTINCT a.cmd_clt, b.cmd_clt
2 FROM commandes AS a
3 JOIN commandes AS b ON a.cmd_clt <> b.cmd_clt
4 WHERE DATEDIFF(day, a.cmd_date, b.cmd_date) = 0
5 -- sinon il s’agit d’une egalite a 3.33 ms pres
Remarque : la requˆete précédente n’est pas équivalente `a la suivante.
4 SéLECTIONNER LES DONNéES 27
1 SELECT DISTINCT a.cmd_clt, b.cmd_clt
2 FROM commandes AS a
3 JOIN commandes AS b ON a.cmd_clt <> b.cmd_clt
4 WHERE DATEDIFF(hour, a.cmd_date, b.cmd_date) BETWEEN -24 AND 24
5 /* dans ce cas les clients ont commande a moins de 24h d’intervalle
6 mais pas forcement le meme jour */
=== Fonctions sur les chaˆines de caractères ===
Notamment : LEN (longueur), LOWER (convertit tout en minuscule), REPLACE, SUBSTRING et UPPER
(tout en majuscule).
=== Principales fonctions mathématiques ===
`A
savoir : ABS (valeur absolue), CEILING (partie entière +1), COS, EXP, FLOOR (partie entière), LOG
(logarithme neperien), LOG10, PI, POWER, SIGN, SIN, SQRT, SQUARE et TAN.
Par exemple, on peut écrire la dernière requˆete ainsi :
1 SELECT DISTINCT a.cmd_clt, b.cmd_clt
2 FROM commandes AS a
3 JOIN commandes AS b ON a.cmd_clt <> b.cmd_clt
4 WHERE ABS(DATEDIFF(hour, a.cmd_date, b.cmd_date)) <= 24
4.7.6 Fonctions utilisateur
On peut aussi définir ses propres fonctions. Syntaxe :
CREATE FUNCTION ... (son nom)
(...) (ses paramètres)
RETURNS ... (le type de la valeur de retour)
AS
BEGIN
...
RETURN ... (la valeur de retour)
END
La rédaction de ces fonctions est la mˆeme que celle des procédures stockées (cf. §9 page 44) :
1 CREATE FUNCTION EcartEnHeure
2 (
3 @date1 DATETIME,
4 @date2 DATETIME
5 )
6 RETURNS INT
7 AS
8 BEGIN
9 RETURN ABS(DATEDIFF(hour, @date1, @date2))
10 END
Puis on peut l’utiliser dans une requête :
1 SELECT DISTINCT a.cmd_clt, b.cmd_clt
2 FROM commandes AS a
3 JOIN commandes AS b ON a.cmd_clt <> b.cmd_clt
4 WHERE dbo.EcartEnHeure(a.cmd_date, b.cmd_date) <= 24
5 /* dans le cas d’une fonction utilisateur
6 il ne faut pas oublier le proprietaire */
Remarques :
– on peut mettre jusqu’`a 1024 paramètres ;
– on dispose de ALTER FUNCTION et de DROP FUNCTION.
==== Conclusion ====
On aboutit finalement `a la stratégie suivante pour élaborer une requˆete de sélection :
1. décomposer au maximum en plusieurs sélection que l’on pourra réunir avec UNION ;
2. décomposer chaque sélection complexe en requˆete et sous-requˆetes simples ;
3. et pour chaque requˆete et chaque sous-requˆete :
(a) déterminer les tables en jeu pour remplir la clause FROM et les JOIN nécessaires ;
(b) déterminer les colonnes `a afficher pour remplir la clause SELECT ;
(c) déterminer les conditions de sélection pour remplir la clause WHERE ;
(d) ajouter les éventuels ORDER BY, DISTINCT et TOP en dernier.
Agreger des Données
place les informations relatives `a ces niveaux.
En dessous du Détail viennent les pieds des niveaux de groupement successifs dans lesquels on place
généralement les résultats d’agrégation voulus.
Il reste :
– l’en-tˆete d’état pour son titre et ses conditions de sélection (notamment la ou les dates, les clients
retenus, les articles concernés, etc.), il ne faut surtout pas oublier de les préciser, sans quoi le
document n’a aucune valeur ;
– le pied de page pour la numérotation des pages (ce qui est important si le document est imprimé
et malencontreusement mélangé) ;
– et le pied d’état pour les agrégats du niveau 0 (un total général par exemple).
Exemple :
Exemple d’état le titre
pour les clients ayant plus de 5 commandes les conditions de sélection
pour toutes les dates (dans l’en-tˆete d’état)
Pays NbCommandes Montant les intitulés (en-tˆete de page)
client n 0007 (Razibus) un en-tˆete de groupe
France 4 1000.00 une ligne par sous-groupe
Italie 4 234.56
Total 8 1234.56 un pied de groupe
client n 3344 (Fricotin)
France 6 394.27
Italie 6 394.27
Total 12 788.54
Total général 20 1923.10 le pied d’état
page 1 un pied de page
Les Contraintes
====== Les Contraintes d'Intégrité SQL ======
Les contraintes permettent de sécuriser les données d’une table. On en connaˆit déj`a : les clés primaire
et étrangère, les valeurs par défaut. L’objet de cette section est d’apprendre `a créer ces contraintes.
===== Syntaxe =====
Pour définir une contrainte sur une colonne d’une table, on dispose de deux syntaxe :
– au moment de la création de la table
Exemple : positionnons-nous dans le cas d’une mutuelle
<code>
1 CREATE TABLE assures
2 (
3 num INT PRIMARY KEY IDENTITY(1,1),
4 numSS CHAR(15),
5 titre VARCHAR(5),
6 age INT,
7 date_entree DATETIME,
8 num_rue INT,
9 rue VARCHAR(255), -- 256 est un multiple de 8
10 code_postal CHAR(5),
11 ville VARCHAR(63)
12 CONSTRAINT cst_num_rue -- nom de la contrainte
13 CHECK(num_rue > 0) -- corps de la contrainte
14 CONSTRAINT cst_code_postal
15 CHECK(code_postal LIKE (’[0-9][0-9][0-9][0-9][0-9]’))
16 )
</code>
– après la création de la table :
<code>
1 ALTER TABLE assures ADD CONSTRAINT cst_numSS CHECK (numSS LIKE (’[0-2][0-9]...’))
</code>
Remarques :pour pouvoir ajouter une contrainte, les données existantes doivent vérifier cette contrainte ;
sur insertion ou mise-`a-jour, les nouvelles données sont contrˆolées (si une donnée ne vérifie pas une contrainte alors toute la transaction est annulée) ;
on peut manipuler plusieurs contraintes dans un seul ALTER TABLE, il suffit de les séparer par des virgules ;
on peut imposer plusieurs contraintes sur une même colonne.
Pour retirer une contrainte :
<code>
ALTER TABLE assures DROP CONSTRAINT cst_code_postal
</code>
Pour modifier une contrainte, il faut d’abord la supprimer puis la créer de nouveau.
==== CHECK ====
=== Syntaxe ===
La syntaxe d’une contrainte de type vérification est : CHECK(clause WHERE sans le WHERE).
Exemples : on peut donc
– mettre plusieurs conditions
<code>
1 ALTER TABLE assures
2 ADD CONSTRAINT cst_age
3 CHECK(age >= 0 AND age < 150)
<code>
– préciser une liste de choix desquels on ne peut pas sortir
1 ALTER TABLE assures
2 ADD CONSTRAINT cst_titre
3 CHECK(titre IN (’M.’, ’Mme’, ’Melle’, ’Dr.’, ’Pr.’, ’SAS’, ’Me’))
– utiliser plusieurs colonnes
1 ALTER TABLE articles
2 ADD CONSTRAINT cst_TTCsupH
3 CHECK(art_prixTTC > art_prix)
Remarques : par contre
– la clause CHECK ne peut pas contenir de sous-requˆete ;
– la clause CHECK ne peut pas porter sur une colonne UNIQUEIDENTIFIER ou utilisant IDENTITY (cf.
=== Règle ===
Si plusieurs colonnes (éventuellement dans des tables différentes) utilisent la mˆeme contrainte CHECK,
alors il est intéressant de définir une règle commune `a toutes ces colonnes.
Exemple :
1 CREATE RULE AgeRule
2 AS @age >= 0 AND @age < 150
Remarques :
– @age est une variable locale, son nom n’a pas d’importance ;
– après AS on peut mettre la mˆeme chose qu’après CHECK.
On peut ensuite attacher une règle `a une colonne en utilisant la procédure stockée sp bindrule.
Exemple :
1 sp_bindrule AgeRule, [assures.age]
Remarques :
– une colonne peut cumuler une règle et une contrainte CHECK ;
– mais c’est la contrainte CHECK qui est vérifiée en premier ;– on dispose de la procédure sp unbindrule [assures.age], AgeRule et de DROP RULE AgeRule8.
Il est également possible d’attacher une règle `a un type de données, ce qui permet d’éviter de les
attacher `a toutes les colonnes de ce type.
Exemple
<co
1 sp_addtype CodePostalType, CHAR(5)
2
3 CREATE RULE CodePostalRul
4 AS @cp LIKE(’[0-9][0-9][0-9][0-9][0-9]’
6 sp_bindrule CoePostalRule, CodePostalType
7
8 -- puis
9
10 CREATE TABLE assures
11 (
12 ...
13 code_postal CodePostalType,
14 ...
15 )
8. qui ne fonctionne que si tous les sp unbindrule ont été effectués
<cod
==== Valeur par défat ====
Pour préciser une valeur par défaut on peut le faire simplement `a la création de la table (cf. §3.2 page
16), ou les ajouter a posteriori en tant que contrainte
Exemple
1 ALTER TABLE assures
2 ADD CONSTRAINT def_date_entree
3 DEFAULT GETDATE() FOR date_entree
On peut mettre après DEFAULT :
– une fonction niladique (i.e. sans argument) ;
– une constante ;
– ou NULL.
On peut aussi créer des valeurs par défaut partageables et l’attacher `a une colonne ou `a un type de
données. Exemple :
<code>
1 CREATE DEFAULT Hier
2 AS DATEADD(day, -1, GETDATE())
3
4 sp_bindefault Hier, [assures.date_entree]
5
6 -- ou
7
8 sp_addtype DateEntree, DATETIME
9
10 sp_bindefault Hier, DateEntree
11
12 -- puis
13
14 ALTER TABLE assures
15 ALTER COLUMN date_entree DateEntree
</code>
Remarques :
– si la contrainte DEFAULT est définie, alors une éventuelle valeur par défaut partageable serait ignorée ;
– on dispose de la procédure sp unbindefault et de DROP DEFAULT9.
Astuce : pour utiliser les valeurs par défaut, les règles et les type de données personnels dans plusieurs
bases de données, il suffit de les créer dans la base model car alors toute nouvelle base de données en
héritera.
9. qui ne fonctionne que si tous les sp unbindefault ont été effectués
==== Clé primaire ====
Les clés primaires sont aussi des contraintes et pour les ajouter a posteriori on peut utiliser la syntaxe :
CONSTRAINT nom de la contrainte
PRIMARY KEY (colonne(s) concernée(s) par la clé primaire)
Cette syntaxe est la indispensable pour déclarer une clé primaire composite (c’est-`a-dire portant sur
plusieurs colonnes 10).
Exemple : dans une base de donnée bibliothèque, un exemplaire d’un livre est identifié par son numéro
ISBN et son numéro de copie.
1 ALTER TABLE ouvrages
2 ADD CONSTRAINT pk_ouvrages
3 PRIMARY KEY (isbn, no_copie)
==== UNIQUE ====
On peut imposer `a une colonne (ou plusieurs colonnes) de prendre des valeurs uniques (c’est-`a-dire
sans doublons) mˆeme si ce n’est pas une clé primaire.
Exemples :
1 ALTER TABLE assures
2 ADD CONSTRAINT un_numSS
3 UNIQUE (numSS)
1 ALTER TABLE clients
2 ADD CONSTRAINT un_nom_prenom
3 UNIQUE (clt_nom, clt_prenom)
Remarque : la valeur NULL n’est autorisée qu’une seule fois dans une colonne UNIQUE.
==== Clé étrangère ====
Les clés étrangères sont aussi des contraintes, et `a nouveau, si on a oublié de les préciser dès la
création de la table, on peut les ajouter après. Attention : on ne peut faire de clé étrangère que vers une
clé primaire ou vers une colonne UNIQUE.
Exemple : avec la table feuilles soin qui possède la colonne num assure qui doit prendre ses valeurs
dans la colonne num de la table assures
1 ALTER TABLE feuille_soin
2 ADD CONSTRAINT fk_num_assure
3 FOREIGN KEY (num_assure) REFERENCES Assures(num)
Cette syntaxe est nécessaire si la clé étrangère est composite.
10. il est conseillé d’éviter les clés primaires composites `a chaque fois que cela est possible
Exemple : dans une base bibliothèque un emprunt concerne un exemplaire d’un livre, les numéros
ISBN et de copie doivent donc ˆetre les mˆemes.
1 ALTER TABLE emprunts
2 ADD CONSTRAINT fk_emprunts
3 FOREIGN KEY (isbn, no_copie) REFERENCES ouvrages(isbn, no_copie)
==== Conclusion ====
On vient de rencontrer quelques outils qui nous permette de rendre les données plus cohérentes :
– les colonnes n’acceptent qu’un ensemble de valeurs correctes, c’est l’intégrité de domaine (on spécifie
pour ¸ca le type de données, les contraintes CHECK, les valeurs par défaut et aussi NOT NULL) ;
– les lignes doivent ˆetre identifiables de manière unique, c’est l’intégrité des entités (on utilise pour
¸ca les clés primaires et les contraintes UNIQUE) ;
– on doit maintenir de bonnes relations entre les tables, c’est l’intégrité référentielle (c’est tout le
travail des clés étrangères).
Exemples d’intégrité référentielle :
– il est impossible de créer des factures qui ne sont reliées `a aucun client ;
– et `a l’inverse, il est impossible de supprimer un client `a qui il reste des factures (impayées).
Il reste malgré tout un quatrième type d’intégrité qui regroupe toutes les règles (parfois complexes)
propre `a la politique interne de l’entreprise, c’est l’intégrité d’entreprise.
Exemples de règle spécifiques `a une entreprise :
– un client ne peut pas commander lorsqu’il doit déj`a trop d’argent ;
– un client qui commande régulièrement bénéficie de réductions.
Pour implémenter ce genre de règle, on a besoin d’une programmation plus élaborée que les contraintes.
Les Verrous
Comme les transactions sont traitées en ligne sur un serveur multi-utilisateur, les accès concurrentiels
aux données doivent ˆetre gérés. Pour empˆecher les autres utilisateurs de modifier ou de lire des données
faisant l’objet d’une transaction qui n’est pas encore terminée, il faut verrouiller ces données.
Rappelons que lors d’une transaction :
– les données nécessaires sont lues sur le disque puis chargées en mémoire centrale ;
– les opérations ont lieu dans la mémoire centrale ;
Fig. 7 – Traitement des données d’une transaction en mémoire
– une fois toutes les instructions validées, les nouvelles données sont écrites sur le disque.
Si les données sur le disque sont modifiées pendant la transaction, celle-ci travaille avec des données
fausses. On a alors un problème de cohérence.
==== Isolation ====
Par défaut, SQL Server ne garantit pas que les données utilisées seront les mˆemes pendant toute la
transaction. Pour l’obliger `a rendre maximal le verrouillage des données il faut lui imposer de mettre en
série les transactions concurrentes. Pour cela on dipose de l’instruction :
1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Le problème majeur de la mise en série des transactions est qu’une transaction interminable bloque
toutes les suivantes. Il est possible de préciser un délai d’attente maximal pour cause de verrouillage (par défaut il n’y en a pas). Pour cela on utilise l’instruction :
1 SET LOCK_TIMEOUT 180000
2 -- definit un delai d’expiration de 3 minutes (en millisecondes)
3 -- au dela de ce delai, la transaction en attente est annulee
Remarques :
– ces instructions sont attachées `a la connexion qui les exécute ;
– ces instructions restent valables pour toutes les transactions qui suivent, jusqu’`a la déconnexion ou
jusqu’`a nouvel ordre.
Le niveau d’isolation par défaut est READ COMMITTED, il garantit seulement que les données sont
cohérentes au moment de leur lecture (et pas pendant le reste de la transaction). Pour y revenir il suffit
d’écrire :
1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
==== Verrouillage de niveau table ====
Dans ce paragraphe on suppose que l’on se trouve au niveau d’isolation READ COMMITTED.
`A
chaque transaction on peut indiquer le type de verrouillage pour chaque table utilisée par les instructions
SELECT, INSERT, DELETE et UPDATE. Par défaut, SQL Server verrouille les tables concernées.
On peut obliger SQL Server `a laisser le verrou jusqu’`a la fin de la transaction :
1 BEGIN TRAN
2 UPDATE clients WITH(HOLDLOCK)
3 SET ...
4 COMMIT TRAN
On peut se contenter de verrouiller seulement les lignes concernées par la transaction :
1 BEGIN TRAN
2 UPDATE clients WITH(ROWLOCK)
3 SET ...
4 COMMIT TRAN
Lorsqu’une première requˆete utilise WITH(ROWLOCK), on peut indiquer `a une deuxième d’ignorer les
lignes verrouillées (afin de ne pas bloquer la transaction) :
1 SELECT AVG(clt_ca)
2 FROM clients WITH(READPAST)
==== Conclusion ====
On veillera `a respecter les consignes suivantes pour les transactions :
– elles doivent ˆetre aussi courtes que possible afin d’éviter les files d’attente trop longues ;
– il ne faut pas les imbriquer (mˆeme si c’est possible, ¸ca ne sert `a rien) ;
– ne surtout pas interagir avec l’utilisateur pendant la transaction (mais avant).
Il est souvent bon de suivre ces quelques conseils concernant les verrous :
– mettre en série les transactions de toutes les connexions utilisateurs ;
– laisser SQL Server gérer la granularité des verrous : le laisser décider s’il faut verrouiller les
lignes d’une table ou la table entière, c’est-`a-dire n’utiliser ni WITH(ROWLOCK) ni WITH(PAGLOCK) ni
WITH(TABLOCK) (dont on a pas parlé ici d’ailleurs).
Les Statistiques
Comprendre et utiliser les statistiques
Mise à jour de toutes les stats d'une base
<code>
SET NOCOUNT ON
DECLARE @table_name varchar(127), @cmd varchar(256)
DECLARE table_cursor CURSOR FOR
SELECT s.name + '.' + t.name
FROM sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
ORDER BY t.name
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
PRINT @table_name
set @cmd = 'UPDATE STATISTICS ' + @table_name + ' WITH FULLSCAN'
exec (@cmd)
FETCH NEXT FROM table_cursor
INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
</code>
I. Introduction
Dans cet article nous allons détailler l'utilité et le fonctionnement des statistiques de distribution, présentes sur les index et les colonnes.
Ces statistiques sont indispensable aux bonnes performances de l'exécution des requêtes et leur nature est parfois mal connue.
II. Utilité des statistiques
Prenons un exemple.
Vous avez fait la connaissance sur un chat internet d'une jeune fille qui vous semble charmante et qui vit à Courtrai, en Belgique flamande. Elle semble apprécier vos grandes qualités de coeur et vous convenez d'un premier rendez-vous, afin de vous connaître en vrai. Comme vous êtes galant, vous proposez de faire le voyage à Courtrai. Elle vous donne rendez-vous au café De Brouwzaele.
Si vous n'avez jamais entendu parler de Courtrai, vous ne savez probablement rien de la taille de la ville, de la complexité de ses rues et donc vous ne pouvez par juger, avant de vous y rendre, si vous trouverez le café De Brouwzaele facilement.
Logiquement, votre premier réflexe sera de vous renseigner sur le nombre d'habitants. Si Courtrai est une ville de quelques milliers d'âmes, vous pouvez vous dire qu'elle est peu étendue et que le café De Brouwzaele sera facile à trouver. Par contre, en cherchant sur Wikipédia, vous vous apercevez que Courtrai compte quelque 73'000 habitants. Sachant cela, vous vous attendez à plus de difficultés. Vous allez donc demander l'adresse du café De Brouwzaele. Kapucijnestraat 19... bien, mais, combien y a-t-il de rues à Courtrai, et où se trouve Kapucijnestraat par rapport à la gare ? La connaissance de ces détails risque d'influencer fortement votre méthode de déplacement.
Pour SQL Server, la problématique est exactement la même. Imaginons que nous envoyons cette requête dans la base d'exemple AdventureWorks :
SELECT *
FROM Production.TransactionHistory
WHERE ProductId = 800
La première chose que sait SQL Server, c'est qu'il y a 113'443 lignes dans la table Production.TransactionHistory (dans ce cas, il maintient un row count dans la définition des index). Ensuite, il sait qu'il dispose d'un index sur la colonne ProductId, qui va lui permettre de filter rapidement les enregistrements où ProductId = 800 :
SELECT *
FROM sys.indexes i
WHERE OBJECT_NAME(i.object_id)
L'index s'appelle IX_TransactionHistory_ProductId.
L'optimiseur de SQL Server se base sur l'évaluation du coût d'un plan d'exécution afin de déterminer la meilleure stratégie. C'est ce qu'on appelle une optimisation basée sur le coût (cost-based optimization).
Mais pour évaluer le coût d'un plan d'exécution, SQL Server ne peut se contenter des seules informations que nous venons de voir. Il doit disposer d'une estimation du nombre de lignes qui va être retourné, pour juger au plus juste du coût de celle-ci. C'est là où interviennent les statistiques.
Si SQL Server dispose de statistiques de distribution des valeurs contenues dans une colonne, il peut évaluer avec une bonne précision le nombre de lignes impactées lorsqu'une requête filtre sur ces valeurs, car il sait à l'avance combien de lignes environ correspondent au critère.
Les statistiques sont donc un échantillonnage des données. Elles sont créées et maintenues automatiquement par SQL Server sur la clé de tout index créé.
Jetons un premier coup d'oeil sur les statistiques de l'index sur ProductId.
SELECT *
FROM sys.stats s
JOIN sys.stats_columns sc ON s.object_id = sc.object_id
AND s.stats_id = sc.stats_id
JOIN sys.columns c ON s.object_id = c.object_id
AND sc.column_id = c.column_id
WHERE s.name = 'IX_TransactionHistory_ProductId'
Où nous voyons que des statistiques existent dans cet index, pour la colonne ProductId.
Elles sont en quelque sorte les informations vitales de l'index, ou son diplôme: elles permettent à SQL Server, dans sa stratégie, d'estimer le coût d'utilisation de l'index. En d'autres termes, grâce aux statistiques, SQL Server va pouvoir connaître à l'avance, approximativement, le résultat d'un
SELECT COUNT(*)
FROM Production.TransactionHistory
WHERE ProductId = 800
Si le ratio entre le nombre de lignes qui répond au critère recherché et le nombre total de ligne est faible, SQL Server va choisir d'utiliser l'index. Si par contre il est important, et qu'un bonne partie des lignes de la table doit être retournée par la requête, SQL Server va certainement faire le choix de parcourir la table plutôt qu'utiliser l'index, parce qu'il sera moins coûteux de procéder de cette façon que de résoudre ligne par ligne les adresses de pointeurs contenues dans le dernier niveau de l'index.
II-A. Sur les index
Dans le cas qui nous occupe, ProductId = 800 correspond à 416 lignes / 133'443. Voyons le plan d'exécution estimé:
DBCC FREEPROCCACHE
GO
SET SHOWPLAN_XML ON
GO
SELECT *
FROM Production.TransactionHistory
WHERE ProductId = 800
GO
SET SHOWPLAN_XML OFF
GO
Ci-dessous un extrait de ce plan:
<RelOp NodeId="0" PhysicalOp="Clustered Index Scan"
LogicalOp="Clustered Index Scan" EstimateRows="418"
EstimateIO="0.586088" EstimateCPU="0.124944" AvgRowSize="54"
EstimatedTotalSubtreeCost="0.711032" Parallel="0" EstimateRebinds="0"
EstimateRewinds="0">
Nous voyons que l'optimiseur choisit de parcourir la table (donc ici un scan de l'index ordonné, puisque le dernier niveau de l'index ordonné correspond aux données de la table) au lieu d'utiliser l'index. Nous voyons aussi dans l'attribut EstimateRows que les statistiques permettent à l'optimiseur d'avoir une idée assez précise du nombre de lignes correspondant à la clause WHERE.
Essayons maintenant d'indiquer un nombre plus petit de lignes à retourner:
SELECT ProductId, COUNT(*)
FROM Production.TransactionHistory
GROUP BY ProductId
Nous voyons par exemple que le ProductId 760 ne se retrouve que 6 fois dans la table. Essayons avec cela :
DBCC FREEPROCCACHE
GO
SET SHOWPLAN_XML ON
GO
SELECT *
FROM Production.TransactionHistory
WHERE ProductId = 760
GO
SET SHOWPLAN_XML OFF
GO
Un extrait du plan d'exécution estimé:
<RelOp NodeId="2" PhysicalOp="Index Seek" LogicalOp="Index Seek"
EstimateRows="5.28571" EstimateIO="0.003125" EstimateCPU="0.000162814"
AvgRowSize="15" EstimatedTotalSubtreeCost="0.00328781" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
...
<Object Database="[AdventureWorks]" Schema="[Production]"
Table="[TransactionHistory]" Index="[IX_TransactionHistory_ProductID]" />
Cette fois-ci nous voyons que l'index est utilisé: c'est la solution la moins coûteuse. L'estimation des lignes à retourner, basée sur les statistiques de distribution, donne 5.28571 lignes. Nous verrons plus loin d'où SQL Server tire cette approximation.
II-B. Colonnes non indexées
Les statistiques sont-elles utiles uniquement pour les index ?
Non.
L'optimiseur peut profiter de la présence de statistiques même sur des colonnes qui ne font pas partie de la clé d'un index. Cela permettra à SQL Server d'évaluer le nombre de lignes retournées dans une requête qui filtre sur les valeurs de cette colonne, et donc de choisir un bon plan d'exécution. Par exemple, si la colonne participe à un JOIN, cela permettra à l'optimiseur de choisir le type de jointure le plus adapté.
En SQL Server 2000, comme les statistiques de colonne sont stockées dans la table système sysindexes avecla définition des index, la création de statistiques avait comme conséquence de diminuer le nombre possible d'index pouvant être créés sur la table.
Vous étiez limités à 249 index non ordonnés par table, donc à 249 index plus statistiques.
Sur des tables contenant beaucoup de colonnes, la création automatique des statistiques pouvait vous faire atteindre cette limite. Il vous fallait alors supprimer des statistiques (avec la commande DROP STATISTICS ) pour permettre la céation de nouveaux index.
Dans SQL Server 2005, la limite du nombre de statistiques de colonne sur une table a été augmentée à 2000, plus 249 statistiques d'index, poussant le nombre de statistiques possibles sur une table à 2249.
Pour voir les statistiques créées automatiquement :
SELECT * FROM sys.stats WHERE auto_created = 1
leur nom commencent par _WA_Sys_.
II-C. Sélectivité et densité
Ces statistiques de distribution permettent de déterminer la sélectivité d'un index. Plus le nombre de données uniques présentes dans la colonne est élevé, plus l'index est dit sélectif. La plus haute sélectivité est donnée par un index unique, où toutes les valeurs sont distinctes, et les plus basses sélectivités sont amenées par des colonnes qui contiennent beaucoup de doublons, comme les colonnes de type bit, ou char(1) avec seulement quelques valeurs (par exemple H et F pour une colonne de type sexe).
A l'inverse, la densité représente le nombre de valeurs dupliquées présentes dans la colonne. Plus il y a de valeurs dupliquées, plus la densité est élevée.
Sur l'index dans son entier, le pourcentage moyen de lignes dupliquées donne la sélectivité et la densité : plus ce pourcentage est faible, plus l'index est sélectif, et plus il est élevé, plus l'index est dense. La densité s'oppose donc à la sélectivité.
III. Où se trouvent-elles
J'ai quelquefois entendu des imprécisions au sujet des statistiques. Les statistiques ne sont pas des index, et ne sont pas stockés comme les index ou dans les pages d'index. Notamment les informations de distribution ne se situent pas au niveau des noeuds de l'arborescence de l'index. Si c'était le cas, le plan d'exécution ne pourrait pas faire le choix d'utiliser l'index ou non... avant de l'utiliser, puisqu'il devrait entrer dans l'arborescence pour retrouver l'estimation de lignes à retourner, puis ensuite quitter l'index pour faire un scan si ce choix lui paraît meilleur.
Ce genre de décision doit se prendre avant de commencer le processus de recherche des données.
Ainsi, les données de statistiques sont disponibles en tant que métadonnées de l'index, et non pas dans sa structure propre. Elle sont stockées dans une colonne de type LOB (objet de grande taille), dans la table système sysindexes.
En faisant cette requête :
SELECT statblob
FROM sys.sysindexes
On peut voir que la colonne statblob, qui contient ces informations de statistiques, retourne NULL. La colonne contient bien des données, mais elles ne sont pas visibles par ce moyen, et la valeur retournée par requête sera toujours NULL. D'ailleurs,
SELECT *
FROM sys.indexes
qui est la vue de catalogue affichant les données d'index, ne retourne pas cette colonne.
La seule façon de retourner les données de statistiques est d'utiliser une commande DBCC :
DBCC SHOW_STATISTICS
ou d'afficher les propriétés des statistiques dans l'explorateur d'objets de SQL Server Management Studio (noeud Statistiques), page Détails, qui affiche les résultats du DBCC SHOW_STATISTICS dans une zone de texte.
Exemple avec notre index :
DBCC SHOW_STATISTICS ( 'Production.TransactionHistory', X_TransactionHistory_ProductID)
qui retourne un entête, la densité par colonne de l'index, et un histogramme de distribution des données, selon l'échantillonnage opéré:
III-A. Entête
Name Updated Rows Rows Sampled Steps Density Average key length String Index
IX_TransactionHistory_ProductID Apr 26 2006 11:45AM 113443 113443 200 0,01581469 8 NO
Vous trouvez dans l'entête les informations générales des statistiques: nom, date de dernière mise à jour, nombre de lignes dans l'index et nombre de lignes échantillonnées, nombre d'étapes (steps) effectuées pour effectuer cet échantillonnage, densité de l'index, longueur moyenne de la clé de l'index. Le booléen String Index indique si la colonne contient des résumés de chaîne, qui est une fonctionnalité de SQL Server 2005 que nous détaillerons plus loin.
III-B. Densité
All density Average Length Columns
0,0022675736961 4 ProductID
8,815E-06 8 ProductID, TransactionID
Le deuxième résultat vous indique la densité de chaque colonne de l'index. La clé de l'index dépendant toujours de la première colonne, la densité individuelle est calculée seulement pour celle-ci, ensuite les densités calculée sont celles des colonnes agrégées dans l'ordre de leur présence dans l'index (le préfixe de la clé), une colonne après l'autre, si l'index est composite, ou si l'index est non ordonné et présent sur une table ordonnée. Dans ce cas, comme nous le voyons ici, chaque index non ordonné contiendra la ou les colonnes de l'index ordonné en dernière position.
La densité est donnée par le nombre moyen de lignes pour une valeur de la colonne divisé par le nombre total de lignes. Le nombre moyen de lignes est calculé en prenant le nombre total de ligne (T) divisé par le nombre de valeurs distinctes (VD) dans la colonne. Ce qui donne :
(T / VD) / T
Qui équivaut à
1 / VD
Et en effet :
SELECT 1.00 / COUNT(DISTINCT ProductId))
FROM Production.TransactionHistory
retourne bien 0,0022675736961
Plus la densité est faible, plus la sélectivité est élevée, et plus l'index est utile. La sélectivité maximale est offerte par un index unique, comme ici l'index ordonné unique sur la clé primaire de la table.
La densité de la colonne unique est donc 1 / VD, qui correspond logiquement à 1 / T, puisque le nombre de valeurs distinctes équivaut au nombre de lignes de la table.
SELECT 1.00 / COUNT(*)
FROM Production.TransactionHistory
retourne 0.0000088149996, ce qui représente une très basse densité, donc une sélectivité très élevée. Pour vérifier que nous obtenons bien la même valeur que la deuxième densité (ProductID, TransactionID), forçons la notation scientifique en retournant une donnée float :
SELECT CAST(1.00 as float) / COUNT(*)
FROM Production.TransactionHistory
qui donne une valeur approximative de 8,815E-06, CQFD.
Le couple ProductID + TransactionID ne peut être que d'une sélectivité maximale, puisque incluant une colonne unique, chaque ligne est donc unique, et sa densité est 1 / T.
III-C. Echantillonnage
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
1 0 45 0 1
3 45 307 1 45
316 45 307 1 45
319 144 118 2 72
322 218 108 2 109
...
748 0 386 0 1
779 37 771 7 5,285714
Avec les deux affichages précédents, l'optimiseur SQL sait quel est le nombre de lignes dans la table, et le nombre de valeurs distinctes dans la colonne indexée. Il lui faut maintenant pouvoir évaluer, pour une certaine valeur de colonne, quel sera le nombre approximatif de lignes concernées (combien ProductId = 760 concerne-t-il de lignes ?). Pour cela, il maintient un échantillonnage des valeurs établi selon un certain nombre de sauts (les steps que nous avons vus dans les informations de header), sous forme d'un tableau. Dans notre exemple, un tableau de 200 steps. SQL Server crée un maximum de 200 steps par statistiques. Détaillons la signification des colonnes:
RANGE_HI_KEY La valeur de colonne de l'échantillon, donc de la dernière ligne de l'ensemble échantillonné (le bucket)
RANGE_ROWS Le nombre de lignes entre l'échantillon et l'échantillon précédent, ces deux échantillons non compris
EQ_ROWS Nombre de lignes dans l'ensemble dont la valeur est égale à celle de l'échantillon
DISTINCT_RANGE_ROWS Nombre de valeurs distinctes dans l'ensemble
AVG_RANGE_ROWS Nombre moyen de lignes de l'ensemble ayant la même valeur, donc RANGE_ROWS / DISTINCT_RANGE_ROWS
Nous voyons donc ici comment l'optimiseur a estimé le nombre de lignes retournées par la clause WHERE ProductId = 760 : il s'est positionné sur l'échantillon 779, puisque la valeur 760 est contenu dans l'ensemble échantillonné de cette ligne, et a retrouvé la valeur de AVG_RANGE_ROWS.
Dans le case de ProductId = 800, l'optimiseur a trouvé la ligne où le RANGE_HI_KEY = 800. Le nombre d'EQ_ROWS est 418.
Pourquoi l'optimiseur fat-il le choix de parcourir la table au lieu d'utiliser l'index ? Sachant qu'il aura à retourner 418 lignes, cela fait donc potentiellement un maximum de 418 pages de 8 ko en plus des pages de l'index à parcourir.
Nous savons, grâce à la requête suivante, combien de pages sont utilisées par chaque index :
SELECT o.name AS table_name,
p.index_id,
i.name AS index_name,
au.type_desc AS allocation_type,
au.data_pages, partition_number
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
JOIN sys.objects AS o ON p.object_id = o.object_id
LEFT JOIN sys.indexes AS i ON p.index_id = i.index_id
AND i.object_id = p.object_id
WHERE o.name = N'TransactionHistory'
ORDER BY o.name, p.index_id
index_name allocation_type data_pages
PK_TransactionHistory_TransactionID IN_ROW_DATA 788
IX_TransactionHistory_ProductID IN_ROW_DATA 155
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID IN_ROW_DATA 211
Nous pouvons donc assumer qu'un scan de la table (donc de l'index ordonné) coûtera la lecture de 788 pages, donc 788 reads. Cela représente bien plus que 418 pages. Pourquoi choisir un scan ?
Analysons ce qui se passe réellement lorsque nous utilisons un plan ou un autre. Nous pouvons le découvrir en forçant l'optimiseur à utiliser l'index en ajoutant un index hint dans la requête :
SET STATISTICS IO ON
GO
SELECT *
FROM Production.TransactionHistory
WITH (INDEX = IX_TransactionHistory_ProductID)
WHERE ProductId = 800
Voici les résultats de pages lues (reçues grâce à SET STATISTICS IO ON qui active le renvoi d'information d'entrées/sorties), et le plan d'exécution utilisé :
Table 'TransactionHistory'.
Scan count 1, logical reads 1979, physical reads 3, read-ahead reads 744,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Relançons ensuite la requête en laissant SQL Server choisir son plan :
SELECT *
FROM Production.TransactionHistory
WHERE ProductId = 800
Résultat :
Table 'TransactionHistory'.
Scan count 1, logical reads 792, physical reads 0, read-ahead reads 44,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Alors qu'en SQL Server 2000, l'utilisation de l'index aurait entraîné un bookmark lookup, c'est-à-dire une recherche à partir du pointeur présent dans le dernier niveau de l'index, SQL Server 2005 essaie autant que possible d'éviter le bookmark lookup. Le plan généré utilise donc une jointure entre les deux index : l'index non ordonné IX_TransactionHistory_ProductID est utilisé pour chercher les lignes correspondant au critère, et une jointure de type nested loop est utilisée pour parcourir l'index ordonné à chaque occurrence trouvée, pour extraire toutes les données de la ligne (SELECT *).
Cette jointure doit donc lire bien plus de pages que celles de l'index, puisqu'on doit aller chercher toutes les colonnes de la ligne. A chaque ligne trouvée, il faut parcourir l'index ordonné pour retrouver la page de données et en extraire la ligne. En l'occurrence cela entraîne la lecture de 1979 pages.
Nous avons vu que l'index ordonné utilise 788 pages. Le scan de cet index, selon les informations d'entrées/sorties, a coûté 792 lectures de page. D'où sortent les quatre pages supplémentaires ?
Selon la documentation de la vue sys.allocation_units dont la valeur 788 est tirée, la colonne data_pages exclut les pages internes de l'index et les les pages de gestion d'allocation (IAM). Les quatre pages supplémentaires sont donc probablement ces pages internes. Nous pouvons le vérifier rapidement grâce à une commande DBCC non documentée qui liste les pages d'un index : DBCC IND
DBCC IND (AdventureWorks, 'Production.TransactionHistory', 1)
Ou le troisième paramètre est l'id de l'index. L'index ordonnée prend toujours l'id 1.
Le résultat de cette commande nous donne 792 pages, donc 788 sont des pages de données, plus une page d'IAM (PageType = 10) et trois pages internes (PageType = 2). Voilà pour le mystère.
IV. Résumés de chaîne
Les résumés de chaîne (string summary) sont une addition aux statistiques dans SQL Server 2005. Comme vous l'avez vu dans le header retourné par DBCC SHOW_STATISTICS, la valeur de String Index indique si les statistiques pour cette clé de l'index contiennent des résumés de chaîne. Il s'agit d'un échantillonnage à l'intérieur d'une colonne de type chaîne de caractères (un varchar par exemple), qui permet à l'optimiseur de décider d'utiliser un index lors d'une recherche avec un opérateur LIKE. Ces résumés de chaîne ne couvrent que 80 caractères de la donnée (les 40 premiers et 40 derniers si la chaîne est plus longue). Vous avez une exemple de test de cette nouvelle fonctionnalité dans AdventureWorks dans cette entrée de blog : Lara's Blog
Dans SQL Server 2000, un index sur une colonne varchar n'était utilisé dans le cas d'un LIKE que lorsque le début de la chaîne était connu, ou selon un algorithme d'estimation nettement moins précis que les résumés de chaînes.
V. Gérer les statistiques
V-A. Consultation
Nous pouvons vérifier la présence de statistique de plusieurs manières :
•sp_helpstats est l'ancienne méthode d'obtention des statistiques. Ne l'utilisez plus et préférez les vues de catalogues ci-dessous :
•sys.stats : liste des statistiques présentes dans la base de données
•sys.stats_columns: colonnes présentes pour chaque statistique.
SELECT *
FROM sys.stats s
JOIN sys.stats_columns sc ON s.object_id = sc.object_id
AND s.stats_id = sc.stats_id
JOIN sys.columns c ON s.object_id = c.object_id
AND sc.column_id = c.column_id
•STATS_DATE() : Vous pouvez récupérer la date de dernière mise à jour des statistiques en utilisant la nouvelle fonction STATS_DATE() de SQL Server 2005, à laquelle vous passez un id de table et un id d'index.
SELECT OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
STATS_DATE(i.object_id, i.index_id)
FROM sys.indexes AS i
WHERE OBJECT_NAME(i.object_id) = N'TransactionHistory'
•DBCC SHOW_STATISTICS : comme nous l'avons déjà vu, DBCC SHOW_STATISTICS affiche les détails d'un jeu de statistiques.
V-B. Création
Les statistiques sur les index sont créées automatiquement, sans possiblité de désactivation: un index sans statistiques serait inutilisable.
Les statistiques sur les colonnes non indexées peuvent être créées manuellement ou automatiquement.
Par défaut, les bases de données sont créées avec l'option AUTO_CREATE_STATISTICS à ON, c'est-à-dire que les statistiques dont l'optimiseur de requêtes a besoin pour générer son plan d'exécution seront générées à la volée. Certes, il y aura un ralentissement du service de la requête dû à la création des statistiques, mais cela ne se produira que la première fois, et potentiellement ce ralentissement sera moindre que celui induit par un plan d'exécution mal évalué.
Cette fonctionnalité, de création et mise à jour automatique des statistiques par SQL Server, est appelée Auto Stats.
Pour créer manuellement des statistiques sur une ou plusieurs colonnes, vous pouvez utiliser la commande CREATE STATISTICS:
CREATE STATISTICS statistics_name
ON { table | view } ( column [ ,...n ] )
[ WITH
[ [ FULLSCAN
| SAMPLE number { PERCENT | ROWS }
[ NORECOMPUTE ]
] ;
Il peut être intéressant par exemple de créer manuellement des statistiques sur une combinaison de colonnes pour améliorer l'estimation du plan d'une requête dont la clause WHERE filtre sur ces colonnes.
WITH FULLSCAN vous permet d'indiquer que toutes les valeurs doivent être parcourues. Sur les tables volumineuses, l'échantillonnage ne se fait pas sur toutes les valeurs, les statistiques seront donc moins précises. En indiquant WITH FULLSCAN (ou WITH SAMPLE 100 PERCENT, qui est équivalent), vous forcez SQL Server à prendre en compte toutes les lignes. Pour les tables de taille moyenne, l'échantillonnage se fait de toute manière sur toutes les valeurs. SQL Server s'assure un minimum de 8 MB de données échantillonnées (1024 pages ou plus), ou la taille de la table si elle pèse moins..
A l'inverse, vous pouvez demander un échantillonnage moins précis avec l'option WITH SAMPLE. Notez que si SQL Server considère que votre sampling n'est pas suffisamment élevé pour générer des statistiques utiles, il corrige lui-même la valeur à la hausse.
Avec NORECOMPUTE, vous forcez SQL Server à ne jamais mettre à jour automatiquement ces statistiques. Ne l'utilisez que si vous savez ce que vous faites.
Vous pouvez supprimer des statistiques avec l'instruction DROP STATISTICS. Vous ne devriez normalement jamais avoir à faire cela.
Si à la suite de cet article, vous êtes tombé amoureux des statistiques, vous pouvez, grâce à la procédure sp_createstats, créer en une seule fois, des statistiques pour toutes les colonnes de toutes les tables de votre base.
Cela doit être une charmante expérience que je n'ai jamais tentée. Si vous voulez réaliser des optimisations poussées de votre système, vous pouvez vous y lancer. Cela vous permettra d'éviter le temps d'attente à la première exécution d'une requête qui va nécessiter la création de statistiques. Dans la plupart des cas, la fonctionnalité Auto Stats vous suffira amplement.
V-C. Mise à jour
La mise à jour des statistiques est importante. Pour reprendre notre exemple, supposons qu'avant de partir pour Courtrai, vous en parlez avec votre grand-père qui vous annonce qu'il connaît bien Courtrai pour y avoir passé ses vacances d'été pendant plusieurs années avec sa famille lorsqu'il était enfant. Par politesse vous évitez de commenter les choix de destination de vacances de vos arrière grand parents, d'autant plus qu'il se souvient d'avoir eu en son temps un plan détaillé de la ville. Intéressant, cela vous permettrait de vous faire une idée plus précise de vos déplacements. Après quelques recherches au grenier, il vous ressort un vieux plan très jauni, qui date de 1933. Pensez-vous réellement qu'il va vous être très utile, sachant que la ville a été très endommagée par les bombardements de 1944, et qu'une grande partie de celle-ci a été reconstruite après la guerre ?
Le contenu de votre table évolue, et vous ne pouvez baser ad vitam aeternam l'évaluation de la sélectivité d'une colonne sur les statistiques telles qu'elles ont été créées. Cette mise à jour doit se faire régulièrement et prendre en compte les larges modifications de données. Comme la création, elle peut se déclencher automatiquement ou manuellement.
Automatiquement avec l'option de base de données AUTO UPDATE STATISTICS, qu'il est vivement recommandé de laisser à ON, tel qu'il est par défaut.
SELECT DATABASEPROPERTYEX('IsAutoUpdateStatistics') -- pour consulter la valeur actuelle
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS [ON|OFF] -- pour modifier la valeur
En SQL Server 2000, la mise à jour automatique des statistiques était déclenchée par un nombre de modifications de lignes de la table. Chaque fois qu'un INSERT, DELETE ou une modification de colonne indexée était réalisée, la valeur de la colonne rowmodctr de la table sysindexes était incrémentée. Pour que la mise à jour des statistiques soit décidée, il fallait que la valeur de rowmodctr soit au moins 500. Si cela vous intéresse, vous trouverez une explication plus détaillée de ce mécanisme dans le document de la base de connaissances Microsoft 195565, listé en bibliographie.
Avec SQL Server 2005, ce seuil (threshold) est géré plus finement. La colonne rowmodctr est toujours maintenue mais on ne peut plus considérer sa valeur comme exacte. En effet, SQL Server maintient des compteurs de modifications pour chaque colonne, dans un enregistrement nommé colmodctr présente pour chaque colonne de statistique. Cependant, à ma connaissance, cette colonne n'est pas visible. Vous pouvez toujours vous baser sur la valeur de rowmodctr pour déterminer si vous pouvez mettre à jour les statistiques sur une colonne, ou si la mise à jour automatique va se déclencher bientôt, car les développeurs ont fait en sorte que sa valeur soit " raisonnablement " proche de ce qu'elle donnait dans les versions suivantes.
V-C-1. sp_autostats
Vous pouvez activer ou désactiver la mise à jour automatique des statistiques sur un index, une table ou des statistiques, en utilisant la procédure stockée sp_autostats :
sp_autostats [@tblname =] 'table_name'
[, [@flagc =] 'stats_flag']
[, [@indname =] 'index_name']
Le paramètre @flagc peut être indiqué à ON pour activer, OFF pour désactiver, NULL pour afficher l'état actuel.
V-C-2. sp_updatestats
sp_updatestats met à jour les statistiques de toutes les tables de la base courante, mais seulement celles qui ont dépassé le seuil d'obsolescence déterminé par rowmodctr (contrairement à SQL Server 2000 qui met à jour toutes les statistiques). Cela vous permet de lancer une mise à jour des statistiques de façon contrôlée, durant des périodes creuses, afin d'éviter d'éventuels problèmes de performances dûs à la mise à jour automatique, dont nous allons parler dans la section suivante.
V-C-3. Mise à jour et performances
Nous avons vu que les mises à jour de statistiques sont indispensables à la bonne performance des requêtes, et que leur maintenance est nécessaire pour assurer une bonne qualité à travers le temps. Mais, quand cette opération se déclenche-t-elle ?
Tout comme la création automatique, elle se produit au besoin, c'est-à-dire au moment où une requête va s'exécuter et pour laquelle ces statistiques sont utiles, aussi bien dans le cas d'une requête ad hoc (c'est-à-dire d'une requête écrite une fois pour un besoin particulier), que d'une procédure stockée. Si le seuil de modification de la table est atteint, il va d'abord lancer une mise à jour des statistiques nécessaires, puis ensuite générer son plan d'exécution. Cela peut évidemment provoquer une latence à l'exécution de la requête. Pour les plans d'exécution déjà dans le cache (le cache de plans d'exécution, ou plan cache, qui contient des plans réutilisables pour des requêtes répétitives, et notamment les procédures stockées), la phase d'exécution inclut une vérification des statistiques, un update éventuel de celles-ci, puis une recompilation, ce qui dans certains cas peut se révéler encore plus lourd.
Cela pose rarement un problème, mais si vous êtes dans le cas où cela affecte vos performances, vous avez quelqus solutions à votre disposition :
•Une nouvelle option de SQL Server 2005 : AUTO_UPDATE_STATISTICS_ASYNC, vous permet de réaliser une mise à jour des statistiques de façon asynchrone. Cela veut dire que lorsque le moteur SQL décide de mettre à jour ses statistiques, il n'attend pas la fin de cette opération pour exécuter la requête qui déclenche cette mise à jour. Elle s'exécute donc plus vite, mais pas forcément plus rapidement, car avec un plan d'exécution potentiellement obsolète. Les exécutions suivantes profiteront des statistiques rafraîchies. En temps normal, cette option n'est pas conseillée.
•Vous pouvez mettre à jour manuellement vos statistiques durant vos fenêtres d'administration, en utilisant par exemple sp_updatestats, ou une tâche d'un plan de maintenance.
V-C-4. Plan de maintenance
Dans la boîte du plan de maintenance, l'outil d'optimisation et de sauvegarde de vos bases, vous disposez d'une tâche pour planifier aisément la mise à jour de vos statistiques. Vous pouvez découvrir dans les deux copies d'écran ci-dessous les options à votre disposition.
VI. Etudier leur comportement
Pour étudier la vie des statistiques, pas besoin de rester des journées entières caché dans une cabane au milieu des roseaux, la caméra à la main.
Votre meilleur ami, dans ce cas comme dans tous les autres, est le profiler (générateur de profils), l'outil de la palette SQL Server qui vous permet de tracer tous les événements (ou presque) déclenchés par le serveur SQL.
Dans les exemples que nous allons prendre, nous allons utiliser le profiler en limitant la trace à nos propres opérations. Pour cela, le moyen le plus simple est de filtrer la trace par notre SPID (l'identificateur du processus/de la connexion dans SQL Server).
Vous pouvez identifier votre SPID soit par la variable @@SPID :
SELECT @@SPID
soit directement dans SSMS (SQL Server Management Studio), dans la barre d'état, comme encadré dans la copie d'écran ci-dessous.
Nous voyons ici que notre SPID est le 56. Ouvrons maintenant le profiler : Dans le menu Démarrer / Programs / Microsoft SQL Server 2005 / Performance Tools / SQL Server Profiler. Nous créons une nouvelle trace, et dans l'onglet Events Selection de la configuration de la trace, on clique sur le bouton Column Filters.... Dans la liste des colonnes, nous cherchons SPID. Nous entrons le numéro du SPID comme valeur Equal, comme indiqué dans la copie d'écran.
Ensuite, après avoir fermé cette fenêtre par OK, nous activons la case Show all events, et ajoutons les événements :
•Errors and Warnings / Missing Column Statistics
•Performance / Auto Stats
•TSQL / SQL:StmtStarting
•TSQL / SQL:StmtCompleted
•TSQL / SQL:StmtRecompile
Nous ajoutons également quelques colonnes en activant Show All Columns, et en sélectionnant ObjectId et IndexId pour l'événement Auto Stats, et EventSubClass pour le SQL:StmtRecompile.
Nous sommes prêts à lancer la trace.
Nous allons nous concentrer sur la table Person.Contact, de la base de données d'exemple de Microsoft : AdventureWorks.
Tout d'abord, vérifions quelles sont les statistiques présentes sur cette table, avec la requête suivante:
SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('Person.Contact')
Nous voyons qu'il n'y a pour l'instant que des statistiques correspondant à des index.
Exécutons une requête qui filtre les données sur une colonne non présente dans un index :
SELECT *
FROM Person.Contact
WHERE FirstName = 'Lily'
Il n'y a pas de prénom qui correspond à Lily dans les données de la table, le résultat revient donc vide.
Des statistiques ont-elles été créées ? Nous allons voir dans le profiler :
Nous voyons une ligne d'exécution de la fonctionnalité Auto Stats : des statistiques ont été créées sur notre colonne FirstName. On s'y attendait un peu... La génération des statistiques a duré 355 millisecondes.
Notons également l'objectId. Vérifions qu'il s'agit bien de notre table :
SELECT OBJECT_ID('Person.Contact')
retourne 309576141. CQFD.
Donc, un nouveau jeu de statistiques doit être présent dans sys.stats. Vérifions :
Parfait ! Nous voyons l'ObjectId, le nom des statistiques qui commence toujours par _WA_Sys pour les colonnes non indexées, et le flag auto_created à 1.
Que se passe-t-il dans la table système sysindexes ? Jetons un oeil à travers la vue qui y donne accès :
SELECT name, status, root, indid, rowcnt, rowmodctr
FROM sys.sysindexes WHERE id = OBJECT_ID('Person.Contact')
Nous retrouvons bien nos statistiques, le pointeur sur la page racine (root) de l'index, qui est ici naturellement à NULL, puisqu'il ne s'agit pas d'un index, le rowcnt à 0 pour la même raison, et le rowmodctr lui aussi à 0, puisqu'aucun changement n'a été effectué dans les données.
A ce propos, que va-t-il se passer si nous changeons massivement les valeurs de cette colonne ? Faisons le test, en conservant le prénom original dans une colonne de sauvegarde :
ALTER TABLE Person.Contact
ADD FirstNameSave Name - type défini par l'utilisateur dans la base AdventureWorks
UPDATE Person.Contact
SET FirstNameSave = FirstName
UPDATE Person.Contact
SET FirstName = 'Lily'
Revérifions sysindexes :
SELECT name, status, root, indid, rowcnt, rowmodctr
FROM sys.sysindexes WHERE id = OBJECT_ID('Person.Contact')
rowmodctr contient maintenant 19972, c'est bien le nombre de lignes de notre table : elles ont toutes été modifiées.
Relançons notre SELECT, et voyons si les statistiques vont être mises à jour...
Bingo ! Le profiler nous montre en détail ce qui se passe : Le SELECT, dont le plan d'exécution a été conservé dans le cache, est exécuté. Mais en comparant son seuil de recompilation contre rowmodctr, le moteur SQL s'aperçoit que le plan n'est plus optimal. Il prend donc la décision de le recompiler, recalcule les statistiques, et relance l'exécution. La mise à jour des statistiques a duré 121 millisecondes.
Voyons ensuite ce qui se passe lorsque nous désactivons la mise à jour automatique des statistiques:
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS OFF
UPDATE Person.Contact
SET FirstName = FirstNameSave
SELECT *
FROM Person.Contact
WHERE FirstName = 'Lily'
Cette fois-ci, comme on peut s'y attendre, plus d'Auto Stats, et plus de recompilation.
Que se passe-t-il maintenant lorsque nous désactivons la création automatique des statistiques, et que nous effectuons une requête sur une colonne qui en est dépourvue, par exemple LastName ?
ALTER DATABASE AdventureWorks SET AUTO_CREATE_STATISTICS OFF
SELECT *
FROM Person.Contact
WHERE LastName = 'Smith'
Regardons d'abord quel est le plan d'exécution estimé, en sélectionnant la requête et en pressant CTRL+L :
Nous voyons que l'optimiseur regrette l'absence de statistiques sur LastName, et nous en avertit (le point d'exclamation sur l'étape de scan). En laissant la souris sur l'étape du scan de l'index ordonné, on voit en bas le détail du warning.
De même, le problème nous est signalé par la trace du profiler :
Une dernière question nous hante : est-ce que ce warning est reflété dans les nouvelles vues de gestion dynamiques de l'optimiseur ? Rappelons qu'une nouveauté de SQL Server 2005 est la mise à disposition de différentes informations et statistiques dans des vues dites de gestion dynamique (dynamic management), qui affichent des valeurs glanées depuis le lancement du service, et qui concernent notamment les index. Trois vues sont utiles à ce titre :
•sys.dm_db_missing_index_groups
•sys.dm_db_missing_index_group_stats
•sys.dm_db_missing_index_details
qui conservent les informations d'index manquant qui auraient été utiles à l'optimiseur pour l'exécution d'une requête. Jetons un oeil :
SELECT equality_columns, inequality_columns, statement
FROM sys.dm_db_missing_index_details
WHERE database_id = DB_ID() AND
object_id = OBJECT_ID('Person.Contact')
Procédures stockées
En pratique, les programmes qui utilisent les données d’une base ne font pas directement appel aux
transactions, mais plutˆot `a des procédures auxquelles ils peuvent passer des arguments.
==== Syntaxe ====
Le langage Transact-SQL permet de programmer ces procédures selon la syntaxe suivante :
<code>
CREATE PROC ... le nom de la procédure
(...) les paramètres d’entrée et de sortie séparés par des virgules
AS
DECLARE ... les variables locales
BEGIN
... les instructions, les transactions
END
</code>
Remarques :
– on peut utiliser jusqu’`a 1024 paramètres ;
– la syntaxe d’une procédure stockée est limitée `a 128 Mo.
Exemple : une requˆete paramétrée
<code>
1 CREATE PROC InfoDuClient
2 (@numero INT) -- ne pas oublier de preciser le type
3 AS
4 SELECT *
5 FROM clients
6 WHERE clt_num = @numero
</code>
Autre exemple avec un paramètre de sortie :
<code>
1 CREATE PROC NbClients
2 (@resultat INT OUTPUT)
3 AS
4 SET @resultat = (SELECT COUNT(*) FROM clients)
5 -- il s’agit-la d’une sous-requete
</code>
Dernier exemple avec un paramètre d’entrée muni d’une valeur par défaut :
<code>
1 CREATE PROC FiltrerClients
2 (@filtre VARCHAR(255) = ’%’)
3 AS
4 SELECT *
5 FROM clients
6 WHERE clt_nom LIKE @filtre
7 -- en l’absence de parametre tous les clients seront affiches
</code>
Pour modifier une procédure stockée :
<code>
1 ALTER PROC InfoDuClient
2 (...) -- les parametres
3 AS
4 ... -- nouveau corps
</code>
Pour supprimer une procédure stockée :
1 DROP PROCEDURE InfoDuClient
==== Utilisation ====
On peut ensuite utiliser ces procédures stockées dans du code SQL avec l’instruction EXEC.
Exemple : pour avoir les informations relatives au client 12
1 EXEC InfoDuClient 12
2 -- 12 est la valeur du paramètre
Remarques :
– on peut aussi utiliser des variables comme valeurs de paramètre (et pas seulement des constantes
comme dans l’exemple) ;
– si la procédure a besoin d’une liste de paramètres, il faut les séparer par des virgules ;
– s’il y a un paramètre de sortie, il faut en stocker la valeur de retour dans une variable.
Exemple :
<code>
1 DECLARE @NombreTotalDeClients INT
2 EXEC NbClients @NombreTotalDeClients OUTPUT
3
4 -- et apres, on peut utiliser le contenu de la variable @NombreTotalDeClients
</code>
==== Cryptage ====
Lorsque de la création ou de la modification d’un déclencheur, une vue, une fonction ou une procédure
stockée (bref, tout ce qui contient le code SQL destiné aux utilisateurs), on peut préciser la clause WITH
ENCRYPTION qui permet de crypter le code de ces objets. Cela permet de protéger la propriété intellectuelle
des développeurs sous SQL Server.
Exemples :
<code>
1 CREATE VIEW VueCommandes(Client, Article)
2 WITH ENCRYPTION
3 AS
4 ...
1 ALTER PROC InfoDuClient
2 (@numero INT)
3 WITH ENCRYPTION
4 AS
5 ...
</code>
Permissions
===== Connexions =====
On a déj`a plusieurs fois mentionné la nécessité d’attribuer les bons droits aux utilisateurs de notre
base de données (cf. §5 page 28). L’objectif de cette section est d’apprendre `a gérer ces utilisateurs, leurs
droits et de protéger les développeurs.
11.1 Création
IL existe deux fa¸con d’ajouter un nouveau compte de connexion :
– on peut le créer de toute pièce
1 sp_addlogin
2 ’Paul’, -- le login
3 ’luaP’, -- le mot de passe
4 ’Northwind’ -- la base par defaut
– ou bien hériter d’une connexion Windows
1 sp_grantlogin ’STID/Henri’
2 -- STID etant le nom du domaine
3
4 sp_defaultdb ’STID/Henri’, ’Northwind’
Il reste ensuite `a lui donner accès au serveur :
1 sp_grantdbaccess ’Paul’
On dispose évidemment des procédures :
1 sp_revokedbaccess ’Paul’
2
3 sp_droplogin ’Paul’
11.2 Rˆole
Il est possible (et conseillé) de regrouper les utilisateurs selon les autorisations qu’ils ont, c’est-`a-dire
de définir des rˆoles.
11.2.1 Sur le serveur
Il existe 8 rˆoles sur serveur dans SQL Server dont :
nom du rˆole droits de ses membres
sysadmin tous les droits sur le système et toutes les base
securityadmin gestion des accès `a SQL Server
dbcreator création de bases de données
Pour ajouter et radier un utilisateur `a l’un de ces rˆoles :
1 sp_addsrvrolemember ’Paul’, ’dbcreator’
2
3 sp_dropsrvrolemember ’Paul’, ’dbcreator’
Un mˆeme utilisateur peut cumuler plusieurs rˆoles.
11.2.2 Dans une base de données
Dans chaque base on dispose des rˆoles suivants :
nom du rˆole droits de ses membres
db owner tous les droits sur les objets de la base
db accessadmin ajout d’utilisateurs et de rˆoles
db datareader lire le contenu des tables
db datawriter insertion, suppression et modification sur toutes les tables
db ddladmin création, modification, suppression d’objet
db securityadmin gestion des rˆoles et des autorisations
db public `a définir
Tous les utilisateurs appartiennent au rˆole public et peuvent appartenir `a d’autres rˆoles.
Pour ajouter un rˆole et un utilisateur `a ce rˆole :
1 sp_addrole ’ServiceInformatique’
2
3 sp_addrolemember ’ServiceInformatique’, ’Henri’
On a aussi :
1 sp_droprolemember ’ServiceMarketing’, ’Paul’
2
3 sp_droprole ’ServiceMarketing’
4 -- possible uniquement s’il ne reste plus aucun membre dans ce role
11 CONNEXIONS 50
11.3 Droits
Dans ce paragraphe, on se place dans une base.
11.3.1 Sur les instructions
Exemple : pour autoriser les utilisateurs Paul et Henri `a créer des tables et des déclencheurs
1 GRANT CREATE TABLE, CREATE TRIGGER
2 TO Paul, Henri
Remarque : Paul et Henri doivent déj`a posséder un compte utilisateur sur SQL Server.
Autre exemple : pour empˆecher Paul de créer des vues
1 DENY CREATE VIEW
2 TO Paul
Dernier exemple : pour lever les autorisations et les empˆechements de Paul
1 REVOKE CREATE VIEW, CREATE TABLE
2 TO Paul
Remarques :
– REVOKE annule le dernier GRANT ou DENY correspondant ;
– après un REVOKE, SQL Server s’en remet aux autorisations par défaut du rˆole dont Paul est membre ;
– on peut utiliser le mot-clé ALL pour désigner toutes les instructions.
11.3.2 Sur les objets
Dans une base de données, pour chaque table, chaque colonne et chaque instruction on peut préciser
les autorisations.
Exemple : pour autoriser la sélection sur la table clients
1 GRANT SELECT ON clients
2 TO Paul
Autre exemple : pour empˆecher les autres instructions
1 DENY INSERT, UPDATE, DELETE ON clients
2 TO Paul
Dernier exemple : pour autoriser la modification mais seulement du nom de client
1 GRANT UPDATE(clt_nom) ON clients
2 TO Paul
Remarques :
– en général on a pas besoin de descendre jusqu’`a la colonne, il est préférable de créer une vue et de
donner les droits sur cette vue ;
12 FORMULAIRES 51
– (important) il vaut mieux utiliser ALTER ... car les autorisations sur l’objet concerné sont conservées,
contrairement `a DROP ... suivi de CREATE ...
11.3.3 Chaˆine d’autorisation
Sont habilités `a délivrer des autorisations GRANT, DENY et REVOKE :
– les membres du rˆole sysadmin dans toutes les bases ;
– les membres du rˆole db owner sur les instructions et le objets de leur base ;
– les propriétaires 14 d’objet(s) sur leur(s) objet(s).
Pour habiliter un utilisateur `a délivrer les autorisations dont il bénéficie, il suffit d’ajouter la clause
WITH GRANT OPTION. Exemple :
1 -- avec
2 GRANT SELECT ON clients
3 TO Paul
4 WITH GRANT OPTION
5
6 -- Paul peut desormais ecrire
7 GRANT SELECT ON clients
8 TO Henri
Conseil : pour éviter tout problème de rupture de chaˆine d’autorisation avec les vues, il faut que le
dbo15 soit propriétaire des vues.
Programmation Evènementielle
La première chose `a savoir est que pour chaque table il existe en SQL trois événements (ni plus ni
moins). Ils sont soulevés respectivement par les instructions INSERT, DELETE et UPDATE (cf. §5 page 28).
L’objet de cette section est d’apprendre `a les utiliser.
===== Mise-`a-jour et suppression en cascade =====
Exemple : si on veut désormais que la suppression d’un client entraˆine automatiquement celle de ses commandes, 11 il suffit pour cela de préciser une option lors de la définition de la contrainte clé étrangère dans la table commandes.
1 ALTER TABLE commandes
2 ADD CONSTRAINT fk_cmd_clt
3 FOREIGN KEY (cmd_clt) REFERENCES clients
4 ON DELETE CASCADE
5 ON UPDATE CASCADE
Remarques :
– de cette fa¸con, la relation entre les deux tables devient non bloquante en suppression et en mise-`ajour
;
– il n’y a pas ON INSERT CASCADE.
Exercice : pourquoi n’y a-t-il pas d’insertion en cascade ?
===== Déclencheurs AFTER =====
Un déclencheur est une procédure attachée `a un événement, en anglais on dit TRIGGER. Ces procédures se déclenchent automatiquement après que l’événement concerné a été soulevé (donc bien souvent `a l’insu de l’utilisateur) et ne peuvent ˆetre appelées directement 12.
Exemple : la table articles contient une colonne qui précise le nombre d’articles en commande ; pour
mettre `a jour cette colonne lors d’insertion de nouvelles commandes on crée un déclencheur.
<code>
1 CREATE TRIGGER commandes_insert -- le nom du declencheur
2 ON commandes AFTER INSERT -- la table et l’evenement concernes
3 AS -- la programmation du declencheur
4 UPDATE articles SET nb_commande = nb_commande + cmd_qte
5 FROM articles AS a
6 JOIN inserted AS b ON (a.art_num = b.cmd_art)
7
8 -- (si plusieurs instructions : utiliser un bloc BEGIN ... END)
</code>
Quelques mots sur les tables inserted et deleted :
* il s’agit de tables temporaires créées et disponibles pendant l’événement ;
* leurs colonnes sont identiques `a celles de la table sur laquelle l’événement a été levé ;
* le déclencheur AFTER INSERT peut utiliser la table inserted qui contient toutes les lignes insérées ;
* le déclencheur AFTER DELETE peut utiliser la table deleted qui contient toutes les lignes supprimées ;
11. ce n’est pas très conseillé
12. en conséquence de quoi la seule fa¸con de les tester est de soulever l’événement par une requˆete appropriée
– le déclencheur AFTER UPDATE peut utiliser les deux tables (ce qui est logique puisqu’une mise-`a-jour
consiste en une insertion et une suppression).
Autre exemple avec cette fois-ci la table deleted :
<code>
1 CREATE TRIGGER commandes_delete
2 ON commandes AFTER DELETE
3 AS
4 UPDATE articles SET nb_commande = nb_commande - cmd_qte
5 FROM articles AS a
6 JOIN deleted AS b ON (a.art_num = b.cmd_art)
</code>
Troisième exemple, sur mise-`a-jour cette fois-ci : pour ˆetre tout `a fait complet, il faut également un
déclencheur qui réagisse si la colonne cmd qte est touchée par une mise-`a-jour.
<code>
1 CREATE TRIGGER commandes_update
2 ON commandes AFTER UPDATE
3 AS
4 IF UPDATE(cmd_qte) -- si la colonne cmd_qte est touchee par la modification
5 BEGIN
6 UPDATE articles SET nb_commande = nb_commande - b.cmd_qte + c.cmd_qte
7 FROM articles AS a
8 JOIN deleted AS b ON (a.art_num = b.cmd_art)
9 JOIN inserted AS c ON (a.art_num = c.cmd_art)
10 END
</code>
Dernier exemple : on veut empˆecher la modification du numéro ISBN d’un ouvrage.
<code>
1 CREATE TRIGGER ouvrages_update
2 ON ouvrages AFTER UPDATE
3 AS
4 IF UPDATE(isbn)
5 BEGIN
6 RAISERROR (’Le numero ISBN ne peut pas etre modifie’, 0, 1)
7 -- 0 indique la gravite de l’erreur et 1 l’etat (a oublier)
8 ROLLBACK TRANSACTION
9 -- on annulle la transaction qui a declenche l’evenement
10 END
</code>
Remarques :
* les déclencheurs sont des transactions ;
* il faut que l’utilisateur qui tente d’insérer un emprunt, dispose des droits sur toutes les tables impliquées dans la programmation du déclencheur ;
* comme on vient de le voir, les déclencheurs sont notamment utiles pour :
* implémenter des règles trop complexes pour les contraintes (ne serait que parce qu’une contrainte ne peut porter que sur une table) ;
* afficher un message d’erreur personnalisé et annuler la transaction appelante.
* comme leur nom l’indique, un déclencheur AFTER se produisent après un événement ;
* du coup, les contraintes sont vérifiées avant le lancement des déclencheurs AFTER, ce qui a pour une conséquence fˆacheuse : les mises-`a-jour en cascade éventuellement soulevées par ces déclencheurs ne se font qu’après vérification des contraintes ;
* avec SQL Server il n’y a pas de déclencheurs BEFORE ;
* par contre les déclencheurs INSTEAD OF (au lieu de) existent ; c’est l’objet du paragraphe suivant.
Exercice : en quoi le cinquième point est-il fˆacheux ?
===== Déclencheurs INSTEAD OF =====
On les utilise si on veut que leurs instructions se lancent `a la place de l’insertion, de la suppression
ou de la mise-`a-jour qui a soulevé l’événement. Avec un déclencheur AFTER la modification des données a
lieu puis le déclencheur est exécuté, tandis qu’avec un déclencheur INSTEAD OF le corps du déclencheur
se substitue `a la modification des données.
D’un point de vue syntaxique, il suffit de remplacer AFTER par INSTEAD OF. Exemple : on historise
automatiquement les commandes insérées dans une table historique commmandes.
<code>
1 CREATE TRIGGER commandes_insert2
2 ON commandes INSTEAD OF INSERT
3 AS
4 BEGIN
5 INSERT commandes SELECT * FROM inserted
6 -- cette ligne fais l’insertion prevue
7 INSERT historique_commmandes SELECT * FROM inserted
8 END
9
10 -- on aurait donc pu se contenter d’un declencher AFTER
11 -- avec seulement le 2e INSERT
</code>
Remarques :
– les tables provisoires inserted et deleted existent et sont remplies pour les déclencheurs INSTEAD
OF (heureusement) ;
– les déclencheurs INSTEAD OF ne se déclenchent pas eux-mˆemes (heureusement) ;
– il ne peut y avoir qu’un déclencheur INSTEAD OF par événement et par table (alors qu’il peut y
avoir plusieurs déclencheurs AFTER) ;
– s’il existe une clé étrangère avec une action en cascade (DELETE ou UPDATE) dans la table, alors on
ne peut pas écrire le déclencheur INSTEAD OF correspondant, et inversement.
Exercice : pourquoi ces trois dernières règles existent-elles ?
===== Compléments =====
Toutes les instructions SQL ne sont pas autorisées dans le code d’un déclencheur ; on se limitera
généralement `a : INSERT, DELETE, UPDATE, RAISERROR et ROLLBACK TRANSACTION.
Pour modifier un déclencheur on a :
1 ALTER TRIGGER commandes_insert
2 ... -- son nouveau code
Pour supprimer un déclencheur on a :
1 DROP TRIGGER commandes_insert
7 PROGRAMMATION éVéNEMENTIELLE 40
Pour suspendre provisoirement un déclencheur (sans le supprimer) on a :
1 ALTER TABLE commandes
2 DISABLE TRIGGER commandes_insert
3
4 ... -- d’autres instruction puis
5
6 ALTER TABLE commandes ENABLE TRIGGER commandes_insert
Remarque : on peut remplacer commandes insert par ALL ou commandes insert, commandes insert2.
On peut créer un déclencheur pour deux ou trois événements `a la fois. Exemple :
1 CREATE TRIGGER ...
2 ON ... AFTER INSERT, UPDATE
3 AS
4 ...
===== Conclusion =====
Faisons une synthèse sur le déroulement d’une transaction. Pour chaque instruction de la transaction on a :
vérification des autorisations de l’utilisateur (*)
puis
transfert des données nécessaires du disque dans la mémoire
puis
remplissage des tables inserted et/ou deleted
puis
modifications (prévues ou INSTEAD OF et/ou en cascade) des données dans la mémoire (*)
puis
vérification des contraintes (*)
puis
déclencheurs AFTER (*)
(*) signifie qu’`a ce stade la transaction peut-ˆetre annulée.
L’écriture des données sur le disque n’intervient qu’`a la fin de la transaction lorsque toutes ses instructions ont été validées.
Commentaires
Enregistrer un commentaire