Discussion :

[informer]

Bonjour à tous,

Dans toutes mes tables je crée :

1. Un champ auto incrément qui est la clé technique
2. Des champs composant la clé unique véritable que je qualifie de fonctionnelle. Sur ce groupe de champs je declare une contrainte d'unicité ordonnée

Par défaut, SQL Server applique le CLUSTER sur la clé primaire considérant que la "CLUSTERISATION" ne peut être définie qu'une fois par table.

Ma question est faut-il

1. Appliquer le CLUSTER sur la clé primaire (auto incrément) ou sur la clé fonctionnelle (groupe de champs) ?
2. Voire déclarer la clé fonctionnelle comme clé primaire et appliquer une contrainte d'unicité sur le champ auto incrément ( redondant puisque incremental)et qui seront les CLES ÉTRANGÈRES dans les autres tables ?

Merci par avance pour toute aide et de ne pas lancer une contreverse sur le bien-fondé de la clé technique.

[escartefigue]

Bonjour informer

Bonjour à tous,

Dans toutes mes tables je crée :

1. Un champ auto incrément qui est la clé technique
2. Des champs composant la clé unique véritable que je qualifie de fonctionnelle. Sur ce groupe de champs je declare une contrainte d'unicité ordonnée

Il ne faut pas confondre les champs qui sont les zones de saisie des formulaires et les colonnes des tables relationnelles.
Il ne faut pas non plus confondre les colonnes de type "auto_increment" propres à MySQL / MariaDB et les colonnes de type "identity" de SQL server.
Contrairement à MySQL, SQL server comme beaucoup d'autres SGBD autorise un décrément.

faut-il [...] déclarer la clé fonctionnelle comme clé primaire et appliquer une contrainte d'unicité sur le champ auto incrément (redondant puisque incremental)et qui seront les CLES ÉTRANGÈRES dans les autres tables ?

La contrainte unique sur une colonne identity n'est pas une redondance, sans cette contrainte, en activant la possibilité de forcer la valeur de la colonne identity (SET IDENTITY_INSERT ma_table ON), on peut insérer plusieurs fois la même valeur !
Et utiliser une ou plusieurs colonnes fonctionnelles comme PK est ce qu'il ne faut jamais faire : une colonne fonctionnelle possède un contenu qui peut changer. Auquel cas, la propagation en cascade du changement dans les FK peut provoquer des mises à jour en masse dans les tables.

[informer]

Bonjour escartefigue

Merci pour ton retour et retiens les éléments suivants

1. La PK, champ auto incrémenté par identity peut potentiellement 'dérailler', aussi est-il judicieux de déclarer une contrainte d'unicité sur la PK
2. Les champs de la clé fonctionnelle,permettant l'unicité des enregistrements, ne doivent pas être déclarés comme champs de la PK
3. Une contrainte d'unicité doit être déclarée sur les champs de la clé fonctionnelle

Reste la question du CLUSTER et sur quelle clé on doit le déclarer ?

[escartefigue]

Bonjour escartefigue

Merci pour ton retour et retiens les éléments suivants

1. La PK, champ auto incrémenté par identity peut potentiellement 'dérailler', aussi est-il judicieux de déclarer une contrainte d'unicité sur la PK
2. Les champs de la clé fonctionnelle,permettant l'unicité des enregistrements, ne doivent pas être déclarés comme champs de la PK
3. Une contrainte d'unicité doit être déclarée sur les champs de la clé fonctionnelle

Reste la question du CLUSTER et sur quelle clé on doit le déclarer ?

Pour le premier point
Dérailler n'est pas un terme adapté car ce n'est pas un bug, une colonne de type identity n'est pas naturellement unique par construction.
Mais, comme le plus souvent on laisse le SGBD calculer la valeur des colonnes identity, le risque de valeurs en doublons est marginal.
Cela étant dit, si la colonne (et non pas champ) identity est déclarée comme PK, ajouter une contrainte unique est inutile, puisque qui dit PK dit forcément unique.


Pour le deuxième point
On peut tout à fait déclarer une contrainte unique sur une clef candidate fonctionnelle, que cette clef candidate soit mono ou multicolonne, mais, en effet, il est très dangereux de déclarer une contrainte PK sur celle-ci.


Pour le troisième point
Une contrainte d'unicité ne doit être déclarée sur une clef fonctionnelle que si les règles de gestion le stipulent.
il existe de nombreuses clefs fonctionnelles non uniques.


Concernant le choix de l'index cluster
L'index cluster correspond au critère de rangement physique des lignes après réorg.
Il est intéressant de choisir un index cluster qui correspond à un critère de recherche séquentiel fréquent, car il permet de se positionner sur la première ligne correspondant au critère puis de balayer les lignes suivantes sans recherche supplémentaire.

[SQLpro]

Pour appuyer encore plus...
La clé primaire autoincrémentée de type IDENTITY ou via une séquence (et donc mono colonne) devrait toujours être composée par un index de type CLUSTERED. En effet, c'est par ce biais que se font les jointures qui découlent du modèle relationnel.

Comme tous les index "secondaire" (comprenez NONCLUSTERED) incorporent la valeur de la clé primaire, SQL Server ne passent plus par la table et saute d'index en index pour toutes les jointures et recherche. Il n'est donc pas rare dans SQL Server, que le taux de recherche ("seek") par rapport au taux de balayage ("scan") soit de l'ordre de 1000 à 10000, là ou d'autres SGBDR arrivent péniblement à un taux de 30... Ce qui permet à SQL Server de battre systématiquement tous les records de performances par rapport à la concurrence (Oracle, PostgReSQL, MySQL...°.

A +