Discussion :

[Artemus24]

Salut à tous.

Je cherche à faire une conversion de secondes dans un format qui soit compréhensible. Ces secondes expriment une différence entre deux date+time.

A l'affichage, j'aimerai obtenir le format suivant : '000 00:00:00', avec nombre de jours, heures, minutes et secondes ou autre chose.
A priori, le nombre de jours est superflu si l'on peut mettre le débordement en heure.
Au lieu de 1 jour, ajouter plutôt 24 aux heures déjà présentes.

Voici le test que j'ai fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
-- ==========
-- Paramètres
-- ==========
 
SET NOCOUNT ON
SET DATEFORMAT ymd
 
 
-- ===========================
-- Suppression Database 'base'
-- ===========================
 
if db_id(N'base') is not null
   drop database base
 
 
-- ========================
-- Création Database 'base'
-- ========================
 
create database base
       collate French_CI_AS
       WITH TRUSTWORTHY ON,
            DB_CHAINING OFF
 
use base
 
Le contexte de la base de données a changé*; il est maintenant 'base'.
 
-- =====================
-- Création Table 'test'
-- =====================
 
create table test (
  id            smallint identity(1, 1) NOT NULL,
  date_deb      datetime                NOT NULL,
  date_fin      datetime                NOT NULL,
  constraint pk_test_id   primary key clustered (id)
)
 
-- =====================
-- Insertion dans 'test'
-- =====================
 
INSERT INTO test (date_deb,date_fin) VALUES
  ('2016-05-08 15:00:00', '2016-05-08 15:21:11'),
  ('2016-05-08 15:00:00', '2016-05-10 15:21:11')
 
 
-- ================
-- Vidage de 'test'
-- ================
 
select * from test
 
id     date_deb                date_fin
------ ----------------------- -----------------------
     1 2016-05-08 15:00:00.000 2016-05-08 15:21:11.000
     2 2016-05-08 15:00:00.000 2016-05-10 15:21:11.000
 
-- ===========
-- Requête CTE
-- ===========
 
with CTE (id, diff) as (
select id, datediff(second, date_deb, date_fin) as diff
from       test
)
 
select id, diff, concat(diff/86400, ' jours ', convert(varchar, dateadd(second, diff%86400, '00:00:00'), 108)) as affichage
from CTE
 
id     diff        affichage
------ ----------- -------------------------------------------------
     1        1271 0 jours 00:21:11
     2      174071 2 jours 00:21:11
 
Appuyez sur une touche pour continuer...

Peut-on améliorer la conversion d'une façon ou d'une autre ?
Je pensais qu'il existait sous Sql Server un format genre "ddd hh:mi:ss".

Autre question : si je ne précise rien dans mon script, quel est le format de la date par défaut ?
Est-ce "dmy" ?

@+

[SQLpro]

PEtite remarque d'ordre général : au lieu de faire des scripts avec création d'une base, utilisez la base tempdb ou des tables temporaires pour ce faire...

Peut-on améliorer la conversion d'une façon ou d'une autre ?

A mon avis non.

Je pensais qu'il existait sous Sql Server un format genre "ddd hh:mi:ss".

La notion de "format" n'existe pas dans le langage SQL. Il y a des types de données un point c'est tout. Les format de représentation sont liés aux outils que vous utilisez et non au serveur SQL. Tout ceci étant de la cosmétique.
Pour ce qui est des types, le type TIME normalisé de SQL ne compte que 24 heures et va donc jusqu'à 23h59m59s .9999999

Autre question : si je ne précise rien dans mon script, quel est le format de la date par défaut ?

Bonne question, nous en avons parlé en long en large et en travers dans cet article :
http://baptiste-wicht.developpez.com...-sql/datetime/

Le principe est simple :
1) les types temporels utilisent par défaut quelque soit les configurations régionales, le format de conversion ISO (norme SQL) :

• pour le type DATETIME (datant de la norme SQL2 de 1992) : 'AAAAMMJJ HH:MM:SS.nnn' (appelé ISO court)
• pour les types DATETIME2 et TIME (datant de la norme SQL:1999 : 'AAAA_MM_JJ HH:MM:SS.nnnnnnn' (le T peut remplacer le blanc de séparation date/heure - appelé ISO long, la norme ayant changée entre temps !)

Certaines partie d'heure et de date pouvant être omises.

2) en fonction de votre paramétrage régional, le format est spécifique à la langue et dépend de l'utilisateur. Par exemple sur un serveur SQL anglais, avec un utilisateur paramétré en français, vous pouvez forcer des dates avec ce format de conversion chaine->date :
JJ/MM/AA[AA]
La partie de l'année manquante étant réglée par la paramètre de configuration "two digit year cutoff" (procédure sp_configure
Pour voir dans quelle langue est votre session :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT @@LANGUAGE

3) vous pouvez forcer un format de conversion particulier chaine->date de plusieurs manières :
en modifiant la langue de la session :
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SET LANGUAGE Français

Ou encore en spécifiant un format particulier à l'aide de DATEFORMAT
Exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SET DATEFORMAT DYM

Utilise toute combinaisons de D M et Y

A +

[Artemus24]

Salut SQLPRO.

Merci de vous intéresser à mes problèmes de débutant en SQL SERVER.

Petite remarque d'ordre général : au lieu de faire des scripts avec création d'une base, utilisez la base tempdb ou des tables temporaires pour ce faire...

Je procède ainsi pour les tests sous MySql et sous FireBird. En quoi est-ce problématique sous SQL SERVER ?
Est-ce que cela va rendre SQL Server instable en créant et détruisant la base de données ?

Ce sont des scripts de tests qui n'ont aucune vocation à devenir permanente dans l'environnement SQL SERVER.
Je vais faire un essai et voire si cela me convient car après tout, c'est du temporaire que je fais.
Quelques autres conseils sur la façon d'écrire les scripts de test ?

La notion de "format" n'existe pas dans le langage SQL. Il y a des types de données un point c'est tout.

Je sais que je fais des abus de langage, mais pas tant que cela, car je distingue le stockage de la donnée, de sa représentation à l'affichage.

Les format de représentation sont liés aux outils que vous utilisez et non au serveur SQL. Tout ceci étant de la cosmétique.

Oui, je sais que le SGBD n'a pas vocation à faire de la présentation.
Mais bon, si je peux dégrossir le travail sous SQL SERVER, par ailleurs, cela va me simplifier la vie.

Bonne question, nous en avons parlé en long en large et en travers dans cet article :
http://baptiste-wicht.developpez.com...-sql/datetime/

Merci pour le lien, mais la page est introuvable.

J'ai vérifié la langue dans mon environnement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
-- ==========================
-- Liste des bases de données
-- ==========================
 
SELECT @@LANGUAGE
 
 
--------------------------------------------------------------------------------------------------------------------------------
Français
 
(1 lignes affectées)
 
Appuyez sur une touche pour continuer...

et c'est bien du français.

N'y a-t-il pas un paramétrage à mettre quelque part dans SQL SERVER afin de ne pas toujours les préciser dans les scripts ?
A moins que c'est la bonne façon de faire, mettre le paramétrage dans le script.

J'ai fait une fonction que voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- ==============================
-- Suppression Function 'affiche'
-- ==============================
 
IF OBJECT_ID(N'dbo.affiche', N'FN') IS NOT NULL
    DROP FUNCTION dbo.affiche
;
-- ==================
-- Fonction 'affiche'
-- ==================
 
CREATE FUNCTION dbo.affiche(@Diff int)
RETURNS char(18)
AS 
BEGIN
  DECLARE @Parte int = @Diff/86400
  DECLARE @Reste int = @Diff%86400
 
  return left(concat('  ', @Parte, ' jours ', convert(varchar, dateadd(second, @Reste, '00:00:00'), 108)), 18)
END
;

J'ai plusieurs remarques à faire :

1) pour la suppression de la fonction, j'ai procédé par la fonction "OBJECT_ID()".
Je suppose que c'est la forme condensée ou moderne, en remplacement de cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = N'affiche' AND type = 'FN')

2) je constate que le point-virgule n'est plus nécessaire pour séparer les différentes requêtes.

3) dans la fonction, les variables locales commencent par '@'.
Visuellement, cela permet de faire la distinction avec les noms des colonnes.

