CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008indexing
Praneeth
Praneeth

Reputation: 2547

Does SQL Server creates Non clustered index on all columns of a table by default

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

Upvotes: 2

Views: 1154

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 10

Related Questions