Built-in functions for compression/decompression in SQL Server 2016

SQL Server 2016 provides built in functions for compression and decompression: COMPRESS – compress data using GZip algorithm and returns binary data. DECOMPRESS – decompress binary data using GZip algorithm and returns binary data. You will need to cast binary data to text if you have originally compressed text. These functions use standard GZip algorithm so you… Read more

Data Compression and Fill Factor

Recently, I was asked this question “If I have a compressed index and I rebuild it with fill factor 70%, do I actually get any fill factor?  Or does compression take away the empty space?”. The answer to this question is ‘yes’. In fact all index options listed below available with data compression (both for… Read more

Changes to sp_estimate_data_compression_savings stored procedure in SQL2008R2

When you compress an object (index or table or partition), there are two components of space savings that you get. First component is fragmentaton (i.e. the original object might have been fragmented). The object gets degragmented as part of doing compression so you get some space savings. Second component is actual data compression savings. Many customers have asked the… Read more

A Unicode Compression example

Now that we have Unicode compression available in SQL Server 2008R2 as described in, let me take a simple example using AdventureWorksDW database to show you the additional compression that can be achieved on tables with one or more columns of type NCHAR or NVARCHAR. use [AdventureWorksDW2008] — the table FactResellerSales is a FACT table with… Read more

Unicode Compression in SQL Server 2008R2

In my previous blog, I had mentioned that unicode compression will be available as part of next SQL Server release named SQL 2008R2. You can down load the CTP2 version http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx  to play with it. This is a new exciting addition to the data compression offering.  Let me describe the problem and how it has been addressed in… Read more

Customer feedback on Data Compression

As I described in my previous blog, the data compression feature has been very successful. We do appreciate all the feedback that we have received as this provides us a way to know how customers have been using the feature and the challenges they are facing. Based on the feedback, we plan to improve our data compression… Read more

Update on data compression performance/space-savings and links to published white papers

It has been a while since I blogged about data compression so I thought it will be good to provide an update on data compression usage within SQL Server community. I am happy to say that the Data compression feature has been a tremendous success in SQL Server 2008 with customers.  Many customers have been able to reduce… Read more

DML operations on a HEAP and compression

In my previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/20/enabling-compression-on-a-heap.aspx, I described how to enable compression on a HEAP. You can enable either ROW or PAGE compression on the heap. Let me now describe the implications of DML operations on a heap. ROW compression: No issues. The updated/inserted data stays ROW compressed though out its life time. PAGE Compression: There… Read more

Enabling compression on a HEAP

I often get a question how to do enable compression on a table that is a heap (i.e. it does not have a clustered index). Clearly, one could create the clustered index with compression option set to PAGE and then drop the clustered index.  This is an expensive operation because ·         Creating a clustered index… Read more

Does data compression lead to more fragmentation?

Lately I have been asked how data compression impacts fragmentation (i.e. does it cause more or less fragmentation?).  I believe this question is best answered by looking at how does fragmentation occur in the first place and then analyze each of these cases for compressed and uncompressed data. Let us start off with create clustered index on a table… Read more