4) dans tous les exemples que j'ai vu, il y a comme préfixe au nom de la fonction 'dbo'.
Qu'est-ce que c'est ce 'dbo' ?

@+

[SQLpro]

J'ai plusieurs remarques à faire :

1) pour la suppression de la fonction, j'ai procédé par la fonction "OBJECT_ID()".

C'est une manière assez rapide de faire spécifique à SQL Server en attendant un CREATE or ALTER (et non CREATE or REPLACE parfaitement imbécil !)

Je suppose que c'est la forme condensée ou moderne, en remplacement de cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = N'affiche' AND type = 'FN')

Effectivement beaucoup plus proche de la norme SQL, mais tant qu'à faire utiliser INFORMATION_SCHEMA.ROUTINES à la place de sys.objects !

2) je constate que le point-virgule n'est plus nécessaire pour séparer les différentes requêtes.

Il n'a jamais été nécessaire dans Transact SQL (je rappelle que c'est Sybase qui a inventé le code côté serveur de bases de données en 1986 en commençant avec les déclencheurs...).
Il est cependant indispensable dans certains scripts sinon il y a risque de confusion pour le "parser". Par exemple, point-virgule impératif avant toute CTE. En effet le mot clef WITH des CTE existe à l'origine dans Transact-SQL pour ajouter des indicateurs de tables dans les requêtes. Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM MaTbale WITH(NOLOCK) --> entre nous à ne jamais faire en production !!!

3) dans la fonction, les variables locales commencent par '@'.
Visuellement, cela permet de faire la distinction avec les noms des colonnes.

C'est fait pour. À titre d'information @ signifie littéralement "chez" c'est à dire, un lieu, un emplacement, une adresse... et qu'est-ce donc qu'une variable, si ce n'est une adresse ou mémoire ou figure une valeur ! Beaucoup de langages ont repris cette façons de faire....

4) dans tous les exemples que j'ai vu, il y a comme préfixe au nom de la fonction 'dbo'.
Qu'est-ce que c'est ce 'dbo' ?

C'est le schéma par défaut de toute base de données dans SQL Server (comme l'est public pour PostGreSQL).
Vous pouvez créer autant de schémas SQL que vous souhaitez et dispatcher vos objets relationnels dans les différents schémas, y compris les y transférer... Un petit exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
-- nous sommes connecté avec sa et sommes donc utilisateur dbo (à ne pas confondre avec le schéma du même nom)
-- pour en être sûr :
SELECT SYSTEM_USER AS CONNEXION, USER ;
GO
 
USE tempdb;
GO
 
CREATE TABLE T_PERSONNE_PRS --> sera créé dans dbo, sauf si l'utilisateur n'a pas dbo comme schéma par défaut
(PRS_ID        INT IDENTITY PRIMARY KEY,
 PRS_NOM       VARCHAR(32) NOT NULL);
GO
-- création d'un schéma SQL particulier :
CREATE SCHEMA S_CONTACT;
GO
 
-- testons un utilisateur avec ce nouveau schéma par défaut:
CREATE USER U_CON
WITHOUT LOGIN
WITH DEFAULT_SCHEMA = S_CONTACT;
GO
-- donnons lui le privilège de créer des tables
GRANT CONTROL TO U_CON;
GO
 
-- faisons nous passer pour U_CON :
EXECUTE AS USER = 'U_CON';
GO
 
-- et créons une table sns préciser le préfixe de schéma
CREATE TABLE T_TEL
(TEL_ID        INT IDENTITY PRIMARY KEY,
 PRS_ID        INT NOT NULL REFERENCES dbo.T_PERSONNE_PRS(PRS_ID),
 TEL_NUM       CHAR(20) NOT NULL) 
GO
 
-- revenons à notre utilisateur précédent.
REVERT;
GO
 
-- créons un 2e schéma
CREATE SCHEMA S_REF;
GO
 
-- et une table dans ce schéma SQL (tables des types de téléphone)
CREATE TABLE S_REF.T_TEL_TYPE_TTP
(TTP_ID        INT IDENTITY PRIMARY KEY,
 TTP_LIBELLE   VARCHAR(32) NOT NULL);
GO
 
