Administration des Bases de Données | IC3 1

INTRODUCTION

SYSTEME DE GESTION DE BASE DE DONNEES (SGBD)

DESCRIPTION GENERALE
De manière générale, un SGBD est un logiciel qui définit le modèle d’un
système de base de données et constitue ainsi une composante indispensable
à la création, à la gestion et à l’utilisation d’une base de données.
Des interfaces spécifiques à l’application et un langage de base de données
adapté permettent les accès en écriture et en lecture des données stockées
ainsi que les fonctionnalités d’administration générales.
Enjeux des Bases de Données
Au cœur de tout système important existe en arrière-plan un stockage
de données. Ce stockage de données est ce qu’on appelle une base de
données. Considérant qu’une base de données constitue le noyau des
systèmes, il est nécessaire d’en assurer l’intégrité, la disponibles, mais aussi la
sécurité. Les systèmes de base de données, lorsqu’ils sont bien conçus, vont
garantir que le système va atteindre ces objectifs. Les personnes ayant des
compétences pour développer et gérer ces bases de données sont donc
indispensables dans les systèmes modernes.
Ces personnes sont appelées DBA (DataBase Administator) et sont
responsable de maintenir les performances des bases de données et d’en
assurer la sécurité.

LE MODE DE FONCTIONNEMENT CLIENT-SERVEUR

Actuellement, la plupart des SGBD fonctionnent selon un mode
client/serveur. Le serveur (sous-entendu l’ordinateur qui stocke les données)
reçoit des requêtes de plusieurs clients et ceci de manière concurrente. Le
serveur analyse la requête, la traite et retourne le résultat au client. Le modèle
client/serveur est assez souvent implémenté au moyen de l'interface des
sockets (voir le cours de réseau : 3306 MySql / 1433 Sql Server).
L’architecture client/serveur permet un déploiement optimum des
applications clientes sur des nombreux postes tout en conservant une gestion
centralisée des données au niveau du serveur.
Il est possible d’avoir plusieurs applications clientes sur le même serveur de base
de données, toutes fois il faut veiller à ce que la charge de travail sur le serveur
ne soit pas trop importante au regard des capacités de la machine.
Cette architecture client/serveur est respectée par tous les outils permettant
d’accéder à des informations contenues dans le serveur SQL, dont les outils
d’administration, même s’ils sont installés sur le serveur.
 Administration des Bases de Données | IC3 2

Toutes les demandes en provenance des clients vers le serveur sont

écrites en Transact-SQL. Ce langage de requête des bases de données propre
à SQL Server fournit un ensemble de commandes pour gérer les objets et
manipuler les données dans les bases.
Toutes les applications qui utilisent SQL Server pour gérer les données
s’appuient sur une architecture client/serveur. L’application cliente est
chargée de la mise en place de l’interface utilisateur.
Cette application s’exécute généralement sur plusieurs postes clients
simultanément. Le serveur quant à lui est chargé de la gestion des données, et
repartie les ressources du serveur entre les différentes demandes (requêtes) des
clients.

Figure.1. Fonctionnement du mode Client /Serveur

Mode de Fonctionnement :
- Le modèle client-serveur de base, L’application côté client
communique directement avec la base de données côté serveur. Des
API comme ODBC, JDBC sont utilisées pour cette interaction. Le côté
serveur est chargé de fournir les fonctionnalités de traitement des
requêtes et de gestion des transactions. Côté client, les interfaces
utilisateurs et les programmes d’application sont exécutés. L’application
côté client établit une connexion avec le côté serveur afin de
communiquer avec le SGBD.

Figure.2. Architecture Client /Serveur

 Administration des Bases de Données | IC3 3

- L’architecture à trois niveaux, dans ce type, il existe une autre couche

entre le client et le serveur. Le client ne communique pas directement
avec le serveur. Au lieu de cela, il interagit avec un serveur
d’applications qui communique en outre avec le système de base de
données, puis le traitement des requêtes et la gestion des transactions
ont lieu. Cette couche intermédiaire sert de support pour l’échange de
données partiellement traitées entre le serveur et le client. Ce type
d’architecture est utilisé dans le cas de grandes applications Web.

C’est dans ce model qu’intervient la notion de Middle Ware qui est en

fait la couche applicative intermédiaire (des serveurs d’applications tel
que : Appache, NGinx, …).

PRESENTATION DE SQL SERVER

Microsoft SQL Server est un Système de gestion de base de données relationnel

et transactionnel développé et commercialisé par Microsoft.

Microsoft SQL Server utilise le langage T-SQL (Transact-SQL) pour ses requêtes,
c'est une implémentation de SQL qui prend en charge les procédures stockées
et les déclencheurs. La dernière version est SQL Server 2019. La première ayant
apparu en 1994 sous le label de SQL Server 6.0.

Architecture de SQL Server

SSIS SSAS SSRS

MSSQLSERVER INSTANCE X

Moteur Sql Servic Moteur Sql Servic

SQL SEVER CLR e SQL SEVER CLR e
SERVER AGENT Broker SERVER AGENT Broker

BD Utilisateurs BD Systèmes BD Utilisateurs BD Systèmes

Figure.3. Architecture de SQL Serveur

 Administration des Bases de Données | IC3 4

Les Editions de SQL Server

SQL Server est disponible sous la forme de plusieurs éditions, chaque édition se
distinguant par des caractéristiques spécifiques. Il y en a trois principalement
qui permettent de répondre à la majorité des besoins en entreprise, et toutes
ces éditions sont disponibles en 32 et 64 bits jusqu’à la version 2014, À partir de
SQL Server 2016, SQL Server est disponible uniquement en tant qu’application
64 bits.
(32 et 64 bits font référence au mot machine qui indique l’unité de base
manipulé par un processeur en un cycle, ou encore le nombre de bits
échangés entre un CPU et la RAM, d’où un processeur 32 bits traite jusqu’à 232
bits à la fois c.-à-d. près de 4Go, tandis qu’un processeurs 64 va jusqu’à 264 bits,
ce qui n’est pas le double mais beaucoup plus)
Edition Entreprise : Cette Edition est la version complète, intégrant plusieurs
options tel la réplication avec d’autres SGBD, la haute disponibilité, la
restauration à chaud, la prise en charge de la compression des données et des
sauvegardes, etc.
Edition Standard : Cette version est un peu limitée, entre autre dans les aspects
haute disponibilité, mais aussi en ce qui concerne la gestion de très gros
volumes de données parce que ne pouvant pas aussi exploiter plus de 64 Go
de mémoire vive.
Edition Business Intelligence : L’Edition Business Intelligence est dédiée à
l’informatique décisionnelle, elle complète l’Edition standard dans les aspects
d’analyse visuel des données ou de modèle sémantique des données, etc.

A noter :

- Il existe d’autres éditions tels que :

o Express : Qui n’est pas une version dégradée, mais elle est limitée en terme de
volume de données (10 Go) et de mémoire exploitable (1 Go). Mais on peut
l’utiliser en production puis migrer vers une édition supérieure.
o Developer : Généralement utilisée pour les tests des équipes de développeurs,
la mise en production de cette version n’est pas légale.
o Compact : C’est une Edition destinée à être installé sur des terminaux mobiles,
synchronisable avec les données de l’entreprise par une réplication dans le
cadre de développement d’applications autonomes.
o Web : Cette version offre juste un moteur de base de données pour des sites web
à faible coûts.

Déroulement de l’Installation de SQL Server

Le processus d’installation se déroule en trois principaux étapes:

- L’analyse de l’environnement et l’installation des composantes

nécessaires à la bonne exécution du processus d’installation de SQL
Server.
- Le paramétrage des différents composants à installer
- L’Installation des composants sélectionnés au préalable
 Administration des Bases de Données | IC3 5

1. Lancement du Centre d’Installation SQL Server

2. Analyse de l’environnement

3. Installation des Composantes nécessaires à l’Installation de SQL Server

 Administration des Bases de Données | IC3 6

4. Sélection des fonctionnalités à installer

5. Configuration de l’Instance
 Administration des Bases de Données | IC3 7

6. Configuration des Services

7. Configuration du Moteur de base des données

 Administration des Bases de Données | IC3 8

8. Résumé des configurations définies

9. Installation des composantes

 Administration des Bases de Données | IC3 9

A noter :

- L’installation de Framework 3.5 est nécessaire à l’installation depuis SQL Server 2012.
- Lors du choix des composantes à installer seules deux composantes sont essentiels à la
base :
o Le Service moteur de base des données
o L’outil de gestion de base des données
- SQL Server Offre la possibilité d’installer plusieurs instances de moteurs de base des
données qui fonctionnent de manière autonome, et c’est leurs noms qui les distinguent.
- La première Instance est souvent l’instance par défaut (MSSQLSERVER), mais il est
possible d’installer des instances nommées.

Composants et Services SQL Server

En fonction des choix effectués lors de l’installation de SQL Server, on peut avoir
des nombreux services installés. Tous les logiciels coté serveur fonctionnent sous
forme des services, et comme tout service, ils devront utiliser le contexte d’un
compte utilisateur pour pouvoir accéder aux ressources de l’ordinateur. (Par
défaut, ils s’exécutent dans le contexte du compte local système)

Les deux services principaux que sont MS SQL Server et SQL Server Agent sont
directement liés à l’Instance, tandis que d’autres services servent pour d’autres
fonctionnalité de SQL Server tel que :
- SQL Server Browser : Pour écouter les demandes entrantes, et il affiche
aussi les serveurs disponible (en ligne)
- SQL Server Integration Service : qui gère les importations et exportation
des données
- SQL Server Reporting Service : qui permet de concevoir des rapports ou
des modèles de reports.
- …
A noter :

- L'Agent SQL Server est un service Microsoft Windows qui exécute des tâches
administratives planifiées, appelées « travaux » dans SQL Server.
- La gestion du réseau entre le client et le serveur passe principalement par TCP/IP, d’où
la gestion de ce protocole est incluse par défaut lors de l’installation du serveur ou des
utilitaires clients. (Le socket TCP/IP utilise le port par défaut :1433)

Mode d’Authentification
MS SQL Server étant un produit de Microsoft, La gestion des comptes peut
totalement s’appuyer sur les comptes utilisateurs Windows, mais il est
préférable de gérer les comptes des utilisateurs intégralement au sein de SQL
Server. Et pour cela il est possible de faire ce choix à l’installation à l’étape de
la configuration du moteur de base de données.
 Administration des Bases de Données | IC3 10

Si cela n’a pas été fait, il est possible de le faire plus tard, en allant sur le nœud
du serveur, en faisant un clic droit puis aller sur propriété ;

Puis sécurité et cocher le mode voulu ;

 Administration des Bases de Données | IC3 11

Et enfin, redémarrer le serveur, en faisant un clic droit sur le nœud du serveur.

A noter :

- L’Authentification fait référence à l’accès dans le serveur de base des données

- Les Autorisations feront elles référencent aux habilitations octroyés dans la base.

Les Bases de données Systèmes

Pour gérer l’ensemble des données, SQL Server s’utilise lui-même, c.-à-d. qu’il y
a des bases de données systèmes qui contiennent les informations des
utilisateurs, et des bases utilisateurs. Ces bases contiennent en quelque sorte
ce qu’on appelle des métadonnées.

a. La base Master : C’est la Base de données principale de SQL Server,

l’ensemble des données stratégique pour le bon fonctionnement du
serveur y est stocké (Comptes de connexion, option de configuration
des bases utilisateurs, existence des bases utilisateurs et référence vers
les fichiers composant ces bases, etc.)

b. La base Model : Cette base contient l’ensemble des éléments inscrit

dans toutes les bases utilisateurs nouvellement créée. On y configure les
paramètres par défaut que l’on veut imposer à toutes les bases qui
seront créées sur l’Instance.

c. La base TempDB : est un espace temporaire de travail servant à gérer

des tables temporaires, des tables intermédiaires pour des tris par
exemple, etc.

d. La base MSdb : Elle Contient les informations utilisées par le service SQL
Server Agent, pour déclencher des alertes ou exécuter des tâches
planifiées.
 Administration des Bases de Données | IC3 12

Les Tables et les Procédures stockées systèmes

Dans SQL Server il existe des tables systèmes qui contiennent des informations
sur la structure des objets de la base de données, par exemple la liste des
tables et de leurs colonnes.
Il est formellement déconseillé de manipuler directement ces tables en utilisant
par exemple des requêtes de type SELECT.
C’est ainsi que les procédures stockées systèmes sont utilisées afin d’interroger
ces tables systèmes et de consulter l’état du serveur, des bases de données et
d’autres informations liées à la structure des objets des bases de données.
Quelques procédures stockées systèmes :

Procédure Stockée Syntaxe Rôle ou Utilisation

sp_help sp_help [ [ @objname = ] Fournit des informations sur un
'name' ] objet de base de données
sp_helpdb sp_helpdb [ [ @dbname= ] Affiche des informations sur une base de
'name' ] données précise ou sur toutes les bases
de données.
sp_detach_db sp_detach_db [ @dbname= ] Détache d'une instance de serveur une
'database_name' base de données qui n'est pas en cours
d'utilisation
sp_attach_db sp_attach_db [ @dbname= ] Attache une base de données à un
'dbname' serveur.
, [ @filename1= ]
'filename_n' [ ,...16 ]
sp_addlogin sp_addlogin [ @loginame = Crée une nouvelle connexion SQL Server
] 'login' qui permet à un utilisateur de se
[ , [ @passwd = ]
'password' ]
connecter à une instance de SQL Server
[ , [ @defdb = ] à l'aide de l'authentification SQL Server
'database' ]
[ , [ @deflanguage =
] 'language' ]
[ , [ @sid = ] sid ]
[ , [ @encryptopt = ]
'encryption_option' ]
[;]
sp_addrole sp_addrole [ @rolename = Crée un rôle de base de données dans la
] 'role' [ , [ @ownername base de données active.
= ] 'owner' ]
sp_adduser sp_adduser [ @loginame = Ajoute un nouvel utilisateur dans la base
] 'login' de données active.
[ , [ @name_in_db = ]
'user' ]
[ , [ @grpname = ]
'rle'
sp_dropuser sp_dropuser [ @name_in_db Supprime un utilisateur de base de
= ] 'user' données de la base de données active.
sp_addrolemember sp_addrolemember [ Ajoute un utilisateur à un rôle de base
@rolename = ] 'role', [ de données dans la base de données
@membername = ]
'security_account' active
 Administration des Bases de Données | IC3 13

sp_droprolemember sp_droprolemember [ Supprime un compte de sécurité d'un

@rolename = ] 'role' , rôle SQL Server dans la base de données
[ @membername = ]
'security_account' active.
sp_defaultdb sp_defaultdb [ @loginame Modifie la base de données par défaut
= ] 'login', [ @defdb = ] pour une Microsoft SQL Server
'database'
connexion
sp_password sp_password [ [ @old = ] Ajoute ou modifie un mot de passe pour
'old_password' , ] une Microsoft SQL Server connexion
{ [ @new =]
'new_password' }
[ , [ @loginame = ]
'login' ]

Le stockage des Bases des données

Lorsque l’installation est exécutée avec les paramètres par défauts, les fichiers
des données se trouvent dans l’arborescence suivant :
C:\Program Files\Microsoft SQL Server\[Link]\MSSQL\DATA

Vous y trouverez deux types de fichiers pour chaque base de données : L’un
.mdf et l’autre .ldf.

Les données sont stockées dans un fichier MDF (qui est le fichier primaire),
toutes les transactions, les modifications de la base de données SQL Server
effectuées par chaque transaction sont stockées dans un fichier LDF (qui est le
fichier secondaire).
 Administration des Bases de Données | IC3 14

CHAPITRE I. INDEXATION ET OPTIMISATION

DESCRIPTION ET DEFINITION
Un index est une structure de données qui permet d'accélérer les recherches
dans une table en associant à une clé d'index (la liste des attributs indexés)
l'emplacement physique de l'enregistrement sur le disque.

Quand une table est volumineuse, un parcours séquentiel (Table Scan) de

toute la table est une opération relativement lente et pénalisante pour
l’exécution des requêtes ; La création d’un index permet d’améliorer
considérablement les temps de réponse en créant des chemins d’accès aux
enregistrements beaucoup plus directs.

L’index peut exister indépendamment de l’organisation du fichier de données,

ce qui permet d’en créer plusieurs si on veut être en mesure d’optimiser
plusieurs types de requêtes. En contrepartie la création sans discernement d’un
nombre important d’index peut être pénalisante pour le SGBD qui doit gérer,
pour chaque opération de mise à jour sur une table, la répercussion de cette
mise à jour sur tous les index de la table. Un choix judicieux des index, ni trop ni
trop peu, est donc un des facteurs conditionnant la performance d’un
système.

En prenant n’importe quel livre, il contient un index. Cet index présente une
liste des termes considérées comme importants, classés selon un certain ordre,
et associées aux numéros des pages où on trouve un développement
consacré à ce terme. On peut donc, avec l’index, accéder directement à la
page (ou aux pages en général) contenant un terme donné.

Les index dans un SGBD suivent exactement les mêmes principes. On choisit
dans une table un (au moins) ou plusieurs attributs, dont les valeurs constituent
la clé d’indexation. Ces valeurs sont l’équivalent des termes indexant le livre.
On associe à chaque valeur la liste d’adresse(s) vers le (ou les enregistrements)
correspondant à cette valeur: c’est l’équivalent des numéros de page. Et
finalement, on trie alors cette liste selon l’ordre alphanumérique pour obtenir
l’index.
Les accès effectués sur un index peuvent donc se faire sur des structures
optimisées pour la recherche (liste triée, B-tree, ...) au lieu de se faire par
parcours séquentiel et intégral des enregistrements.

Voici la signification des termes propres à l’indexation :

 Une clé (d’indexation) est une liste triée (l’ordre est important) d’attributs
d’une table. En toute rigueur, il faudrait toujours distinguer la clé (les noms
d’attributs) de la valeur de la clé (celles que l’on trouve dans un
enregistrement).
 Administration des Bases de Données | IC3 15

 Une adresse est un emplacement physique dans la base de données, qui

peut être soit celle d’un bloc, soit un peu plus précisément celle d’un
enregistrement dans un bloc.
 Une entrée (d’index) est un enregistrement constitué d’une paire de
valeurs. La première est la valeur de la clé, la seconde une adresse.
 Un index est un fichier structuré dont les enregistrements sont des entrées.

Quand on considère le cas d’un fichier trié sur la clé primaire, Il n’y a qu’un seul
enregistrement pour une valeur de clé, et le fichier a globalement la même
structure d’un dictionnaire. Dans ce cas particulier il est possible d’effectuer
une recherche par dichotomie qui s’appuie sur une division récursive du fichier,
avec des performances théoriques très satisfaisantes.

ILLUSTRATION
TABLE EMPLOYES
NE NOM SALAIRE
0 ALICE 5000
5 BOB 2000
1 NADIA 10000 SELECT * FROM EMP WHERE NE=4
2 GRACE 8000
4 JOHN 3000
3 SARA 7000

Sans index l’exécuteur de requête devra passer en revu chaque

enregistrement, mais en créant un index sur le numéro des employés (NE), ils
seront d’abord triés en ordre croissant ; ensuite, un algorithme sera utilisé pour
trouver l’employé dont le numéro est le 4.
Le curseur va diviser le tableau des index en deux, dans notre cas il se place
sur la valeur 2. Après cela, il compare la valeur pointée avec la valeur
recherchée pour trouver sur quelle moitié de tableau pointer à nouveau ; dans
notre cas, 2 étant inférieur à 4 il pointera le milieu de la deuxième moitié du
tableau, il pointe sur 4. En comparant, 4 est égale à 4 donc il a trouvé la valeur
et affiche la ligne.
NE NE
0 0
1 1 4 JOHN 3000
2 2
3 3 AFFICHAGE DE LA LIGNE
4 4
5 5

L’index est lui-même un fichier, contenant des entrées (voir définition ci-
dessus) [valeur, adresse] où valeur désigne une valeur de la clé de recherche,
et adresse l’adresse d’un bloc.

Toutes les valeurs de clé existant dans le fichier de données ne sont pas
représentées dans l’index: on dit que l’index est non-dense. On tire parti du fait
que le fichier est trié sur la clé pour ne faire figurer dans l’index que les valeurs
de clé du premier enregistrement de chaque bloc.
Fichier d’index non dense sur le nom
[Alice, ] [Grace, ] [Nadia, ]
 Administration des Bases de Données | IC3 16

Figure.4. Structure d’Index Non Dense

Quand on veut indexer un fichier qui n’est pas trié sur la clé de recherche, On
ne peut plus tirer parti de l’ordre des enregistrements pour introduire seulement
dans l’index la valeur de clé du premier élément de chaque bloc. Il faut donc
baser l’index sur toutes les valeurs de clé existant dans le fichier, et les associer
à l’adresse d’un enregistrement, et pas à l’adresse d’un bloc. Un tel index
est dense.
Fichier d’index non dense sur le nom
[Alice, ] [Grace, ] [Nadia, ]

Bloc 1 Bloc 2 Bloc 3

Alice, … Grace, … Nadia, …

Bob, … John, … Sara, …
Fichier de données trié sur le nom
Figure.5. Structure d’Index Dense

Syntaxe
La création d’un Index en SQL Server se fait par la syntaxe suivante :
CREATE INDEX nom_index
ON nom_table (Champ1 [ASC | DESC], Champ2 [ASC | DESC], …)

Ou par la création d’une contrainte (Clé Primaire ou Unique)

ALTER TABLE nom_table

ADD CONSTRAINT nom_contrainte [PRIMARY KEY | UNIQUE]
[CLUSTERED | NONCLUSTERED] (Champ1 [ASC | DESC], Champ2 [ASC | DESC], …)
INDEX CLUSTER ET NON-CLUSTER

Par défaut, Tous les SGBD entretiennent un index primaire qui est l’index crée
sur la clé primaire. Cependant l’administrateur de base des données peut
décider de créer d’autres index sur des colonnes qui ne sont pas des clés
primaires dans le but d’améliorer les performances des requêtes de recherche
(c.-à-d. lors d’un SELECT).
 Administration des Bases de Données | IC3 17

MS SQL Serveur manipule deux types d’index, les index CLUSTERED et les NON
CLUSTERED.

A. INDEX CLUSTER (ORGANISES)

Les index organisés, tel que leur nom l’indique, sont ceux qui organisent
(classent) physiquement les données dans la table. Cela signifie qu’aucune
donnée supplémentaire n’est nécessaire pour l’index, et l’accès aux données
à l’aide d’un index cluster est le plus rapide.

Et pour chaque table, il ne peut y avoir qu’un seul index cluster, car les données
ne peuvent être organisés sur la table que selon un seul ordre de tri.

B. INDEX NON-CLUSTER (NON ORGANISES)

Les index non-organisés ou « Non-Cluster » sont ceux qui stockent les données
à un endroit et les index à un endroit différent et les index auraient des
pointeurs vers l’emplacement de stockage des données. Une table peut avoir
plusieurs index non cluster car l’index de l’index non cluster est stocké à un
emplacement différent.

Cela signifie que l’accès aux données via un index non cluster doit passer par
une couche supplémentaire. Cependant, si vous sélectionnez uniquement les
données disponibles dans les colonnes indexées, vous pouvez récupérer les
données directement à partir des données d’index dupliquées (on parle
d’index couvrant).
ILLUSTRATION
En ayant une table Employes(Id_E,NomE,Salaire,Genre,#Dept), une sélection sur la
table donne le résultat de gauche, mais en supprimant l’index Clé Primaire [DROP
INDEX nom_index], et en créant un autre Index Cluster sur les champs Genre et Salaire
c’est le résultat de droite qui s’affiche lors d’une sélection.

A noter :
- Un Index Non-Cluster peut avoir un ordre de tri différent de l’ordre physique des données
sur la table.
- Etant donné que l’index Cluster organise les données physiquement sur la table, il est bien
souvent associé à la clé primaire.
 Administration des Bases de Données | IC3 18

- Syntaxe de l’index couvrant :

CREATE INDEX nom_index
ON nom_table (Champ1 [ASC | DESC], Champ2 [ASC | DESC], …)
INCLUDE (Champ1 [ASC | DESC], Champ2 [ASC | DESC], …)

En dehors du script, il est également possible de définir l’index directement à

partir de SSMS (SQL Server Management Studio)

1 - Dérouler le nœud de la table Concernée,

2 - Puis faire un clic droit sur « Index » et aller sur « Nouvel Index » et choisir
le type d’index.
 Administration des Bases de Données | IC3 19

3 - Voici la boite de dialogue « Nouvel Index », puis aller sur Ajouter, pour
sélectionner le ou les champs concerné(s) par la définition d’index.

4 - Puis, définir le tri dans le champs ordre de tri (croissant/décroissant)

A noter :

- Pour afficher tous les index définis sur une table on utilise une procédure stockés systèmes :
EXEC sys.sp_helpindex nom_table
- Créez des index sur des champs avec des valeurs uniques, ceux fréquemment utilisés pour
trier des données extraites d’une table
 Administration des Bases de Données | IC3 20

OPTIMISATION DES BASES DE DONNEES

Les SGBD ont un optimiseur de requête qui analyse, optimise et exécute les
requêtes.
Etape de l’optimisation :
1. Traduction de la requête SQL en algèbre relationnel
2. Tracer l’arbre algébrique
3. Déduire les plans d’exécutions pour l’arbre algébrique
4. Calculer les coûts des plans
5. Sélectionner un des plans

Figure.6. Optimisation et exécution d’une requête

Exemple :
SELECT NomE, Departement

FROM Employes, Departements

WHERE [Link] = [Link] AND Salaire > 1000

NomE, Departement

[Link] > 1000

[Link] = [Link]

Employes Departements

Figure.7. Optimisation et exécution d’une requête

 Administration des Bases de Données | IC3 21

NomE, Departement

NomE, Departement

[Link] = [Link]

[Link] = [Link]

NomE, Id_Dept Departement, Id_Dept

Departements
[Link] > 1000
Departements
[Link] > 1000

Employes

NomE, Id_Dept, salaire

Figure.8. Plan optimisé (1) & Plan optimisé (2) Employes

Le plan d’exécution n’est rien d’autre qu’un arbre d’opérateurs

communiquant entre eux.
Et l’optimisateur de requête choisi pour chaque requête la meilleure façon de
l’exécuter ; il s’agit de de sélectionner le plan qui a le moins d’accès disque
possible, c.-à-d. qui parcours le moins de tuple possible avant d’afficher les
résultats.
A noter :

- L’existence ou non d’index dans une table donnée détermine le choix du plan d’exécution
optimal, du fait que cela réduit le parcours des tuples.
- Pour activer OU désactiver l’affichage des mesures de performances des requêtes dans
SSMS, on exécute la script suivant : SET STATISTICS IO, TIME ON | OFF
- Dans SSMS, l’affichage du Plan d’exécution des requêtes se fait avec la commande
CTRL+M

Quelques règles d’optimisation des requêtes :

Lorsque vous écrivez vos requêtes, même si les SGBD ont des optimiseurs, voici
quelques règles à respecter pour optimiser vos requêtes. :

1. Éviter le SELECT * : écrire plutôt le nom des colonnes dont vous avez besoin
pour la requête.
2. Créez des indexes sur les colonnes que vous utilisez dans la clause WHERE.
3. Lorsque c’est possible, utilisez le WHERE à la place du HAVING.
4. Éviter les jointures dans le WHERE, utilisez plutôt le INNER JOIN.
5. Lorsque c’est possible, utilisez une jointure à la place d’une sous-requête.
 Administration des Bases de Données | IC3 22

CHAPITRE II. TRANSACTION ET CONCURRENCE

TRANSACTIONS
DEFINITION
Les transactions sont une instruction ou un ensemble d’instruction qui fait
passer la base de données d’un état cohérent à un autre état cohérent.

t1 t2
Temps

Etat Etat
1 2

Les transactions sont une fonctionnalité absolument indispensable, permettant

de sécuriser une application utilisant une base de données. Sans transactions
certaines opérations risqueraient d’être à moitié réalisées, et la moindre erreur,
la moindre interruption pourrait avoir des conséquences énormes. En effet, les
transactions permettent de regrouper des requêtes dans des blocs, et de faire
en sorte que tout le bloc soit exécuté en une seule fois, cela afin de préserver
l’intégrité des données de la base.
Exemple de transaction : Un exemple connu et représentatif de la notion de
transaction est celui du retrait d’argent au près d’un distributeur automatique
de billet. La transaction est alors constituée de deux opérations : le débit du
compte et la distribution d’argent ; s’il n’est pas possible de réaliser l’une des
deux opérations, c’est l’ensemble des opérations qui doit être annulé. Le
Compte ne peut être débité si la somme correspondante n’est pas distribuée
au client de la banque.

LES PROPRIETES DES TRANSACTIONS

Il y a quatre (4) propriétés importantes qui sont définies pour les transactions,
ces propriétés sont :
- L’ATOMICITE : Car la transaction constitue une unité indivisible de travail pour
le serveur.
- LA COHERENCE : Car à la fin d’une transaction, les données dans la base sont
soit celles d’avant transaction (dans le cas d’une annulation de la transaction)
soit celle d’après transaction (dans le cas d’une validation).
- L’ISOLATION : Car il est possible de verrouiller (isoler) les données pendant
l’exécution de la transaction (verrouillage en lecture, en écriture, …).
- LA DURABILITE : Car les changements apportés sur des données par une
transaction sont durables (non volatiles).
 Administration des Bases de Données | IC3 23

L’acronyme ACID (Atomicité Cohérence Isolation Durabilité Ou Atomic

Consistency Isolation Durability En Anglais) fait référence aux propriétés des
transactions, on parle alors de « L’ACIDITE » d’une transaction.
A noter :
- Dans les SGBD, toute instruction est considérée comme un transaction, mais cela est implicite ;
par contre les transactions explicites sont contenues dans des blocs délimités par les
commandes : BEGIN TRANSACTION et COMMIT TRAN | ROLLBACK TRAN
- On utilise soit un Commit soit un Rollback en fin de Transaction

Il y a trois cas en fin de transaction :

o Une Validation volontaire de toute la transaction : COMMIT ;

o Une Annulation Volontaire de toute la transaction : ROLLBACK ;
o Une Erreur générée par une requête (violation de contrainte) OU
une interruption dû à une panne : un Rollback est
automatiquement exécuté.

ROLES DES TRANSACTIONS

Les transactions sont utilisées dans deux cas :
 Pour contrôler temporairement l’effet des requêtes sans vouloir modifier
la base des données (très utilisés dans les tests unitaires, les tests de
fonctionnements ou pour faire des simulations)

 Pour protéger l’intégrité de la base de données (utilisés par exemple lors

de la mise à jour des données qui dépendent d’autres données, ou pour
gérer des accès concurrents)
L’ISOLATION
La notion d’isolation fait référence au fait qu’une transaction doit être
isolée, c.-à-d. qu’elle doit s’exécuté de manière à éviter toute interférence
avec d’autres transactions.
Il existe pour ce faire, deux mécanismes pour isoler les transactions :
 Les Verrous : Ces sont des blocages d’accès (en lecture, ou en écriture)
à une partie de la base, limitant les possibilités des transactions
concurrentes.
 La Sérialisation : La sérialisation consiste en ce que les transactions
concurrentes s’exécutent séquentiellement (des verrous sont placés sur
toutes les ressources concernées par la transaction et ne sont libérés
qu’en fin de transaction)

Tous ces mécanismes exigent une bonne réflexion afin de ne pas bloquer
les accès n’importe comment, faire la part des choses entre la gestion des
possibles conflits entre transactions et la libération en temps et en heure des
ressources pour un maximum de disponibilité des données.
 Administration des Bases de Données | IC3 24

CONCURRENCE
DESCRIPTION
Une base de données n’est pas d’essence interrogée ni modifiée par un
seul utilisateur ; de ce fait lorsque des opérations soit de lecture soit d’écritures
sont effectuées sur les données, les utilisateurs y accèdent concurremment. Les
accès concurrents peuvent alors donner lieu à des incohérences dans la base
de données lorsque les opérations effectuées sont conflictuelles et s’ils ne sont
pas bien gérés.
Ces problèmes sont liés à des défauts d’isolation, car comme vu
précédemment, les transactions doivent être isolées en vue de leurs permettre
de s’exécuter correctement sans entamer l’intégrité de la base de données.
Exemple :
Sur une table Employes(NE, NOM, SALAIRE)
On a deux utilisateurs Samuel et Israël qui vont effectuer des transactions :

1er CAS : Ils lisent tous les deux des tuples distincts

SELECT [Link] FROM EMP E NE NOM SALAIRE

WHERE [Link]=NADIA 10000$ 0 ALICE 5000
1 BOB 2000
2 NADIA 10000
Samuel 3 GRACE 8000
… … …

BASE DE
DONNEES
SELECT [Link] FROM EMP E
Israël WHERE [Link]=GRACE 8000$

2ème CAS : Ils lisent tous les deux les mêmes tuples

NE NOM SALAIRE
SELECT [Link] FROM EMP E
0 ALICE 5000
WHERE [Link]=BOB 2000$ 1 BOB 2000
2 NADIA 10000
Samuel 3 GRACE 8000
… … …

BASE DE
DONNEES
SELECT [Link] FROM EMP E
Israël WHERE [Link]=BOB 2000$
 Administration des Bases de Données | IC3 25

3ème CAS : Israël modifie des tuples que Samuel ne lit pas
NE NOM SALAIRE
0 ALICE 5000
SELECT [Link] FROM EMP E
1 BOB 2000
10000 2 NADIA 10000
WHERE [Link]=NADIA
3 GRACE 10000
Samuel … … …

BASE DE
DONNEES
UPDATE EMP SET
Israël SAL=10000 1 tuple Modifié
WHERE [Link]=GRACE

Dans tous ces cas, aucun problème ne se pose peu importe l’ordre dans lequel
les transactions ont été effectuées. Que ce soit Samuel ou Israël qui effectue
ses transactions en premier, Les transactions de l’autre n’en souffrent
aucunement.
Par contre, des problèmes vont commencer à se poser lorsque l’un lira et
l’autre écrira sur les mêmes tuples, ou encore lorsque les deux écriront sur les
même tuples.
4ème CAS : Samuel modifie des tuples qu’Israël lit

NE NOM SALAIRE
0 ALICE 5000
1 BOB 2000
2 NADIA 10000
UPDATE EMP SET
3 GRACE 10000
SAL=3000 1 tuple Modifié
… … …
WHERE [Link]=BOB
Samuel

BASE DE
DONNEES

NE NOM SALAIRE
SELECT [Link] FROM EMP E 0 ALICE 5000
Israël
WHERE [Link]=BOB ? 1 BOB 3000
2 NADIA 10000
Le résultat qu’Israël obtiendra dépendra 3 GRACE 10000
de l’ordre dans lequel les transactions … … …
s’effectueront.
 Administration des Bases de Données | IC3 26

Si Samuel effectue la mise à jour et qu’ensuite Israël lit le salaire de BOB, il aura
pour résultat de sa requête 3000$, par contre s’il lit et qu’ensuite Samuel
effectue la mise à jour, alors sa requête aura pour résultat 2000$.

LES ANOMALIES

a. La Lecture Non Reproductible

NE NOM SALAIRE
SELECT [Link] FROM EMP E T1 0 ALICE 5000
WHERE [Link]=BOB 2000$ 1 BOB 3000
2 NADIA 10000 Samuel
3 GRACE 10000

TEMPS
… … …
Israël UPDATE EMP SET
SELECT [Link] FROM EMP E SAL=3000 T2
T3 WHERE [Link]=BOB 3000$ WHERE [Link]=BOB

Cette anomalie fait qu’Israël lit « directement » en un laps de temps la même

valeur, mais il obtient deux résultats différents. Le tuple qui est lu est celui qui est
concerné par la modification effectuée par Samuel.
b. La Lecture Fantôme

NE NOM SALAIRE
0 ALICE 5000
1 BOB 2000
SELECT AVG([Link])
FROM EMP E T1 2 NADIA 10000
3 GRACE 10000
TEMPS

6750$ Samuel
Israël INSERT INTO EMP T2
VALUES(4, “CHRIS”, 25000)
SELECT AVG([Link]) NE NOM SALAIRE
T3 10400$
FROM EMP E 0 ALICE 5000
… … …
4 CHRIS 25000

Israël exécute la même requête deux fois, mais obtient deux résultats différents.
Sauf qu’ici il ne « lit pas directement » le tuple concerné par la modification,
mais le résultat d’agrégat de sa requête incorpore ce tuple.

Ici, les enregistrements lus par Israël n’ont pas été modifié, mais la requête
d’agrégat intègre la nouvelle donnée insérée par Samuel
 Administration des Bases de Données | IC3 27

5ème CAS : Ils modifient tous les deux les mêmes tuples
c. La Perte de mise à jour

UPDATE EMP SET NE NOM SALAIRE

SAL=3000 0 ALICE 5000
WHERE [Link]=BOB 1 BOB 3000
1 tuple Modifié 2 NADIA 10000

TEMPS
Samuel 3 GRACE 10000
NE NOM SALAIRE
UPDATE EMP SET
0 ALICE 5000
SAL=2500
1 BOB 2050
WHERE [Link]=BOB
2 NADIA 10000
1 tuple Modifié 3 GRACE 10000
Israël

Ici on est dans la logique du « dernier qui met à jour gagne » ; les mises à jour
précédentes sont écrasées, il y a donc perte de mise à jour.

d. La Lecture « Sale »
Il s’agit ici de la lecture des données écrites par une transaction non validée.
Exemple : Deux transactions TR1 et TR2 qui modifient simultanément le champ
Quantité dans une table, mais transaction TR1 est annulée alors que TR2 est
validée.

UPDATE EMP SET SAL=SAL+1000 NE NOM SALAIRE

… … …
T1 (TR1) WHERE NE=1
1 BOB 2000
1 tuple Modifié
Israël UPDATE EMP SET
TEMPS

NE NOM SALAIRE SAL=SAL+500 (TR2) Samuel

WHERE NE=1
0 ALICE 5000 T2 1 tuple Modifié
1 BOB 3500
2 NADIA 10000 COMMIT TR2 T3
3 GRACE 10000
T4 ROLLBACK TR1

Voilà dans un tableau ce que ça donne :

Temps Etat de la Base Transaction TR1 Transaction TR2

T0 SAL=2000

T1 SAL SAL+1000

T2 SAL=3000 Ecrire SAL SAL SAL+500

T3 SAL=3500 Valider TR2 (COMMIT)

T4 SAL=3500 Annuler TR1 (ROLLBACK)

 Administration des Bases de Données | IC3 28

LES VERROUS

DEFINITION

Un verrou, permet de placer une interdiction temporaire d’accès à une

partie de la base de données, dans le cas d’accès simultanés qui pourraient
aboutir à des incohérences ; il permet donc de limiter les risquer liées aux accès
simultanés.
C’est donc le mécanisme utilisé pour contrôler l’accès à une même donnée
par des multiples utilisateurs.

Les verrous implicites :

Les SGBD gèrent des verrous qui sont évidents lors de l’exécution de
certaines requêtes.
Exemple :
Lorsqu’on exécute un ALTER TABLE, il est évident qu’aucune autre opération ne
doit être exécutée sur la table dont la structure est en cours de modification.

Les verrous explicites :

Les verrous implicites ne suffisent pas dans le cas de certains accès
concurrents, d’où la nécessité de placer manuellement des verrous en vue de
protéger l’intégrité des données.
En posant un verrou, il est nécessaire de trouver le verrou le plus adapté pour
protéger l’intégrité des données en limitant les problèmes de performance. Il
est ici question de faire des choix stratégiques en respectant deux aspects
fondamentaux :
 La disponibilité de l’information : Il ne faudra pas bloquer tout le monde
parce qu’une seule personne travaille

Exemple : On ne va pas bloquer toutes les connexions sur un site de

réservation de chambre d’hôtel parce qu’un client fait une réservation.

 La Cohérence de l’information : il ne faudra pas rendre les données

incohérentes en tenant de plusieurs demandes en concurrence en
même temps.

Exemple : On ne va pas donner la même chambre à deux clients

distincts.
 Administration des Bases de Données | IC3 29

A. LES NIVEAUX D’ISOLATION (DE VERROU)

a) Lecture dégradée (READ UNCOMMITED) 4

C’est le niveau d’isolation le plus bas. Il est possible ici de lire une valeur
dès lors qu’elle est mise à jour, même si elle est modifiée par une transaction
non validée. On parle aussi de lecture des données intermédiaires. Il s’agit d’un
niveau qui autorise la lecture sale, donc la lecture des données non validées.

SELECT SAL FROM SELECT SUM(SAL) UPDATE EMP SELECT SELECT SAL ROLLBACK SELECT SAL FROM
EMP WHERE NE=0 FROM EMP SET SUM(SAL) FROM EMP EMP WHERE NE=0
2050 5750 SAL=2100 FROM EMP WHERE NE=0 2050
WHERE 5800 2100
NE=0

b) Lecture Propre (READ COMMITED) 3

Ce niveau permet de ne lire que des données qui ont été validées, donc pas de
lecture sale ; mais il autorise la lecture non reproductible et l’apparition des fantômes.
SELECT SAL SELECT UPDATE EMP COMMIT SELECT SELECT SAL FROM
FROM EMP SUM(SAL) FROM SET SUM(SAL) FROM EMP WHERE NE=0
WHERE NE=0 EMP SAL=2100 EMP 2100
2050 5750 WHERE NE=0 5800

c) Lecture Reproductible (REPEATABLE READ) 2

Ce niveau n’admet pas de lecture sale et interdit toute lecture non reproductible.
Par contre les fantômes continuent à apparaitre. Ici toute instruction d’une transaction ne
peut voir que les lignes validées avant le début de la transaction. S’il y a des transactions
en parallèle qui sont validées, ces données-là ne sont pas vues.

SELECT SAL SELECT INSERT INTO SELECT UPDATE EMP COMMIT UPDATE EMP
FROM EMP SUM(SAL) FROM EMP VALUES(4, SUM(SAL) FROM SET SET
WHERE NE=0 EMP PATRICK,7000) EMP SAL=2100 SAL=2100
2050 5750 5750 WHERE NE=0 WHERE NE=0
En Attente Exécuté

d) Sérialisable (SERIALIZABLE) 1

C’est le niveau le plus élevé. Ici aucune transaction ne peut s’exécuter en même
temps qu’une autre en cours. Donc toute transaction qui veut s’exécuter est mise en
attente si elle en trouve une autre en cours. Les données auxquelles on accède sont
verrouillées jusqu’à la fin de la transaction. La sérialisation est très importante pour les
opérations conflictuelles (C.à.d., des opérations que l’on ne peut pas permuter sans
changer le résultat). On fait alors un Ordonnancement Des Transactions.

A noter :
- Le Mode verrou par défaut de : MySql -->2 ; Sql Server -->3

- SET TRANSACTION ISOLATION LEVEL [READ COMITED|READ

UNCOMITED|REPEATABLE READ|SERIALIZABLE] est l’ordre SQL qui permet de
gérer les accès concurrents aux données.
 Administration des Bases de Données | IC3 30

CHAPITRE III. PROGRAMMABILITE DES BASES DE DONNEES

INTRODUCTION

La programmabilité des bases de données est l’un des aspects les plus
exploité et qui s’avère être très utile aux administrateurs à plusieurs points de
vues. C’est la possibilité d’intégrer des programmes écrit en SQL directement
dans la base de données.
La programmabilité des bases de données présente un intérêt du fait qu’elle
permet de :
 Limiter le flux entre serveurs de traitement et serveur de données ;
 Réutiliser les programmes écrits directement dans la base de données
pour plusieurs applications ;
 Protéger l’intégrité de la base de données (en particulier avec les
triggers)

Ces Programmes sont exécutés dans la base de données comme des requêtes
classiques, et ils sont créés dans la base de données de la même façon que
l’on pourrait créer des nouvelles tables.

III.2. LES TRIGGERS (DECLANCHEURS)

III.2.1. INTRODUCTION

Un trigger également appelé déclencheur, permet d’exécuter un

ensemble d’instruction SQL juste après un évènement. Cela permet de faciliter
et d’automatiser des actions au sein d’un Système de Gestion des Bases de
Données.
Les Triggers les plus couramment utilisés sont ceux qui sont attachés aux tables,
et qui s’exécutent automatiquement lors d’un évènement spécifique sur une
table spécifique à laquelle il est attaché.
Quand on parle d’évènement, il s’agit précisément d’une insertion (insert),
d’une suppression (delete) ou d’une modification (update) ; et selon que cela
est spécifié à la création (description) du Trigger, l’un de ces évènements sur
une table déclenche l’exécution d’une suite d’instructions SQL définie.

Syntaxe
La création d’un déclencheur en SQL peut être effectué via la syntaxe
suivante :

CREATE TRIGGER nom_du_trigger

ON nom_de_table
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
corps_du_trigger
 Administration des Bases de Données | IC3 31

A noter :
- Le déclencheur s’exécute automatiquement lorsque l’évènement indiqué (insertion,
modification, suppression) est lancé sur la table à laquelle il est attaché.

- A la création d’un déclencheur, selon les cas, des tables temporaires sont créées. Pour une
insertion une table inserted contenant les données à insérer, pour une suppression une table
deleted, contenant les données à supprimer, pour une modification deux tables : inserted
pour les nouvelles données à insérer et deleted pour les données que l’on veut remplacer.

Exemples de Déclencheur (ou Trigger 1)

-
CREATE TRIGGER Apres_Insert_Client ----------------SUR INSERTION DE CLIENT --------
ON - CLIENT
AFTER
- insert
AS
-
DECLARE
-
@nomC nvarchar(50),
-
@PrenC nvarchar(50),
@sexe
- varchar(1),
@dateN
- date,
@Adresse
- nvarchar(50),
@Tel nvarchar(15)
-
-
BEGIN
- select @nomC = Nomc from inserted
- select @PrenC = Prenomc from inserted
- select @sexe = sexe from inserted
select @dateN = dateN from inserted
-
select @Adresse = Adr from inserted
- select @Tel = Tel from inserted
-
- IF (SELECT OBJECT_ID('Histo_clients')) IS NULL
- BEGIN
- create table Histo_clients(
nom_cli nvarchar(25),
- Pren_cli nvarchar(20),
- sexe_cli nvarchar(20),
- DateN_cli nvarchar(20),
- Adresse_cli nvarchar(20),
- Tel_cli nvarchar(15)
);
-
- INSERT INTO Histo_clients
- VALUES (
- @nomC,
@PrenC, qu’à chaque insertion de ligne, la même ligne est insérée
- Ce Déclencheur s’assure
@sexe,
dans une table historique qui est créée à la première insertion après sa création.
@dateN,
- Insertion d’une ligne pour tester le trigger:
@Adresse,
@Tel
)
END
- ELSE
INSERT INTO Histo_clients
VALUES (
@nomC,
@PrenC,
@sexe,
@dateN,
@Adresse,
@Tel
)
END
 Administration des Bases de Données | IC3 32

Ce Déclencheur s’assure qu’à chaque insertion de ligne dans la table CLIENT,

la même ligne est insérée dans une table historique HISTO_CLIENT qui est créée
à la première insertion sur la table.
Insertion d’une ligne pour tester le trigger et illustrer:

Exemples de Déclencheur (ou Trigger 2)

CREATE TRIGGER inserer_vente -----------------SUR ENREGISTREMENT DES VENTES -------

ON vente
instead of INSERT
AS
DECLARE @DEPT INT
--DECLARE @CLI INT

SET @DEPT=(SELECT Dept from employe WHERE ID_E=(SELECT emp from inserted)) --------

IF @DEPT = 1 ----------------------------------------------------------------------

BEGIN
INSERT INTO VENTE(REF_P,ID_C,MONTANT,DATEV,EMP)
SELECT REF_P,ID_C,MONTANT,DATEV,EMP FROM inserted
END

