April 24, 2013 at 2:39 am
Hi all experts,
Is there any practical advantage of using Heap as a storage instead of clustered index for table.
April 24, 2013 at 3:10 am
Not many. It may give slightly faster inserts but there's enough downsides that it;s not usually a good idea. Unless you've tested and know that a heap is faster for the operations you're doing on a table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2013 at 8:23 am
Bulk insert into a staging table and then building the clustered and other indexes afterwards will generally be faster.
This is also useful when you then want to switch the data into an existing partitioned table.
April 25, 2013 at 2:00 pm
I've seen clustered tables with a key that was so poorly implemented, I wish the developer had just left it as a heap. A heap will perform better and require less maintenance (less fragmentation) than a table with a non-sequential, changing, or extremely wide clustered key. That means don't cluster on something like a GUID, zip code, or customer phone number, because SQL Server will expend a lot of I/O splitting pages and organizing rows.
However, a table with a well chosen clustered key is generally best. Most large transactional tables have the equivalent of a transaction_date, insert_date, sequence_number, or some sequentially incrementing column with static values that would make a good candidate for a clustered key.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply