Table compression in SQL Server

If you are using SQL Server 2008 or SQL Server 2008R2, or 2012 you can use table compression in addition to the indexes to get more efficient storage.

Basically table compression has two levels:

1. Row :row-level compression, sql server applies compact storage format for each row in table
2. Page: page-level compression, sql server apply compression algorithms to get more efficient storage on page level.

here is syntax to apply table compression while creating table:

Create table [tablename] (column_name column_type) with (DATA_COMPRESSION = ROW).

To measure how efficient is row or page compression is, you can use sp_estimate_data_compression_savings stored procedure.

The stored procedure accepts table_name, index_id, partition_type and type of compression you would like to apply and return how much data space would be allocated with given compression.

Usually with page compression you can get more efficient storage rather than row based compression as SQL server use compression algorithm for storage.

Advertisements
About

hi there, I am software engineer, working in e-commerce company and passionate about all thing digital. On this blog, I share my experiments with different technology.

Tagged with:
Posted in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

What people say about post?
seo hizmeti on Awesome wordpress ecommerce…

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 38 other followers

%d bloggers like this: