DbSchema | SQL Server - How to Implement Data Compression?

SQL Server: How to Implement Data Compression in sqlcmd and DbSchema

Introduction
Prerequisites
Understanding Data Compression
Types of Data Compression
Advantages and Limitations of Data Compression
Differences between Row and Page Compression
Implementing Compression in sqlcmd and DbSchema
Conclusion
References

Introduction

As databases grow, managing and storing large amounts of data efficiently becomes a challenge. This is where data compression comes into play. This article covers how to implement data compression in SQL Server using sqlcmd and DbSchema tools. It also discusses the types of data compression, their advantages and limitations, and how to navigate the restrictions and permissions required to implement them.

Prerequisites

To follow this guide, you should have:

Basic knowledge of SQL Server.
Installed SQL Server.
The sqlcmd utility and DbSchema installed on your machine.
A sample database to use for testing.

For installation and establishing connection you can read our article SQL Server-How to create a database?

Understanding Data Compression

Data compression is a feature in SQL Server that helps reduce the size of database files. It does this by reducing the amount of disk space required to store data, resulting in improved I/O performance.

Usage of Data Compression

Data compression is used when database size and disk I/O operations become a bottleneck for application performance. It reduces the amount of disk space required to store data, reduces memory usage for cached data, and minimizes I/O operations.

Permissions and Restrictions

For implementing data compression, you need to have the ALTER permission on the table or index. Also, data compression is not available in every edition of SQL Server. It is available only in the Enterprise edition of SQL Server 2008 and later.

Types of Data Compression

There are two types of data compression in SQL Server:

row compression
page compression.

Row Compression

Row compression changes the format of physical storage of data, reducing its space requirements. It stores fixed character strings using variable-length format and does not store null or zero values.

Page Compression

Page compression includes the features of row compression and also adds prefix compression and dictionary compression. It reduces the redundancy in data and can deliver a higher level of compression than row compression.

Advantages and Limitations of Data Compression

*Advantages*	*Limitations*
Reduces storage space	Requires CPU resources during data compression and decompression
Improves I/O performance	Available only in the Enterprise edition
Reduces memory usage	Might not be beneficial for all types of data

Differences Between Row and Page Compression

*Row Compression*	*Page Compression*
Stores fixed character strings using variable-length format	Includes row compression, prefix, and dictionary compression
Does not store null or zero values	Reduces data redundancy
Less storage reduction compared to page compression	Greater storage reduction but uses more CPU resources

Implementing Compression in sqlcmd and DbSchema

Here are the steps to implement data compression using sqlcmd and DbSchema:

sqlcmd

sqlcmd is a command-line utility that comes with SQL Server. You can use it to execute T-SQL commands and scripts directly from the command prompt or from a script file.

Here are detailed steps to implement data compression using sqlcmd:

Launch the Command Prompt: To open the command prompt, click on the Start button, type cmd in the search box, and press Enter.
Connect to your SQL Server instance with sqlcmd: Run the following command to connect to your SQL Server. Replace server_name, database_name, username, and password with your actual server name, database name, username, and password.
```
sqlcmd -S server_name -d database_name -U username -P password
```
You should now be connected to your SQL Server instance.
Implement Row Compression: To apply row compression on a table, use the following command. Replace table_name with the name of your table.
```
ALTER TABLE table_name REBUILD WITH (DATA_COMPRESSION = ROW);
```
This command changes the table’s storage format to variable length, reducing storage space.
Implement Page Compression: To apply page compression, use the following command:
```
ALTER TABLE table_name REBUILD WITH (DATA_COMPRESSION = PAGE);
```
Page compression includes the benefits of row compression and also minimizes data redundancy.
Exit sqlcmd: To exit sqlcmd, simply type exit and press Enter.
```
exit
```

Results from Query:

Consider we have a table named Orders in our database.

First, let’s see the data space used by the Orders table before compression.

Let’s say the result is:

*name*	*rows*	*reserved*	*data*	*index_size*	*unused*
Orders	10000	10000 KB	5000 KB	4500 KB	500 KB

This shows the Orders table has 10,000 rows and is taking up 10,000 KB of space, with 5,000 KB used for data.

Now, let’s implement row compression on the Orders table:

ALTER TABLE Orders REBUILD WITH (DATA_COMPRESSION = ROW);

After compression, we’ll check the space used again:

The result will be:

*name*	_rows_	_reserved_	*data*	*index_size*	*unused*
`Orders`	10000	7000 KB	3500 KB	3200 KB	300 KB

This shows that the size of the Orders table has been reduced to 7,000 KB after row compression.

Similarly, we can implement page compression and check the space used.

Please note, the amount of space saved depends on the type of data stored. Not all tables will see the same level of reduction.

DbSchema

DbSchema is a diagram-oriented database designer with integrated data and query tools. Here’s how to implement data compression using DbSchema:

Launch DbSchema: Find the DbSchema icon on your desktop or in your installed programs list and click it to open.
Connect to your SQL Server: Once DbSchema is open, you will need to connect to your SQL Server. Click on Connect and enter your server details (server name, database name, username, and password), then click Connect.
Select the table for compression: In the schema view, locate and select the table you want to compress. You can do this by expanding the list of tables on the left side and clicking on the table’s name.
Open the Table Editor: With the table selected, open the table editor by clicking on the Edit button.
Select Compression Option: Go to the Options tab. In the Compression section, select either Row or Page based on your needs.
Apply Changes: Click the Apply button to implement the changes. A dialog box may appear asking for confirmation. Click Yes to proceed.
Check the Compression Status: You can verify if the compression is applied successfully by running a SQL script or by using the DbSchema’s inbuilt Table Data viewer to examine the table’s storage statistics.

These are the steps you can follow to implement data compression in SQL Server using sqlcmd and DbSchema. Remember, always backup your database before applying compression to avoid any unwanted data loss.

Visually Manage SQL Server using DbSchema

DbSchema is a SQL Server client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

Conclusion

Data compression in SQL Server can significantly reduce storage requirements and improve overall database performance. However, it’s important to consider the trade-offs such as CPU overhead and the types of data before implementing it. This guide provided a comprehensive overview of data compression and demonstrated how to implement it using sqlcmd and DbSchema.