Quelle est la différence entre ces deux index dans SQL Server?

La question est, quelles requêtes essayez-vous d'optimiser?

Ces deux indices sont très différents. Le premier index serait idéal pour une requête comme celle-ci:

 SELECT CaseId FROM Rec.RecoveryDate WHERE RecoveryTypeKey = 5 AND CurrentFlag = 1 -- or whatever 

Le premier indexe les colonnes dans la clause WHERE. SQL Server serait capable de searchr les RecoveryDateTypeKey et CurrentFlag spécifiés. Étant donné que CaseId est INCLUDEd dans les noeuds feuille d'index, SQL Server n'aura pas à join la table pour l'get.

En utilisant la même requête, le deuxième index se comporterait différemment. Si vous êtes chanceux, SQL Server searchra tous les loggings où CurrentFlag est 1. Ensuite, il traverserait ces nœuds feuilles à la search de la key RecoveryTypeKey correspondante. D'autre part, s'il y a beaucoup d'loggings où le CurrentFlag est 1, SQL Server peut choisir de faire un scan d'index à la place.

Là encore, si vous souhaitez optimiser une requête comme celle-ci:

 SELECT CaseId, RecoveryTypeKey FROM Rec.RecoveryDate WHERE CurrentFlag = 1 

Le premier index serait inutile, car CurrentFlag est la deuxième colonne de l'index. SQL Server ne serait pas capable de le searchr pour CurrentFlag = 1, donc il ferait probablement un balayage d'index.

ils sont différents. L'index 1 indexe deux colonnes, l'index 2 indexe une colonne mais inclut deux colonnes dans les nœuds.

La différence signifie essentiellement que si vous effectuez une search en utilisant les deux colonnes, l'index 1 pourrait être beaucoup plus rapide que l'index 2.
L'index 2 serait meilleur qu'un index normal d'une colonne, parce que si vous avez besoin de l'autre colonne dans votre résultat, l'index 2 a déjà la valeur ainsi aucune search avec la table réelle ne serait nécessaire.

Les index stockent les mêmes informations (1 ligne par ligne dans la table de récupération avec les colonnes caseid, RecoveryDateTypeKey, CurrentFlag), mais sont organisés dans un ordre différent et peuvent donc être utilisés pour différentes requêtes.

Le premier index peut gérer où des clauses telles que

 WHERE RecoveryDateTypeKey = @p1 --Prefix matching! 

et

 WHERE RecoveryDateTypeKey = @p1 AND CurrentFlag = @p2 

Le deuxième index ne gère que

 WHERE CurrentFlag = @p2 

Si CurrentFlag est une colonne à faible cardinalité, comme un bit, ou un caractère char (1) (O / N), je reorderais des index filtrés.

 CREATE INDEX IX_REC_Yes_fltr on Recovery (RecoveryDateTypeKey) WHERE (CurrentFlag = 'Y') INCLUDE (CaseId) --Assumes that CurrentFlag = 'Y' is the most used value --Maybe even a second one. CREATE INDEX IX_REC_No_fltr on Recovery (RecoveryDateTypeKey) WHERE (CurrentFlag = 'N') INCLUDE (CaseId) --Maybe handle CurrentFlag = 'N' as well. 

Chaque index filtré n'inclut que les valeurs qui répondent aux critères, donc combinés, ils ont la même taille que l'index non filtré.