Discussion :

[StringBuilder]

Bonjour,

J'ai par exemple deux tables dont voici le DDL :

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
 
create table dbo.sexe
(
  id int identity not null primary key,
  libelle varchar(10) not null unique
);
 
create table dbo.personne
(
  id int identity not null primary key,
  sexe_id int null references dbo.sexe(id),
  nom varchar(50) not null,
  email varchar(256) not null unique
);
 
create unique index uix_personne_email on dbo.personne(email);

J'ai quelques questions basiques, dont je pense connaître déjà les réponses, mais je voudrais être certain. En effet, si le SQL est un langage fortement sémantique, je trouve qu'il y a beaucoup de "non dit", ou plus exactement d'automatismes qui dépassent parfois, ou pas, la volonté de l'utilisateur.

1/ Lorsque j'ai le mot clé PRIMARY KEY, de facto, il y a un CLUSTERED UNIQUE INDEX qui est créé, n'est-ce pas ?
2/ Lorsque j'ai le mot clé REFERENCES, de facto, il y a un NONUNIQUE INDEX posé sur la colonne, n'est-ce pas ? Si la colonne est nullable, quel est l'impact sur l'index associé ?
3/ Lorsque j'ai le mot clé UNIQUE, là, je sèche un peu... Un contrainte UNIQUE n'implique pas NOT NULL, donc contrairement à ce que j'ai pu lire sur des forums, ce n'est pas forcément une clé alternative qui est créée. Est-ce un index unique ?
4/ Comment je fais si je veux que ma clé primaire ne soit pas organisée en cluster, mais que ce soit un autre index, pas forcément unique ? Actuellement je me contente de mettre "cluster" en face de la colonne désirée. Mais si la table existe déjà ?

Et enfin, la dernière question qui tue : est-ce que les réponses aux 3 premiers points ci-dessus sont identiques sur tous les SGBD (à défaut, au moins Oracle) ?

En effet, il y a très longtemps, à l'âge d'or des dinosaures, quand j'ai appris le SQL sur Ingres, les "automatismes" su-cités n'étaient tout bonnement pas présents.
Un contrainte d'unicité n'impliquait en rien la création d'un index. Pas plus même qu'une clé primaire, il fallait faire les deux ! Ou alors mon prof était franchement à la rue, ce qui n'est pas forcément impossible.
Et lorsque les dinosaures sont morts, remplacés par quelques homo érectus, on m'a expliqué un jour qu'une base de données Oracle ne contenant pas de clés primaires mais que des index uniques, pour la même raison (et encore plus loin, que les deux ne pouvaient pas cohabiter !) .A nouveaux, gros doutes, mais après tout, tout est possible la preuve, Oracle est toujours le SGBD le plus utilisé avec MySQL...

[Oishiiii]

Hello,

Il faut bien garder à l'esprit qu'il existe un principe d'indépendance entre le niveau logique et le niveau physique d'une base de données.

Au niveau logique on manipule des tables en SQL, on peut définir des contraintes d'unicité des clés primaires, des clés étrangères, etc.
Au niveau physique, là où on nottament on s'intérèsse aux performances, on utilise des indexs, en arbre ou non, clustered ou pas, on partitionne, etc.

Qu'un SGBDR créé automatiquement un index au niveau physique lorsque l'on créé une contrainte d'unicité, il faut le savoir lorsqu'on porte la casquette DBA mais l'utilisateur lambda ne s'intéresse qu'au niveau logique.

[aieeeuuuuu]

Bonjour,

1/ Lorsque j'ai le mot clé PRIMARY KEY, de facto, il y a un CLUSTERED UNIQUE INDEX qui est créé, n'est-ce pas ?

Exact, c'est le comportement par défaut de SQL Server. Vous pouvez cependant spécifier NONCLUSTERED dans la déclaration de votre clef primaire afin que l'index ne soit pas cluster.

2/ Lorsque j'ai le mot clé REFERENCES, de facto, il y a un NONUNIQUE INDEX posé sur la colonne, n'est-ce pas ? Si la colonne est nullable, quel est l'impact sur l'index associé ?

Non, l'index sur la clef étrangère n'est pas créé automatiquement.

3/ Lorsque j'ai le mot clé UNIQUE, là, je sèche un peu... Un contrainte UNIQUE n'implique pas NOT NULL, donc contrairement à ce que j'ai pu lire sur des forums, ce n'est pas forcément une clé alternative qui est créée. Est-ce un index unique ?

