Tag: Data Compression

Clustered column store indexes (CCI) in SQL Server vNext and Azure SQL Database support LOB types like NVARCHAR(MAX), which allows you to store string with any size, including JSON documents with any size. With CCI you can get 3x compression and query speedup compared to regular tables without any application or query rewrites. In this… Read more

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

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

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

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

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

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

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

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

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