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 INT
s 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.