Lors de la déclaration d'un contrainte d'unicité, un index unique est effectivement créé. D’ailleurs, il ne peut pas être supprimé (il faut supprimer la contrainte d'unicité, ce qui entraine aussi automatiquement la suppression de l'index unique associé).
il est possible de créer une contrainte (ou un index) unique sur une colonne nullable, mais SQL server à un défaut d'implémentation de la norme SQL sur ce point, et n'autorisera qu'une seule ligne à NULL. Il est cependant possible de contourner ce comportement en plaçant un index unique filtré (sur les NULL).

4/ Comment je fais si je veux que ma clé primaire ne soit pas organisée en cluster, mais que ce soit un autre index, pas forcément unique ? Actuellement je me contente de mettre "cluster" en face de la colonne désirée. Mais si la table existe déjà ?

Lors de la création de la table, comme indiqué plus haut, vous pouvez spécifier NONCLUSTERED lors de la déclaration de la clef primaire.
Après coup, il me semble qu'il faut supprimer l'index cluster et le recréer non cluster pour pouvoir définir un autre index cluster.
Attention toutefois aux impacts que cela peut avoir sur les autres requêtes. en alternative, afin de garder votre index cluster sur la clef primaire, vous pouvez créer une vue indexée, ou simplement un index non cluster avec colonnes incluses. Mais comme toujours dans les index, c'est à voir au cas par cas.

[StringBuilder]

Pour le point 2, merci pour la rectification, car je croyais qu'un index était automatiquement créé.

Quant au point 3, effectivement, j'ai été très surpris en faisant des tests pendant que j'écrivais mon post de voir que SQL Server ne supportait qu'une ligne à NULL dans un index unique... (aussi bien avec un INDEX qu'une contrainte), alors que de mémoire, c'est qui ce qui censé différencier les deux dans la norme SQL : contrainte = 1 seul NULL autorisé, et INDEX = plusieurs NULL autorisés, si je ne m'abuse.

En tout cas, merci pour ces précisions.

[SQLpro]

1/ Lorsque j'ai le mot clé PRIMARY KEY, de facto, il y a un CLUSTERED UNIQUE INDEX qui est créé, n'est-ce pas ?

Oui. Tous les SGBDR créée un index dès qu'il existe une contrainte d'unicité quelle soit PK ou pas. La notion d'index CLUSTERED propre à SQL Server, fait que seul SQL Server utilise ce type d'index pour une PK par défaut. En précisant NONCLUSTERED dans la définition de contrainte PK, alors ce ne sera pas un index CLUSTERED

2/ Lorsque j'ai le mot clé REFERENCES, de facto, il y a un NONUNIQUE INDEX posé sur la colonne, n'est-ce pas ? Si la colonne est nullable, quel est l'impact sur l'index associé ?

Non, aucun index n'est créé. A me lire :
http://sqlpro.developpez.com/cours/quoi-indexer/#LIV
http://sqlpro.developpez.com/cours/quoi-indexer/#LVI-A

3/ Lorsque j'ai le mot clé UNIQUE, là, je sèche un peu... Un contrainte UNIQUE n'implique pas NOT NULL, donc contrairement à ce que j'ai pu lire sur des forums, ce n'est pas forcément une clé alternative qui est créée. Est-ce un index unique ?

Une contrainte UNIQUE est une clef alternative ou subrogée (littéralement "qui peut être utilisée à la place de ..." ou "qui peut se substituer à ..." la clef primaire). Tous les SGBDR créée un index dès qu'il existe une contrainte d'unicité quelle soit PK ou pas. Dans le cas de SQL Server c'est un index NONCLUSTERED (puisque le CLUSTERED est déjà utilisé par la PK en principe et qu'une table ne peut avoir qu'un seul index CLUSTERED, puisque l'index CLUSTERED c'est la table elle même)

4/ Comment je fais si je veux que ma clé primaire ne soit pas organisée en cluster, mais que ce soit un autre index, pas forcément unique ? Actuellement je me contente de mettre "cluster" en face de la colonne désirée. Mais si la table existe déjà ?

Précisez NONCLUSTERED ou CLUSTERED, selon. Mais attention à l'impact.
1) un index CLUSTERED et non UNIQUE, comme un index sur une donnée de grande taille est particulièrement catastrophique pour les performances !
http://blog.developpez.com/sqlpro/p5...t_ce_que_c_est

Et enfin, la dernière question qui tue : est-ce que les réponses aux 3 premiers points ci-dessus sont identiques sur tous les SGBD (à défaut, au moins Oracle) ?

Sauf pour la notion CLUSTERED, oui

A nouveaux, gros doutes, mais après tout, tout est possible la preuve, Oracle est toujours le SGBD le plus utilisé avec MySQL...

Ça fait un moment que Oracle n'est plus leader en matière de SGBDR !!!
A lire : http://blog.developpez.com/sqlpro/p1...-declin-oracle

A +