CODEWITHSUNDEEP
sqlsql-serverindexing
Dale Fraser
Dale Fraser

Reputation: 4758

Should I define indexes for both directions

Using SQL Server 2012. Let's say I have a link table linking item & size

The table might look like

itemSize:

itemSizeId (int)
itemId (int)
sizeId (int)

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?

Upvotes: 1

Views: 87

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

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 order by.

Upvotes: 3

Related Questions