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...
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.
Press on Next button to continue to following step.
Select Compression Type
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.
The below screenshot is showing values from database tool where row compression is selected for the same SQL Server table Sales.SalesOrderDetail
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.
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.
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.
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.
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.
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)