SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


Table Data Compression Tool and Scripts in SQL Server 2014

Data compression in SQL Server can be managed using Data Compression tool or by running ALTER TABLE SQL scripts in table partition base for row or page based compression types. This SQL tutorial will be showing SQL Server Management Studio tool, "Data Compression Wizard" and sharing the SQL scripts created by this SQL Server tool that can be executed for other database tables with different data compression options.

Data Compression using SQL Server Management Studio

Right click on table definition. From context menu, select Storage then choose Manage Compression...

manage compression for specific table in SQL Server 2014

Data Compression Wizard will start.
Data Compression Wizard is a free tool shipped with SQL Server 2014 in SQL Server Management Studio that helps the database administrators to estimate space gains from different types of compression options including row based or page based compression. After a satisfactory compression gain on table data or on table partition, database administrator can use this SQL Server tool to compress using specific compression option.

SQL Server 2014 Data Compression Wizard

Press on Next button to continue to following step.

Select Compression Type

select compression type for table partition in SQL Server 2014

Estimated compressed table data size or table partition size is displayed in the below list according to the selected compression type. The requested compressed space and current space figures gives an idea to the SQL Server 2014 database administrator to choose the related compression type and continue to complete the data compression or to choose an other compression type.

Without choosing any compression type, SQL Server Data Compression Wizard will only rebuild all table partitions without any data compression option.

SQL table partition compressed space

The below screenshot is showing values from database tool where row compression is selected for the same SQL Server table Sales.SalesOrderDetail

row based table partition data compress

An other option for table partition compression is page compression in SQL Server 2014. Note that that for the SQL Server sample database table SalesOrderDetail page compression analysis offered a higher compression ratio.

page based table partition data compress selection

After database administrator decided to the compression type on the table partition base for the selected SQL Server table, he or she can directly execute compress command, or schedule it for later run, or even can take the SQL scripts required for selected compression configuration.

Following screen titled "Select an Output Option" enables SQL Server admins to create a SQL script to compress, decompress or change the compression state of a database table. Besides, the database programmer or administrator can run the SQL script immediately or schedule a job for the execution of the script.

SQL Server database table compression script output options

If we continue with Next button within the SQL Server tool, the summary screen will be displayed. Nothin fancy in this screen actually for a SQL developer or administrator.

SQL Server 2014 Data Compression Wizard Summary

When the Finish button is pressed, the action that is selected is taken with the configuration settings defined in the previously mentioned Output Option screen.

Data Compression Progress screen will report the status of the compress task and also provides a means of stopping the started process.

SQL data compression progress screen


Data Compression Scripts in SQL Server 2014

If the database administrator or SQL developer chooses to script data compression command selected for the table partitions, SQL scripts similar to ones seen below will be created by the SQL Server tool.

database table compression scripts in SQL Server

Alter Table Sales.SalesOrderDetail Rebuild Partition = ALL
  With (Data_Compression = NONE)

Alter Table Sales.SalesOrderDetail Rebuild Partition = ALL
  With (Data_Compression = ROW)

Alter Table Sales.SalesOrderDetail Rebuild Partition = ALL
  With (Data_Compression = PAGE)
Code


SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.