Création de base de données SQL Server

Evaluation
Période en
entreprise
Apprentissage
Accueil
Activité « Développer la persistance des données »
Secteur Tertiaire Informatique
Filière étude - développement
Création de bases de données SQL Server 2012
Code barre

Création de bases de données SQL Server 2008
afpa  2011– Informatique et télécoms – filière étude - développement
2/46
SOMMAIRE
I CREATION DE LA BASE DE DONNEES ........................................................................... 3
I.1 Stockage physique des données .................................................................................................. 3
I.2 Création d’une base de données .................................................................................................. 5
I.3 Déplacement de base de données ........................................................................................... 12
I.4 Administration ................................................................................................................................. 13
I.5 Travailler avec des schémas......................................................................................................... 14
II CREATION D’OBJETS DE BASE DE DONNEES ........................................................... 16
II.1 Les types de données système ................................................................................................... 16
II.2 Les types de données utilisateur ............................................................................................... 19
II.3 Création de tables ........................................................................................................................... 21
II.4 Modification de la structure de tables ..................................................................................... 25
II.5 Tables partitionnées ....................................................................................................................... 26
II.6 Génération de scripts ..................................................................................................................... 28
III INTEGRITE DES DONNEES ........................................................................................... 29
III.1 Les contraintes ................................................................................................................................. 29
III.2 Valeurs par défaut et règles ......................................................................................................... 35
IV GESTION DES INDEX...................................................................................................... 36
IV.1 Introduction ...................................................................................................................................... 36
IV.2 Architecture et création d’index ................................................................................................ 37
IV.3 Afficher les propriétés des index ............................................................................................... 38
IV.4 Gestion et maintenance de l’index ........................................................................................... 39
IV.5 Utilisation de l’instruction CREATE INDEX .............................................................................. 40
V LES VUES................................................................................................................................ 42
V.1 Création .............................................................................................................................................. 43
V.2 Mises a jour de données dans les vues .................................................................................... 45
V.3 Les vues Indexées ........................................................................................................................... 45
V.4 Modification / Suppression .......................................................................................................... 45

Création de bases de données SQL Server 2008
afpa  2011– Informatique et télécoms – filière étude - développement
3/46
I CREATION DE LA BASE DE DONNEES
I.1 STOCKAGE PHYSIQUE DES DONNEES
Les bases de données SQL Server sont stockées dans des fichiers. Ces fichiers sont créés
automatiquement lors de la création de la base.
Le fichier primaire (d’extension .mdf) est le point de départ de la base de données.
Les fichiers secondaires, facultatifs (d’extension .ndf) contiennent toutes les données et
objets qui ne se trouvent pas dans le fichier primaire.
Les fichiers journaux (d’extension .ldf) contiennent toutes les informations relatives aux
transactions effectuées et permettent de récupérer la base en cas d’incident.
Considérations relatives aux fichiers de base de données :
• La création d’une base de donnée s’effectue par copie de la base ‘model’,
comprenant les tables système ;
• Les données sont stockées dans des pages de 8 Ko d’espace disque contigu ;
• Les lignes ne peuvent pas s’étendre sur plusieurs pages ;
• Les tables, les index sont stockés dans des extensions (une extension = 8 pages
contiguës donc 64 Ko) ;
• 8 objets peuvent se partager une extension(extension mixte).
Lorsqu’une table dépasse 8 pages, elle utilise ses propres extensions (extension
uniforme).
• La taille du journal des transactions représente 25% de la taille des données.

Création de bases de données SQL Server 2008
afpa  2011– Informatique et télécoms – filière étude - développement
4/46
Mode de fonctionnement du journal des transactions
Le journal des transactions consigne les modifications apportées aux données, via les
instructions INSERT, UPDAT ou DELETE.
1. Lorsque la modification est exécutée, les pages de données concernées sont
chargées dans une zone mémoire appelée cache de données ;
2. Chaque instruction de modification de données est enregistrée ;
3. dans le journal des transactions au moment ou elle est exécutée. La modification
est toujours enregistrée dans le fichier journal, avant d’être appliquée à la base
(journal à écriture anticipée) ;
4. Le processus de point de contrôle écrit toutes les transactions validées (par une
instruction COMMIT TRANSACTION) dans la base.
En mode de fonctionnement normal, le processus de point de contrôle vérifie
régulièrement que pour toutes les transactions validées, les données correspondantes ont
bien été réécrites dans la base. Un point de contrôle est créé automatiquement.
En cas de défaillance du système, le processus de récupération automatique s’exécute au
redémarrage de SQL Server pour rétablir l’intégrité des données.
La programmation des « triggers » utilise aussi le journal de transactions pour confirmer
ou invalider des mises à jour qui doivent être contrôlées.

Création de bases de données SQL Server 2008
afpa  2011– Informatique et télécoms – filière étude - développement
5/46
I.2 CREATION D’UNE BASE DE DONNEES
Lorsqu’une nouvelle base de données est créée dans SQL Server 2012, le niveau de
compatibilité est par défaut 110.
Lorsqu’une base de données est importée dans SQL Server, elle garde son niveau de
compatibilité existant (obtenue dans Propriétés de la base de données /Options).
• 100 pour le niveau de compatibilité SQL Server 2008
• 90 pour le niveau de compatibilité SQL Server 2005
• 80 pour le niveau de compatibilité SQL Server 2000
La vue Explorateur d’objets
Dans l’outil d’administration « SQL Server Management Studio », lorsqu’on développe le
dossier base de données, ainsi que l’entrée d’une des bases, on affiche une liste de nœuds
d’objets de la base :
• Schémas de base de données
Schémas relationnels (MLD) de la base ainsi que les informations en rapport
• Tables
Table système (messagerie, plans de maintenance, réplication,
sauvegarde/restauration, envoi des journaux) et tables utilisateurs contenant les
données