-- accrochons la à la table des téléphones
ALTER TABLE S_CONTACT.T_TEL
   ADD TTP_TYPE INT 
       REFERENCES S_REF.T_TEL_TYPE_TTP (TTP_ID) ;  
GO
 
-- ou sont mes tables ???
SELECT * FROM INFORMATION_SCHEMA.TABLES
GO
 
--créons un dernier schéma :
CREATE SCHEMA S_TOUT;
GO
 
-- finalement transférons tous nos objets dans ce schéma :
ALTER SCHEMA S_TOUT TRANSFER S_CONTACT.T_TEL;
ALTER SCHEMA S_TOUT TRANSFER S_REF.T_TEL_TYPE_TTP;
ALTER SCHEMA S_TOUT TRANSFER dbo.T_PERSONNE_PRS;  
GO
 
-- ou sont passées mes tables ???
SELECT * FROM INFORMATION_SCHEMA.TABLES
GO

En sus de la gestion des privilèges au niveau des schémas (ce qui permet une sécurité générique par "héritage", il y a un autre intérêt, peu connus, normatif, mais génial...
A lire : http://blog.developpez.com/sqlpro/p5...des_schema_sql

A +

[Artemus24]

Salut SQLPRO.

J'ai modifié mes quelques scripts, et en effet, avec un "use tempdb, c'est bien plus rapide à l'exécution. Je vais adopter cette excellente idée. Merci pour l'idée !

2) je constate que le point-virgule n'est plus nécessaire pour séparer les différentes requêtes.

Il n'a jamais été nécessaire dans Transact SQL ...

J'ai dit cela, car dans beaucoup d'exemples, je voie encore ce point-virgule qui me semble non nécessaire.

C'est le schéma par défaut de toute base de données dans SQL Server

Je vais passer pour un idiot (il vaut mieux poser la question que de l'être), mais je n'ai jamais bien compris cette notion de schema, même quand je faisais du DB2 Z/OS.
Je ne pense pas que cela soit synonyme d'une database, mais que cela regroupe plutôt le concept d'environnement.
C'est-à-dire un regroupement des permissions, des fonctions utilisateurs, de l'espace alloués ...
Peux-tu m'éclairer un peu plus à ce sujet ?
Et est-ce que cette notion de schema est la même dans tous les SGBD ?

@+

[aieeeuuuuu]

Bonjour,

concernant le point virgule, il n'est en effet pas nécessaire en général (sauf comme évoqué par SQLPro lors de l'utilisation de CTE, ou du service broker).
Il est cependant maintenant requis par la norme SQL.

Il pourrait aussi l'être prochainement par SQL Server :

;	Transact-SQL statement terminator.Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

C'est donc plutôt une bonne habitude à prendre !

[SQLpro]

Salut SQLPRO.

J'ai modifié mes quelques scripts, et en effet, avec un "use tempdb, c'est bien plus rapide à l'exécution. Je vais adopter cette excellente idée. Merci pour l'idée !

En fait tempdb ne fonctionne pas comme les autres db et journalise moins. En sus elle est détruite à chaque démarrage de l'instance (données volatiles) elle est donc "naturellement" plus rapide et plus sûre pour les tests.

J'ai dit cela, car dans beaucoup d'exemples, je voie encore ce point-virgule qui me semble non nécessaire.


