[Conception] Index et contrainte unique [Résolu]

[JmL40]

Bonjour,

Je débute un projet par la mise en place d'une base de données sous MS SQL Server 2008 R2. Pour optimiser au maximum cette base, je souhaite que la partie conception base de données soit la plus optimale que possible.

C'est pourquoi, je souhaite avoir des renseignements sur les différences possibles et les cas d'utilisation entre un INDEX UNIQUE et une CONTRAINTE UNIQUE.

Pour être concret, j'ai deux tables à savoir, une table MARQUEUR et une table ALLELE. J'utilise dans ces deux tables une PRIMARY KEY de type IDENTITY pour des questions de performances et de paginations. Voici l'exemple de mes deux tables avec ma vision sans CONTRAINTE mais INDEX UNIQUE dans le cas 1 et ma vision avec CONTRAINTE et INDEX UNIQUE dans le cas 2 :

Code :

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
 
 
-- CAS 1 : CONTRAINTE & INDEX UNIQUE
 
-- TABLE MARQUEUR : INDEX UNIQUE SUR LA COLONNE MARQUEUR
--			        PRIMARY KEY LAB_MARQUEUR_ID
CREATE TABLE [dbo].[MARQUEUR]
(
	[LAB_MARQUEUR_ID] [int] IDENTITY(1,1) NOT NULL,
	[MARQUEUR] [varchar](15) NOT NULL,
	CONSTRAINT [PK_LAB_MARQUEUR] PRIMARY KEY CLUSTERED ([LAB_MARQUEUR_ID] ASC)
)
 
CREATE UNIQUE NONCLUSTERED INDEX [IX_MARQUEUR] ON [dbo].[LAB_MARQUEUR] ([MARQUEUR] ASC)
 
-- TABLE MARQUEUR : INDEX UNIQUE SUR LA COLONNE ALLELE
--			        INDEX SUR LA COLONNE LAB_MARQUEUR_ID (FOREIGN KEY)
--					PRIMARY KEY LAB_MARQUEUR_ALLELE_ID
CREATE TABLE [dbo].[LAB_MARQUEUR_ALLELE]
(
	[LAB_MARQUEUR_ALLELE_ID] [int] IDENTITY(1,1) NOT NULL,
	[ALLELE] [varchar](10) NOT NULL,
	[LAB_MARQUEUR_ID] [int] NOT NULL,	
	CONSTRAINT [PK_LAB_MARQUEUR_ALLELE] PRIMARY KEY CLUSTERED ([LAB_MARQUEUR_ALLELE_ID] ASC),
	CONSTRAINT [FK_LAB_MARQUEUR_ALLELE_LAB_MARQUEUR] FOREIGN KEY([LAB_MARQUEUR_ID])
)
 
CREATE NONCLUSTERED INDEX [LAB_MARQUEUR_ID] ON [dbo].[LAB_MARQUEUR_ALLELE] ([LAB_MARQUEUR_ID] ASC)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ALLELE] ON [dbo].[LAB_MARQUEUR_ALLELE] ([ALLELE] ASC)
 
-- -------------------------------------------------------------------------
 
-- CAS 2 : CONTRAINTE & INDEX UNIQUE
 
-- TABLE MARQUEUR : INDEX UNIQUE SUR LA COLONNE MARQUEUR
--					CONTRAINTE UNIQUE SUR LA COLONNE MARQUEUR
--			        PRIMARY KEY LAB_MARQUEUR_ID
CREATE TABLE [dbo].[MARQUEUR]
(
	[LAB_MARQUEUR_ID] [int] IDENTITY(1,1) NOT NULL,
	[MARQUEUR] [varchar](15) NOT NULL,
	CONSTRAINT [PK_LAB_MARQUEUR] PRIMARY KEY CLUSTERED ([LAB_MARQUEUR_ID] ASC),
	CONSTRAINT [PK_CONSTRAINT_MARQUEUR] UNIQUE ([MARQUEUR])
)
 
CREATE UNIQUE NONCLUSTERED INDEX [IX_MARQUEUR] ON [dbo].[LAB_MARQUEUR] ([MARQUEUR] ASC)
 
