Heap V.S. Indexed table (Insert performance)

[steppinthrax]

I got into a little argument with some office buddies over Unindexed tables v.s. indexed. I understand when you add a index to a table you increase select performance because you don't have to do a full table scan. However you take a performance hit with Inserts. Because SQL has to search the structure, looking for a space to insert it.

In a heap, since there is no structure inserts should be the fastest, however selects should take a performance hit. But Microsoft says otherwise!!!!!!!!!!

http://support.microsoft.com/kb/297861

This article indicates heaps are slower with inserts?????

Someone explain

 

[ForumMaster]

generally speaking, the more indexes you have. the slower inserts will be but the faster selects will be. and yes, without indexes, inserts are faster.

that's why often, oracle and i'll imagine MS will suggest to build the indexes after bulk loads.

that article you linked to was talking about clustered indexes which is something else.
in oracle, when you want inserts to really be the fastest, you can give it the hint:
/* append parallel */ and then it appends it at the end. but that can increase the size of the table. if it's partitioned, then no biggie...

 

[degibson]

Clustered indexes are slower because they require order in the data itself, hence making inserts rather expensive. However, they are more efficient for bulk-loaded data.

Unclustered indexes do not require ordered data -- instead, the index itself is kept in an ordered representation with pointers into the data table.