Discussion :

[americ]

Bonjour,
j'ai une question concernant une bonne pratique.
je voudrai créer des index pour faciliter mes intégrations SSIS.

Création d'une primary key PK sur la table dbo.TABLE1

Mon where contient les filtres suivant
COL1 = identifiant source (INT)
COL2 = libelle source (VARCHAR)

Le mieux est :
Choix 1 - crée deux index non cluster
IDX_COL1 NonCluster NonUnique
IDX_COL2 NonCluster NonUnique

Choix 2 - crée un index NonCluster Unique composé (PK + COL1 + COL2 )

ajouter le PK dans l'index composé est -il cohérent ?

MErci de pour votre aide

[Boubou2020]

en terme coût il est préférable d’utiliser le choix n:2 moins d'espace disque surtout si la table est très volumineux
Dans les deux cas vous aurez la couverture du vos requêtes avec des index seek
autre remarque la création du PK va assurer une création automatique d'un index cluster donc c'est pas nécessaire de mettre (PK + ....)

[americ]

Merci pour ta réponse.

[SQLpro]

Ce que vous nous donnez n'est absolument pas suffisant pour un réponse adéquate et la réponse fournie par abdallah_mehdoini est stupide !

En effet, tant que nous ne pouvons pas voir quel est la clause WHERE en question et plus globalement le reste de votre requête il est strictement impossible de prédire quel sera le meilleur index.

En sus, nous ne savons rien de la composition de la table ni de sa PK....

Donnez nous tout ceci sous forme SQL DDL. En cela vous respecterez la charte de postage !!! http://www.developpez.net/forums/d96...vement-poster/

A +

[americ]

Stupide carrément ? Disons quelle était mauvaise pour rester courtois. Désolé je n'avais pas de requête à ce moment, c'etait une question ouverte car j'étais en pleine réflexion.
Je confirme que le choix 2 n'est pas une bonne pratique.
J'ai fini par créer deux index non-cluster sur mes deux champs respectifs utilent à mes 2 critères de recherches.
j'en ai profité pour bachoter les principes de création d'index.

[hmira]

J'ai fini par créer deux index non-cluster sur mes deux champs respectifs utilent à mes 2 critères de recherches.

Cette réponse peut, elle aussi, être considérée comme stupide (pardon une mauvaise réponse, nous sommes après tout de parfaits GENTLEMAN !) , certes beaucoup moins catastrophique que celle d'abdallah_mehdoini, mais une mauvaise réponse quand même, si dans toutes vos requêtes les 2 colonnes sont systématiquement présentes dans la clause WHERE.
En effet, dans de tel contexte, l'index le plus sélectif sera utilisé (du moins on peu l'espérer parce que, comme l'a dit SQLPro, nous n'avons aucune information ni sur sur la structure de vos tables ni sur la requête SQ) , et l'autre index ne sera peut-être jamais utilisé !
Or, un index a des avantages et et des inconvénients, présentés sommairement, on peut dire ceci :
Avantage : Optimisation de l'accès aux données
Inconvénient : Coût des mises à jour de l'index après chaque opération Insert, Update , Delete, ..
Donc , un index qui ne sera jamais utilisé est non seulement inutile, mais en plus a des coûts parfois non négligeables, coûts inhérents à sa maintenance.

A+

[elsuket]

Bonjour,

Je viens de remettre la main sur un petit billet que j'avais écrit il y a maintenant longtemps :

Différence entre un index ordonné en cluster et un index non-cluster

En le relisant je me rends compte qu'il faudrait que je le polisse un peu.
Si l'on s'en tient uniquement aux index cluster et non cluster (exit les index XML, spatiaux, columnstore, hash et range), on peut présenter ceux-ci par analogie aux index que l'on trouve dans les livres.

L'index au début du livre est un index cluster : il présente les notions exposées dans le livre suivant l'ordre physique des pages, et chaque page dispose d'un numéro qui permet de la trouver rapidement.

L'index en fin de livre est un index non-cluster : il présente les notions exposées dans le livre suivant l'ordre alphabétique de mots-clé sélectionnés par les auteurs. Son tri est donc purement logique. A chaque mot de cet index est associé le numéro de la page, c'est à dire une des valeurs de l'index cluster. Dans certains ouvrages, on trouve aussi une bibliographie, qui pointe aussi vers les pages où tel ou tel ouvrage a été utilisé : c'est un autre index non-cluster.

