Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks Jon, good clear explanation! And its good to hear that things work as I expected they should (which is definitely not always the case!) –  paulH Sep 13 '13 at 9:29
    
@paul: You're welcome. –  Jon Seigel Sep 13 '13 at 12:53

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. :-)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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