ELSE
PRINT 'CET AGENT N EST PAS DU COMMERCIAL'
GO

Ce Déclencheur s’assure qu’avant l’insertion d’une ligne dans la table VENTE,

l’employé référencé comme vendeur est du département commercial. Sinon,
la vente ne sera pas enregistrée.
Insertion de quelque ligne pour tester le trigger et illustrer:

Rôle des triggers :

• Garantir l’intégrité référentielle (DELETE, ou UPDATE CASCADE)

• Tenir un journal des logs.
 Administration des Bases de Données | IC3 33

III.3. LES PROCEDURES (FONCTIONS) STOCKEES

INTRODUCTION
Une Procédure Stockée, aussi appelée Stored Procedure en anglais, est
un concept utilisé en administration de base de données afin d’exécuter un
ensemble d’instructions SQL. Une telle procédure est stockée au sein du
Système de Gestion de Base de Données (SGBD) et peut être appelée à tout
moment par son nom afin de l’exécuter.
Pour les développeurs, la manière la plus simple de comprendre une
procédure stockée consiste à considérer que c’est l’équivalent de ce que l’on
nomme une “fonction” au sein d’autres langages informatiques.

A noter : les procédures stockées sont des éléments qui seront enregistré au sein de la base de
données afin de pouvoir être appelé ultérieurement, en opposition aux requêtes SQL qui sont
des instructions exécutées une fois puis qui sont supprimées à la fin de la session.

Les intérêts sont multiples:

o Rapidité d’exécution, puisque les procédures stockées sont déjà

compilées.
o Réutilisation de la procédure stockée
o Possibilité d’exécuter un ensemble de requêtes SQL
o Clarté du code : dans un code C#, VB .Net, PHP ou autre, il vaut mieux
utiliser l’appel d’une procédure que l’instruction SQL, en particulier
lorsque l’instruction SQL est longue et complexe.
o Modularité. Facilite le travail d’équipe
o Sécurité : des applications peuvent avoir accès uniquement aux
procédures stockées, sans avoir accès directement aux données des
tables directement, et/ou s’assurer que l’accès aux données soit toujours
effectué de la même manière.

A noter : On parle de Fonction Stockée lorsque la procédure enregistrée renvoie une valeur.

Syntaxe
La création ou la modification d’une Procédure Stockée en SQL peut être
effectué via la syntaxe suivante :

[CREATE | ALTER] PROCEDURE nom_procedure

[@Parametre1 type1,
…]
AS
BEGIN
corps_procedure
END
GO

La procédure stockée est supprimée par l’instruction suivante :

DROP nom_procedure
 Administration des Bases de Données | IC3 34

La procédure stockée s’exécute par l’instruction suivante :

EXEC nom_procedure [Val_param1, Val_param2, Val_param3,…]

Ou

EXEC nom_procedure
[@Parametre1=’Val_1’,
@Parametre2=’Val_2’,
@Parametre3=’Val_3’,
…]

A noter : Même s’il est conseillé de passer les paramètres dans l’ordre de leur apparition dans
la procédure, MS SQL Server peut accepter la passation des paramètres dans n’importe quel
ordre. Par contre, les noms des paramètres sont très importants.

Exemple de Procédure Stockée

Cette Procédure permet juste l’affichage de la liste de tous les employés; En y

ajoutant un paramètre genre, nous pourrons afficher les employés d’un genre
donné :

En exécutant la procédure, une erreur s’affiche réclamant de passer un

paramètre
 Administration des Bases de Données | IC3 35

Après exécution de la procédure en passant le paramètre, le résultat s’affiche.

Syntaxe
La création ou la modification d’une Fonction Stockée en SQL peut être
effectué via la syntaxe suivante :

[CREATE | ALTER] FUNCTION

nom_fonction [CREATE | ALTER] FUNCTION
[@Parametre1 type1, nom_fonction
…] RETURNS data_type [@Parametre1 type1,
…] RETURNS TABLE
AS
BEGIN AS
corps_fonction RETURN requête_select
RETURN expression_scalaire GO
END
GO
A noter :
- L’exécution d’une fonction se fait en précisant le schema dbo

SELECT bdo.nom_fonction(paramètre)

- Lorsqu’une fonction doit retourner une table son execution se fait comme suit :

DECLARE parametre type ;

SET parametre = val;
SELECT * FROM bdo.nom_fonction(parameter)
 Administration des Bases de Données | IC3 36

Exemple de Fonctions Stockées

 Administration des Bases de Données | IC3 37

CHAP. IV. SECURITE D’ACCES ET REPRISE SUR PANNE

IV.1. SECURITE D’ACCES
Au-delà du simple fait de disponibiliser les données et de veiller à leur
intégrité, l’administrateur de base de données a une autre tâche qui n’est pas
la moindre, qui est celle de sécuriser la base de données contre toutes sortes
d’intrusion mais aussi d’éviter toute perte de données dû à diverses sortes de
pannes.
Le premier aspect est essentiellement lié aux utilisateurs de la base de données,
c.-à-d. comment garantir une utilisation correcte des données.

Et les principaux mécanismes de défense de ce point de vue sont :

 L’Authentification
 L’Autorisation
 Le Chiffrement des communications entre le terminal de l’utilisateur et le
Serveur
Ceci se résume par le Contrôle D’accès. Le contrôle d’accès défini quel
« utilisateur » est « autorisé à faire quoi » (Lectures, Ecritures, …) « sur quelle
données » (Base de données, tables, tuples, …) dans « quelles conditions ».

Permission
Objet
(Table, tuple, vue, …)
Sujet
Avoir Interdiction Réaliser Action Sur
(Utilisateur)

Ensemble d’Objets

Obligation

Exemple : Un Système de Paie dans une Entreprise

Sujets = Employés de l’Entreprise AUTRES SERVICES

DRH (Alice, Bob) DAF (Nadia, Grace)
(Ewing, Xraël)

RH Salaires
Objets = Informations sur les Dossier Admin.
employés Paie Heures Sup.

Accéder au
Actions : Calcul de la Paye Salaires

Accéder au HS

Changement Modifier le
d’Adresse Dossier RH
 Administration des Bases de Données | IC3 38

Les règles peuvent ici être définies comme suit :

 Indépendamment du contenu de l’objet auquel on accède (Par
Exemple : Les agents de la DRH ont au minimum un droit de lecture sur
tous les Dossiers RH de tous les employés)

 En fonction du Contenu (Chaque employé a le droit de lecture sur les

informations de leurs propres dossiers)

 Et enfin La Délégation, qui donne à certains utilisateurs de droit

d’octroyer d’autres droits à d’autres utilisateurs.

Illustration : Contrôle d’accès via des vues

DAF
DRH
NE NOM SALAIRE
NE NOM TEL ADRESSE VILLE 0 ALICE 5000
0 ALICE 0810000001 LIMETE KINSHASA 1 BOB 2000
1 BOB 0810000002 GOMBE KINSHASA 2 NADIA 10000
2 NADIA 0810000003 NGALIEMA KINSHASA 3 GRACE
Vue DAF
8000
3 GRACE 0810000004 NGALIEMA KINSHASA

Admin Table EMPLOYES

NE NOM TEL ADRESSE VILLE SALAIRE
0 ALICE 0810000001 LIMETE KINSHASA 5000
1 BOB 0810000002 GOMBE KINSHASA 2000
2 NADIA 0810000003 NGALIEMA KINSHASA 10000
3 GRACE 0810000004 NGALIEMA KINSHASA 8000

IV.1.1. GESTION DE SECURITE D’ACCES DANS SQL Server

Le contrôle d’accès représente une opération importante au niveau de la
gestion de la sécurité du serveur de base des données. La sécurisation des
données nécessite une organisation des objets de façon indépendante des
utilisateurs, ce qui est rendu possible dans SQL Server par les schémas.

La politique de sécurité est rendue efficace avec une hiérarchisation des

éléments de sécurité ; nous avons :

- Les entités de sécurité : ce sont les comptes des utilisateurs

- Les sécurisables : les objets (tables, base de données, schémas, …)
- Les autorisations : autorisations accordées aux entités sur les sécurisables.
 Administration des Bases de Données | IC3 39

A. MODE DE SECURITE WINDOWS

Ce modèle de gestion de sécurité permet de s’appuyer sur les utilisateurs et les

groupes Windows dans un domaine ou en local. SQL Server utilise la gestion
des utilisateurs de Windows et récupère uniquement les noms pour créer des
connexions au serveur.

Authentification Windows

En mode d’authentification Windows seuls les noms d’utilisateurs sont stockés ;

la gestion des mots de passe est laissé à Windows, donc SQL Server gère
essentiellement les données et Windows se charge de la gestion des utilisateurs.

Avec SSMS : Sécurité Nouvelle connexion Recherche du Nom d’accès

 Administration des Bases de Données | IC3 40

Avec du Code Transact :

L’instruction CREATE LOGIN permet d’annoncer à SQL Server que nous allons
créer une connexion. Il est nécessaire de donner le nom de la connexion
Windows associée. La clause FROM WINDOWS, permet de dire que le login
existe dans le système d’exploitation Windows. Les deux options suivantes
permettent quant à elles de choisir, et la base par défaut de la connexion, et
la langue par défaut de cette même connexion.

B. MODE DE SECURITE MIXTE

Avec ce mode d’authentification, tous les utilisateurs sont entièrement gérés

par SQL Server (nom et mot de passe). Ce type de gestion de connexion est
bien adapté pour les clients qui ne s’identifient pas auprès de Windows.

Authentification SQL Server

En mode d’authentification SQL Server, les noms d’utilisateurs ainsi que les mots
de passe associés sont stockés et gérés par SQL Server. Donc la Gestion des
utilisateurs est entièrement faite au niveau de SQL Server.

Avec du Code Transact :

 Administration des Bases de Données | IC3 41

L’instruction CREATE LOGIN annonce à SQL Server qu’une nouvelle connexion

va être créée. On donne impérativement un nom à cette connexion. La clause
FROM permet de donner plusieurs propriétés à cette connexion, qui sont les
suivantes :

 PASSWORD : Permet de préciser un mot de passe. L’option HASHED,

permet de hacher le mot de passe lors de son stockage en fonction de
la chaine de caractères précisée avant de mot clé.
 DEFAULT_DATABASE : Permet de préciser le nom de la base de données
par default.
 DEFAULT_LANGUAGE : Permet de préciser un langage par défaut.
 CHECK_EXPIRATION : A OFF par défaut. Il n’est possible d’activer cette
option que si CHECK_POLICY est aussi activé. Elle permet d’appliquer la
politique de changement des mots de passe défini sur le serveur.
 CHECK_POLICY : A ON par défaut, cette option permet de récupérer au
niveau serveur, les options définies pour la politique de sécurité.
 CREDENTIAL : Permet de relier la connexion à un credential créé
auparavant. Nous verrons par la suite ce qu’est un credential.

Avec SSMS : Sécurité Nouvelle connexion Saisie du Nom d’accès

A noter : Dans un premier temps, lorsqu’un utilisateur du réseau tente de se connecter au

Serveur SQL, un test est fait en utilisant la sécurité Windows, si l’utilisateur est approuvé pour
accéder à SQL Server il sera connecté, dans le cas contraire un message d’échec de connexion
s’affichera.
 Administration des Bases de Données | IC3 42

Les Rôles SQL Server prédéfinis

SQL Server a défini d’avance certain rôles, qui lorsqu’ils sont attribués aux
utilisateurs lui donne un certain niveau d’accès soit au serveur, soit à la base
de donnée.

Les Rôles niveau serveur sont attribués aux connexion SQL Server et Les rôles
niveau base de données sont attribués à un utilisateur mappé sur la connexion.

Rôles serveur :

 Sysadmin : Administrateur du serveur.

 Serveradmin : Permet de configurer les paramètres niveau serveur.
 Setupadmin : Permet d’exécuter certaines procédures stockées et
d’ajouter des serveurs liés.
 Securityadmin : Permet de gérer les connexions serveur.
 Processadmin : Permet de gérer les traitements au sein de SQL Server.
 Dbcreator : Permet de créer ou modifier des bases de données.
 Diskadmin : Permet de gérer les fichiers sur le disque.
 Bulkadmin : Permet d’exécuter l’instruction BULK INSERT.

Rôles base de données :

 Db_owner : Equivalent à propriétaire base de données.

 Db_accessadmin : Permet d’ajouter et supprimer des utilisateurs de base
de données.
 Db_datareader : Permet d’utiliser l’instruction SELECT.
 Db_datawriter : Permet les instructions INSERT, UPDATE et DELETE.
 Db_ddladmin : Permet les opérations sur les objets de base de données.
 Db_securityadmin : Permet de gérer les éléments de sécurité sur la base
de données.
 Db_backupoperator : Permet l’utilisation des backups.
 Db_denydatareader : Interdit l’instruction SELECT.
 Db_denydatawriter : Interdit l’écriture sur la base de données.
 Administration des Bases de Données | IC3 43

Syntaxe
La création de connexion et d’utilisateur mappé à la connexion:

Ce script permet de créer une connexion « CATHY » avec comme mot de

passe ‘12345’ ; à cette connexion on a associer le nom d’utilisateur « CAT »

Ensuite, on peut octroyer des droits à l’utilisateur CAT pour accéder aux
données de la base.

A noter :
- Le mot clé GRANT permet d’octroyer les droits aux utilisateurs

GRANT [ALL| PRIVILEGES] ON SECURISABLE TO UTILISATEUR

- Le mot clé REVOKE permet de retirer les droits aux utilisateurs

REVOKE [ALL| PRIVILEGES] ON SECURISABLE TO UTILISATEUR

- Au lieu d’octroyer les droits aux utilisateurs directement, cela peut se faire via des rôles

CREATE ROLE Nom_Role

ALTER ROLE Nom_Role ADD MEMBER Nom_Utilisateur

GRANT [ALL| PRIVILEGES] ON SECURISABLE TO Nom_Role

 Administration des Bases de Données | IC3 44

C. GESTION DES SCHEMAS

Un schéma est un ensemble logique d’objets à l’intérieur des bases de

données sur le serveur. Le but est de faciliter, entre autre, l’échange de
données entre les utilisateurs, sans pour autant affecter la sécurité.

Ces schémas ne sont pas à confondre avec le schéma de base des données
qui est en fait un diagramme ; ce sont en fait des conteneurs qui permettent
de regrouper des objets de la base des données pour une gestion plus aisée
des privilèges d’utilisation des objets.

A la création de chaque objet de la base de données ils sont mappé à un

schéma, si aucun nom de schéma n’est précisé, alors l’utilisateur sera mappé
sur le schéma dbo qui est le schéma par défaut.

Fonctionnement

Par exemple, si un utilisateur est mappé sur un schéma nommé RU. Pour
requêter sur les objets de la base, il pourra écrire directement uniquement les
noms des objets compris dans le schéma sur lequel il est mappé. Dans le cas
contraire, l’utilisateur devra préciser le schéma de l’objet, le nom de l’objet et
à ce moment-là, SQL Server cherchera si le dit utilisateur possède les droits
d’utiliser l’objet auquel il tente d’accéder.

Avec du Code Transact :

Avec SSMS : Sécurité Nouveau Schéma…

ALTER SCHEMA nomschema TRANSFERT nomobjet (Pour mapper un objet à

un schéma, avec le « nomobjet » au format : [Link] )
 Administration des Bases de Données | IC3 45

IV.2. SAUVEGARDE ET RESTAURATION

SAUVEGARDE

Une sauvegarde est une image d’une base de données à un instant « t » et la

restauration de cette base avec cette image ramène la base de données à
l’état ou elle était à cet instant « t ».
A noter :

Chaque Base de données a un journal des transactions qui enregistre toutes les transactions et les
modifications y apportées ; des nombreux types d’opérations sont enregistrées dans le journal des
transactions ; ceux-ci comprennent essentiellement :
- Le début et la fin de chaque transaction
- Chaque modification des données (insertion, mise à jour et suppression)
- Création ou suppression des tables ou des index

Dans SQL Server il existe trois types de sauvegarde : Complete, Différentielle et De

Journal de Transaction

La sauvegarde totale d’une base de données permet de fournir un point de

départ pour les restaurations. Si uniquement des sauvegardes complètes sont
effectuées en cas de problème, les transactions validées depuis la dernière
sauvegarde complète seront perdues.
Les sauvegardes complètes nécessitent un temps relativement long et occupe
sur le support un espace conséquent. Ce type de sauvegarde constitue un
point de départ pour toute stratégie de sauvegarde.

En complément aux sauvegardes complètes, il est possible d’effectuer des

sauvegardes des journaux de transactions, qui permettent de récupérer la
totalité ou une partie des transactions validées depuis la dernière sauvegarde
complète.
 Administration des Bases de Données | IC3 46

Les sauvegardes différentielles pour leurs parts ne prennent en compte que les
données modifiées depuis la dernières sauvegarde complète. Ces
sauvegardes sont plus rapides et moins volumineuses que les sauvegardes
complètes.

A noter :

- S’il faut monter une stratégie de sauvegarde, l’association des sauvegardes différentielles
aux journaux des transactions est la solution idéale.

Avec SSMS : Clic droit sur bd Tâches Sauvegarder

- Sélectionner la Base de
Donnée
- Sélectionner le type de
sauvegarde voulu
- Cliquer sur Ajouter pour
indiquer le nom de l’unité de
sauvegarde et l’emplacement
de la sauvegarde.
 Administration des Bases de Données | IC3 47

PLANIFICATION DE LA SAUVEGARDE

- Saisie nom du travail

- Clic sur « Etape »

- Dans la Nouvelle fenêtre
« Nouvelle étape du travail » :
Saisir un nom d’étape
- Définir le type de travail
(Script Transact-SQL)
- Sélectionner la base de
données
- Saisir le script de sauvegarde
 Administration des Bases de Données | IC3 48

- Clic sur « Planification »

- Dans la Nouvelle fenêtre
« Nouvelle planification du
travail » : Saisir un nom de
planification
- Sélectionner le type de
planification (Périodique)
- Sélectionner la Périodicité
(Quotidienne)

- Indiquer la fréquence
(Toutes les 5 Minutes)
- Les dates début et fin
- Valider

--------------------------------SCRIPT DE SAUVEGARDE----------------------------------

IF EXISTS (SELECT * FROM [Link] WHERE([name]= 'db'))

BEGIN

DECLARE @path VARCHAR(200)

DECLARE @filename VARCHAR(200)
DECLARE @filedate VARCHAR(20)
DECLARE @description VARCHAR(100)
DECLARE @dbname VARCHAR(50)

SET @path = 'D:\backup\db\'

SET @filedate = REPLACE(CONVERT(varchar(20),GETDATE(),120),':','-' )

SET @dbname = 'db'
SET @filename = @path + @dbname +'_Full_'+ @filedate + '.bak'
SET @description = 'Sauvegarde Complete de la base'+ @dbname + ' du '+@filedate

BACKUP DATABASE [db]

TO DISK = @filename
----WITH DIFFERENTIAL,
WITH DESCRIPTION = @description,
NOFORMAT, NOINIT,
NAME = N'db-FullBackup', SKIP, NOREWIND, NOUNLOAD, CHECKSUM
END
 Administration des Bases de Données | IC3 49

RESTAURATION DES BASES DE DONNEES

Avec SSMS :

- Clic droit sur le nœud « Base de données »

- Sélectionner « Périphérique » sur « … »
- Dans la nouvelle boîte de dialogue « Sélectionner les
unités de sauvegarde », Clic sur « Ajouter »

- Dans la boite de dialogue « Localiser

le fichier de sauvegarde », aller
sélectionner le fichier de sauvegarde
dans l’emplacement où il se trouve.

A Noter :
 Une unité physique de
sauvegarde correspond au nom
complet du fichier de
sauvegarde sous Windows.
 Il existe aussi la possibilité de
créer des unités logiques de
sauvegarde, ce qui permet
d’utiliser des unités différentes
pour des types de sauvegardes
différents.
 Administration des Bases de Données | IC3 50

Avec du Code Transact :

BACKUP DATABASE [db] TO DISK = N'C:\Users\DSI\Documents\Nouveau

dossier\FichierDeSauvegarde'
GO

DROP TABLE VENTE;

RESTORE DATABASE db FROM DISK = 'C:\Users\DSI\Documents\Nouveau

dossier\FichierDeSauvegarde' WITH REPLACE
GO

IV.3. LA REPLICATION

La réplication est un procédé qui consiste à recopier les données sur plusieurs
serveurs. Le serveur principal est appelé « Maître » et le serveur secondaire est
« esclave »

Requête de Mise à Jour

(UPDATE, DELETE, DROP, …)

Données MAJ Serveur

Maitre

Données MAJ Serveur

Esclave

Principe de mis en œuvre

 La base maitresse reçoit un ordre de mise à jour (INSERT, UPDATE, DELETE,

…)

 Les modifications faites sur les données sont détectées et stockées en

vue de leurs propagation.

 Et enfin, un processus de réplication prend en charge la propagation

des modifications à faire sur la (les) base (s) esclave (s).
 Administration des Bases de Données | IC3 51

Type de Réplication
Dans la réplication des bases de données, on distingue deux types de
réplications :
1. Réplication Synchrone
Dans ce type de réplication dès qu’il y a un traitement de données, le
serveur maitre envoie les données vers le serveur esclave qui lui retourne aussi
tôt un accusé de réception confirmant que les données ont effectivement été
reçu.
Avantage : En cas de panne, les données sur le serveur esclave sont
exactement celles qu’il y avaient que le serveur maitre.
Désavantage : Le temps de latence est extrêmement important du fait
des échangent liés aux accusés de réceptions.

2. Réplication Asynchrone
Dans ce type de réplication, à des intervalle de temps défini, le serveur
maitre envoie les données vers le serveur esclave qui ne lui retourne aucun
accusé de réception garantissant que les données ont été correctement reçu.
Avantage : Il n’y a pas de surcharge sur le serveur maitre quant au
traitement des accusés de réceptions.
Désavantage : Aucune garanti que les données ont été correctement
reçu.
Rôle de la Réplication

 La réplication prend en charge l’aspect de tolérance aux pannes, et

permet à l’administrateur de base des données de s’assurer que son
système est résilient.

 La réplication permet en même temps à l’administrateur, en l’associant à

la répartition des charges d’assurer une bonne performance de son
système, de sorte que l’on peut repartir l’exécution des requêtes sur les
différents serveurs en vue de gagner en temps de réponse.

Configuration de la Replication sous SQL-Serveur

La réplication consiste à copier et de distribuer un ensemble de données et

d’objets d’une base de données vers une autre, puis de synchroniser ces bases
de données afin de préserver leur cohérence. Les deux bases de données
peuvent être stockées à des emplacements distincts. Il existe sous SQL Server
plusieurs types de réplication : Instantanée (snapshot), transactionnelle, Fusion.
 Administration des Bases de Données | IC3 52

- La réplication instantanée : est en général utilisée pour fournir le jeu

