SQL Server need to run compress commands for both Table & Clustered Index?

Question

Hello I have a particular Table that I want to compress and also any existing Indexes as well.

If I run the following to compress this particular Table first:

USE [DB_Name]
GO
ALTER TABLE [dbo].[Table_1] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE
)
go

Which has a Clustered Index within...do I also need to run the following...

USE [DB_Name]
GO
ALTER INDEX [ClusteredIndex_1] ON [dbo].[DB_Name] REBUILD PARTITION = ALL 
 WITH 
(DATA_COMPRESSION = PAGE
)
GO

...or above Clustered Index command can be skipped since I already ran Table level compression ?

Thanks in advance.

Answer 1

Hi @techresearch7777777 ,

>or above Clustered Index command can be skipped since I already ran Table level compression

Yes. When you run the table compression, the clustered index is also compressed. But the compression setting of a table is not automatically applied to its non-clustered indexes.

And there's no inheritance for compression settings: "When you compress tables and indexes, it’s a one-time action. You’re only taking care of what exists today. If someone (or even you) turns around and creates an index on that same table tomorrow, it won’t be compressed by default. Whoever creates the index has to make sure that it’s compressed. We can use the second query to compress the index.

Please refer to similar thread Does adding page compression to a table also compress existing indexes and this blog.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 2

Sorry mistyped the Clustered Index statement to refer to the Table name not DB name as follows:

 USE [DB_Name]
 GO
 ALTER INDEX [ClusteredIndex_1] ON [dbo].[Table_1] REBUILD PARTITION = ALL 
  WITH 
 (DATA_COMPRESSION = PAGE
 )
 GO

Answer 3

The clustered index is the table, so that is the same thing. But you need to compress non-clustered index separately if you want them to be compressed to.

You can use this command to inspect your compression status

SELECT * FROM sys.partitions WHERE object_id = object_id('YourTable)

Answer 4

Adding to Erlands answer I would use the first command you have mentioned to page compress table that has no clustered index basically a heap table. Peopl can argue to create clustered index on it and compress like using the 2nd query but sometime we just need plain table nothing else.

Answer 5

Interesting thanks bunch everyone for all the quick great answers.