Par conséquent, la définition d'un index non-cluster ne doit pas inclure les colonnes participant à la clé primaire : les valeurs de celles-ci y sont nécessairement référencées.

Lorsque j'interviewe certains candidats, je remarque très souvent que l'amalgame entre clé primaire et index cluster est fait. Or, on peut tout à fait créer une clé primaire supportée par un index non-cluster. Il en va de même pour les index supportant les contraintes d'unicité : par défaut ces index sont non-cluster, mais on peut spécifier que l'on souhaite qu'ils soient cluster. Par ailleurs, il me semble que les index ne font pas partie de la norme SQL.

En espérant vous avoir éclairés,

@++

[Sergejack]

Si tu as deux clauses dans ton WHERE, il est peu probable (selon mon expérience) que SQL Server 2008r2 utilise et combien 2 indexes.

L'important quand vous faites un index composé de plusieurs colonnes est de bien comprendre que SQL Server ne peut pas ignorer la première colonne pour se contenter de la seconde.

Pour illustrer ceci, imagine que je te demande de trouver dans un dictionnaire tous les mots qui commencent par A : c'est facile.
Je te demande ensuite d'y trouver tous les mots qui commencent par AB : c'est facile.
Mais si je te demande de trouver tous les mots qui commencent par n'importe quelle lettre suivie de B : t'es foutu, tu peux parcourir tous le dictionnaire (bien qu'à la main autant chercher AB*, BB*, CB*, ... ce que ne ferait pas SQL Serveur qui ignore s'il y a 27 valeurs possible ou 1 milliards).

Dans un dictionnaire (et l'ordre alphabétique en générale), la clé se conçoit comme la composition [1er lettre] + [2eme lettre] + ...

Donc si tu veux un indexe, mets d'abord les colonnes dont tu es sûr qu'elles participeront aux filtres.

[mikedavem]

Par conséquent, la définition d'un index non-cluster ne doit pas inclure les colonnes participant à la clé primaire : les valeurs de celles-ci y sont nécessairement référencées.

A noter que SQL Server est assez intelligent pour ne pas dupliquer les colonnes dans l'index dans ce cas mais oui pas besoin d'inclure les colonnes participant à la clé primaire comme dit ici

Si tu as deux clauses dans ton WHERE, il est peu probable (selon mon expérience) que SQL Server 2008r2 utilise et combien 2 indexes.

J'ai vu dans certain cas dernièrement l'utilisation de plusieurs index d'une même table (aka index intersection) pour combiner au final un jeu de données global par l'optimiseur de requêtes. (n'oublions pas l'hypothèse d'indépendance faite par l'optimisateur.
Bien entendu ceci dépend fortement des prédicats, des données, des index et de leurs statistiques ...

++

[StringBuilder]

Avec tout ça, on n'a toujours pas la structure de la table, de sa PK, ni des requêtes qui tapent dedans...

En tout cas, si les requête contiennet "where col1 = xxx and col2 = zzz", et que ni col1 ni col2 ne sont présents dans la clé primaire, alors il est absolument débile de rajouter la PK dans l'index...

Et au contraire, si la requête contient "where pk = www and ...", quelle que soit les filtres suivants, l'intérêt de s'occuper d'un index complémentaire à la celui de la clé primaire me semble assez probablement inutile, même s'il existe des cas où les autres clauses pourront être plus drastiques (si y'a un IN sur la PK par exemple)

[SQLpro]

Avec tout ça, on n'a toujours pas la structure de la table, de sa PK, ni des requêtes qui tapent dedans...

En tout cas, si les requête contiennet "where col1 = xxx and col2 = zzz", et que ni col1 ni col2 ne sont présents dans la clé primaire, alors il est absolument débile de rajouter la PK dans l'index...

Et au contraire, si la requête contient "where pk = www and ...", quelle que soit les filtres suivants, l'intérêt de s'occuper d'un index complémentaire à la celui de la clé primaire me semble assez probablement inutile, même s'il existe des cas où les autres clauses pourront être plus drastiques (si y'a un IN sur la PK par exemple)

Débile !!!! Tu as été plus fort que mon stupide...
Attends toi à des foudres.....

A +