des données initiales pour les autres réplications (transactionnelle et
fusion). Elle transmet les données telle qu’elles à un moment donnée
définit dans la configuration. Elle est importante pour avoir un état
complet du serveur à un moment précis ou répétitif.
- La réplication transactionnelle est en général utilisée dans les
environnements serveur à serveur dans les cas suivants :
o pour des applications nécessitant une mise à jour instantanée
et incrémentielle des données vers les différents abonnés
o pour les applications pouvant nécessiter l’accès aux états
intermédiaires des données: avec la possibilité d’activer un
déclencheur pour chacune des transactions (insertion,
modification, …) quel qu’en soit le nombre sans se limiter
simplement aux résultats.
o Pour les applications ayant un gros volume d’activité
(insertion, modification, suppression) du côté du distributeur.
o Pour les cas de réplication où les abonnées sont des bases
de données non-SQL Server.
- La réplication fusion est généralement utilisée dans des
environnements serveur à client et plus particulièrement dans les cas
suivants :
o Plusieurs abonnés peuvent mettre à jour les mêmes données
à différents moments et propager ces modifications au
serveur de publication et à d'autres Abonnés.
o des abonnés doivent recevoir des données, apporter des
modifications hors connexion et synchroniser ultérieurement
ces modifications avec l'éditeur et d'autres abonnés ;
o Chaque Abonné requiert une partition de données différente.
o Des conflits peuvent se produire et, le cas échéant, vous
devez pouvoir les détecter et les résoudre.
o L'application requiert le résultat des modifications des
données au lieu de devoir accéder aux états intermédiaires
des données. Par exemple, si une ligne change cinq fois sur un
Abonné avant qu'il se synchronise avec un serveur de
publication, la ligne ne change qu'une seule fois sur le serveur
de publication pour refléter le résultat final des modifications
(c'est-à-dire la cinquième valeur).
Administration des Bases de Données | IC3 53
Département d’informatique
Cours : 420-KBA-LG, programmation de bases de données

Automne 2019 Programmation

de bases de
données
Transact-SQL (SQL Server)

Saliha Yacoub
COLLEGE LIONEL-GROULX
Table des matières
Historique des versions ................................................................................................................... 5
Chapitre 1, pour bien commencer…................................................................................................ 6
Chapitre 2, installation, configuration et connexion ....................................................................... 9
Mode d’authentification : ......................................................................................................... 10
Authentification Windows..................................................................................................... 10
Authentification SQL server................................................................................................... 10
Étape 1 : Changer le mode d’authentification .......................................................................... 10
Étape 2 : Créer une nouvelle connexion ................................................................................... 13
Étape 3 : Attribuer les rôles ....................................................................................................... 15
Étape 4, Connexion avec l’authentification SQL Server et création de la base de données. .... 17
Où est stockée la base de données ? ........................................................................................ 20
Chapitre 3, création des tables ...................................................................................................... 24
Types de données SQL Server ................................................................................................... 24
La propriété « IDENTITY » d’une table ...................................................................................... 25
Création des tables avec SQL Server ......................................................................................... 28
Chapitre 4, le modèle de données avec SQL Server Management Studio. ................................... 30
Étape 0 : création de la base de données ................................................................................. 30
Étape 2 : Création des tables : ................................................................................................... 30
Étape 3, créer le schéma de la BD ............................................................................................. 31
Étape 4 : Définir les relations (la clé étrangère) ........................................................................ 33
Définir la clé primaire composée .............................................................................................. 35
Chapitre 5, éléments du langage Transct-SQL.............................................................................. 37
Définitions ................................................................................................................................. 37
Éléments du langage Transact-SQL : ......................................................................................... 37
Les variables et leurs déclarations......................................................................................... 37
Les mots réservés : BEGIN …END .......................................................................................... 37
Les structures de contrôles ................................................................................................... 37
Les curseurs : ............................................................................................................................. 41
Chapitre 6, les procédures stockées.............................................................................................. 44
Définition ................................................................................................................................... 44
Avantages à utiliser les procédures stockées ............................................................................ 44
Syntaxe simplifiée de définition d’une procédure stockée avec Transct-SQL .......................... 44

1
Saliha Yacoub
 Exemple1 : Tous les paramètres sont en IN. (Insertion) ....................................................... 45
Exécution d’une procédure dans son SGBD natif (MS SQL Server) ....................................... 45
Exemple 3, utilisation de LIKE dans une procédure stockée ................................................. 46
Exemple 4 : Procédure avec un paramètre en OUTPUT........................................................ 47
Les fonctions stockées : Syntaxe simplifiée............................................................................... 48
Cas d’une fonction qui ne retourne pas une table ................................................................ 48
Exemple 1, fonction avec paramètres ................................................................................... 48
Exécution d’une fonction dans MS SQL Server ..................................................................... 48
Exemple2 : fonction sans paramètres ................................................................................... 49
Cas d’une fonction qui retourne une table. .......................................................................... 49
Exemple ................................................................................................................................. 49
Supprimer une fonction ou une procédure :............................................................................. 50
En conclusion pour les procédures et les fonctions. ................................................................. 50
Les procédures stockées et les fonctions : les Templates. ........................................................ 52
Chapitre 7, les Triggers ou déclencheurs ...................................................................................... 54
Définition : ................................................................................................................................. 54
Rôle des triggers : ...................................................................................................................... 54
Syntaxe simplifiée : .................................................................................................................... 54
Principe de fonctionnement pour les triggers DML. ................................................................. 55
Exemple 1, suppression en cascade .......................................................................................... 55
Exemple 2 .................................................................................................................................. 56
Exemple 3 .................................................................................................................................. 56
RAISERROR: ................................................................................................................................... 57
Activer /désactiver un trigger .................................................................................................... 59
Supprimer un trigger. ................................................................................................................ 60
Retour sur la commande CREATE TABLE : ON DELETE CASCADE .............................................. 60
En conclusion : ............................................................................................................................... 63
Chapitre 8, les transactions ........................................................................................................... 64
Notions de Transactions : .......................................................................................................... 64
Propriétés d’une transaction ..................................................................................................... 64
Récupération d’une transaction ................................................................................................ 66
Récupération complète de la base de données ............................................................................ 66
Transactions concurrentes ........................................................................................................ 67

2
Saliha Yacoub
 Perte de mise à jour .................................................................................................................. 68
Les verrous ................................................................................................................................ 68
Chapitre 9, optimisation de requêtes............................................................................................ 70
Introduction. .................................................................................................................................. 70
Les index .................................................................................................................................... 70
Types d’index : ........................................................................................................................... 72
Les CLUSTERED INDEX : ......................................................................................................... 72
Les index non CLUSTERED INDEX : ........................................................................................ 74
La commande CREATE INDEX .................................................................................................... 75
Suppression d’un index ............................................................................................................. 76
Afficher les index définis sur une table ..................................................................................... 76
Outils de mesures des performances ........................................................................................ 76
Règles d’optimisation de requêtes : .......................................................................................... 76
Chapitre 10, introduction à la sécurité de données ...................................................................... 77
Introduction ............................................................................................................................... 77
Menaces courantes : ................................................................................................................. 77
Injection SQL .......................................................................................................................... 77
Élévation de privilège : .......................................................................................................... 78
Détection des attaques et surveillance intelligente .............................................................. 79
Mots de passe........................................................................................................................ 79
Rôles du serveur : ...................................................................................................................... 80
Rôles niveau bases de données : ............................................................................................... 81
Privilèges sur les objets (tables, colonnes, lignes) :................................................................... 82
Par l’interface SQL Server Management Studio : .................................................................. 82
Avec les commandes SQL ...................................................................................................... 85
Les commandes GRANT, REVOKE et DENY ................................................................................ 88
La command GRANT, syntaxe simplifiée ............................................................................... 88
Les roles creés par les utilisateurs. (pas ceux prédéfinis). .................................................... 90
La commande REVOKE. ......................................................................................................... 91
La commande DENY .............................................................................................................. 91
Les vues pour la sécurité des données : contrôle sur les lignes ............................................ 92
Conclusion ................................................................................................................................. 93
Le chiffrement des données ...................................................................................................... 93

3
Saliha Yacoub
 Définition : ............................................................................................................................. 93
Hachage « hashing » (chiffrement unidirectionnel) .............................................................. 93
Chiffrement des données (chiffrement bidirectionnel) ........................................................ 94
Chiffrement des procédures et fonctions de la base de données ........................................ 95
Chiffrer les données contenues dans une table .................................................................... 95
Chiffrement des données dans le SGBD MS SQL Server ....................................................... 95
Chiffrement des données dans le logiciel client ou le serveur d’application web ................ 97
Autre exemple chiffrement par clé symétrique sans certificat ............................................. 98
Autre exemple chiffrement par ENCRYPTBYPASSPHRASE .................................................. 100
Sources ........................................................................................................................................ 102

4
Saliha Yacoub
Historique des versions

Numéro de Tâches/modifications Auteur Date

version
1.0 Chapitres 1-6 Saliha Yacoub Août 2019
1.1 Chapitre 7 Saliha Yacoub Octobre 2019
1.2 Chapitre 8 Saliha Yacoub Octobre 2019
Marc Beaulne
1.3 Chapitre 9 Saliha Yacoub Octobre 2019
1.4 Chapitre 10 Saliha Yacoub Novembre 2019
1.5 Chapitre 10, le chiffrement Marc Beaulne, Saliha Yacoub Novembre 2019

5
Saliha Yacoub
Chapitre 1, pour bien commencer….
Microsoft SQL Server est un Système de gestion de base de données relationnel et
transactionnel développé et commercialisé par Microsoft.

Microsoft SQL Server utilise le langage T-SQL (Transact-SQL) pour ses requêtes, c'est une
implémentation de SQL qui prend en charge les procédures stockées et les
déclencheurs. La dernière version est SQL Server 2017. La première ayant appartenu à
Microsoft seul est en 1994. (Contrairement à Oracle qui sort la première version en
1979 voire 1977)

Durant, la session 2 nous avons étudié SQL en utilisant le SGBD Oracle. Il faut savoir que,
tous les SGBDs relationnels (Oracle, MS SQL Server, MySQL, SQLite, DB2, PostgreSQL ..)
utilisent un SQL standard.

Ce qui implique que TOUS ce que vous avez appris durant le cours de « Introduction aux
bases de données » de la session 2 s’applique et reste valable pour les autres SGBDs à
quelques exceptions près.

• La Commande CREATE TABLE reste la même. Mais certains SGBDs comme Oracle
12c et plus, MS SQL Server, et MY SQL ont implémenté le concept de
l’incrémentation automatique de la clé primaire.

• La commande ALTER Table est la même. De même que la commande DROP

Table.

• La commande SELECT reste la même. Les jointures se font au niveau du FROM et

non au niveau du WHERE.

• Sauf le SQLite, les SGBD cités plus haut sont TOUS des SGBDS SERVEURS. SQLite
est un SGBD embarqué.

• TOUS les SGBDs offrent une interface ou un logiciel de gestion des bases de
données. Pour Oracle, nous l’avons vu, c’est SQL Developer. Pour MS SQL Server
c’est SQL Server Management Studio, pour MySQL c’est MySQL Workbench,
pour SQLite c’est SQLite DB Browser.

6
Saliha Yacoub
Cependant,

• Les SGBDs n’ont pas la même architecture. Pour Oracle, quelle que soit la
version, il manque la couche « Base de Données ». Tous les usagers sont
connectés à une unique base de données qui est ORCL dans la plupart des cas
(sinon xe). La base de données et créée au moment de l’installation. Ce point est
très important pour la suite du cours. Pour MS SQL Server, chaque utilisateur
doit créer sa propre base de données, et il peut en créer plusieurs BD.

Lorsque vous êtes connectés à un serveur MS SQL Server, la première opération

à exécuter est : (si vous n’avez pas de BD)

CREATE DATABASE nomdelaBD;

Exemple :

CREATE DATABASE empclg;

Comme il est possible que vous ayez plus qu’une base de données, avant toute
utilisation de celle-ci il faudra l’indique au SGBD.

USE nomdelaBD;

Exemple

USE empclg;

Il est de même pour le SGBD MySQL concernant le CREATE DATABASE et le USE .

• Les attributs n’utilisent pas les mêmes types. Exemple, pour Oracle, on utilise le
VARCHAR2(n) alors pour MS SQL server c’est le VARCHAR(n). Avant de créer une
table, ce serait utile de consulter les types de données manipulés par le SGBD.

• MS SQL Server a implémenté la propriété IDENTITY pour l’incrémentation

automatique de la clé primaire. Cette propriété se retrouve dans ORACLE 12c et
plus. Pour MySQL, il utilise la propriété : AUTO_INCREMENT.

• Pour Oracle 11g (la base de données que nous avons utilisée lors de la dernière
session), le principe de l’incrémentation automatique utilise une séquence et un
trigger. On utilise [Link] pour incrémenter automatiquement
une valeur.

7
Saliha Yacoub
 Attention : (Rappel)
Si une séquence démarre à 1 pour Oracle 11g, la valeur qui sera insérée est 2.
(nextval).Ce qui n’est pas le cas avec IDENTIT (1,1) qui indique que la valeur qui
sera insérée est 1.

• Les SGBD sont très différents concernant l’extension de la couche SQL. Ils sont
différents pour l’écritures des procédures stockées et des triggers. Cette session,
nous allons étudier le Transact-SQL qui est l’extension du SQL pour MS SQL
Server. À titre d’information, pour ORACLE, cette extension du SQL s’appelle :
PL/SQL. Pour SQLite, cette couche gère uniquement les triggers.

• L’interface graphique de SQL Server Management Studio permet de créer

directement la base de données à l’aide du schéma. En d’autres mots, pas besoin
de générer le code SQL du diagramme pour l’exécuter puisque les tables sont
déjà créées. Ce qui n’était pas le cas avec Oracle SQL developer Data Modeler
où est-ce qu’il faut générer le code SQL puis l’exécuter. En ce sens,
MY SQL WorkBench est semblable à Oracle.

• SQL Server Management Studio est un excellent outil pour créer et exploiter vos
bases de données MS SQL Server indépendamment d’un langage de
programmation. MAIS… il faut savoir que Visual Studio vous permet aussi de
créer et gérer vos bases de données MS SQL Server. On verra ce point plus loin.

• Ce qu’il faut savoir pour la suite du cours, c’est que votre poste de travail est à la
fois serveur et client. Pas comme l’installation qu’on avait avec Oracle. Dans ce
cas, il faut être conscient que n’importe qui peut supprimer votre BD puisque
tous les étudiants sont ADMIN de leur poste de travail. Par conséquent il faut :
o Essayer le plus possible de garder votre poste de travail le reste de la
session.
o Garder en tout temps vos scripts SQL.

8
Saliha Yacoub
Chapitre 2, installation, configuration et
connexion
Si vous n’avez pas déjà installé SQL server, vous devez le faire. L’installation de la base
de données est très simple et se fait automatiquement.
Nous avons besoin d’installer :
1- Le serveur de bases de données :
Vous devez aller sur le site suivant pour télécharger et installer SQL Server
Express 2017.
[Link]

Vous devez choisir installation Standard, et tout se déroule automatiquement.

Attention ! vous devez vérifier les paramètres de langue de votre ordinateur.
2- L’outil de gestion de bases de données
Une fois que le serveur est installé, vous devez installer SSMS version 18.2 (SQL Server
Management Studio), ce qui vous permet de gérer et d’exploiter vos bases de données
avec SQL Server. Pour cela vous devez vous rendre sur le site :
[Link]
ssms?view=sql-server-2017
L’installation se fait automatiquement.

Attention :

Il faut redémarrer l’ordinateur pour que l’installation soit complète

Si votre serveur ne démarre pas, il faudra le faire manuellement :

9
Saliha Yacoub
Mode d’authentification :
Authentification Windows : Si vous choisissez ce mode d’authentification, cela veut dire
que le serveur de base de données, lorsque vous essayez de vous connecter, ne vous
demandera pas de mot de passe. Utiliser ce mode d’authentification si vous n’avez pas
de compte sur le serveur de base de données. C’est avec ce mode que l’on se connecte
pour la première fois.
Authentification SQL server : Si vous choisissez ce mode d’authentification, cela veut dire
que vous avez un compte sur le serveur de bases de données. Vous avez besoin d’un
nom d’usager, d’un mot de passe et d’une base de données. C’est ce mode
d’authentification que l’on va utiliser durant toute la session. C’est ce mode
d’authentification que vous allez avoir en entreprise.

Étape 1 : Changer le mode d’authentification

Lorsqu’on établit une connexion pour la première fois, nous allons faire une
authentification Windows. (Vous n’avez pas encore de compte sur le Serveur SQL
server) —voir la figure suivante.

Attention :

Le nom du serveur est le nom de votre ordinateur\nom de l’instance

10
Saliha Yacoub
Sinon, dérouler le nom du serveur, faire parcourir (ou <Browse for more …> et trouver
votre serveur et son instance.
Une fois que vous êtes connecté, allez sur les propriétés de votre connexion et changez
le mode d’authentification. → Figure suivante.

Bouton droit sur votre

serveur, puis propriété
Sécurité

A l’onglet Sécurite, choisir Mode d’authentification SQL Server et Windows. Faites OK.
Redémarrer le serveur. (Bouton droit puis redémarrer.).

11
Saliha Yacoub
Attention :

Vous devez redémarrer le serveur

12
Saliha Yacoub
Il est probable que le serveur vous fasse une mise en garde quant au changement du
mode d’authentification et qu’il faut redémarrer le serveur. Faîtes juste OK.

Étape 2 : Créer une nouvelle connexion

Sur le bouton droit de l’onglet Sécurité, créer une nouvelle connexion.

13
Saliha Yacoub
 • Donner un nom significatif sans caractères spéciaux et sans accents
• Choisir Authentification SQL Server.
• Choisir un mot de passe qui respecte la stratégie des mots de passe Windows
Server

• Décocher l’utilisateur doit changer le mot de passe.

• Vous pouvez décocher la case « Conserver la stratégie des mots de passe. Mais
ce n’est pas conseillé.
• Comme vous n’avez pas de base de données, la connexion utilise la Base de
données par défaut qui master.
• Ne vous inquiétez pas, vous aller avoir votre propre base de données

14
Saliha Yacoub
Une fois que cette étape est terminée, vérifier que votre connexion est bien créée. Pour
cela allez dans l’onglet Sécurité-puis Connexion et repérer votre connexion

Étape 3 : Attribuer les rôles

Pour pouvoir créer votre propre base de données vous devez posséder les droits
nécessaires (ou le rôle).
Si vous êtes administrateur alors vous avez déjà ces rôles, sinon vous devez les attribuer
à votre connexion avant de créer la bd.

Attention :

Pour créer la base de données vous devez avoir au moins le rôle dbcreator
Les membres du rôle de serveur dbcreator peuvent créer, modifier, supprimer et
restaurer n'importe quelle base de données.

Pour donner les droits à votre connexion, allez à votre connexion, bouton droit,
propriétés puis rôle du serveur

15
Saliha Yacoub
Puis Rôles du serveur.

Puis cocher dbcreator puis cliquer sur OK.

Attention :

Ne jamais donner le rôle sysadmin. Les membres du rôle sysadmin peuvent effectuer
toute activité sur le serveur. Faîtes attention !!

16
Saliha Yacoub
Étape 4, Connexion avec l’authentification SQL Server et création de la base de
données.
Vous pouvez vous déconnecter du serveur et vous reconnecter avec votre nouvelle
connexion (SQL Server) comme suit.

Pour vous déconnecter du serveur, utiliser le bouton Déconnecter

Ou bien Bouton droit sur la Votre serveur, puis déconnecter .

Attention :

Ne jamais mémoriser le mot de passe

Attention :

Ne jamais mémoriser le mot de passe

17
Saliha Yacoub
Pour créer une nouvelle base de données, placez-vous à l’onglet bases de données, puis
nouvelle base de données. Ou utiliser la commande CREATE DATABASE

Donnez un nom significatif à votre base de données.

18
Saliha Yacoub
Avant de cliquer sur OK, cliquer sur propriétaire, vous allez avoir la figure suivante :

Cliquez ensuite sur parcourir, puis trouvez votre connexion et cochez-la. (voir figure
suivante).

Cliquez OK sur chaque fenêtre

Après la création de la base de données, nous allons faire en sorte que le login pointe
directement sur la nouvelle base de données.
19
Saliha Yacoub
Sous l’onglet Sécurité, déroulez les connexions. Repèrerez la vôtre. Puis bouton droit de
la souris et Propriétés.

Choisir ensuite le nom de votre BD par défaut. Tester à nouveau votre connexion.

Important :
Vous pouvez également créer votre base de données avec la commande CREATE

CREATE DATABASE nomdelaBD;

Où est stockée la base de données ?

En cliquant sur le bouton droit de votre base de données, puis propriétés à l’onglet
fichier vous allez trouver les deux fichiers de la bd et leur emplacement.

20
Saliha Yacoub
Ces fichiers sont dans :
C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS2017\MSSQL\DATA
Vous y trouverez deux types de fichiers pour chaque BD : L’un .mdf et l’autre. ldf
Les données sont stockées dans un fichier MDF, toutes les transactions, les
modifications de la base de données SQL Server effectuées par chaque transaction sont
stockées dans un fichier LDF
[Link] et Patochebd_log.ldf

Vous pouvez récupérer votre base en faisant : Bouton droit sur Bases de données, puis
Joindre. Vous aurez la fenêtre suivante.

21
Saliha Yacoub
Cliquer sur le bouton Ajouter. Choisir le fichier en question (le [Link]) puis faire OK,
puis OK.
Votre base de données va apparaitre dans l’explorateur d’objets. Vous pouvez alors
l’exploiter comme vous voulez.

22
Saliha Yacoub
Attention :
Une base de données ne peut être jointe plus qu’une fois.
Lorsque vous essayez de joindre une Base de données déjà jointe, cela provoquera une
erreur.
Il faut que vos fichiers soient dans le bon dossier.

Attention Récupération de la base de données:

Pour récupérer votre base de données effectuer les étapes suivantes :
1- Dans tous les cas garder vos scripts SQL.
2-Copier les deux fichiers .mdf et .ldf de votre base de données (patochebd et
patoche_log) dans votre clé USB
3-pour ouvrir les fichiers que vous avez copiés dans votre clé USB sur un autre
ordinateur, vous devez d’abord JOINDRE le [Link]
4-Si vous êtes certains que votre BD a été copiée proprement alors vous pouvez la
supprimer pour qu’il n’y ait pas de plagiat.

23
Saliha Yacoub
Chapitre 3, création des tables
Types de données SQL Server

Types numériques exacts

Type À partir de À
bigint -[Link].854.775.808 [Link].854.775.807
int -2147483648 2147483647
smallint -32768 32767
tinyint 0 255
bit 0 1
Decimal -10 ^ 38 1 10 ^ 38 -1
numeric -10 ^ 38 1 10 ^ 38 -1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647

Numerics approximatif

Type À partir de À
float 1,79 E + 308- 1,79 E + 308
reel -3.40E + 38 3.40E + 38

datetime et smalldatetime

Type À partir de À
datetime (3,33 exactitude millisecondes) 1 janvier 1753 31 déc 9999
smalldatetime (précision de 1 minute) 1 janvier 1900 6 juin 2079

Chaînes de caractères

Type Description
De longueur fixe de caractères Unicode avec une longueur maximum de
char
8000 caractères.
varchar Texte unicode de longueur variable allant jusqu’à 2 Go.
text Texte non unicode de longueur maximale 2Go

24
Saliha Yacoub
Les chaînes de caractères Unicode

Type Description
la longueur de données Unicode-fixe avec une longueur maximale de 4000
nchar
caractères.
la longueur de données Unicode et variable, avec une longueur maximum
nvarchar
de 4000 caractères.
nvarchar longueur Unicode données variables avec une longueur maximale de
(max) 230 caractères (SQL Server 2005 uniquement).
la longueur de données Unicode et variable, avec une longueur maximale
ntext
de 1073741823 caractères.

Binary Cordes

Type Description
De longueur fixe des données binaires d'une longueur maximale de 8000
binaire
octets.
De longueur variable des données binaires d'une longueur maximale de
varbinary
8000 octets.
varbinary De longueur variable des données binaires d'une longueur maximale de
(max) 231 octets (SQL Server 2005 uniquement).
De longueur variable des données binaires d'une longueur maximale de
image
2147483647 octets.

Pour plus de détails, allez sur :

[Link]
server-2017

La propriété « IDENTITY » d’une table

Vous pouvez mettre en œuvre des colonnes d'identification à l'aide de la propriété

IDENTITY. Ce qui permet de réaliser un auto incrément sur une colonne.

En général, la propriété IDENTITY se définie sur la clé primaire.

SI la propriété IDENTITY est définie sur une colonne, alors c’est le système qui insères
des données dans cette colonne (pas l’utilisateur).

25
Saliha Yacoub
Attention :
Vous ne pouvez [Link] une colonne existante pour y ajouter la prorité IDENTITY.

Exemple1 :

create table Eleves

(
num int identity,
nom varchar(30),
prenom varchar(30),
constraint pkeleve primary key(num));

insert into Eleves (nom,prenom) values('Patoche','Alain');

insert into Eleves (nom,prenom) values('Simba','Chat');

Remarquez :

1. La colonne num a la propriété IDENTITY.

2. Nous n’avons pas inséré dans la colonne num. C’est le système qui le fait pour
nous.
3. Le num de Patoche sera 1, le num de Simba sera 2 et ainsi de suite.

Lorsque vous utilisez la propriété IDENTITY pour définir une colonne d'identification,
tenez compte des éléments suivants :

• Une table ne peut comprendre qu'une colonne définie à l'aide de la propriété

IDENTITY, et cette colonne doit être définie à l'aide d'un type de
données decimal, int, numeric,smallint, bigint ou tinyint.
• Vous pouvez spécifier la valeur de départ et l'incrément. La valeur par défaut est 1
dans les deux cas.
• La colonne d'identification ne doit ni accepter les valeurs NULL, ni contenir une
définition ou un objet DEFAULT. En général c’est la clé primaire.
• La colonne peut être référencée dans une liste de sélection par l'emploi du mot
clé $IDENTITY après la définition de la propriété IDENTITY. La colonne peut
également être référencée par son nom.
• SET IDENTITY_INSERT ON peut être utilisé pour désactiver la propriété IDENTITY
d'une colonne en activant les valeurs à insérer explicitement.

26
Saliha Yacoub
Exemple 2
La numérotation automatique commence à 10 et elle est à pas de 2.

create table ClientsInfo

(
numcl smallint IDENTITY(10,2),
nomcl varchar(30),
constraint pkcl primary key (numcl)
);

insert into Clientsinfo (nomcl) values ('Gavroche');

Sion on veut faire une insertion manuelle dans la colonne num (IDENTITY) il faut mettre
INDENTITY_INSERT a ON
Exemple 3 :

set identity_insert eleves on;

insert into eleves (num,nom, prenom) values

(20,'Simpson','Fred');

Attention, le num de Simpson est 20

Si on veut revenir à l’incrémentation automatique il faut faire

set identity_insert eleves off;

insert into eleves(nom, prenom) values ('Simon','Pascal');

Attention: le num de Simon est 21.

27
Saliha Yacoub
Création des tables avec SQL Server
Les mêmes syntaxes s’appliquent à la création de tables avec SQL Server.
Exemple1 : Dans l’exemple qui suit remarquez la colonne identity et le type money

create table programmes

(codep char(3),
nomprogramme varchar(30),
constraint pkprg primary key(codep)
);

create table etudiants

(
numad int identity ,
nom varchar(20),
prenom varchar(30),
salaire money,
codep char(3),
constraint fkprg foreign key(codep)
references programmes(codep),
constraint pketudiant primary key(numad)
);

Remarque1 : Avec SQL Server on peut définir un seul INSERT INTO et donner la lise des
valeurs.

insert into programmes values

('inf','Informatique'),
('tge','Techniquw de genie'),
('ele','Electronique'),
('sim','Sciences maths info');

Pour exécuter un commit après une opération DML, il faut mettre l’opération entre

begin transaction;
opérations DML
commit;

28
Saliha Yacoub
Exemple 2

begin transaction;
insert into etudiants (nom, prenom,salaire,codep) values
('Patoche','Alain',12.33,'tge');
insert into etudiants (nom, prenom,salaire,codep) values
('Gavroche','Miserable',1.33,'inf');
insert into etudiants (nom, prenom,salaire,codep) values
('Bien','Henry',18.33,'inf');
insert into etudiants (nom, prenom,salaire,codep) values
('Leriche','Alain',40.00,'inf');
commit;

En général, l’ensemble des contraintes que nous avons vues avec ORACLE se définissent
de la même façon avec SQL Server.

alter table etudiants add constraint

cksal check(salaire>1);

29
Saliha Yacoub
Chapitre 4, le modèle de données avec
SQL Server Management Studio.
Parfois, il est intéressant, même très utile de concevoir le modèle de la base de données
(modèle relationnel) puis de générer le code SQL. C’Est le cas de la plupart des SGBD. On
se souvient par exemple du SQL Data Modeler du SGBD Oracle.

Pour MS SQL Server, c’est très simple de créer la base de données en utilisant un
schéma relationnel.

Vous pouvez soit obtenir une modèle relationnel d’une base de données déjà créée ou
tout simplement créer un nouveau schéma.

Étape 0 : création de la base de données

Créer une nouvelle base de données avec un nom significatif (ou votre nom) Faîtes en
sorte que vous en soyez le propriétaire.

Étape 2 : Création des tables :

1- Faire bouton droit de sur l’onglet Tables de votre BD, puis table

2- Créer une table avec les colonnes souhaitées. Les types de données sont ceux
que nous avons au chapitre 3

30
Saliha Yacoub
 3- Sélectionner la colonne de vous voulez qu’elle soit clé primaire, puis bouton
droit et faire : définir la clé primaire : cette étape est obligatoire si vous voulez
que la BD soit en 1FN.

Si vous voulez que votre clé primaire soit définie comme IDENTITY, alors :
a. Positionnez-vous à la colonne de la clé primaire→1
b. Vérifiez que dans les propriétés de cette colonne, (à gauche→2) la propriété
« colonne d’identité » soit la clé primaire.
c. Par la suite, vous allez remarquer que la propriété IDENTITY est bien définie
sur la colonne →3

4- Enregistrer la table : cliquez sur le bouton enregistrez, puis donnez un nom à

votre table. Notre table a pour nom : EtudiantsInfo

Étape 3, créer le schéma de la BD

Cette étape peut se faire après avoir créé l’ensemble des tables, ou après avoir créé la
première table.
1. Sur le bouton droit de la BD, faire nouveau schéma :

31
Saliha Yacoub
 2. Si vous avez ce message, faites OK

3. Ajouter ensuite les tables à votre schéma. Pour l’instant la seule table que nous
avons est EtuiantsInfo

4. Enregistrez votre diagramme.

32
Saliha Yacoub
Étape 4 : Définir les relations (la clé étrangère)
Une fois que vos tables sont créées, ou bien au fur et à mesure que vos tables vont se
créer, il sera important de définir les liens entre les tables. Ces liens sont évidemment
définis par le concept de Foreign Key ou clé étrangère.
Il est important de rappeler que les types de données et la taille des colonnes qui
définissent la clé primaire et la clé étrangère soient les mêmes.
On suppose que la table ProgrammesInfo est créée.

1. Sur la colonne Codep de EtudiantsInfo, (la colonne qui sera clé étrangère), faire
Relation comme le montre la figure

2. Une fenêtre s’ouvre, faire Ajouter. Une fenêtre s’ouvre.

3. Dérouler, spécification des tables et des colonnes

33
Saliha Yacoub
 4. Vérifiez que vous avez bel et bien les bonnes colonnes avec les bonnes tables :
a. Vous pouvez changer le nom de la contrainte de FK →1
b. Vérifier la table et la colonne de la primary Key →2
c. Vérifier la table et la colonne de la FK→3

34
Saliha Yacoub
 5. Faire OK, puis fermer pour terminer.
6. Enregistrez.

Attention :
Si au moment d’enregister le diagramme vous avez cette fenêtre ……. Alors

allez à Outils, puis Options, à concepteur de bases de données, décochez la case

« Empêcher l’enregistrement ……… » voir figure suivante

Définir la clé primaire composée

Pour définir une clé primaire composée sur une table, c’est très facile. Il suffit des
sélectionner TOUTES les colonnes que l’on souhaite qu’elle soit clé primaire et d’ajouter
une clé primaire comme au point 3 de l’étape 1. Il est probable que, les colonnes de

35
Saliha Yacoub
votre clé primaire composées soient des clés étrangères comme dans la plupart des cas.
Il faudra alors les définir comme telle.
EtudiantsInfo
numad PogrammesInfo
codep
nom
nomprog
codep
adresse

CoursInfo
Nom de la colonne Type de données Autoriser les...
CodeCours char(3)
titrecours varchar(50)

ResultatsInfo
Nom de la colonne Type de données Autoriser les...
numad int
codeCours char(3)
note float

Il n’est pas nécessaire de générer le code SQL pour créer les tables, puis que celles-ci
sont déjà créées

36
Saliha Yacoub
Chapitre 5, éléments du langage
Transct-SQL
Définitions

La plupart des SGBDs relationnels offrent une extension du SQL, en y ajoutant des
déclarations de variables, des structures de contrôles (alternatives et les répétitives)
pour améliorer leurs performances
Transact-SQL ou T-SQL ou TSQL est l’extension du langage SQL pour Microsoft SQL
Server et Sybase. Transact-SQL est un langage procédural permettant d’implémenter
des fonctionnalités de bases de données que SQL seul ne peut implémenter.

Éléments du langage Transact-SQL :

Les variables et leurs déclarations
• Dans Transact SQL, on utilise le mot réservé DECLARE pour déclarer des
variables.
• Les noms de variables sont précédés du symbole @
• Les types de variables, sont les types SQL
• Les variables peuvent être initialisées avec des valeurs en utilisant la fonction
SET.

Exemple :

DECLARE
@CHOIX int ;
SET @CHOIX =1;

Les mots réservés : BEGIN …END

Ces mots réservés permettent de définir un bloc ou un groupe d’instructions qui doivent
être exécutées.
Les structures de contrôles
L’alternative :
L’ Instruction IF
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
37
Saliha Yacoub
Ou encore
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE IF Boolean_expression
{ sql_statement | statement_block } ]
[ ELSE
{ sql_statement | statement_block } ]

Exemple: (ce bout de code est ce que nous appelons un bloc anonyme)
DECLARE
@sal money;
set @sal =40.00;
if @sal = (select salaire from etudiants where numad =8)
(select * from etudiants where numad =8);
else if @sal=1.33 (select * from ETUDIANTS WHERE NUMAD=6);
else (select * from etudiants);

Attention:
Lorsque vous avez un bloc d’instructions, celui-ci doit être placé entre BEGIN et END.
Exemple1:
DECLARE
@code char(3);
begin
set @code ='tge';
if @code like '%'+ (select codep from etudiants where numad =6) +'%'
(select * from etudiants where numad =6);
else (select * from ETUDIANTS WHERE NUMAD=1);
end;
Dans les exemples précédents, remarquez:

• Le bloc d’instructions BEGIN .. END

• Le IF ..ELSE
• Le IF ..ELSE IF ..ELSE
• Comment est construit le LIKE

38
Saliha Yacoub
Exemple2
DECLARE
@code char(3);
begin
set @code ='tge';
if @code like '%'+ (select codep from etudiants where numad =6) +'%'
(select * from etudiants where numad =6);

else
BEGIN
(select * from ETUDIANTS WHERE NUMAD=1);
INSERT INTO ETUDIANTS (NOM,PRENOM,SALAIRE,CODEP)
VALUES('Mosus','Chat',12,'sim');
update etudiants set salaire = salaire + 5 where codep = 'inf';
END;
end;

Remarquez:
Après le ELSE, nous avons trois instructions à exécuter. Un SELECT, un INSERT et un
UPDATE. Ces instructions sont placées entre BEGIN et END :

L’instruction CASE
Syntaxe :

CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

Ou bien.

CASE
WHEN Boolean_expression THEN result_expression [ ..n ]
[ ELSE else_result_expression ]
END

39
Saliha Yacoub
Exemple 1, case avec un SELECT
SELECT nom,prenom, codep =
CASE codep
WHEN 'inf' THEN 'Informatique'
WHEN 'tge' THEN 'Genie Ele'
WHEN 'ele' THEN 'Electronique'
ELSE 'Aucun Programme'
END, salaire

FROM etudiants ;

Exemple 2, de CASE dans un UPDATE

UPDATE etudiants
SET salaire=
( CASE
WHEN (salaire < 5) THEN salaire + 40
ELSE (salaire + 20.00)
END
) ;
La répétitive
La répétitive est implémentée à l’aide de la boucle WHILE.

Syntaxe :
WHILE Boolean_expression
{ sql_statement | statement_block | BREAK | CONTINUE }
Exemple
Augmenter le salaire des étudiants, tant que la moyenne est inférieure à 80. Mais si le
maximum des salaires dépasse 100 on arrête,

BEGIN
WHILE (select avg(salaire) from etudiants )<= 80
BEGIN update etudiants set salaire = salaire +10;
IF(select max(salaire) from etudiants) >100 BREAK;
ELSE CONTINUE;
END;
END;

40
Saliha Yacoub
Les curseurs :
Les curseurs sont des zones mémoire (mémoire tampon) utilisées par les SGBDs pour
récupérer un ensemble de résultats issu d’une requête SELECT.
Pour MS SQL Server, les curseurs sont explicites, ce qui veut dire qu’ils sont associés à
une requête SELECT bien précise. Comme par exemple, le curseur CUR1 contiendra le
resultra de la requête : SELECT ename, job from emp where deptn=30;
Pour utiliser un curseur, nous avons besoin de le déclarer.
DECLARE nomCurseur CURSOR FOR SELECT … FROM

Exemple :

DECLARE
cur1 CURSOR FOR
SELECT idcircuit, coutcircuit FROM circuits;

Pour lire le contenu d’un curseur, on procède comme suit :

1- Ouvrir le curseur avec OPEN.
2- Lire le curseur avec FETCH ……INTO et une boucle WHILE: pour aller chercher
chaque enregistrement dans l’ensemble actif, une ligne à la fois, nous utiliserons la
commande FETCH. À chaque fois que le FETCH est utilisé, le curseur avance au
prochain enregistrement dans l’ensemble actif
3- Fermer le curseur avec la commande avec CLOSE
4- Supprimer la référence au Curseur avec DEALLOCATE

La fonction : @@FETCH_STATUS : Renvoie l'état de la dernière instruction FETCH

effectuée sur un curseur. Elle renvoie 0 si tout s'est bien passé, -1 s'il n'y a plus de
lignes, -2 si la ligne est manquante et -9 le curseur ne fait aucune opération
d’extraction.

La fonction @@CURSOR_ROWS, renvoie le nombre de lignes qualifiantes actuellement

dans le dernier curseur ouvert sur la connexion.

Exemple1

41
Saliha Yacoub
DECLARE @id int, @cout int;
DECLARE cur1 CURSOR FOR SELECT idcircuit, coutcircuit
FROM circuits;
BEGIN
OPEN cur1 ;
print concat('numero','---','cout');

-- on initialise les variable @id et @cout avec le premier

FETCH(la première ligne)
FETCH NEXT FROM cur1 INTO @id, @cout;
-- Tant que le FETCH se fait normalement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT concat(@id,' -------' ,@cout);
FETCH NEXT FROM cur1 INTO @id, @cout;
END;
CLOSE cur1;
DEALLOCATE cur1;
END

Exemple 2

DECLARE @cout int;

DECLARE Cout_cursor CURSOR FOR SELECT coutcircuit FROM circuits;

BEGIN
OPEN Cout_cursor ;
FETCH NEXT FROM Cout_cursor INTO @cout;

WHILE @@FETCH_STATUS = 0
BEGIN

IF @cout<500 update circuits set coutcircuit = coutcircuit+

(coutcircuit*0.1) WHERE CURRENT OF Cout_cursor ;

ELSE IF @cout BETWEEN 500 and 900 update circuits set coutcircuit
=coutcircuit+(coutcircuit*0.05) WHERE CURRENT OF Cout_cursor;

ELSE update circuits set coutcircuit

=coutcircuit+(coutcircuit*0.01) WHERE CURRENT OF Cout_cursor;

FETCH NEXT FROM Cout_cursor INTO @cout;

END;
CLOSE Cout_cursor;
DEALLOCATE Cout_cursor;
END

42
Saliha Yacoub
Par défaut, les curseurs sont Forward ONLY : ils ne sont pas scrollables.
Lorsqu’un curseur est déclaré avec l’attribut SCROLL alors on peut accéder au contenu
du curseur par d’autres option de la fonction FETCH. Nous pouvons avoir accès à la
première ligne, la dernière ligne, une position absolue, exemple la ligne 3. Position
relative à partir d’une position prédéfinie.

DECLARE Curmonument SCROLL CURSOR FOR

SELECT nomMonument , nbEtoiles FROM Monuments
ORDER BY nbEtoiles desc;

declare @nom varchar(30), @nb int;

BEGIN
OPEN Curmonument;

print(' la premiere ligne');

FETCH FIRST FROM Curmonument into @nom,@nb;
print concat(@nom,'----', @nb)

print('la dernière ligne');

FETCH LAST FROM Curmonument into @nom,@nb;
print concat(@nom,'----', @nb)

print('la ligne numero 3');

FETCH ABSOLUTE 3 FROM Curmonument into @nom,@nb;
print concat(@nom,'----', @nb)

print('la deusième ligne aprè la ligne 3');

FETCH RELATIVE 2 FROM Curmonument into @nom,@nb;
print concat(@nom,'----', @nb)

print('le num immediatement avant la poisition courante');

FETCH PRIOR FROM Curmonument into @nom,@nb;
print concat(@nom,'----', @nb)

print('le num qui est deux lignes avant la ligne courante');

FETCH RELATIVE -2 FROM Curmonument into @nom,@nb;
print concat(@nom,'----', @nb)

CLOSE Curmonument;
DEALLOCATE Curmonument;
END

Remarque : Nous reviendrons sur les détails concernant les curseurs plus loin dans le
cours.

43
Saliha Yacoub
Chapitre 6, les procédures stockées
Définition
Une procédure stockée est un ensemble d’instructions SQL précompilées stockées dans
le serveur de bases de données

Avantages à utiliser les procédures stockées

Il existe plusieurs avantages à utiliser des procédures stockées à la place de simple
requêtes SQL

• Rapidité d’exécution, puisque les procédures stockées sont déjà compilées.

• Clarté du code : dans un code C#, PHP pou autre, il vaut mieux utiliser l’appel
d’une procédure que l’instruction SQL, en particulier lorsque l’instruction SQL est
longue et complexe.
• Faciliter le débogage.
• Réutilisation de la procédure stockée.
• Possibilité d’exécuter un ensemble de requêtes SQL
• Prévention d’injections SQL
• Modularité. Facilite le travail d’équipe.

Syntaxe simplifiée de définition d’une procédure stockée avec Transct-SQL

CREATE [ OR ALTER ] { PROC | PROCEDURE }

[schema_name.] procedure_name
[ { @parameter data_type }
[ OUT | OUTPUT ]
AS
{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

CREATE PROCEDURE : indique que l’on veut créer une procédure stockée.
OR ALTER est optionnel, indique que l’on veut modifier la procédure stockée si celle-ci
existe déjà.
@parameter data_type : On doit fournir la liste des paramètres de la procédure avec le
type de données correspondant à chacun des paramètres.
[OUT | OUTPUT ] : Indique la direction en OUT ou en OUTPUT des paramètre de la
procédure. Par défaut les paramètres sont en IN. Lorsque les paramètres sont en IN, il
n’est pas nécessaire (c’est même une erreur) d’indiquer la direction.

44
Saliha Yacoub
AS : mot réservé qui annonce le début du corps de la procédure et la fin de la
déclaration des paramètres
BEGIN
Bloc SQL ou Transact-SQL
END;

Attention :
Les paramètres sont précédés du symboles @
Le type de paramètre IN OUT est indiqué uniquement si le paramètre est en OUT ou
INOUT (le type IN est par défaut) : La direction IN provoque une erreur si indiquée.
Exemple1 : Tous les paramètres sont en IN. (Insertion)

create procedure insertionEtudiants

(
@pnom varchar(20), @pprenom varchar(30),@psal
money,@pcodep char(3)
)
AS
begin
insert into etudiants(nom , prenom ,salaire ,codep )
values (@pnom , @pprenom ,@psal ,@pcodep)
end;

Exécution d’une procédure dans son SGBD natif (MS SQL Server)
Pour exécuter une procédure stockée, on utilise les commandes execute ou exec. Il
faudra fournir la valeur des paramètres.
Exemple :

execute insertionEtudiants
@pnom ='Lenouveau',
@pprenom ='lenouveau',
@psal=22.5,
@pcodep ='sim';

45
Saliha Yacoub
Même s’il est conseillé de passer les paramètres dans l’ordre de leur apparition dans la
procédure, MS SQL Server peut accepter la passation des paramètres dans n’importe
quel ordre. Par contre, les noms des paramètres sont très importants. En ce sens SQL
Server est contraire d’ORACLE (pour ORACLE c’est l’ordre des paramètres qui est
important et non le nom)
On aurait très bien pu faire ceci , le paramètre @nom est fourni en dernier.
execute insertionEtudiants
@pprenom ='aaaa',
@psal=22.5,
@pcodep ='sim',
@pnom ='patate'Exemple 2 : Les paramètres en IN avec une sortie (SELECT)

create procedure lister

(
@pcodep char(3)
)
AS
begin
select nom,prenom from etudiants where @pcodep = codep;
end;

Execution:

execute lister
@pcodep='inf';

Exemple 3, utilisation de LIKE dans une procédure stockée

create procedure ChercherNom

(
@pnom varchar(20)
)
AS
begin
46
Saliha Yacoub
select * from etudiants where nom Like '%'+ @pnom +'%';
end;

Execution

execute ChercherNom
@pnom='Le';

Exemple 4 : Procédure avec un paramètre en OUTPUT

create procedure ChercherNom2
(
@pnum int,
@pnom varchar(20) out
)
AS
begin

select @pnom = nom

from etudiants where numad =@pnum;
end;
go

Execution

declare @pnum int =1;

declare @pnom varchar(20);
execute ChercherNom2
@pnum ,
@pnom output;
print @pnom;

47
Saliha Yacoub
Les fonctions stockées : Syntaxe simplifiée.
Les fonctions stockées sont des procédures stockées qui retournent des valeurs. Leurs
définitions sont légèrement différentes d’une procédure stockée mais le principe
général de définition reste le même.
Cas d’une fonction qui ne retourne pas une table
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name parameter_data_type
} ]
)
RETURNS return_data_type

[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]

Exemple 1, fonction avec paramètres

create function compteretudiants(@pcode char(3)) returns int

as
begin
declare @total int;
select @total = count(*) from Etudiants where codep =@pcode;
return @total;
end
go

Exécution d’une fonction dans MS SQL Server

Pour exécuter une fonction qui ne retourne pas une table, il faudra utiliser la commande
SELECT, suivie du nom de la fonction Il faudra passer les valeurs des paramètres pour la
fonction.

Attention :
1. Pour l’appel des fonction (Eéxécution), nous avons besoin de préciser le shéma
de la BD. Le shéma est toujours : [Link] .
2. Pour l’instant, tous les objets appartient à l’usager dbo.
3. Pour une fonction qui ne retourne pas une table, pas besoin du FROM pour le
select.

48
Saliha Yacoub
Remarque : le mappage des utilisateurs aux connexions, sera abordé plus loin.
Pour exécuter la fonction précédente :
select [Link]('inf');

---Pas de clause FROM.

Exemple2 : fonction sans paramètres

create function compter() returns int

as
begin
declare @total int;
select @total = count(*) from etudiants;
return @total;
end;

select [Link]();
--pas de clause FROM

Cas d’une fonction qui retourne une table.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name

( [ { @parameter_name parameter_data_type
} ]
)
RETURNS TABLE