Je vais passer pour un idiot (il vaut mieux poser la question que de l'être), mais je n'ai jamais bien compris cette notion de schema, même quand je faisais du DB2 Z/OS.
Je ne pense pas que cela soit synonyme d'une database, mais que cela regroupe plutôt le concept d'environnement.

C'est plus proche de la notion de bibliothèque ou du moderne mais identique concept de "namespace" sauf qu'il n'y a qu'un seul niveau. Cela fait partie de la norme SQL et tous les SGBDR pratique la notion de schéma SQL, sauf... MySQmerde !

C'est-à-dire un regroupement des permissions, des fonctions utilisateurs, de l'espace alloués ...

C'est purement logique. Donc oui pour les privilèges non pour l'espace (notion purement physique)

Peux-tu m'éclairer un peu plus à ce sujet ?
Et est-ce que cette notion de schema est la même dans tous les SGBD ?

@+

Quelques extrait de mes livres :

***********************************
SQL - 4e édition - collection Synthex - Pearson
***********************************

3.2.3 Catalogues et shémas SQL

Comme nous venons de le définir, le terme CATALOG permet de définir une collection de schémas SQL.
La notion de SCHEMA recouvre ce que l'on a pris l'habitude d'appeler base de données, mais va plus loin que ce simple vocable laisse sous entendre. En fait le schéma est un concept SQL qui permet de créer de toutes pièces tous les éléments d'une base de données sans que l'ordre ait une importance. Un SCHEMA est le nom logique que l'on donne à un espace d'objets au sein d'une base de données.
A l'aide d'une spécification de schéma on peut commencer par créer un utilisateur et ses privilèges sur une vue qui sera créée après, vue qui elle même peut précéder la création des tables sur laquelle elle repose. Cette particularité peut paraître surprenante, mais elle possède deux avantages : ne pas se soucier de l'ordre de dépendance des objets, et par ce fait permettre de créer des contraintes mutuellement dépendantes.

L'ordre SQL de création d'un schéma revêt la syntaxe suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE SCHEMA { nom_schéma |
                AUTHORIZATION propriétaire |
                nom_schéma AUTHORIZATION propriétaire }
   [ DEFAULT CHARACTER SET jeu_de_caractères ]
   [ PATH nom_schéma {, nom_schéma ... } ] ]
   [ <objet> | <privilège> [, <objet> | <privilège> ...] ]
 
<objet> ::= 
CREATE { DOMAIN | TABLE | VIEW | ASSERTION |
         CHARACTER SET | COLLATION | 
         TRANSLATION | TRIGGER | TYPE |
         PROCEDURE | FUNCTION | ROLE } définition_objet
 
<privilège> ::= 
GRANT définition_privilège

Le nom d'un schéma est libre. Le propriétaire est en fait l'utilisateur qui créé les objets dans le schéma. Un schéma à toujours un propriétaire.
La clause PATH permet de définir un nom qui pourra être utilisé pour qualifier certaines routines (fonctions, procédures).

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE SCHEMA K_CATALOG1.B_COMMERCIAL AUTHORIZATION U_MONA_LISA
DEFAULT CHARACTER SET LATIN1
 
GRANT SELECT ON V_CLIENT_CLI
      TO U_LEONARDO_DA_VINCI
 
CREATE VIEW V_CLIENT_CLI
AS
    SELECT * FROM T_CLIENT_CLI
 
CREATE TABLE TAB_CLIENT_CLI 
(CLI_ID INTEGER NOT NULL PRIMARY KEY,
 PSP_ID INTEGER FOREIGN KEY REFERENCES T_PROSPECT_PSP (PSP_ID),
 CLI_NOM CHAR(16))
 
CREATE TABLE T_PROSPECT_PSP
(PSP_ID INTEGER NOT NULL PRIMARY KEY,
 CLI_ID INTEGER FOREIGN KEY REFERENCES T_CLIENT_CLI (CLI_ID),
 NOM CHAR(16))

Dans cet exemple nous commençons par créer un privilège pour l'utilisateur U_LEONARDO_DA_VINCI (cet utilisateur SQL est supposé exister) sur une objet de nom V_CLIENT_CLI qui n'a pas encore été créé. Ensuite nous créons l'objet V_CLIENT qui est une vue basé sur une table ou une vue de nom T_CLIENT_CLI qui n'a pas encore été créée. Ensuite nous l'objet T_CLIENT_CLI qui est une table avec une contrainte de référence de clef étrangère envers une table qui n'a pas encore été crée et qui s'appelle T_PROSPECT_PSP.
Les objets table et vue sont la propriété de l'utilisateur U_MONA_LISA (utilisateur SQL supposé pré exister). Enfin, tout ceci est créé dans un schéma de nom B_COMMERCIAL sous le catalogue K_CATALOG1 (bases de données).

L'idée du concept de schéma est double : pouvoir décrire toute ou partie d'une base de données sans jamais avoir à se préoccuper du sens dans lequel chaque objet doit être créé du fait des interdépendances, et pouvoir créer des objets mutuellement dépendants. C'est le cas dans notre exemple entre les tables T_CLIENT_CLI et T_PROSPECT_CLI qui se référencent l'une l'autre.

