DbSchema Database Designer

DbSchema | SQL Server - How to Implement Data Compression?

Publish on DbSchema Blog >>>

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

SQL Server alt >

Table of Contents


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.


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:

  1. row compression
  2. 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 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:

  1. Launch the Command Prompt: To open the command prompt, click on the Start button, type cmd in the search box, and press Enter.

  2. 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.

  3. Implement Row Compression: To apply row compression on a table, use the following command. Replace table_name with the name of your table.


    This command changes the table’s storage format to variable length, reducing storage space.

  4. Implement Page Compression: To apply page compression, use the following command:


    Page compression includes the benefits of row compression and also minimizes data redundancy.

  5. Exit sqlcmd: To exit sqlcmd, simply type exit and press Enter.


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:


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 is a diagram-oriented database designer with integrated data and query tools. Here’s how to implement data compression using DbSchema:

  1. Launch DbSchema: Find the DbSchema icon on your desktop or in your installed programs list and click it to open.

  2. 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.

  3. 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.

  4. Open the Table Editor: With the table selected, open the table editor by clicking on the Edit button.

  5. Select Compression Option: Go to the Options tab. In the Compression section, select either Row or Page based on your needs.

  6. Apply Changes: Click the Apply button to implement the changes. A dialog box may appear asking for confirmation. Click Yes to proceed.

  7. 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.


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.


Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.