SQL Server Index Tuning – Does It Make Sense to Create Index with All Columns Included?

index-tuningnonclustered-indexsql server

We have a table with columns 

 ID
 OtherTableId
 DataA
 DataB
 DataC
 DataD

Does it make sense to create index on OtherTableID INCLUDE (ID, DataA, DataB, DataC, DataD) when we need all columns, or is it enough to create index just on (OtherTableID)?

I know that index with include will effectively duplicate table size, but will it give performance improvement?

Query is:

select * from Table Where OtherTableID = ?

Best Answer

If

  • your query uses all those fields,
  • the sort order needs to be different than that of the clustered index, AND
  • you can't change the clustered index,

an additional (covering) index with a different column order may work for you. As with anything index-related, more (and wider) indexes means slower write/update performance. This is particularly true if you're effectively doubling the size of the table.

If you just index OtherTableId without including the other columns, you'll get a smaller index but your query may end up either just using the clustered index with expensive Scan or Sort operators, or using Key Lookups from the non-clustered index, which may be great if you're querying just a few rows at a time but disastrous if you're scanning the entire table.

This is general advice. For specifics, edit your question to include query, table definitions and a query plan.

Related Question