Bien entendu SQL propose un ordre de suppression de schéma :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DROP SCHEMA [ nom_schema ] { RESTRICT | CASCADE }

Qui permet de supprimer le schéma courant : s'il ne contient ni ne réfère rien avec l'option RESTRICT, sinon, en détruisant en chaîne tous les objets créés ou référencés dans ce schéma à l'aide de l'option CASCADE.


***********************************
SQL Server 2014 - Eyrolles
***********************************


Schémas SQL

Le but des schémas SQL est d’assurer un cloisonnement logique des objets au sein d’une base. Prenons
l’exemple d’une base dans laquelle on trouverait pêle-mêle plusieurs centaines de tables, de vues de
procédures, sans aucune classification… Or, il n’est pas possible de créer un quelconque objet de base
de données, sans qu’il figure dans un schéma SQL. En l’absence de précision, le schéma utilisé par
défaut sera dbo (qui correspond aussi au nom d’un utilisateur particulier associé au schéma dbo dans
SQL Server), qu’il n’est pas possible de supprimer.

Le cloisonnement par schéma SQL présente un très grand intérêt tant pour l’organisation que pour la
sécurité. En effet, il est plus facile de retrouver une table particulière dans un ensemble restreint et
déterminé. Mais il est encore plus intéressant de savoir que la sécurité peut être gérée au niveau des
schémas SQL plutôt que directement au niveau des objets, ce qui facilite grandement la gestion de la
sécurité pour les évolutions de la base de données.

De la même façon qu’une base de données possède un schéma par défaut (dbo), chaque utilisateur de
cette base en possède un également. Le schéma de la base de données est immuable, contrairement au
schéma par défaut d’un utilisateur qui peut être quelconque et modifié à votre guise.

En principe, tout objet d’une base (vue, table, procédure, fonction, type…) doit être identifié par le
couple d’information formé par son schéma et son nom, séparés par un point, soit nom_schema.nom_objet.
Il peut être omis de préciser le préfixe de schéma lorsque l’objet que l’on vise est contenu dans le
schéma par défaut de la base ou dans celui de l’utilisateur.

C’est pour cette raison qu’il est déconseillé qu’un compte de connexion de type Windows soit le créateur d’une base
de données. En effet, en cas de portage de cette base dans un autre environnement que celui d’origine (cas fréquent
chez les éditeurs de logiciels), la base pourrait connaître certains dysfonctionnements. À moins qu’il existe une particularité
propre à votre organisation, il convient donc de créer toute nouvelle base de données sous un compte de
connexion purement SQL, le plus générique possible, comme c’est le cas avec le compte sa.

NOTA : Il existe toutefois des objets non relationnels qui, par principe, ne peuvent figurer dans aucun schéma. Il s’agit des
objets de Service Broker (contrat, type de message, liaison de service distant, routage, service), des objets de cryptage
(clés, certificats), des objets relatifs à la sécurité (utilisateur, rôles), des assembly, des catalogues d’indexation
textuelle et bien entendu des schémas eux-mêmes !

ATTENTION : L’omission du préfixe de schéma quand celui-ci correspond au schéma par défaut nécessite plus de travail pour le
moteur SQL. En effet, il doit constamment chercher où se trouve l’objet considéré en essayant successivement de le
trouver dans le schéma par défaut de l’utilisateur, puis dans celui de la base de données. Soyez donc clair et précis,
préfixez toujours vos objets par le nom du schéma, même s’il s’agit du schéma dbo, sauf cas très particulier…

Création des schémas SQL

Vous pouvez créer autant de schéma SQL que vous le souhaitez. Il est recommandé d’en créer un pour
chaque grand pan fonctionnel (découpage vertical) ou technique (découpage horizontal) de votre
application.

