Showing posts with label heap. Show all posts
Showing posts with label heap. Show all posts

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.