Guids and compression

Published 14 July 09 09:00 AM | MartinBell

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):

object_name Type % When compressed
TransactionHistory_int PAGE 35.14
TransactionHistory_int ROW  56.87
TransactionHistory_sequentialguid PAGE 56.21
TransactionHistory_guid PAGE 67.58
TransactionHistory_guid ROW  73.42
TransactionHistory_sequentialguid ROW  73.03


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
.

Comments

# Dew Drop – July 14, 2009 | Alvin Ashcraft's Morning Dew said on July 14, 2009 02:14 PM:

Pingback from  Dew Drop – July 14, 2009 | Alvin Ashcraft's Morning Dew

# Dew Drop – July 15, 2009 | Alvin Ashcraft's Morning Dew said on July 15, 2009 01:32 PM:

Pingback from  Dew Drop – July 15, 2009 | Alvin Ashcraft's Morning Dew

This Blog

SQL Blogs

Syndication