Friday, March 9, 2012

non clustered index on heap

What would be the implications in terms performance and i/o operations, if a non clustered index created on heap ie. without a clustered index on table.It depends. There is no straight-forward answer. It depends on the schema, row size etc. Generally speaking, it is recommended to have a clustered index on every table especially so for large tables. There are however cases where you can get the best bulk insert performance by inserting into a heap vs clustered index. Also the more indexes you have on a table slower the performance. Search in MSDN for the whitepaper of bulk load that should give some ideas on one aspect of this problem.

No comments:

Post a Comment