How to Enable Compression on an Existing Table in SQL Server (T-SQL)

 Tags: SQL Server, T-SQL, compression, database optimization

 

Compression is a powerful feature in SQL Server that can help reduce the size of your database and improve query performance. By compressing your data, you can save space on your hard drive and reduce the amount of data that needs to be read from disk, which can lead to faster query times.

 

In this blog post, we'll walk through the steps to enable compression on an existing table in SQL Server using T-SQL.

 

Step 1: Determine the current compression setting

 

Before you enable compression on a table, it's a good idea to determine the current compression setting. You can do this by running the following query:

 

select distinct t.name AS CompressedTables
from sys.partitions p
inner join sys.tables t
on p.object_id = t.object_id
where p.data_compression > 0

 

This query will return information about the current compression setting for the specified table.

 

Step 2: Enable compression

 

Once you've determined the current compression setting, you can enable compression by running the following query:

 

ALTER TABLE YourTableName REBUILD WITH (DATA_COMPRESSION = PAGE);

 

This query will rebuild the table with the PAGE compression setting. You can also use other compression options, such as ROW or NONE, depending on your needs.

 

Step 3: Verify compression

 

select distinct t.name AS CompressedTables
from sys.partitions p
inner join sys.tables t
on p.object_id = t.object_id
where p.data_compression > 0

 

Step 4

Here you can check the current table size in the table

SELECT
 s.name + '.' + t.Name AS [Table Name],
 part.rows AS [Total Rows In Table - Modified],
 CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3))
 AS [Table's Total Space In GB],
 CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 AS NUMERIC(18, 3))
 AS [Table's Total Space In MB],
 t.schema_id as schemaid
FROM
 SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
 INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id
 INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id
                    AND idx.Index_id = part.Index_id
 INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id
 INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id
 INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id
WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255
--and t.schema_id ='8'
and t.Name ='YourTableName '
GROUP BY t.Name, s.name, part.rows, t.schema_id
ORDER BY [Table's Total Space In GB] DESC

 

Conclusion

Enabling compression on an existing table in SQL Server can help improve query performance and reduce the size of your database. By following these simple steps, you can easily enable compression on your tables using T-SQL. As always, make sure to test your changes in a non-production environment before making any changes to your production database.