If I set compression (either page or row) on the clustered index of a table, is that the same as setting compression on the table?
SQL Server provides options for doing both, which suggests that they are different, but I was under the impression that a clustered index and a table were essentially the same thing, and my mental model of how clustered indexes work tells me that compressing the clustered index must also compress the table.
If I set compression (either page or row) on the clustered index of a table, is that the same as setting compression on the table?
Yes.
From MSDN:
Use the REBUILD WITH syntax to rebuild an entire table including all the partitions in a partitioned table. If the table has a clustered index, the REBUILD option rebuilds the clustered index.
SQL Server provides options for doing both, which suggests that they are different
The reason why both syntaxes exist is because a table doesn't necessarily have a clustered index. In other words, ALTER INDEX ALL ON ... REBUILD does not affect a heap (though it will touch all nonclustereds), so the other route serves that purpose.
Also, while the documentation doesn't explicitly say so, the ALTER TABLE ... REBUILD syntax does not enable/disable compression of all nonclustered indexes on the table. It only affects the heap or the clustered index.
Finally, if you're testing this through SSMS, be aware that you may get errors when disabling compression -- script things out so you can see what's really going on.
The clustered index is the table. So, setting it on either is the same. You can validate this by comparing both cases here:
SELECT OBJECT_NAME(p.object_id),*
FROM sys.partitions AS p
INNER Join sys.indexes AS i
ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE p.data_compression > 0;
SQL Server also provides several different ways to make a column unique:
CREATE TABLE dbo.foo1(bar INT UNIQUE);
...or...
CREATE TABLE dbo.foo2(bar INT, CONSTRAINT x2 UNIQUE(bar));
...or...
CREATE TABLE dbo.foo3(bar INT);
ALTER TABLE dbo.foo3 ADD CONSTRAINT x3 UNIQUE(bar);
...or...
CREATE TABLE dbo.foo4(bar INT);
CREATE UNIQUE INDEX x4 ON dbo.foo4(bar);
These will all end up with the same underlying implementation (with different names).
Just because there are different ways to get to work doesn't mean you don't still end up at the office. :-)