[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Exemple

Create FUNCTION Cherchertousetudiants

(@pcodep char(3)) returns table
AS
return(
SELECT nom,prenom
FROM etudiants
WHERE @pcodep =codep
) ;
GO

49
Saliha Yacoub
L’appel (Exécution) d’une fonction qui retourne une table est diffèrent. Le SELECT dans
ce cas, doit utiliser la clause FROM puis que ce qui est retourner est une table. De plus,
si la fonction a des paramètres en IN (implicite) il faudra les déclarer et leur affecter des
valeurs.

declare @codep char(3);

set @codep='inf';
select * from Cherchertousetudiants(@codep);

Supprimer une fonction ou une procédure :

Les fonctions et les procédures sont des objets de la base de données. Ils se détruisent
donc avec la commande DROP
drop procedure ChercherNom2;
drop function compteretudiants

En conclusion pour les procédures et les fonctions.

• Pour les procédures et les fonctions les paramètres sont précédés de @
• Le type IN est par défaut.
• Lorsque le paramètre est en OUT ou OUTPUT, il faudra l’indiquer clairement.
• Les procédures et fonctions sont terminées par GO. Il n’est cependant pas
obligatoire.
• Le mot réservé DECLARE est obligatoire pour déclarer des variables.
• Les fonctions peuvent retourner des tables. Elles ne comportent pas les mots
réservés BEGIN et END
• Pour exécuter une procédure il faut utiliser execute ou exec
• Pour exécuter une fonction il faut utiliser select [Link]
(valeur paramètres)
• À l’exécution des procédures, l’affectation des valeurs aux paramètres se fait
avec = pour les int et la fonction set pour les types text.
• Vos fonctions et procédures se trouvent à Programmabilité de la BD

50
Saliha Yacoub
 51
Saliha Yacoub
Les procédures stockées et les fonctions : les Templates.
Voici le code généré par SQL Server lorsque vous essayer de créer une procédure ou une
fonction

================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> =
<Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> =
<Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

52
Saliha Yacoub
Template function TABLE
-- ================================================
-- Template generated from Template Explorer using:
-- Create Inline Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)
GO

53
Saliha Yacoub
Chapitre 7, les Triggers ou déclencheurs
Définition :
Les triggers sont des procédures stockées qui s’exécutent automatiquement quand un
événement se produit. En général cet événement représente une opération DML (Data
Manipulation Language ) sur une table. Les instructions DML doivent inclure INSERT,
UPDATE ou DELETE

Rôle des triggers :

• Contrôler les accès à la base de données
• Assurer l’intégrité des données
• Garantir l’intégrité référentielle (DELETE, ou UPDATE CASCADE)
• Tenir un journal des logs.

Même si les triggers jouent un rôle important pour une base de données, il n’est pas
conseillé d’en créer trop. Certains triggers peuvent rentrer en conflit, ce qui rend
l’utilisation des tables impossible pour les mises à jour.

Syntaxe simplifiée pour créer un trigger avec une opération DML

Syntaxe simplifiée :

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name

ON { table | view }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement }

AFTER spécifie que le déclencheur DML est déclenché uniquement lorsque toutes les
opérations spécifiées dans l'instruction SQL ont été exécutées avec succès.

Un trigger utilisant AFTER va effectuer l’opération DML même si celle-ci n’est pas valide,
un message erreur est quand même envoyé.

54
Saliha Yacoub
Utilisez les triggers AFTER avec une ROLLBACK TRANSACTION

Le FOR fait la même chose que AFTER, donc il va quand même insérer ou mettre à jour.
Par défaut on utilise AFTER.

INSTEAD OF indique un ensemble d’instructions SQL à exécuter à la place des

instructions SQL qui déclenche le trigger.

Au maximum, un déclencheur INSTEAD OF par instruction INSERT, UPDATE ou DELETE

peut être défini sur une table ou une vue. → Définir des vues pour des vues pour des
INSTEAD OF.

PAS de INSTEAD OF sur des vues avec l’option with CHECK OPTION.

Pour INSTEAD OF pas d’instruction DELETE sur des tables ayant l’option ON DELETE
CASCADE (idem pour UPDATE)

Principe de fonctionnement pour les triggers DML.

Lors de l’ajout d’un enregistrement pour un Trigger …INSERT, le SGBD prévoit de

récupérer l’information qui a été manipulée par l’utilisateur et qui a déclenché le
trigger. Cette information (INSERT ) est stockée dans une table temporaire appelée
INSERTED.
Lors de la suppression d’un enregistrement, DELETE, le SGBD fait la même chose en
stockant l’information qui a déclenché le trigger dans une table temporaire appelée
DELETED.
Lors, d’une mise à jour, UPDATE l’ancienne valeur est stockée dans la table DELETED et
la nouvelle valeur dans INSERTED.

Exemple 1, suppression en cascade

create trigger deletecascdeDet on departements

instead of delete as
begin
declare
@code char(3);
SELECT @code = deptno FROM deleted;
delete from EmpPermanent where deptno =@code;
delete from Departements where deptno=@code;
end;

55
Saliha Yacoub
Exemple 2

create TRIGGER ctrlSalairePermanent on EmpPermanent after update

as
declare
@ancienne money,
@nouvelle money;

BEGIN
select @ancienne = Salaire from deleted ;
select @nouvelle = Salaire from inserted;
IF (@ancienne > @nouvelle)
rollback;
RAISERROR (15600,-1,-1, 'pas bon salaire');

END;

Exemple 3

Le contenu de la table Emplois

Le contenu de la table EmployesBidon

Le trigger ci-dessous fait en sorte que les salaires des employés respectent la fourchette
des salaires définie dans la table Emplois.

56
Saliha Yacoub
CREATE TRIGGER CTRLSALAIRES on employesBidon
after INSERT, UPDATE as

DECLARE
@minsalaire money,
@maxsalaire money,
@newsalaire money;
BEGIN
SELECT @minsalaire = salaireMin from emplois WHERE typeemploi =
(select typeemploi from inserted);

SELECT @maxsalaire =salaireMax from emplois WHERE typeemploi =

(select typeemploi from inserted);

select @newsalaire = salaire from inserted;

if (@newsalaire<@minsalaire or @newsalaire>@maxsalaire)

rollback TRANSACTION;

else commit transaction;

end;

RAISERROR:

Génère un message erreur défini par l’utilisateur. Le message n’arrête pas le trigger (ce
n’est pas comme Raise_Application_error d’Oracle).

RAISERROR(id_message, sévérité ,État ,’Message’);

id_message, indique le numéro du message. Ce numéro doit être >50000. Lorsqu’il n’est
pas indiqué ce numéro vaut 5000.
Sévérité : indique le degré de gravité associé au trigger, ce niveau de gravité est défini
par l’utilisateurs. Ce nombre se situe entre 0 et 25. Les utilisateurs ne peuvent donner
que le nombre entre 0 et 18. Les nombre entre 19 et 25 sont réservés aux membres du
groupe sysadmin. Les nombre de 20 à 25 sont considérés comme fatals. Il est même
possible que la connexion à la BD soit interrompue.

57
Saliha Yacoub
Si ce nombre est négatif, il est ramené à 1.
Exemples :
Erreur : Sévérité
Duplication de Clé primaire 14
Problème de FK 16
Problème insertion (valeurs non conformes) 16
Violation de contrainte Check 16
Trigger DML 15 ou 16

Si vous prêtez attention aux messages erreurs renvoyés par le SGBD, vous constaterez
qu’ils se présentent sous la forme du RAISERROR vous pouvez vous baser sur ces
messages pour fixer le degré de sévérité.

État : utilisé lorsque la même erreur définie par l’utilisateur se retrouve à plusieurs
endroits, l’état qui est un numéro unique permet de retrouver la section du code ayant
générée l’erreur. L’état est un nombre entre 0 et 255. Les valeurs >255 ne sont pas
utilisées. Si négatifs alors ramenés à 0.
Exemples :
insert into EmpPermanent values(88,41111,12,'inf');
Ici, nous avons un problème de Forein key puisque le 88 n’est pas un dans la table
EmpClg. Pour la première fois, le niveau de sévérité est 16 est l’état est 0.

Vous pouvez également utiliser un try ---catch pour récupérer le message erreur
proprement : Dans le cas de l’exemple 2

58
Saliha Yacoub
use EmpclgDB;
begin try
begin transaction;
update EmpPermanent set Salaire =1 where empno =12;
commit transaction;
end try

begin catch
select ERROR_MESSAGE() as message, ERROR_SEVERITY() as Gravité,
ERROR_STATE() as etat,@@TRANCOUNT
if @@TRANCOUNT>0 rollback;
end catch;

Message : représente le message défini par l’utilisateur. Au maximum 2047 caractères.

Vous pouvez également laisser le soin au SGBDR d’utiliser ses propres paramètres.

Attention :
Les triggers sont définis sur une table , ce sont donc des objets de la table, tout comme
une colonne, une contrainte…

Activer /désactiver un trigger

