Guids and compression

In a previous post I compared how GUIDS can have an adverse effect on fragmentation. One of the new SQL Server 2008 features available in the Enterprise and Developer editions is data compression. Apart from GUIDs taking more bytes than an integer, I expected that because of the nature of GUIDs they would not compress very well, and tried to set out and prove it.

I created the following script which uses the TransactionHistory table in the Adventureworks2008 database as the main source of data. To do so I have three tables containing five copies of the data from the TransactionHistory table. In two of the tables the four columns that contain identifiers (TransactionID, ProductID, ReferenceOrderID and ReferenceOrderLineID) are changed to be uniqueidentifiers with defaults using the NEWID or NEWSEQUENTIALID functions depending on the table. I then used the function sp_estimate_data_compression_savings to see what the estimated savings would be if I added row or page compression. These are the results I obtained (full results are here):

As I expected page compression when using integers would produce the most compressed data, but this is almost twice as compressed as the page compression obtained using GUIDs. This example also showed that for row compression there is no benefit in having sequential GUIDs.