2

Will sql server create any default non-clustered index? Should we really put all FK as non-clustered index? What is the trade-off here

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Praneeth
  • 2,527
  • 5
  • 30
  • 47

1 Answers1

9

No, SQL Server does not automatically create non-clustered indexes.
A clustered index is created automatically based on the primary key, unless your CREATE TABLE statement says otherwise.

Yes, I would recommend indexing foreign key columns because these are the most likely to be JOIN'd/searched against using IN, EXISTS, etc. However, be aware that an index on a low cardinality set of values (gender for example) will be relatively useless because there's not enough difference in the values.

The trade-off with all indexes is that they can speed up data retrieval, but slow down data insertion/updating/deletion. There's also maintenance that needs to be performed because they can get fragmented (like hard drives) but also might not get used over time. Indexes also take up drive space.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • thanks...What happens if there is non-clustered key and no non-clustered key on FK that i am using it to join with the child table. What scan does it use? Full, Index seek and Index scan? – Praneeth Oct 15 '11 at 22:37
  • Depends on the query, the index, the table statistics & the optimizer -- it means looking at the explain plan. – OMG Ponies Oct 15 '11 at 22:39