Utiliser la commande DISABLE pour désactiver temporairement un trigger
DISABLE TRIGGER {[ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

Exemples :

disable trigger [dbo].[afterInsertemp] ,[dbo].[VerfiferInsert]

on [dbo].[EmpPermanent];
Un trigger désactivé va toujours exister dans le système mais ne fait rien. (sans action).
Dans Management Studio, il est marqué en rouge.

59
Saliha Yacoub
Pour réactiver votre trigger, utiliser la commande ENABLE. Cette commande a la même
syntaxe que la commande DISABLE.

Enable trigger [dbo].[VerfiferIbnsert] on [dbo].[EmpPermanent];

Supprimer un trigger.
Un trigger est un objet de la base de données, il faudra utiliser la commande DROP pour
le détruire.
DROP TRIGGER [ F EXISTS ] [schema_name.]trigger_name [ ,.n ] [; ]

Exemple :

DROP TRIGGER [dbo].[VerfiferInsert];

Retour sur la commande CREATE TABLE : ON DELETE CASCADE

Les triggers sont un bon moyen de contrôler l’intégrité référentielle ( → la Foreign KEY)
lors de la suppression d’un enregistrement référencé (ou des enregistrements
référencés). Si lors de votre conception, vous avez déterminé que les enregistrements
liés par la Foregin KEY doivent être supprimés car il s’agit d’un lien de composition,
comme dans le cas d’un livre et ses chapitres, c’est-à-dire que lorsqu’un livre est
supprimé alors tous les chapitres liés à ce livre doivent être également supprimé, ou
encore lorsqu’il s’agit d’une relation de généralisation, alors vous pouvez le faire à la
création de table.
Exemple
Voici la création de la table livres

60
Saliha Yacoub
create table livres
(
coteLivre char(5),
titre varchar(40) not null,
langue varchar(20) not null,
annee smallint not null,
nbPages smallint not null,
constraint pklivre primary key(coteLivre)
);
Voici la table Chapitres
create table Chapitres
(
idChapitre char(7) constraint pkChapitre primary key,
nomChapitre varchar(40) not null,
coteLivre char(5) not null,
constraint fkLivre foreign key (coteLivre)
references livres(coteLivre)ON DELETE CASCADE
)

Lorsqu’un livre (ou des livres) sont supprimés alors les chapitres de ce livre le sont aussi.

Attention :
La suppression en cascade à la création des tables n’est pas toujours recommandée sauf
si la conception l’exige.…

Pour tester :
---insertion dans livres--
begin transaction trans1
insert into livres values('IF001', 'Introduction à C#','Français',2017,650);
insert into livres values('IF002', 'SQL pour Oracle 12C','Français',2015,500);
insert into livres values('IF003', 'Oracle pour Java et PHP','Français',2016,700);
insert into livres values('IF004', 'Windows Server 2016','Anglais',2016,1100);
insert into livres values('MA001', 'Algébre Linéarie','Français',2013,400);
commit transaction trans1;

---insertion dans Chapitres

begin transaction trans2
insert into Chapitres values('IF00101','Pour bien commencer ','IF001');
insert into Chapitres values('IF00102','introduction à la POO ..','IF001');

61
Saliha Yacoub
insert into Chapitres values('IF00110','les tableaux ','IF001');
insert into Chapitres values('IF00201','Concepts de bases de données ','IF002');
insert into Chapitres values('IF00202','Create table ..','IF002');
insert into Chapitres values('IF00212','les indexs','IF002');
insert into Chapitres values('MA00101','introduction ','MA001');
insert into Chapitres values('MA00102','Les vecteurs','MA001');
insert into Chapitres values('MA0013','les matrices','MA001');
commit transaction trans2;

--pour tester
---en 1
begin transaction trans3;
delete from livres where coteLivre ='MA001' or coteLivre = 'IF002';
---en 2
rollback transaction trans3;

Maintenant, si votre conception initiale, ne doit pas faire de suppression en cascade

comme par exemple les employés et les départements, alors opter pour un trigger.

Exemple :

USE [EmpclgDB]
GO
/****** Object: Trigger [dbo].[deletecascdeDepartement] ****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[deletecascdeDeparetement] on
[dbo].[Departements]
instead of delete as
begin
declare
@code char(3);
SELECT @code = deptno FROM deleted;
update EmpPermanent set deptno = null where deptno =@code;
delete from Departements where deptno=@code;
end;

62
Saliha Yacoub
En conclusion :

1. Pour garantir l’intégrité de données en général et référentielle en particulier,

faîtes-le par la base de données au CREATE TABLE. Comme les PK, le FK, Les
Check…

2. Les triggers sont là pour renforcer l’intégrité des données. Leur avantage est
qu’on peut les désactiver au besoin. De plus ils s’exécutent automatiquement
(même s’ils sont oubliés).

3. Les procédures stockées sont un excellent moyen pour réduire les risques de
briser l’intégrité des données, à condition qu’elles soient utilisées.

4. À moins que ce soit obligé, évitez le ON DELETE CASCADE.

Bonnes pratiques pour les procédures STOCKÉES :

1. Éviter les SELECT*
2. Utilisez des transactions explicites : BEGIN /COMMIT TRANSACTION et
privilégiez des transactions courtes. Les transactions longues utilisent un
verrouillage plus long.
3. À partir de MS SQL server 2016 vous avez la fonction TRY…CATCH, utilisez là si
possible.

create procedure insertDept(@code char(3), @nom varchar(30)) as

begin
begin try
begin transaction;
insert into deparatements values(@code,@nom);
commit transaction;
end try
begin catch
if(@@TRANCOUNT>0)
rollback;
end catch;
end;

4. Privilégiez des jointures à la place de sous-requêtes.

5. Restreindre les résultats le plus tôt possible. (WHERE). Éviter des fonctions qui
retournent un trop gros nombre de données
6. Des procédures peuvent en appeler d’autres. Vous avez jusqu’à 32 niveaux
d’imbrications. Mais faîtes attention

63
Saliha Yacoub
Chapitre 8, les transactions
Notions de Transactions :
Une transaction est un bloc d’instructions DML exécutés et qui laisse la base de données
dans un état cohérent. Si une seule instruction dans le bloc n’est pas cohérente alors la
transaction est annulée, toutes les opérations DML sont annulées. Le principe de
transaction est implémenté dans tous les SGBDs.
Exemple :
begin transaction trans1;
insert into Departements values('dept', 'resources humaines');
update EmpPermanent set deptno ='inf' where empno=1;
update EmpPermanent set Salaire = 45000 where empno =1;
insert into Departements values('dept', 'resources humaines');
commit transaction trans1;
Le bloc d’instruction précédent ne va s’exécuter puisque nous avons un problème avec
le INSERT, la clé primaire est dupliquée.

Propriétés d’une transaction

Les transactions ont la propriété ACID
A : pour Atomicité :
Une transaction doit être une unité de travail indivisible ; soit toutes les modifications
de données sont effectuées, soit aucune ne l'est.
C : pour la Cohérence
Lorsqu'elle est terminée, une transaction doit laisser les données dans un état cohérent.
Dans une base de données relationnelle, toutes les règles doivent être appliquées aux
modifications apportées par la transaction, afin de conserver l'intégrité de toutes les
données.
Des fonctionnalités de gestion des transactions qui assurent l'atomicité et la cohérence
des transactions. Lorsqu'une transaction a débuté, elle doit se dérouler correctement
jusqu'à la fin (validée), sans quoi l'instance du Moteur de base de données annule
toutes les modifications effectuées sur les données depuis le début de la transaction.
Cette opération est appelée restauration d'une transaction, car elle retourne les
données telles qu'elles étaient avant ces modifications.

64
Saliha Yacoub
I : pour Isolement
Les modifications effectuées par des transactions concurrentes doivent être isolées
transaction par transaction. Une transaction reconnaît les données dans l'état où elles
se trouvaient avant d'être modifiées par une transaction simultanée, ou les reconnaît
une fois que la deuxième transaction est terminée, mais ne reconnaît jamais un état
intermédiaire.
Des fonctionnalités de verrouillage (verrou ou LOCK) permettant d'assurer l'isolement
des transactions.
D : Durabilité
Lorsqu'une transaction durable est terminée, ses effets sur le système sont permanents.
Les modifications sont conservées même en cas de défaillance du système
Des fonctionnalités de consignation assurent la durabilité des transactions. Pour les
transactions durables, l'enregistrement du journal est renforcé sur le disque avant les
validations des transactions. Ainsi, en cas de défaillance du matériel serveur, du système
d’exploitation ou de l’instance du Moteur de base de données lui-même, l’instance
utilise au redémarrage les journaux des transactions pour restaurer automatiquement
toutes les transactions incomplètes jusqu’au moment de la défaillance du système

Pour SQL SERVER certaines transactions sont atomique et donc auto-commit, instruction
individuelle qui n’ont pas de BEGIN Transaction.

D’autres transaction sont explicites, dans ce cas elle commence par un : BEGIN
TRANSACTION et se termine par un COMMIT Transaction ou un ROLLBACK.
BEGIN TRANSACTION : est comme un point, ou un état où les données référencées par
une connexion sont cohérentes logiquement et physiquement. En cas d'erreur, toutes
les modifications de données effectuées après BEGIN TRANSACTION peuvent être
annulées pour ramener les données à cet état de cohérence connu. Chaque transaction
dure jusqu’à ce qu’elle soit terminée proprement par un COMMIT ou par un ROLLBACK ;

À chaque BEGIN TRANSACTION, le système incrémente la variable @@TRANCOUNT de

1. Cette variable système retourne le nombre de BEGIN Transaction exécuté pendant la
65
Saliha Yacoub
connexion en cours. Lorsqu’une transaction est comité (COMMIT) alors
@@TRANCOUNT décrémente de 1. Le ROLLBACK TRANSACTION décrémente la variable
@@TRANCOUNT jusqu’à 0. (La base de données est dans un état cohérent)

Récupération d’une transaction

Une transaction débute par un begin transaction et termine par un commit ou un rollback.
L’opération commit détermine le point ou la base de données est de nouveau cohérente.
L’opération rollback annule toutes les opérations et retourne la base de données dans l’état où
elle était au moment du begin transaction, donc du dernier commit.

Une transaction n’est pas uniquement une unité logique de traitement des données, c’est aussi
une unité de récupération.

Après qu’une transaction ait terminé avec succès (commit) le SGBDR garantit que les
changements seront permanents dans la BD. Cela ne veut pas dire, cependant, que les
changements ont été écrits sur le disque dans le fichier physique de la BD. Ils peuvent être
encore seulement dans la mémoire de l’ordinateur.

Supposons que 1 seconde après le commit et avant que les changements soient écrits sur le
disque, une panne électrique vient tout effacer le contenu de la mémoire et en même temps les
changements tout juste ‘comités’.

Dans une telle situation, le SGBDR sera quand même capable, au redémarrage, de poursuivre la
mise à jour en récupérant la transaction des journaux. Cela est possible à cause d’une règle qui
stipule que les journaux sont physiquement sauvegardés sur le disque avant que le commit
complète.

Cette double sauvegarde ou redondance des données permet de récupérer non seulement une
transaction, mais une BD complète advenant une panne du disque.

Récupération complète de la base de données

Au moment d’une panne électrique ou d’une panne d’ordinateur, le contenu de la mémoire est
perdu. L’état des transactions en cours est perdu. Les transactions complétées, mais non écrites
sont disponibles dans les journaux.

Au moment du redémarrage du SGBDR, toutes les transactions qui n’ont pas complété seront
annulées. Celles qui n’ont pas été sauvegardées dans la BD seront rejouées à partir des
journaux.

66
Saliha Yacoub
À intervalle régulier le SGBDR sauvegarde le contenu de ses structures de données en mémoire
dans le fichier physique de la BD. Au même moment un enregistrement ‘CheckPoint’ est ajouté
au journal indiquant que toutes les transactions complétées avant le CP sont contenues dans la
BD sur le disque.

Pour déterminer quelles transactions seront annulées et quelles transactions seront rejouées, le
SGBDR utilise cet enregistrement CP dans le journal.

Supposons la situation suivante

Temps → tcp tp
T1
T2
T3
T4
T5
CheckPoint Panne
Figure 1: États de 5 transactions au moment de la panne dans le journal des transactions

• Le SGBDR tombe en panne au temps tp;

• Le ‘CheckPoint’ le plus récent avant la panne est au temps tcp;
• T1 a complété avant tcp; donc sauvegardé dans le fichier;
• T2 a débuté avant tcp et a complété après, mais avant la pane à tp; donc pas écrit dans
le fichier;
• T3 a débuté avant tcp mais n’a pas complété avant la panne à tp;
• T4 a débuté et complété après tcp; pas écrit dans le fichier;
• T5 a débuté après tcp mais n’a pas complété avant la panne à tp;

Transactions concurrentes
Un SGBDR permet à plusieurs transactions d’accéder la même information en même temps.
Pour éviter que les transactions interfèrent l’une avec l’autre, des mécanismes sont nécessaires
pour contrôler l’accès aux données.

Transaction A temps Transaction B

A(a = 40) A(a = 40)

Update A set A.a = A.a – 20 t1

where …;

t2 Update A set A.a = A.a – 5

where ….
Commit A(a = 20)
Commit A(a = 15)

67
Saliha Yacoub
Perte de mise à jour

Transaction A temps Transaction B

A(a = 40) A(a = 40)

select @v = A.a from A where … t1

@v = @v – 20 (@v == 20)

t2 select @v = A.a from A where ….

@v = @v – 10 (@v = 30)

If @v <= 15 rollback If @v <= 15 rollback

Update A set A.a = @v where …; t3

t4 Update A set A.a = @v where ….

Commit A(a = 20) t5

t6 Commit A(a = 30) → A(a = 10)

Les verrous

Le verrouillage est un mécanisme utilisé par le Moteur de base de données SQL Server
pour synchroniser l'accès simultané de plusieurs utilisateurs à la même donnée.

Avant qu'une transaction acquière une dépendance sur l'état actuel d'un élément de
données, par exemple par sa lecture ou la modification d'une donnée, elle doit se
protéger des effets d'une autre transaction qui modifie la même donnée. Pour ce faire,
la transaction demande un verrou sur l'élément de données. Le verrou possède
plusieurs modes, par exemple partagé ou exclusif. Le mode de verrouillage définit le
niveau de dépendance de la transaction sur les données

Le tableau suivant illustre les modes de verrouillage des ressources utilisés par le
Moteur de base de données.

68
Saliha Yacoub
 Mode de verrouillage Description
Partagé (S) Utilisé pour les opérations de lecture qui n'effectuent
aucune modification ou mise à jour des données, par
exemple une instruction SELECT
Mise à jour (U) Utilisé pour les ressources pouvant être mises à jour.
Empêche une forme de blocage courante qui se produit
lorsque plusieurs sessions lisent, verrouillent et mettent à
jour des ressources ultérieurement.
Exclusif(X) Utilisé par les opérations de modification de données,
telles que INSERT, UPDATE ou DELETE. Empêche des mises
à jour multiples sur la même ressource au même moment.

69
Saliha Yacoub
Chapitre 9, optimisation de requêtes
Introduction.
En principe, lorsqu’une requête SQL est envoyée au SGBD, celui-ci établit un plan
d’exécution. Le module se charge d’établir un plan d’exécution s’appelle Optimizer.

Le fonctionnement de l’Optimizer globalement similaire pour l’ensemble des SGBDs

(Oracle et SQL Server), en utilisant les étapes suivantes :

1. Validation syntaxique
2. Validation sémantique
3. Utilisation éventuelle d’un plan précédemment produit
4. Réécriture/Simplification de la requête
5. Exploration des chemins d’accès et estimation des coûts.
6. Désignation du chemin le moins coûteux, génération du plan d’exécution et mise
en cache de ce dernier.

Les index

Un index est un objet de la base de données permettant d’accélérer l’accès aux

données. C’est un peu comme un code postal qui permet à un facteur de retrouver une
adresse rapidement ou comme une recherche de livres dans une bibliothèque ou alors
comme une recherche d’information dans un livre, voir la table d’index à la fin. Le
principe est d’aller directement à l’information souhaitée dans le cas d’un livre plutôt
que de lire le livre au complet de manière séquentielle pour trouver l’information
recherchée.
Le principe de recherche dans un index se fait un peu comme dans un B-Arbre un arbre
parfaitement équilibré.

70
Saliha Yacoub
Le rôle d’un index est d’accélérer la recherche d’information (lors d’un SELECT) dans une
base une base de données.

Par défaut, TOUS les SGBD entretiennent un index primaire qui est l’index crée sur la clé
primaire. Cependant les développeurs peuvent décider de créer d’autres index sur des
colonnes qui ne sont pas des PK.
• Créer des index sur les colonnes de Foreign KEY pour accélérer les jointures,
sauf si la combinaison de FK forme une clé primaire (redondance d’index).
• Créer des index sur les colonnes de la clause de la clause WHERE sauf si le
WHERE contient un like de fin (WHERE nom like ‘%CHE’), ou si le WHERE
contient une fonction.
• Créer des index sur des colonnes utilisées dans un ORDER BY, un GROUP BY,
un HAVING.
• Créer des index sur une colonne ayant une petite plage de valeurs inutiles.
(NULL)
• Créer des index une fois que les insertions sont complétées.

Attention :
Même si les indexs sont des accélérateurs, trop d’index ralenti le SGBD. Il ne faudrait
pas que le SGBD passe son temps à maintenir TOUS les index.
Les index ralentissent le système durant les insertions, car la table des index doit être
mis à jour.

71
Saliha Yacoub
Types d’index :
MS SQL server manipule deux types d’index : CLUSTERED index et les NON CLUSTERD
index
Les CLUSTERED INDEX :
Il existe un seul CLUSTERED index par table. Ces index stockent les lignes de données de
la table en fonction de leurs valeurs de clé. Les index clustérisés trient et stockent les
lignes de données dans la table ou la vue en fonction de leurs valeurs de clé
En principe, toutes les tables devraient avoir un index cluster défini sur la ou les
colonnes ayant la propriété d’unicité ou de clé primaire. Par défaut lorsque SQL server
crée une table avec clé primaire, il y ajoute un CLUSTERD index .
Exemple, remarquez la table joueurs suivantes créé avec un index Cluster sur la clé
primaire. Cet index est créé à la création de la table joueurs dès que la clé primaire a
été indiquée.
L’index non cluster (vert) a été rajouté par le développeur

Si vous voulez mettre un autre index Cluster sur votre table il faudra :
1. À la création de table indique que la PK n’est pas un index Cluster

create table personnages(id int identity(1,1) not null primary

key nonclustered,
alias varchar(10) NOT NULL,
nom varchar(30) not null,
descriptions varchar(60) not null,
typ char(1) not null
);

2. Créer un Index Cluster sur la colonne que vous souhaitez.

72
Saliha Yacoub
CREATE CLUSTERED INDEX INDXALIAS ON personnages(ALIAS);

Sur quelles colonnes est-ce qu’il est conseillé de créer des index Clustérisés ?

• Des colonnes avec des valeurs uniques ou très peu de valeurs identiques.
• Colonne définie avec IDENTITY
• Colonnes fréquemment utilisées pour trier (ORDER BY) les données extraites
d’une table.
• Colonne avec accès séquentiel mais avec un where between, car un ordre est
spécifié.
Pour quels types de requêtes un index Clustérisé serait conseillé ?

• Requêtes avec qui retournes une plage de valeurs : WHERE >, WHERE <,
WHERE BETWEEN ..
• Retourne un résultat volumineux
• Pour les jointures
• Order by et group by

Éviter les index sur les colonnes :

• Très sujettes au changement : UPDATE

• Les clés étendues (clé composée et de types varchar)
Principe :
Dans les index Clustérisés le système est organisé sous forme d’arborescence binaire
parfaitement équilibré. B-Arbre. Le parcours de l’arbre est suffisant pour obtenir toute
l’information désirée. Voir exemple plus bas.

73
Saliha Yacoub
 empno PageId
1 101
10 102

empno PageId empno PageId

1 105 10 107
5 106 13 108

empno nom Prn empno nom Prn empno nom Prn empno nom Prn
1 Patoc aa 5 Yag ss 10 vlad jj 13 Pic az
2 Roy bb 6 Beta vv 11 ruby so 14 pac ab
3 Bien cc 7 Alp ww 12 mos pp
4 Bla dd 8 Ali aa Page 109
Page 107
9 Boom yy
Page 105
Page 106

Les index non CLUSTERED INDEX :

Un index non-cluster contient les valeurs de clé d'index et les localisateurs de ligne qui
pointent vers l'emplacement de stockage des données de table.
Vous pouvez créer plusieurs index non cluster sur une table ou une vue indexée.
Les index non-cluster doivent, en principe, améliorer les performances des requêtes
fréquemment utilisées qui ne sont pas couvertes par l'index cluster.

74
Saliha Yacoub
La commande CREATE INDEX

Pour les index non cluster :

CREATE INDEX nomIndex ON nomTable(nomColonne);

Exemple :

CREATE ONDEX typeIndex ON empClg(typeEmp);

Pour les index cluster :

CREATE CLUSTERED INDEX nomIndex ON nomTable(nomColonne);

Exemple

CREATE CLUSTERED INDEX INDXALIAS ON personnages(ALIAS);

En général :

CREATE [CLUSTERED] INDEX nom_de_index ON nom_table (nom_colonne)

CREATE INDEX nom_de_index ON nom_table (nom_colonne)

ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte

PRIMARY KEY (nom_colonne)

ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte

PRIMARY KEY NONCLUSTERED (nom_colonne)

ALTER TABLE nom_table ADD CONSTRAINT nom_constrainte

UNIQUE (nom_colonne)

ALTER TABLE nom_table ADD CONSTRAINT nom_constrainte

UNIQUE [CLUSTERED] (nom_colonne)

75
Saliha Yacoub
Suppression d’un index

DROP INDEX nom_index ON nom_table

Afficher les index définis sur une table

EXEC sys.sp_helpindex @objname = 'nom_table'

Outils de mesures des performances

Entrer la commande suivante pour activer les mesures des performances des requêtes
SET STATISTICS IO, TIME ON | OFF

On peut activer l’affichage du plan d’exécution des requêtes avec la commande Ctrl-M dans MS
SQL Studio.

Règles d’optimisation de requêtes :

Lorsque vous écrivez vos requêtes, même si les SGBDs ont des optimiseurs, voici
quelques règles à respecter pour optimiser vos requêtes. (qui ne sont pas
nécessairement d’ans l’ordre).
– R1 : Éviter le SELECT * : écrire plutôt le nom des colonnes dont vous avez besoin
pour la requête.

– R2 : Créez des indexes sur les colonnes que vous utilisez dans la clause WHERE.
Pour plus de performances, ces indexes doivent-être créés après l’insertion des
données dans la table.

– R3 : Lorsque c’est possible, utilisez le WHERE à la place du Having.

– R4 : Éviter les jointures dans le WHERE, utilisez plutôt le INNER JOIN.

– R5 : Lorsque c’est possible, utilisez une jointure à la place d’une sous-requête.

Les jointures sont l’essentiel des SGBDRs alors ils sont optimisés pour l’écriture
des jointures.

76
Saliha Yacoub
Chapitre 10, introduction à la sécurité de
données
Introduction
Aucune méthode universelle n'existe pour créer une application cliente SQL Server
sécurisée. Chaque application est unique au niveau de sa configuration, de son
environnement de déploiement et de ses utilisateurs. Une application relativement
sécurisée lors de son déploiement initial peut devenir moins sécurisée avec le temps. Il
est impossible d'anticiper avec précision sur les menaces qui peuvent survenir dans le
futur.

Menaces courantes :
Les développeurs doivent connaître les menaces de sécurité, les outils disponibles pour
les contrer et la manière d'éviter les défaillances de sécurité qu'ils se créent eux-mêmes.
La sécurité peut être envisagée comme une chaîne dans laquelle un maillon manquant
compromet la solidité de l'ensemble. La liste suivante comprend quelques menaces de
sécurité courantes évoquées plus en détail dans les rubriques de cette section.
Injection SQL
L'injection SQL est le processus qui permet à un utilisateur malveillant d'entrer des
instructions Transact-SQL au lieu d'une entrée valide. Si l’entrée est transmise
directement au serveur sans validation et si l’application exécute accidentellement le
code injecté, l’attaque risque d’endommager ou de détruire des données.

Important :
Vous pouvez déjouer les attaques d'injection SQL Server à l'aide de procédures
stockées et de commandes paramétrées, en évitant le code SQL dynamique et en
limitant les autorisations de tous les utilisateurs :
Validez TOUTES les entrées.

Les Injection SQL peuvent se produire en modifiant une requête de façon à ce qu’elle
soit toujours exécutée (retourne toujours vrai) en changeant la clause WHERE ou avec
un opérateur UNION

77
Saliha Yacoub
Exemples:
SELECT * from utilisateurs where nom = @nom, en [Link]
SELECT * from utilisateurs where nom = ? en PDO
En théorie cette requête ramène les informations (mot de passe) d’un utilisateur dont le
nom est en paramètre, donc seules les personnes connaissant la valeur du paramètre
nom pourront chercher les informations correspondantes

Imaginez maintenant que quelqu’un soit malintentionné remplace la requête par:

SELECT * from utilisateurs where nom =‘Patoche’ OR 1=1;
Comme 1=1 est tout le temps vrai, alors la requête va renvoyer les informations de tous
les utilisateurs.
Ou encore
SELECT Description FROM produits
WHERE Description like '%Chaises'
UNION ALL
SELECT username FROM dba_users
WHERE username like '%'

Si les deux requêtes précédentes étaient dans des procédures stockées, le problème ne
se serait pas posé. La validation des entrées est ESSENTIELLE.

Élévation de privilège :
Les attaques d'élévation de privilège se produisent lorsqu'un utilisateur s'empare des
privilèges d'un compte approuvé, un administrateur ou un propriétaire par exemple.
Exécutez toujours le code sous des comptes d'utilisateurs disposant des privilèges
minimums et attribuez uniquement les autorisations nécessaires

78
Saliha Yacoub
Attention :
Évitez l'utilisation des comptes d'administrateur (comme Sa pour SQL Server, root pour
MySQL et system pour Oracle) pour l'exécution du code.
Supprimer les comptes utilisateurs non utilisés
Supprimer les comptes utilisateurs par défaut
Donnez les privilèges selon les besoins.

Détection des attaques et surveillance intelligente

Une attaque de détection peut utiliser des messages d'erreur générés par une
application pour rechercher des vulnérabilités dans la sécurité. Implémentez la gestion
des erreurs dans tout le code de procédure pour éviter de retourner des informations
d'erreurs SQL Server à l'utilisateur final.

Attention :
Ne pas afficher de messages d'erreur explicites affichant la requête ou une partie de la
requête SQL. Personnalisez vos messages erreur.

Mots de passe
De nombreuses attaques réussissent lorsqu'un intrus a su deviner ou se procurer le mot
de passe d'un utilisateur privilégié. Les mots de passe représentent la première ligne de
défense contre les intrus, la définition de mots de passe forts est donc un élément
essentiel de la sécurité de votre système. Créez et appliquez des stratégies de mot de
passe pour l'authentification en mode mixte.

Attention :
Renforcer les mots de passe.

Utilisez la stratégie des mots de passe pour les comptes sa, root et system.

Supprimer les comptes sans mot de passe.

79
Saliha Yacoub
Rôles du serveur :
SQL Server fournit des rôles au niveau du serveur pour vous aider à gérer les
autorisations sur les serveurs
SQL Server fournit neuf rôles serveur fixes. Les autorisations accordées aux rôles serveur
fixes (à l’exception de public) ne peuvent pas être changées.
Les rôles du serveur sont attribués aux connexions

Rôles Description
sysadmin Les membres du rôle serveur fixe sysadmin peuvent effectuer
n’importe quelle activité sur le serveur.
serveradmin Les membres du rôle serveur fixe serveradmin peuvent modifier
les options de configuration à l’échelle du serveur et arrêter le
serveur.
securityadmin Les membres du rôle serveur fixe securityadmin gèrent les
connexions et leurs propriétés. Ils peuvent attribuer des
autorisations GRANT, DENY et REVOKE au niveau du serveur. Ils
peuvent également attribuer des
autorisations GRANT, DENY et REVOKE au niveau de la base de
données, s’ils ont accès à une base de données. En outre, ils
peuvent réinitialiser les mots de passe pour les connexions SQL
Server .
processadmin Les membres du rôle serveur fixe processadmin peuvent mettre
fin aux processus en cours d’exécution dans une instance de SQL
Server.
setupadmin Les membres du rôle serveur fixe setupadmin peuvent ajouter et
supprimer des serveurs liés à l’aide d’instructions Transact-
SQL. (L’appartenance au rôlesysadmin est nécessaire pour
utiliser Management Studio.)
bulkadmin Les membres du rôle serveur fixe bulkadmin peuvent exécuter
l’instructionBULK INSERT.
diskadmin Le rôle serveur fixe diskadmin permet de gérer les fichiers disque.
dbcreator Les membres du rôle serveur fixe dbcreator peuvent créer,
modifier, supprimer et restaurer n’importe quelle base de
données.
public Chaque connexion SQL Server appartient au rôle
serveur public. Lorsqu'un principal de serveur ne s'est pas vu
accorder ou refuser des autorisations spécifiques sur un objet
sécurisable, l'utilisateur hérite des autorisations accordées à
public sur cet objet. Vous ne devez affecter des autorisations
publiques à un objet que lorsque vous souhaitez que ce dernier

80
Saliha Yacoub
 soit disponible pour tous les utilisateurs. Vous ne pouvez pas
modifier l’appartenance au rôle public.

Rôles niveau bases de données :

Les rôles niveau base de données sont attribués à un utilisateur mappé sur la connexion

Roles Description

db_owner Les membres du rôle de base de données

fixe db_owner peuvent effectuer toutes les activités de
configuration et de maintenance sur la base de données
et peuvent également supprimer la base de données
dans SQL Server. (Dans SQL Database et SQL Data
Warehouse, certaines activités de maintenance
requièrent des autorisations de niveau serveur et ne
peuvent pas être effectuées par db_owners.)

db_securityadmin Les membres du rôle de base de données

fixe db_securityadmin peuvent modifier l’appartenance
au rôle pour les rôles personnalisés uniquement et gérer
les autorisations. Les membres de ce rôle peuvent
potentiellement élever leurs privilèges et leurs actions
doivent être supervisées.

db_accessadmin Les membres du rôle de base de données

fixe db_accessadmin peuvent ajouter ou supprimer
l'accès à la base de données des connexions Windows,
des groupes Windows et des connexions SQL Server.

db_backupoperator Les membres du rôle de base de données

fixe db_backupoperator peuvent sauvegarder la base de
données.

db_ddladmin Les membres du rôle de base de données

fixe db_ddladmin peuvent exécuter n'importe quelle
commande DDL (Data Definition Language) dans une base
de données.

81
Saliha Yacoub
 db_datawriter Les membres du rôle de base de données
fixe db_datawriter peuvent ajouter, supprimer et
modifier des données dans toutes les tables utilisateur.

db_datareader Les membres du rôle de base de données

fixe db_datareaderpeuvent lire toutes les données de
toutes les tables utilisateur.

db_denydatawriter Les membres du rôle de base de données

fixe db_denydatawriter ne peuvent ajouter, modifier ou
supprimer aucune donnée des tables utilisateur d'une
base de données.

db_denydatareader Les membres du rôle de base de données

fixe db_denydatareader ne peuvent lire aucune donnée
des tables utilisateur d'une base de données.

Privilèges sur les objets (tables, colonnes, lignes) :

Par l’interface SQL Server Management Studio :
On suit les mêmes étapes pour créer une connexion, puis avant de cliquer sur OK, il
faudra mapper un utilisateur à cette connexion
L’utilisateur mappé a le même nom que la connexion.
En général, les privilèges sont accordés aux utilisateurs (non aux connexions).
Un utilisateur utilise un login pour se connecter et il est rattaché à une base de données.
Sinon, l’utilisateur est dit orphelin.

82
Saliha Yacoub
Dans cette figure on voit que :

• La connexion interfaceConnexion est mappée sur un utilisateur de même nom.

• La base de données de l’utilisateur est EmpclgDb
• Le role BD de l’utilisateur est public. Un role public ne donne aucun droit sur la
BD. L’utilisateur peut faire un USE EmpclgDb et rien d’autre.
Si on clique pour générer le script on aura le script suivant :
USE [master]
GO
CREATE LOGIN [interfaceConnexion] WITH PASSWORD=N'123456',
DEFAULT_DATABASE=[EmpclgDB], CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
USE [EmpclgDB]
GO
CREATE USER [interfaceConnexion] FOR LOGIN [interfaceConnexion]
GO

83
Saliha Yacoub
Voici le script obtenu pour une connexion AdoConnexion par l’interface Management
Studio
USE [master]
GO
CREATE LOGIN [AdoConnexion] WITH PASSWORD=N'Local$33',
DEFAULT_DATABASE=[ adoExemple], CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
GO
USE [adoExemple]
GO
CREATE USER [AdoConnexion] FOR LOGIN [AdoConnexion]
GO
USE [adoExemple]
GO
ALTER ROLE [db_datareader] ADD MEMBER [AdoConnexion]
GO
USE [adoExemple]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [AdoConnexion]
GO

84
Saliha Yacoub
L’usager AdoConnexion a le droit de faire select, update, insert et Delete sur toutes les
table de la base de données adoExemple. Mais, il ne peut pas créer des objets ou les
altérer
Avec la connexion AdoConnexion, on peut faire :
select * from etudiants;
select * from programmes;
insert into programmes values (200,'Mathématiques');
update programmes set nom_programme ='Art moderne' where
code_prog =412;
delete from etudiants where numad=1;

Avec la connexion AdoConnexion, on NE peut PAS faire :

create table cours (codeCours char(3) not null,
titre_cours varchar(30)not null,
constraint pk_cours primary key(codeCours));

Question : Quel est le rôle Base de données qu’on aurait dû attribuer à AdoConnexion
pour que l’usager puisse créer la table cours ?

Avec les commandes SQL

La commande CREATE LOGIN vous permet de créer une connexion (ce que nous avons
fait avec l’interface graphique au début de la session.).
Évidemment, on pourra donner des ROLE sur le serveur à ce login, on y reviendra plus
loin.
La commande CREATE USER …nomUser . FOR LOGIN nomDuLogin permet de créer un
utilisateur pour le login.
Si au moment de créer l’utilisateur, aucune base de données n’a été sélectionnée, on
dira que l’utilisateur est orphelin.
Exemple : (vous devez avoir le role sysadmin)
create login logPatoche with password ='alainPatoche$33';

create user PatocheUser for login logPatoche;

85
Saliha Yacoub
PatocheUser est un utilisateur orphelin. Aucun accès à aucune BD.

Pour créer un utilisateur non orphelin, donc rattachée à une base de données
EmpclgDB par exemple, il faudra :
1. Avoir le role sysadmin
2. Faire un USE sur la BD EmpclgDB.

En faisant use EmpclgDB, puis CREATE USER, l’utilisateur crée est rattaché à la BD
BdGestion. Mais il n’a aucun droit sur aucun objet de la BD.

Exemple :

use EmpclgDB;
create login logPatoche with password ='alainPatoche$33';
create user PatocheUser for login logPatoche;

Attribution des roles

ALTER ROLE [db_datareader] ADD MEMBER [PatocheUser];
ALTER ROLE [db_datawriter] ADD MEMBER [PatocheUser];
ALTER ROLE [db_ddladmin] ADD MEMBER [PatocheUser];

86
Saliha Yacoub
Avec ces role l’utilisateur PatocheUser peut faire, entre autres :

select * from livres;

update livres set titre ='Comptabilité' where coteLivre ='IF004';

insert into livres values('IM03','Ce livre','Italien',2017,890);

create table TabledePatoche

(
id_Personne int identity(1,1) ,
nom varchar(20) not null,
constraint pk_personnne primary key (id_Personne)
);

insert into TabledePatoche values('Patoche');

Attention :
Lorsque vous donnez des droits sur la base de données, ces droits s’appliquent à toutes
les table de votre base de données.

87
Saliha Yacoub
Attention :
Les droits de PatocheUser se limitent aux TABLES est non aux procédures stockées.

Ce qui veut dire que PatocheUser peut faire ceci :

update empClg set prenom = 'le roy' where nom ='aa';

Et Ne peut PAS faire (Car il n’a pas le droit).

execute majPrenom
@nom ='aa',
@prenom ='Le roy';

Si on veut donner plus de privilèges à PatocheUser, on peut procéder avec la commande

GRANT

grant execute on majPrenom to PatocheUser;

grant execute to PatocheUser; permet à PatocheUser d’executer n’importe quelle

procédure.

Les commandes GRANT, REVOKE et DENY

La command GRANT, syntaxe simplifiée
La commande GRANT permet d’attribuer des privilèges ou des permissions à un
utilisateur sur un objet. Au lieu de donner des droits sur toutes les tables de la base de
données par attribution de ROLE, on utilise la commande GRANT pour cibler les objets
de la base de données qui seront affectés et restreindre les privilèges sur les objets
ciblés pour les utilisateurs.

Exemple, nous souhaitons donner le droit SLECT sur notre table Questions à un autre
utilisateur, qui est UserSimba. UserSimba est un utilisateur lié à une connexion et une
base de données avec aucun role sur le serveur ni sur la base de données (il a le role
Public) car il a été créé comme suit :
use BdJeu;
create login logSimba with password ='Simbaleroy$22';
create user SimbaUser for login logSimba;

88
Saliha Yacoub
On peut cependant permettre certaines actions sur les table pour le user UserSimab

Syntaxe simplifiée de la commande GRANT

GRANT { ALL [ PRIVILEGES ] }
| permission [ ( column [ ,...n ] ) ] [ ,...n ]
[ ON [ class :: ] securable ] TO principal [ ,...n ]
[ WITH GRANT OPTION ] [ AS principal ]

Le ALL est à déconseiller. Il vaut mieux attribuer les autorisations ou les privilèges au
besoin

• Si l'élément sécurisable est une fonction scalaire, ALL représente EXECUTE et

REFERENCES.
• Si l'élément sécurisable est une fonction table, ALL représente DELETE, INSERT,
REFERENCES, SELECT et UPDATE.
• Si l'élément sécurisable est une procédure stockée, ALL représente EXECUTE.
• Si l'élément sécurisable est une table, ALL représente DELETE, INSERT,
REFERENCES, SELECT et UPDATE.
• Si l'élément sécurisable est une vue, ALL représente DELETE, INSERT,
REFERENCES, SELECT et UPDATE.

Exemples :

89
Saliha Yacoub
Grant select, insert on categories to SimbaUser;
grant select, insert, update(enonce) on Questions to SimbaUser;
grant select on personnes to SimbaUser with grant option;

WITH GRANT OPTION, signifie que l’utilisateur qui a reçu le privilège peu donner le même
privilège à un autre utilisateur.

Les roles creés par les utilisateurs. (pas ceux prédéfinis).

On peut créer de ROLES. Un role va regrouper plusieurs privilèges . l’avantage d’avoir

des roles, c’est de donner le même roles à plusieurs utilisateurs. De plus..au lieu
d’ajouter un GRANT pour un user, il suffit de l’appliquer au ROLE.

Situation : vous êtes une équipe de 10, donc 10 users à travailler sur le même projet.
Vous utilisez donc des tables de ce projets. Ces tables ne vous appartiennent pas mais
vous y avez accès avec des autorisations

En tant que propriétaire de la BD BdJeu :

use BdJeu;
create role roleprojet;
Grant select, insert on categories to roleprojet;
grant select, insert, update(enonce) on Questions to roleprojet;

Je viens de créer un role roleprojet avec un certain nombre de droits.

Tous ce que nous avon à faire c’est d’affecter le role aux usager qu’on veut.

EXEC sp_addrolemember roleprojet, RubyUser;

EXEC sp_addrolemember roleprojet, RemiUser;

Maintenant… imaginez que vous avez oublié de donner le privillège SELECT sur la table
joueurs pour les 10 users de l’équipe de projet. Comment allez-vous faire ? et surtout
comment ne pas oublier aucun utilisateur ?

90
Saliha Yacoub
Il suffit de faire un GRANT pour votre role. De cette façon, tous les users qui on eu le
role se verront GRANTÉ, le privilège.

grant select on joueurs to roleprojet;

La commande REVOKE.
La commande REVOKE permet de retirer des droits. (Des privilèges) . En principe les
privilèges ont été attribuer par la commande GRANT
Syntaxe :
REVOKE [ GRANT OPTION FOR ] <permission> [ ,...n ] ON
[ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n
] ) ]
{ FROM | TO } <database_principal> [ ,...n ]
[ CASCADE ]
[ AS <database_principal> ]

Exemple :
revoke insert on Categories from SimbaUser;

La commande DENY
Il arrive qu’un utilisateur, ait hérité des droits car il est membre d’un role. Une façon de
ne pas autoriser (d’interdire ) l’utilisateur en question à ne pas faires certaine
opérations c’est avec la commande DENY
Syntaxe :
DENY { ALL [ PRIVILEGES ] }
| <permission> [ ( column [ ,...n ] ) ] [ ,...n ]
[ ON [ <class> :: ] securable ]
TO principal [ ,...n ]
[ CASCADE] [ AS principal ]
[;]

91
Saliha Yacoub
Les vues pour la sécurité des données : contrôle sur les lignes
Nous avons abordé les vues comme étant des objets de la base de données permettant
la simplification de requêtes. Dans ce qui suit, nous allons voir comment les vues
peuvent contribuer à la sécurité des données.
Les vues permettent de protéger l’accès aux tables en fonction de chacun des
utilisateurs. On utilise une vue sur une table et on interdit l’accès aux tables. C’est donc
un moyen efficace de protéger les données.

CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name

AS select_statement
[ WITH CHECK OPTION ]
[ ; ]

L’option WITH CHECK OPTION permet d’assurer que les modifications apportées à la
table (dont la vue est issue) via la vue respectent la CLAUSE WHERE.
Lorsqu’une ligne est modifiée via la vue, alors les données devraient rester cohérentes.

Exemple :

create view VSport as select * from questions where

code_categorie =3 with check option;

grant select, update, insert on Vsport to user1;

L’instruction suivante exécutée par le user1 va marcher car le

code catégorie est 3:

insert into VSport values('une question',1,'facile',3);

L’instruction suivante exécutée par le user1 NE va PAS

marcher car le code catégorie est 2. Ne respecte pas la clause
WHERE:

insert into VSport values('une autre question',1,'facile',2);

De plus, le USER1, ne voit pas TOUT le contenu de la table

Questions

92
Saliha Yacoub
Conclusion
Voici ce qu’il faudra retenir pour l’instant pour la sécurité des données :
1. Utilisez des procédures stockées.
2. Renforcer les mots de passes des comptes utilisateurs
3. Évitez d’utiliser les comptes des supers usagers (root, sa, system..) pour les
opérations courantes.
4. Restreindre au minimum les autorisations, les privilèges pour les comptes
utilisateurs
5. Supprimer les comptes utilisateurs par défaut. (anonymous, Guest, scott….)
6. Éviter les comptes sans mot de passe
7. Valider toutes les entrées. Éviter les chaines null, drop, or, where …
8. Vérifier le format des données saisie.
9. N’afficher jamais les messages erreurs renvoyés par le SGBD. Personnalisez vos
messages erreurs.

Le chiffrement des données

Définition :
Le chiffrement est un procédé de la cryptographie qui consiste à rendre les données illisible ou
impossible à lire sauf si vous avez une clé de déchiffrement.

Deux techniques peuvent être utilisées pour chiffrer les données

Hachage « hashing » (chiffrement unidirectionnel)

• La technique de hachage des données à la particularité d’être irréversible; il n’est pas

possible de retrouver les données originales après avoir été crypté avec une fonction de
hachage.
• Il s’agit d’une fonction mathématique qui prend en entrée des données (chaine de
caractères de longueur variable) et qui génère en sortie une chaine de caractères de
longueur fixe appelée « hash »;
• La sortie (hash) est toujours la même pour des entrées identiques;
• Cette technique de chiffrement est couramment utilisée pour conserver des mots de
passe ou vérifier l’intégrité d’un document;
• Algorithmes de hachage les plus utilisés;
▪ SHA2_256, SHA2_512
• Dans MS SQL Server le chiffrement par hachage est fait avec la fonction HASHBYTES;
o La fonction prend deux paramètres
▪ L’algorithme à utiliser et les données à chiffrer;
▪ L’algorithme ne chiffre pas des chaines plus longues que 8000
caractères;

93
Saliha Yacoub
Chiffrement des données (chiffrement bidirectionnel)
Chiffrement symétrique
▪ Méthode de chiffrement rapide qui utilise une seule clé; la même clé est utilisée pour
crypter et décrypter les données;
▪ Algorithmes de chiffrement symétrique les plus utilisés;
o AES_128, AES_192 et AES_256;
o Supporté par MS SQL Server;
o Considéré le plus sécuritaire aujourd’hui;
o Choisi par le gouvernement américain pour remplacer l’algorithme de
chiffrement DES;

Données Données
Crypter
originales cryptées

Décrypter

Clé privée

Données
originales

Figure 2: Chiffrement symétrique

Chiffrement asymétrique ([Link]

▪ Méthode de chiffrement relativement lente qui utilise eux clés , une clé publique et une
clé privée;
▪ Comme leur nom l’indique, la clé publique peut être distribuée librement à quiconque
souhaite communiquer de manière confidentielle avec le détendeur de la clé privée;
▪ Les données cryptées avec la clé publique peuvent être décryptées uniquement avec la
clé privée;
▪ L’inverse est aussi vrai, les données cryptées avec la clé privée peuvent être décryptées
par tous ceux qui possèdent la clé publique;
▪ Algorithmes de chiffrement asymétrique les plus utilisés;
• RSA (Rivest, Shamir et Adleman), DSA (Digital Signature Algorithm);

94
Saliha Yacoub
 Clé publique Clé privée

Données Données Décrypter

Crypter
originales cryptées

Données
originales

Figure 3: Chiffrement asymétrique

Chiffrement des procédures et fonctions de la base de données

Il est possible de chiffrer les procédures stockées ainsi que les fonctions. Après avoir crypté une
procédure, il n’est plus possible d’en voir le texte. Une situation qui pourrait demander de
crypter les procédures et fonctions est si la BD est livrée chez un client qui n’a pas payé pour le
code source.

Il fortement recommandé de garder une copie de la BD dans laquelle les procédures ne sont pas
cryptées puisque la procédure pour les récupérer n’est si simple.

Il semblerait que cette méthode de chiffrement des procédures et fonctions ait été compromise
et que plusieurs logiciels commerciaux sont disponibles pour récupérer le code de ces
procédures;

create procedure ValiderMotDePasse(@motDePasse varchar(60))

with encryption
as
begin

end
Figure 4: Procédure chiffrée

Chiffrer les données contenues dans une table

Le chiffrement des données destinées à être conservées dans la base de données peut se faire
soit dans le logiciel client (la page Web ou l’application Form) ou dans le SGBD.

Dans le cas d’une application web, le chiffrement des données se fait typiquement dans le
serveur d’application web. Dans le cas d’un programme Form, chaque programme est
responsable du chiffrement des données.

Chiffrement des données dans le SGBD MS SQL Server

MS SQL Server offre des fonctions permettant de crypter les données dans des procédures
stockées.
95
Saliha Yacoub
Notez que toutes les fonctions de chiffrements documentées dans MS SQL Server ne peuvent
pas être utilisées avec le SGBD Microsoft Azure.

Les logiciels qui utilisent une base de données Azure n’ont d’autres choix que de gérer le
chiffrement des données dans le logiciel client ou dans le cas d’application web dans le serveur
d’application web.

Chiffrement symétrique

ENCRYPTBYKEY, DECRYPTBYKEY

Cette fonction utilise une clé privée pour chiffrer les données. Cette clé doit être préalablement
créée avec la commande ‘CREATE SYMMETRIC KEY’. C’est au moment de créer la clé symétrique
que l’on peut spécifier l’algorithme de chiffrement.

create symmetric key cle_sym with algorithm = AES_256

encryption by password = 'Mon mot de passe robuste'
go

open symmetric key cle_sym

decryption by password = 'Mon mot de passe robuste'
go

declare @info_a_chiffrer varchar(500)

declare @info_chiffree varbinary(8000)

set @info_a_chiffrer = 'Les carottes sont cuites'

print @info_a_chiffrer
print datalength( @info_a_chiffrer )

set @info_chiffree = EncryptByKey( key_guid('cle_sym'),

@info_a_chiffrer )

print @info_chiffree
print datalength( @info_chiffree )

select convert(varchar, DecryptByKey(@info_chiffree))

Figure 5: Exemple de chiffrement symétrique avec la commande EncryptByKey

ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE

Ces fonctions utilisent une clé privée pour chiffrer les données. La clé est fournie sous la forme
d’une chaine de caractères (un mot de passe par exemple). La fonction utilise à l’interne la
commande ‘CREATE SYMMETRIC KEY’ pour créer une clé symétrique.
96
Saliha Yacoub
 create table joueurs
(
no_jou int identity(1, 1)
constraint joueurs_pk primary key,
alias_j varchar(20) unique,
prenom_j varchar(20),
nom_j varchar(20),
carte_credit varbinary(8000) null,
mot_passe varbinary(8000) null
)

-- Le numéro de carte de crédite '6544897' est

-- chiffré avec la clé 'Pa$$w0rd'
update joueurs
set carte_credit = ENCRYPTBYPASSPHRASE('Pa$$w0rd', '6544897', 0)
where alias_j = 'Wi'

-- Le numéro de carte de crédit est déchiffré et convertit en varchar

select convert(varchar, DECRYPTBYPASSPHRASE('Pa$$w0rd',carte_credit,
0))
from joueurs

Figure 6: exemple de chiffrement symétrique avec ENCRYPTBYPASSPHRASE.

Fonction de hachage
HASHBYTES

Cette fonction prend en paramètre la chaine de caractères à chiffrer et l’algorithme de

chiffrement. La fonction retourne une chaine chiffrée.

declare @crypt_pass varbinary(8000);

select @crypt_pass = HASHBYTES('SHA2_512', 'pass123');

select HASHBYTES('SHA2_512', 'pass123');

Figure 7: Exemple de hachage de données

Chiffrement des données dans le logiciel client ou le serveur d’application web

Traiter le chiffrement des données du côté client a l’avantage que l’information confidentielle
est transmise chiffrée au SGBD. Cette approche ne nécessite pas d’avoir un canal de
communication cryptée pour communiquer avec le SGBD.

97
Saliha Yacoub
Le “Framework .Net” offre tous les services de chiffrements dans le domaine
«[Link] »

static string FonctionDeHachage(string infoAHacher)

{
UnicodeEncoding UnicodeString = new UnicodeEncoding();

Byte[] MotDePasseAChiffrer = [Link](infoAHacher);

MD5CryptoServiceProvider MD5 = new MD5CryptoServiceProvider();

byte[] infoHachee = [Link](MotDePasseAChiffrer);

return Convert.ToBase64String(infoHachee);
}

Autre exemple chiffrement par clé symétrique sans certificat

create database ExempleEncryption;

USE ExempleEncryption;

drop table Clients3

create table Clients3
(
id_client int identity(1,1) constraint pkClient3 primary key,
nom varchar(30) not null,
prenom varchar(30) not null,
carteCredit varchar(20)
);

insert into Clients3 values ('Roy', 'Simon','9874-1234-5678-

1111');
insert into Clients3 values ('Lechat', 'Ryby','1234-9874-2222-
4569');
insert into Clients3 values ('Patouche', 'Mosus','2222-3333-4444-
5555');

-----On ajoute une colonne qui va contenir les données chiffrées

alter table Clients3 add CarteEncryptee varbinary(max) null;

98
Saliha Yacoub
-----Creer une clé symétrique encryptée par mot de passe.

USE ExempleEncryption;
CREATE SYMMETRIC KEY SymmetricKey2
WITH ALGORITHM = AES_128
ENCRYPTION BY password ='CemotdePasse123';
GO

USE ExempleEncryption;
GO
--ouverture de la clé pour encryption
OPEN SYMMETRIC KEY SymmetricKey2
Decryption BY password ='CemotdePasse123';
GO
On met à jour la colonne à chiffrer

UPDATE Clients3 SET CarteEncryptee = EncryptByKey

(Key_GUID('SymmetricKey2'),carteCredit)

GO
--fermer la clé de chiffrement
CLOSE SYMMETRIC KEY SymmetricKey2;
GO

--on affiche pour voir que les données ont été cryptée.
A ce stade… on aurait pu supprimer la colonne CarteCredit pour
plus de sécurité.

select * from Clients3;

-on utilise à nouveau la clé pour décrypter

99
Saliha Yacoub
USE ExempleEncryption;
GO
OPEN SYMMETRIC KEY SymmetricKey2
Decryption BY password ='CemotdePasse123';
GO

-- on affiche les truc décripté

SELECT nom, prenom, CarteEncryptee AS 'Carte cryptée',
CONVERT(varchar, DecryptByKey(CarteEncryptee)) AS 'Carte
decrypté' FROM Clients3;

Pour effectuer une insertion, on fait comme suit :

USE ExempleEncryption;
GO
--ouverture de la clé pour encryption
OPEN SYMMETRIC KEY SymmetricKey2
Decryption BY password ='CemotdePasse123';
GO
insert into Clients3 values('Blabla','un nom', '9999-9999-9999-
9999',EncryptByKey (Key_GUID('SymmetricKey2'),'9999-9999-9999-9999'));

GO
--fermer la clé
CLOSE SYMMETRIC KEY SymmetricKey2;
GO

Autre exemple chiffrement par ENCRYPTBYPASSPHRASE

create table Clients2

(
id_client int identity(1,1) constraint pkClient2 primary key,
nom varchar(30) not null,
prenom varchar(30) not null,
carteCredit varchar(20)
);

100
Saliha Yacoub
insert into Clients2 values ('Roy', 'Simon','9874-1234-5678-1111');
insert into Clients2 values ('Lechat', 'Ryby','1234-9874-2222-4569');
insert into Clients2 values ('Patouche', 'Mosus','2222-3333-4444-
5555');

alter table Clients2 add CarteEncryptee varbinary(max) null;

Chiffrement de la colonne ajoutée

update Clients2 set CarteEncryptee=
ENCRYPTBYPASSPHRASE('passord123456','9874-1234-5678-1111') where
id_client=1;
update Clients2 set CarteEncryptee=
ENCRYPTBYPASSPHRASE('password123456','1234-9874-2222-4569') where
id_client=2;
update Clients2 set CarteEncryptee=
ENCRYPTBYPASSPHRASE('pasord123456','9874-1234-5678-1111') where
id_client=3;

Insertion en utilisant le chiffrement

insert into clients2 values('Test','Test','0000-0000-0000-

0000',ENCRYPTBYPASSPHRASE('local$33','0000-0000-0000-0000'));

Decryption
USE ExempleEncryption;

SELECT nom, prenom, CarteEncryptee AS 'carte encrypte',

CONVERT(varchar, DECRYPTBYPASSPHRASE('passord123456',CarteEncryptee))
AS 'carte decryptée' FROM Clients2 where id_client =1;

101
Saliha Yacoub
Sources
[Link]
2017

[Link]

[Link]
server-2017

[Link]

[Link]
versioning-guide?view=sql-server-2014

[Link]
server-2014#Clustered

[Link]
access/server-level-roles?view=sql-server-ver15
[Link]
sql-server-database-engine-and-azure-sql-database?view=sql-server-ver15

[Link]
security-scenarios-in-sql-server

[Link]
c/Inventory/admin/t_sql_backup.html

102
Saliha Yacoub
 2016

MISE EN ŒUVRE DE LA
REPLICATION SOUS SQL
SERVER 2014
Application pas à pas à la réplication fusion

GUY NONO LOUENKAM

Email : gnonog@[Link]
Skype : [Link]
 Sommaire 1

Sommaire
Sommaire ........................................................................................................................................................ 1
Introduction..................................................................................................................................................... 2
Vue d’e se le d fi itio s ...................................................................................................................... 2
A hite tu e à ett e e œuv e pli atio fusio .................................................................................. 3
Configuration de base ................................................................................................................................. 3
Configuration de la distribution sur le server 1 contenant les données à envoyer au serveur central .......... 4
Création de la publication sur le serveur 1 ..................................................................................................... 9
Création des abonnés (subscriptions) ........................................................................................................... 18

Test de la mise à jour des données instantanément .................................................................................... 28

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Introduction 2

Introduction
Vue d’ensemble (définitions)
La réplication consiste à copier et de distribuer un ensemble de données et d’objets d’une base de
données vers une autre, puis de synchroniser ces bases de données afin de préserver leur cohérence.
Les deux bases de données peuvent être stockées à des emplacements distincts. Il existe sous SQL
Server plusieurs types de réplication : Instantanée (snapshot), transactionnelle, Fusion.

- La réplication instantanée : est en général utilisée pour fournir le jeu des données initiales pour
les autres réplications (transactionnelle et fusion). Elle transmet les données telle qu’elles à un
moment donnée définit dans la configuration. Elle est importante pour avoir un état complet du
serveur à un moment précis ou répétitif.
- La réplication transactionnelle est en général utilisée dans les environnements serveur à serveur
dans les cas suivants :
o pour des applications nécessitant une mise à jour instantanée et incrémentielle des
données vers les différents abonnés
o pour les applications pouvant nécessiter l’accès aux états intermédiaires des données:
avec la possibilité d’activer un déclencheur pour chacune des transactions (insertion,
modification, …) quel qu’en soit le nombre sans se limiter simplement aux résultats.
o Pour les applications ayant un gros volume d’activité (insertion, modification, suppression)
du côté du distributeur.
o Pour les cas de réplication où les abonnées sont des bases de données non-SQL Server.
- La réplication fusion est généralement utilisée dans des environnements serveur à client et plus
particulièrement dans les cas suivants :
o Plusieurs abonnés peuvent mettre à jour les mêmes données à différents moments et
propager ces modifications au serveur de publication et à d'autres Abonnés.
o des abonnés doivent recevoir des données, apporter des modifications hors connexion et
synchroniser ultérieurement ces modifications avec l'éditeur et d'autres abonnés ;
o Chaque Abonné requiert une partition de données différente.
o Des conflits peuvent se produire et, le cas échéant, vous devez pouvoir les détecter et les
résoudre.
o L'application requiert le résultat des modifications des données au lieu de devoir accéder
aux états intermédiaires des données. Par exemple, si une ligne change cinq fois sur un
Abonné avant qu'il se synchronise avec un serveur de publication, la ligne ne change
qu'une seule fois sur le serveur de publication pour refléter le résultat final des
modifications (c'est-à-dire la cinquième valeur).

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Introduction 3

Architecture à mettre en œuvre (réplication fusion)

Configuration de base
Créer deux instances de serveur :

- la première : le serveur contenant les données (serveur de distribution),

- la deuxième : le serveur central (abonné).

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Configuration de la distribution sur le server 1 contenant les données à envoyer au serveur central 4

Configuration de la distribution sur le server 1

contenant les données à envoyer au serveur central
1. Sur le serveur 1, on va configurer la distribution

2.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Configuration de la distribution sur le server 1 contenant les données à envoyer au serveur central 5

3.
4. Ensuite, On crée un serveur partagé et car la réplication faite est en mode pull : i.e le serveur
central viens chercher les données lui-même. Dans notre cas, \\CALDERON\ReplData3

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Configuration de la distribution sur le server 1 contenant les données à envoyer au serveur central 6

5. Le premier répertoire indiqué permet de stocke la configuration de la distribution la meta-data et

le second les logs.

6.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Configuration de la distribution sur le server 1 contenant les données à envoyer au serveur central 7

7.

8.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Configuration de la distribution sur le server 1 contenant les données à envoyer au serveur central 8

9.
10. L’erreur de démarrage du « Server Agent » ne pose pas de problème car elle peut être faite
directement via la gestion des services.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création de la publication sur le serveur 1 9

Création de la publication sur le serveur 1

Ici, on fait le choix des éléments qui vont être dupliqué vers le serveur central.

1.

2.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création de la publication sur le serveur 1 10

3. Choix de la base de données concernée par la réplication

4. Choix du type de publication : Fusion

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création de la publication sur le serveur 1 11

5.
6. Il faut préciser que dans le cas d’u e réplication transactionnelle, les tables à répliquer doivent
disposer obligatoirement d’un PRIMARY KEY.
7. Ici on peut définir un filtre horizontal en faisant le choix des colonnes spécifiques à répliquer.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création de la publication sur le serveur 1 12

8.
9. Ici on peut ajouter un filtre vertical avec des where.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création de la publication sur le serveur 1 13

10.
11. On peut changer les paramètres du S apshot afi u’u e ve sio o pl te de la pli atio
puisse être faite selon le planning défini.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création de la publication sur le serveur 1 14

12. Dans notre cas on choisit tous les 7 jours à 23h30.

13. I i vous devez fai e t s atte tio aux d oits utilisateu . Il faut s’assu e ue l’utilisateu ue vous
allez hoisi dispose de tous les d oits essai es pou l’ itu e des fi hie s da s le dossie
partagé à cet effet.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création de la publication sur le serveur 1 15

a. Ne hoisissez ette deuxi e optio ue si vous vous tes assu ue l’age t au a tous
les droits sur le dossier partagé pour la publication des fichiers.

14.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création de la publication sur le serveur 1 16

15.

16.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création de la publication sur le serveur 1 17

17.
18. A la fin de la création, il va générer dans mon dossier partagé un répertoire nommé
[INSTANCE_SQLSERVER][BASE_DE_DONNEE_A_REPLIQUER][NOM_REPLICATION] dans un sous
repertoire « unc ». Dans notre cas : CALDERON$APBS_BANQUE_PUB_FUSION

19. En consultant le contenu du dossier daté qui a été généré dans le dossier …pub_fusion, on a :

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création des abonnés (subscriptions) 18

Création des abonnés (subscriptions)

1.

2.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création des abonnés (subscriptions) 19

3.

4.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création des abonnés (subscriptions) 20

5.
6. Cli ue Add SQL se ve … pou ajoute u aut e se veu o ea o .

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création des abonnés (subscriptions) 21

7. Sur le serveur on a déjà une base de données nommée banque. Pour notre démo, on va créer
u e ouvelle ase de do es u’o appelle a banque_fus.

8. on adopte le paramétrage par défaut.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création des abonnés (subscriptions) 22

9.

10.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création des abonnés (subscriptions) 23

11. I i o va d fi i les d oit d’a s aux fi hie s pu li es pa la pu li atio précédemment crée.

12.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création des abonnés (subscriptions) 24

13. I i o va hoisi l’optio d’ex utio o ti ue.

14. Pour des questions de test, on va faire une souscription instantanée.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création des abonnés (subscriptions) 25

15.

16.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création des abonnés (subscriptions) 26

17.

18.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Création des abonnés (subscriptions) 27

19. Etat de notre serveur ce t al ava t et ap s la atio de l’a o .

20. Comme on peut le constater, la base de données a été créée sur le serveur central avec les
caractéristiques définie lors de la création de la publication.

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion
 Test de la mise à jour des données instantanément 28

Test de la mise à jour des données instantanément

Exécutons la requête suivante chez le serveur 1 pour insérer un nouvel enregistrement dans la table
Users.

USE [banque]
GO

INSERT INTO [dbo].[Users]

([id]
,[Name]
,[Address]
,[Phone])
VALUES
(2,'Guy Nono', 'Douala','655556561')
GO

Si on actualise la table Users sur le serveur central, on peut remarquer que la mise à jour a été faite.

Avec cette démo, il vous sera aisé d’i pl e te tout autre type de publication (Transactionnelle, fusion
ou Snapshot).

MISE EN ŒUVRE DE LA REPLICATION SOUS SQL SERVER 2014 | Application pas à pas à la réplication fusion