2 edited body edited Dec 7 '12 at 17:12 ypercubeᵀᴹ 83.2k1111 gold badges142142 silver badges236236 bronze badges Question2: Considering `Id` is the PK, does it make any sense to have it as the third column in `Index3`, before `QuxId`? This is your index, now: ``````Index3 (FooId, BarId, Id, QuxId) `````` Because `Id` is unique (as it is the primary key), there can be no two rows with same `Id` and different `QuxId`. Therefore, your index is equivalent to this one (which will use slightly less space): ``````Index3b (FooId, BarId, Id) INCLUDES (QuxId) `````` You can even remove the `Id` column and have the: ``````Index3bIndex3c (FooId, BarId) INCLUDES (QuxId) `````` An query like the following can use equally well Index 3, 3b or 3c: ``````SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 ; `````` If however you have a query with a range scan or a sorting on `QuxID`: ``````SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 AND QuxID BETWEEN 123 AND 314 ; SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 ORDER BY QuxID ; `````` the above (3, 3b or 3c) indexes can be used but not as efficiently as this one that has the `QuxID` values in the needed order: ``````Index3d (FooId, BarId, QuxId) `````` In short, the indexes `(FooId, BarId, Id, QuxId)` and `(FooId, BarId, QuxId)` can be used for many queries equally well but they are not exactly equivalent and you may have queries that will use more efficiently the one but not the other. Question2: Considering `Id` is the PK, does it make any sense to have it as the third column in `Index3`, before `QuxId`? This is your index, now: ``````Index3 (FooId, BarId, Id, QuxId) `````` Because `Id` is unique (as it is the primary key), there can be no two rows with same `Id` and different `QuxId`. Therefore, your index is equivalent to this one (which will use slightly less space): ``````Index3b (FooId, BarId, Id) INCLUDES (QuxId) `````` You can even remove the `Id` column and have the: ``````Index3b (FooId, BarId) INCLUDES (QuxId) `````` An query like the following can use equally well Index 3, 3b or 3c: ``````SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 ; `````` If however you have a query with a range scan or a sorting on `QuxID`: ``````SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 AND QuxID BETWEEN 123 AND 314 ; SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 ORDER BY QuxID ; `````` the above (3, 3b or 3c) indexes can be used but not as efficiently as this one that has the `QuxID` values in the needed order: ``````Index3d (FooId, BarId, QuxId) `````` In short, the indexes `(FooId, BarId, Id, QuxId)` and `(FooId, BarId, QuxId)` can be used for many queries equally well but they are not exactly equivalent and you may have queries that will use more efficiently the one but not the other. Question2: Considering `Id` is the PK, does it make any sense to have it as the third column in `Index3`, before `QuxId`? This is your index, now: ``````Index3 (FooId, BarId, Id, QuxId) `````` Because `Id` is unique (as it is the primary key), there can be no two rows with same `Id` and different `QuxId`. Therefore, your index is equivalent to this one (which will use slightly less space): ``````Index3b (FooId, BarId, Id) INCLUDES (QuxId) `````` You can even remove the `Id` column and have the: ``````Index3c (FooId, BarId) INCLUDES (QuxId) `````` An query like the following can use equally well Index 3, 3b or 3c: ``````SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 ; `````` If however you have a query with a range scan or a sorting on `QuxID`: ``````SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 AND QuxID BETWEEN 123 AND 314 ; SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 ORDER BY QuxID ; `````` the above (3, 3b or 3c) indexes can be used but not as efficiently as this one that has the `QuxID` values in the needed order: ``````Index3d (FooId, BarId, QuxId) `````` In short, the indexes `(FooId, BarId, Id, QuxId)` and `(FooId, BarId, QuxId)` can be used for many queries equally well but they are not exactly equivalent and you may have queries that will use more efficiently the one but not the other. 1 answered Dec 7 '12 at 15:57 ypercubeᵀᴹ 83.2k1111 gold badges142142 silver badges236236 bronze badges Question2: Considering `Id` is the PK, does it make any sense to have it as the third column in `Index3`, before `QuxId`? This is your index, now: ``````Index3 (FooId, BarId, Id, QuxId) `````` Because `Id` is unique (as it is the primary key), there can be no two rows with same `Id` and different `QuxId`. Therefore, your index is equivalent to this one (which will use slightly less space): ``````Index3b (FooId, BarId, Id) INCLUDES (QuxId) `````` You can even remove the `Id` column and have the: ``````Index3b (FooId, BarId) INCLUDES (QuxId) `````` An query like the following can use equally well Index 3, 3b or 3c: ``````SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 ; `````` If however you have a query with a range scan or a sorting on `QuxID`: ``````SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 AND QuxID BETWEEN 123 AND 314 ; SELECT QuxId FROM table WHERE FooId = 5 AND BarId = 7 ORDER BY QuxID ; `````` the above (3, 3b or 3c) indexes can be used but not as efficiently as this one that has the `QuxID` values in the needed order: ``````Index3d (FooId, BarId, QuxId) `````` In short, the indexes `(FooId, BarId, Id, QuxId)` and `(FooId, BarId, QuxId)` can be used for many queries equally well but they are not exactly equivalent and you may have queries that will use more efficiently the one but not the other.