April 13, 2016 at 3:13 pm
In examining the indexing design of several databases at my organization, I am coming across more heaps (tables with no clustered or nonclustered indexes) than I would expect.
Unless you're looking at a config table that will never have more than a dozen or so rows, is there ANY scenario where a table with no indexes is a good idea?
April 13, 2016 at 6:43 pm
Yes. Staging tables... but that's about it. What I've personally encountered is 3rd party database developers that either don't have a clue or believe that the use of (especially) clustered indexes will somehow screw up the portability of their application from RDBMS to RDBMS. Of course, in the latter case, they've also made the mistake of believing in true portability to begin with. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2016 at 10:34 am
In some specific situations, heaps offer some performance benefits over clustered indexes. One example is avoiding page splits for tables with lots of inserts and no updates. Another is that a lookup in a heap can be (very slightly) faster then a lookup in a clustered index - but only when the heap has no or little fragmentation.
Most of the time the benefits of having a clustered index far outweigh these benefits, so my general rule to all DBAs and Developers is that they are not allowed to create a heap table unless then can explain exactly to me why it has to be a heap - and that explanation has to prove a full understanding of the issues with heaps
A database with a high number of heaps is with a 99.999% likelihood simply designed badly.
April 14, 2016 at 11:17 am
In many organizations, the physical model of the database is entirely determined by the default behaviour of whatever data modeling or ORM tool was used by the developer. It's not as if someone pondered the issue and then said "I think this table should be heap.".
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 14, 2016 at 4:11 pm
Hugo Kornelis (4/14/2016)
A database with a high number of heaps is with a 99.999% likelihood simply designed badly.
Amen.
_____________
Code for TallyGenerator
April 25, 2016 at 2:28 am
dan-572483 (4/13/2016)
I am coming across more heaps (tables with no clustered or nonclustered indexes) than I would expect.
Just for clarity, a heap is a table that is not clustered.
So a table that has no clustered index but 20 nonclustered indexes is still a heap i.e. independant of the number of nonclustered indexes.
If you are only inserting into a table and never ever do any queries, then a heap without any nonclustered indexes is ideal.
If you a table is predicted to be highly queried, and choose to not cluster the table, you need a very very very good reason as to why you are choosing to leave the highly queried table as a heap. Preferably with tests and results.
April 25, 2016 at 3:00 am
Staging tables can be heap apart from tables storing configuration data which is very less in size.
April 25, 2016 at 5:13 pm
Bhushan Kulkarni (4/25/2016)
Staging tables can be heap apart from tables storing configuration data which is very less in size.
I once saw a configuration data table which was occupying > 6GB space on disk.
That was quite strange, because there were a couple of dozens of records in that table.
Then I noticed that the table was a HEAP, so in order to reorganise its pages I need to add a clustered index.
As soon as I did that the table size dropped to ~30kB, and any further action became unnecessary.
Tables should be allowed to stay HEAPs only if they are dropped and recreated on a regular basis.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply