DBM1 labo2 - IlanRuiz/Cours-TechES GitHub Wiki
Questionnaire
1.Combien de fichiers sont nécessaires pour la création de la base de données ?
- B. 2 fichiers
2.Quel est le rôle de la base Model ?
C'est un model/template qui sera utilisé lors de l'utilisation de l'instruction CREATE DATABASE.
3.Si des modifications sont apportées à la base Model, qui va en bénéficier ?
- C. Les bases qui sont créées par la suite qui vont utiliser ce model
4.Quels sont les groupes de fichiers créés par défaut sur une base de données ?
- C. Primary
5.Quel est le nom du groupe de fichiers utilisé par les tables systèmes ?
- B. Primary
6.Est-il possible de définir une table sur un fichier précis ?
- B. Non
7.Dans quel sens est-il possible de partitionner une table ?
- B. Horizontalement
8.Combien de fichiers, contient, au minimum un groupe de fichiers ?
- A. Oui
9.Est-il possible de déplacer un fichier d'un groupe de fichiers vers un autre ?
- C. 25 % d'espace libre
10.Lorsque l'option AUTO_SHRINK est activée, à partir de quelle quantité d'espace libre le fichier est-il réduit de façon automatique ?
- C. 25 % d'espace libre
11.Le fichier principal de données ppal.mdf a été créé en même temps que la base avec une taille initiale de 10 Mo. Ce fichier est paramétré en croissance automatique. Des tables utilisateurs ont été définies sur le groupe de fichiers PRIMARY, tant et si bien que le fichier a atteint une taille de 20 Mo. Les tables utilisateurs sont supprimées. Le fichier ne contient plus que 4 Mo d'information. Le paramètre AUTO_SHRINK est activé sur la base de données. Quelle est la taille du fichier ppal.mdf après que le processus de réduction automatique a été exécuté ?
- B. 10 Mo
12.Sur une table de base de données combien d'index non-clustered est-il possible de définir ?
- D. 249 sur SQL Server 2005 qu'il y ait ou pas de clé primaire
https://msdn.microsoft.com/en-us/library/ms143432(v=SQL.100).aspx
13.Pour être en mesure de définir un index sur une colonne de type xml, la table doit-elle obligatoirement disposer d'une contrainte de clé primaire ?
- A. Oui
14.La compression des données est :
- C. Configurable au niveau de chaque table
Labo
Créer une base de données
USE master;
GO
CREATE DATABASE TSQL
ON
( NAME = TSQL_dat,
FILENAME = '/var/opt/mssql/data/TSQL.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB )
LOG ON
( NAME = TSQL_log,
FILENAME = '/var/opt/mssql/data/TSQL_log.ldf',
SIZE = 2MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
Définir un groupe de fichiers
USE master
GO
ALTER DATABASE TSQL
ADD FILEGROUP TPTSQL;
Ajouter des fichiers de données
USE master;
GO
ALTER DATABASE TSQL
ADD FILE
(
NAME = TSQL_2,
FILENAME = '/var/opt/mssql/data/TSQL_2.mdf',
SIZE = 10MB,
MAXSIZE = 20MB,
FILEGROWTH = 2MB
);
GO
Ajouter un fichier journal
USE master;
GO
ALTER DATABASE TSQL
ADD LOG FILE
(
NAME = TSQL_log2,
FILENAME = '/var/opt/mssql/data/TSQL_log2.ldf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB
);
GO
Modifier un fichier de données
USE TSQL;
GO
DBCC SHRINKFILE ('TSQL_2', 2);
Réduire la taille d'une base de données
USE TSQL;
GO
DBCC SHRINKDATABASE (TSQL,10);
Créer une table sur un groupe de fichiers
USE SSMS
GO
ALTER DATABASE SSMS
ADD FILEGROUP TPSSMS;
GO
ALTER DATABASE SSMS
ADD FILE
( NAME = 'TPSSMS_dat',
FILENAME ='/var/opt/mssql/data/TPSSMS_dat.ndf',
SIZE = 5MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
TO FILEGROUP TPSSMS;
GO
CREATE TABLE CLIENTS (
numero INT CONSTRAINT pl_clients PRIMARY KEY,
nom NVARCHAR(50) NOT NULL,
prenom NVARCHAR(50) NOT NULL,
adresse NVARCHAR(100),
codepostal CHAR(5),
ville NVARCHAR(50)
) ON TPSSMS;
Créer une table partitionnée
USE SSMS
GO
ALTER DATABASE SSMS
ADD FILEGROUP Ile_De_France;
ALTER DATABASE SSMS
ADD FILEGROUP Centre_Nord;
ALTER DATABASE SSMS
ADD FILEGROUP Nord_Est;
ALTER DATABASE SSMS
ADD FILEGROUP Ouest;
ALTER DATABASE SSMS
ADD FILEGROUP Sud_Ouest;
ALTER DATABASE SSMS
ADD FILEGROUP Sud_Est;
ALTER DATABASE SSMS
ADD FILEGROUP Sud;
GO
ALTER DATABASE SSMS
ADD FILE
( NAME = 'IDF_dat',
FILENAME ='/var/opt/mssql/data/IDF_dat.ndf',
SIZE = 5MB,
MAXSIZE=20MB,
FILEGROWTH=1MB)
TO FILEGROUP Ile_De_France;
ALTER DATABASE SSMS
ADD FILE
( NAME = 'CN_dat',
FILENAME ='/var/opt/mssql/data/CN_dat.ndf',
SIZE = 5MB,
MAXSIZE=20MB,
FILEGROWTH=1MB)
TO FILEGROUP Centre_Nord;
ALTER DATABASE SSMS
ADD FILE
( NAME = 'NE_dat',
FILENAME ='/var/opt/mssql/data/NE_dat.ndf',
SIZE = 5MB,
MAXSIZE=20MB,
FILEGROWTH=1MB)
TO FILEGROUP Nord_Est;
ALTER DATABASE SSMS
ADD FILE
( NAME = 'O_dat',
FILENAME ='/var/opt/mssql/data/O_dat.ndf',
SIZE = 5MB,
MAXSIZE=20MB,
FILEGROWTH=1MB)
TO FILEGROUP Ouest;
ALTER DATABASE SSMS
ADD FILE
( NAME = 'SO_dat',
FILENAME ='/var/opt/mssql/data/SO_dat.ndf',
SIZE = 5MB,
MAXSIZE=20MB,
FILEGROWTH=1MB)
TO FILEGROUP Sud_Ouest;
ALTER DATABASE SSMS
ADD FILE
( NAME = 'SE_dat',
FILENAME ='/var/opt/mssql/data/SE_dat.ndf',
SIZE = 5MB,
MAXSIZE=20MB,
FILEGROWTH=1MB)
TO FILEGROUP Sud_Est;
ALTER DATABASE SSMS
ADD FILE
( NAME = 'S_dat',
FILENAME ='/var/opt/mssql/data/S_dat.ndf',
SIZE = 5MB,
MAXSIZE=20MB,
FILEGROWTH=1MB)
TO FILEGROUP Sud;
GO
CREATE PARTITION FUNCTION regionpartition (int)
AS RANGE LEFT FOR VALUES (1,2,3,4,5,6);
GO
CREATE PARTITION SCHEME regionscheme
AS PARTITION regionpartition
TO (Ile_De_France, Centre_Nord, Nord_Est, Ouest, Sud_Ouest, Sud_Est, Sud);
GO
CREATE TABLE CLIENTS (
numero INT,
nom NVARCHAR(50) NOT NULL,
prenom NVARCHAR(50) NOT NULL,
adresse NVARCHAR(100),
codepostal CHAR(5),
ville NVARCHAR(50),
region int,
CONSTRAINT pl_clients PRIMARY KEY (numero, region)
) ON regionscheme(region);
Pour supprimer la partition et le schema en cas d'erreur:
USE SSMS
DROP PARTITION FUNCTION regionpartition;
USE SSMS
DROP PARTITION SCHEME regionscheme;
Mettre en place des index
USE TSQL;
GO
CREATE TABLE ARTICLES (
refart char(10) constraint pk_articles primary key,
designation nvarchar(100),
prixht money
);
GO
ALTER TABLE ARTICLES
REBUILD PARTITION = ALL WITH (
DATA_COMPRESSION = ROW
);
Mettre en place des index
USE TSQL;
GO
CREATE TABLE produits (
id int identity(1,1) constraint pk_produits primary key,
libelle nvarchar(200),
description xml,
categorie varchar(10),
marque varchar(50)
);
CREATE INDEX produitsdistinc ON produits (id);
Correction: remplacer Id par marque and la creation de l'index
Indexer une colonne de type xml
USE TSQL
GO
CREATE PRIMARY XML INDEX XML_desc_prod on dbo.produits (description)
Définir un index couvrant
USE TSQL
GO
CREATE NONCLUSTERED INDEX [IX_libelle_marque] ON produits (libelle, marque)
Lister les index
SELECT
TableName = t.Name,
i.*
FROM
sys.indexes i
INNER JOIN
sys.tables t ON t.object_id = i.object_id
WHERE
T.Name = 'nomtable'
sources
https://www.sqlservertutorial.net/sql-server-administration/sql-server-table-partitioning/
https://www.sqlshack.com/tracing-and-tuning-queries-using-sql-server-indexes/