Using SQL Server 2012. Let's say I have a link table linking item & size
The table might look like
If I define a composite index on
itemId & sizeId so that I can quick look for an item by size should I also define an index for
sizeId & itemId or is the alternate direction covered by the first index?
You should define an index based on the queries that you want to use on it.
If you want to do queries that have:
where itemId = @itemId
and where size = @size
(and no other clauses on these two fields), then you should have two different indexes.
This is also true if you want SQL Server to use the index for other purposes, such as for an