Prenons l’exemple d’une application de type ERP (Enterprise Resource Planning). Nous pouvons ainsi
créer des schémas fonctionnels pour la vente, la production, le stock, les ressources humaines, le commercial,
le marketing, la comptabilité… et au niveau technique pour les données de référence (tables
d’aide à la saisie telles que les civilités, les types d’adresses, calendrier…), les données système (telles
que les tables d’utilisateurs, de gestion de versions…) ou encore les données externes (tables de codes
postaux, nomenclatures administratives…). Voici la syntaxe minimale pour la création d’un schéma
SQL au niveau d’une base de données :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE SCHEMA nom_schema
[AUTHORIZATION nom_proprietaire] [;]

Si vous ne précisez pas le nom du propriétaire (un utilisateur SQL), l’utilisateur qui lance la commande
sera considéré comme étant le propriétaire du schéma. Il possède alors tous les droits sur tous les objets
contenus dans le schéma, y compris le schéma lui-même.

Modification des schémas

Une fois créé, un schéma ne peut être modifié dans sa structure qu’au niveau du propriétaire. La commande
à utiliser est la suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER AUTHORIZATION
ON SCHEMA::nom_schema TO utilisateur_SQL[;]

Par ailleurs, il est possible de modifier le contenu d’un schéma. Vous pouvez ainsi créer un ou plusieurs
objets relationnels comme une table, une vue ou une procédure, les modifier ou les supprimer.
La commande suivante permet quant à elle de transférer un objet d’un schéma à un autre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER SCHEMA nom_schema
TRANSFER [type_d'entite :: ] objet [;]

Supposons que la base de données contienne les utilisateurs SQL USR_COMPTA et USR_RH, ainsi qu’une table
T_EMPLOYE et une vue V_EMPLOYE_SALAIRE, toutes deux créées dans le schéma de la base par défaut (dbo). Au
terme des opérations suivantes, la base contient deux nouveaux schémas (S_RH et S_COMPTA) et deux objets
ont changé de schéma, à savoir la table S_RH.T_EMPLOYE et la vue S_COMPTA.V_EMPLOYE_SALAIRE.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- On crée le schéma S_COMPTA
CREATE SCHEMA S_COMPTA;
GO
-- On crée le schéma S_RH, propriété de l’utilisateur USR_RH
CREATE SCHEMA S_RH AUTHORIZATION USR_RH;
GO
-- On transfère la propriété du schéma S_COMPTA à l’utilisateur USR_COMPTA
ALTER AUTHORIZATION ON SCHEMA::S_COMPTA TO USR_COMPTA;
GO
-- On transfère la vue dbo.V_EMPLOYE_SALAIRE dans le schéma S_COMPTA
ALTER SCHEMA S_COMPTA TRANSFER dbo.V_EMPLOYE_SALAIRE;
GO
-- On transfère la table dbo.T_EMPLOYE dans le schéma S_RH
ALTER SCHEMA S_RH TRANSFER dbo.T_EMPLOYE;
GO

Pour supprimer un schéma, vous devez utiliser l’instruction DROP SCHEMA…, à condition qu’il ne contienne
plus aucun objet.

Sécurité au niveau des schémas

Gérer la sécurité d’une base au niveau des schémas est l’une des choses les plus structurantes, les plus sures et
les plus évolutives possibles. En effet, le schéma étant un « conteneur », une fois la sécurité mise en place par
le biais des privilèges et des utilisateurs, il ne reste qu’à glisser l’objet dans le bon schéma pour que la sécurité
s’applique immédiatement. Il n’y a donc rien à faire de plus que de créer l’objet au bon endroit !

Schémas particuliers

Lors de la création d’une nouvelle base de données, SQL Server y ajoute automatiquement plusieurs
objets particuliers (utilisateurs et schémas SQL) :
• INFORMATION_SCHEMA et sys : utilisateurs propriétaires des schémas de métadonnées INFORMATION_SCHEMA
(vues normative SQL) et sys (vues systèmes Microsoft SQL Server).
• db_… (db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader,
db_denydatawriter, db_owner et db_securityadmin) : ces neuf schémas sont créés pour éviter que vous les
créiez vous-même. Ils sont censés empêcher certaines manipulations et c’est pourquoi ils correspondent
à des rôles prédéfinis. Vous pouvez néanmoins les supprimer.
• guest : schéma affecté à l’utilisateur guest (voir plus loin).



A +

[Artemus24]

Salut SQLPRO.

Merci pour toutes ses explications !

@+