CODEWITHSUNDEEP
sql-server
Thom Ash
Thom Ash

Reputation: 221

Why aren't system tables updated after compressing tables

SQL Server 2012

I wanted to compress tables and indexes. I did a search to find tables that weren't compressed and manually checked accuracy of script by looking at table properties/storage prior to compressing. I generated scripts for tables as follows:

ALTER TABLE [R_CompPen].[CP2507BodySystem] 
   REBUILD WITH (DATA_COMPRESSION=PAGE);

After the script ran I verified compression through SMS however, the script I ran to find the uncompressed tables and generate scripts still showed them as uncompressed.

So the question is why didn't the Alter Table script update system tables and if it actually is but showing indexes, how can the script be written to only show tables and conversely a separate script to only show indexes?

SELECT distinct 'ALTER TABLE [' 
   + sc.[name] + '].[' + st.[name] 
   + '] REBUILD WITH (DATA_COMPRESSION=PAGE);'
FROM sys.partitions SP
INNER JOIN sys.tables ST ON st.object_id = sp.object_id
INNER JOIN sys.Schemas SC on sc.schema_ID = st.schema_ID
WHERE sp.data_compression = 0

Upvotes: 0

Views: 164

Answers (1)

souplex
souplex

Reputation: 981

The 'DISTINCT' is the culprit here. Once you have multiple indexes, you also have multiple entries in sys.partitions. But the distinct hides the other entries.

Here I have a table called Album with 2 indexes, which I compressed using 

ALTER TABLE Album REBUILD WITH (DATA_COMPRESSION = PAGE);

After running this statement, the non clustered index remains uncompressed and keeps appearing in the list.

image of compression state

EDIT: Turns out that when you only want to know about table level compression, you simply filter for index_id 0 or 1. Higher numbers refer to non clustered indexes. Shameless copy from Barguast's solution on his own question: 

SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
    [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

Upvotes: 2

Related Questions