-- TABLE MARQUEUR : INDEX UNIQUE SUR LA COLONNE ALLELE
--			        INDEX SUR LA COLONNE LAB_MARQUEUR_ID (FOREIGN KEY)
--					PRIMARY KEY LAB_MARQUEUR_ALLELE_ID
--					CONTRAINTE UNIQUE SUR LES COLONNES MARQUEUR ET LAB_MARQUEUR_ID
CREATE TABLE [dbo].[ALLELE]
(
	[LAB_MARQUEUR_ALLELE_ID] [int] IDENTITY(1,1) NOT NULL,
	[ALLELE] [varchar](10) NOT NULL,
	[LAB_MARQUEUR_ID] [int] NOT NULL,	
	CONSTRAINT [PK_LAB_MARQUEUR_ALLELE] PRIMARY KEY CLUSTERED ([LAB_MARQUEUR_ALLELE_ID] ASC),
	CONSTRAINT [FK_LAB_MARQUEUR_ALLELE_LAB_MARQUEUR] FOREIGN KEY([LAB_MARQUEUR_ID]),
	CONSTRAINT [PK_CONSTRAINT_MARQUEUR_ALLELE] UNIQUE ([ALLELE],[LAB_MARQUEUR_ID])
)
 
CREATE NONCLUSTERED INDEX [LAB_MARQUEUR_ID] ON [dbo].[LAB_MARQUEUR_ALLELE] ([LAB_MARQUEUR_ID] ASC)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ALLELE] ON [dbo].[LAB_MARQUEUR_ALLELE] ([ALLELE] ASC)

En quoi, le fait de déclarer une CONTRAINT et un INDEX UNQUE est plus performant ? un index UNIQUE suffit-il seulement ?
Je pensais aussi à un autre point de vu à savoir mettre LAB_MARQUEUR_ID en PRIMARY KEY dans ALLELE, mais dans ce cas, j'ai une clé composé.

Faut-il réellement dans cette relation entre les deux tables, DECLARER la clé primaire de MARQUEUR en PRIMARY KEY dans ALLELE ? On ne par plus d'ALTERNATIVE KEY à ce moment la ?

J'attends vos réponses, merci à vous !

Cordialement

[hmira]

- La différence entre contrainte unique et index unique n'est toujours bien comprise !
En fait, les contraintes uniques font partie de la la norme SQL et représentent une définition (ou contrainte) établie lors la conception du modèle logique de la base de données. De plus, les contraintes uniques peuvent être créées dans le cadre de la définition même d'une table.

- Chaque fois que tu créé une contrainte unique, SQL Server créé automatiquement, en arrière plan, un index unique correspondant. Et il est impossible de supprimer cet index unique sans au préalable supprimer la contrainte unique. Ce qui rend les contraintes uniques plus robuste et plus sûr.

- Les indexes uniques quant à eux, font partie de la conception du modèle physique des données.

- Du point de vue performance, les contraintes unique et les index uniques sont exactement les mêmes pour l'optimiseur de requêtes. Tu ne verras aucune différence en terme de gain de performances en utilisant l'une ou l'autre.

- Personnellement, je recommande l'utilisation des contraintes uniques. C'est plus clair et correspond plus à la norme SQL.

- Pour revenir à ton exemple, je te conseille de privilégier la solution n° 2 (Cas 2).

A+

[iberserk]

Bonjour,
Tout d'abord bravo pour le sérieux que vous comptez mettre dans la modélisation de votre base de données...vous ne le regretterez pas...

Juste une simple question: qu'entendez vous par:

et de paginations

Si vous parlez de la possibilité d'utiliser la pagination de résultat par exemple lors de l'affichage d'une grille web paginée cela ne vous sera d'aucune utilité car la pagination (fenêtrage) s'appuie sur les fonctions SQL de fenêtrage (ROW NUMBER() etc.) et dépendent du tri appliqué aux données de votre grille?

Pour votre question hmira vous a parfaitement donné la réponse...

[SQLpro]

Envoyé par hmira

- Personnellement, je recommande l'utilisation des contraintes uniques. C'est plus clair et correspond plus à la norme SQL.

ATTENTION :car les contraintes UNIQUE de SQL Server ne sont pas conforme à la norme SQL.
En effet une contrainte se doit de pouvoir avoir de multiples NULLs. Or SQL Server ne l'accepte pas.
Pour contourner ce problème il suffit depuis 2008 de faire une index UNIQUE filtré (WHERE macolonne IS NOT NULL).

A +

[JmL40]

Merci beaucoup pour vos réponses, je met en application de suite ....

Cordialement