31

I am currently doing some data imports into a legacy system and discovered that this system does not use a single clustered index. A quick Google search introduced me to the concept of HEAP tables and now I am curious in what usage scenarios a HEAP table should be preferred over a clustered table?

As far as I understood a HEAP table would only be useful for audit tables and/or where inserts happen far more often than selects. It would save disk space and disk I/O since there is no clustered index to maintain and the additional fragmentation wouldn’t be a problem because of the very rare reads.

2

3 Answers 3

22

The only valid uses are for

  • staging tables used in import/export/ETL processes.
  • ad-hoc, temporary and short term backup of tables using SELECT * INTO..

Staging tables are typically quite flat and truncated before/after use.

Note that a clustered index is typically few small compared to the data size: the data is the lowest level of the index structure.

Heap tables also have problems. At least these:

Also see

0
9

Major Considerations

I see one important advantage for heaps and one for clustered tables, plus a third consideration which can go either way.

  • A heap saves you a layer of indirection. Indexes contain row IDs, pointing directly (well, not really, but as directly as possible) to a disk location. Thus, an index seek against a heap should cost roughly half a non-clustered index seek against a clustered table.

  • A clustered index is sorted, per se, thanks to an (almost) free index. Because the clustering index is reflected in the physical order of the data, it takes up relatively little space on top of the actual data itself, which of course you have to store anyway. Because it's physically ordered, a range scan against this index can seek to the start point and then zip along to the end point very efficiently.

  • Indices on heaps reference RIDs, which are 64 bits. As mentioned, the non-clustered indices on a clustered table reference the clustering key, which can be smaller (a 32-bit INT), the same (a 64-bit BIGINT), or larger (a 48-bit DATETIME2() plus a 32-bit INT, or a 128-bit GUID). Obviously a wider reference makes for larger and more expensive indices.

Space Requirements

With these two tables:

CREATE TABLE TmpClustered
(
ID1 INT NOT NULL,
ID2 INT NOT NULL
)
ALTER TABLE TmpClustered ADD CONSTRAINT PK_Tmp1 PRIMARY KEY CLUSTERED (ID1)
CREATE UNIQUE INDEX UQ_Tmp1 ON TmpClustered (ID2)

CREATE TABLE TmpNonClustered
(
ID1 INT NOT NULL,
ID2 INT NOT NULL
)
ALTER TABLE TmpNonClustered ADD CONSTRAINT PK_Tmp2 PRIMARY KEY NONCLUSTERED (ID1)
CREATE UNIQUE INDEX UQ_Tmp2 ON TmpNonClustered (ID2)

...each populated with 8.7 M records, the space required was 150 MB for data for both; 120 MB for the clustered table's indices, 310 MB for the non-clustered table's indices. This reflects that the clustered index is narrower than a RID, and that the clustering index is mostly a "freebie." Without the unique indices on ID2, the index space required drops to 155 MB for the non-clustered table (half, as you'd expect) but just 150 KB for the clustered PK - close to nothing.

So a non-clustered index of a 32-bit field in a clustered table with a 32-bit index (total 64 bits, nominally) took 120 MB, while an index of a 32-bit field in a heap with a 64-bit RID (total 96 bits, nominally) took 155 MB, a little less than the 50% increase one would naively expect going from 64-bit to 96-bit keys, but of course there's overhead which reduces the effective difference in size.

Populating the two tables and creating their indices took the same amount of time for each table. Running simple tests involving scans or seeks, I found no material performance differences between the tables, which matches the Microsoft white paper which gbn helpfully linked. Said paper does show a significant difference for highly concurrent access; I'm not sure why that happens, hopefully someone with more experience than I with high-volume OLTP systems can tell us.

Adding ~40 bytes of random variable-length data did not appreciably change this equivalence. Replacing the INTs with wide UUIDs did not either (each table was slowed to about the same extent). Your mileage may vary, but in most cases whether an index is available is more important than what kind.

Bits and Pieces

Doing a range scan against a non-clustered index - either because the table is a heap or the index is not the clustered index - involves scanning the index and then doing a lookup against the table for each hit. This can be very expensive, so it's sometimes cheaper to just scan the table. You can work around this with a covering index, however. This applies whether you've clustered your table or not.

As @gbn pointed out, there's no simple way to compact a heap. However, if your table gradually increases over time - a very common case - there will be little waste as space freed by deletions will be filled by new data.

Several of the heap vs clustered table discussions I've seen make a curious strawman argument that a heap without indexes is inferior to a clustered table in that it always requires a table scan. This is certainly true, but the more meaningful comparison is "large well-indexed clustered table" vs "large well-indexed heap." If your table is very small or you're always going to be doing table scans, then it just doesn't matter much if you cluster it or not.

Because each index in a clustered table references the clustering index, they are in effect all covering indices. A query which references an indexed column and the clustering column(s) can do an index scan without any table lookups. This generally isn't valuable if your clustering index is a synthetic key, but if it's a business key which you'd need to retrieve anyway, it's a nice feature.

TL;DR

I'm a data warehousing guy, not an OLTP expert. For fact tables I almost always use a clustering index on the field which is mostly likely to need range scans, typically a date field. For dimension tables I cluster on the PK so it's presorted for merge joins against fact tables.

There are several reasons to use clustering indices, but if none of those reasons apply then the overhead may not be worthwhile. I suspect there's a lot of "we've always done it this way" and "it's just best practice" behind people using clustered indexes universally. Try both with your data and your load and see what works best.

5

I think saying "The only valid use is for staging tables used in import/export/ETL processes" is a little restrictive to say the least. You have to take a given system's expected use case and then choose based on the merits of heaps or index organised tables (I know, an Oracle term but it describes it nicely).

Our warehouse loads ~1.5 billion rows a day and has to support highly concurrent writes and processing as well as reads. The relational store supports an OLAP database and thus the reads tend to be primarily table scans. The reports and downstream feeds that are generated are also generally not selective enough such that any index would be useful. The system supports a sliding window of data and thus once a table is loaded we rarely write to it again and given the rather poor implementation of table partitioning requiring Sch-M locks for partition splits, switches and merges versus Sch-S locks for reads etc, the system had to make use of many tables, though we do have some partitioned tables too. The use of many tables facilitates ease of segmentation of data and cleanup cycles whilst also reducing contention.

As such, the added overhead of an index organised table (clustered table) on some arbitrary column(s) versus being able to bcp into a heap, process the OLAP partitions, perform some table scan queries and then 3 days later drop it means it is just not worth it. Note that in our case the data comes back from a large grid cluster so there is no ordering to the data either, so inserting into a table with a clustered index could introduce other issues such as "hot spots" and page splits and the like.

Also, I think the argument about pages being scattered is a little disingenuous. Clustered indexes can also have their pages scattered throughout the file. It's just that after re-indexing (assuming more than 1000 pages) this may be better than a heap but then you also had to re-index too.

It is also possible to save space using sparse columns and compression if that is a concern. It is true that in some cases selects on a table with a clustered index can be faster but you have to weigh that up with the resources required to load it and maintain it.

[Edit] I should probably make clear that only our non-partitioned fact tables are heaps. Partitioned tables and dimension tables all have clustered indexes to support efficient lookups etc. [Edit2] Corrected 2.5 billion to 1.5 billion. Tut, those two numbers being next to each other. What happens when typing responses on a phone I guess...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.