re: Types of data compression in SQL Server 2008

MBParker — Thu, 03 Jan 2008 11:23:03 GMT

Thanks for the great tech specs, as record format. It would be even better if these essential specs were in a wiki.

Question: In any version of SQL (and especially SQL Server 2005 & 2008),

WHAT COMPRESSION is there FOR A COLUMN/FIELD OF REPEATING VALUES

(contiguous series of rows with the same value for that that field/column)

--MOST ESPECIALLY FOR SHORT FEW-BYTE VALUES as INT?

Say in an Order-Item table: an order is generally entered all once (or about the same time), each Order-Item tends to reference the same Order ID as the prior Order-Item (very frequently)).

As a simple do-it-yourself version of this, you could have your row-insert procedure (plus update & delete procedures) put null for a value of a field/column (expected to have a value) if that field's value desired value happens to be same value as the prior row's value for that field/column -- storing the same data with just 1 bit (for the null option, which is allocated anyway in all cases in SQL Server 2008 server). I call this DITTO Compression 20080103075141, because it's exactly what the ditto mark does, used here in the cell of a table. Then have a view of the data which actually restores the desired value (when the view sees null, it uses the prior non-null value). Then even index this computed column (and perhaps persist it) to get the same performance as if you actually had this underlying data, but without having to permanently waste the space. How well would that work?

However, something as basic & simple as this ("ditto compression") I would expect would already be built-in to SQL. Indeed I seem to recall reading years ago it was, perhaps in Oracle. But I can't find docs of this, for SQL Server nor SQL in general. Is such basic "ditto compression" built-in? If not, why not?

-- http://www.infoq.com/news/2007/11/SQL-Server-Compression talks of "page level" "prefix" compression (which is quite clever), but that's not until the latest 2008, and it seems like that might only work well on strings, and still require (for a 100% repeated value) a minimum of 3 bytes or so, instead of a bit.

Thanks! Mike Parker

Row compression – internal structure

Danny's SQL Server and Internals Viewer Blog — Sun, 24 Feb 2008 23:02:54 GMT

The CTP6 release of SQL Server 2008 includes row and page compression. It’s a feature that will only

re: Types of data compression in SQL Server 2008

soundararajan.d — Mon, 04 Aug 2008 12:57:04 GMT

Your article is impressive. Thanks for all these internals.

One Question : Will this work for BLOB data as well, since you mentioned compression happens at binary level.

re: Types of data compression in SQL Server 2008

Sunil Agarwal — Mon, 04 Aug 2008 21:20:44 GMT

only in-row data gets compressed. if BLOB is stored off row, a common case, it won't be compressed. you have two options for off row

(1) use file stream

(2) use RBS (remote blob storage)

thanks for your interest and comments

Improving SharePoint with SQL Server 2008

SharePoint Hosting and Development — Tue, 19 Aug 2008 22:00:53 GMT

There is a vast array of new features in SQL Server 2008. While I would love to explore all of them,