What’s in Enterprise only? (Support for Data Compression & Vardecimal Storage Format)

  • Article

Starting with version 2008, SQL Server supports both row and page compression for both tables and indexes. Also SQL Server 2008 R2 continues to support, although it has been deprecated, the vardecimal storage format that was introduced with SQL Server 2005 Service Pack 2. However, because row-level compression achieves the same goals, the vardecimal storage format is deprecated and will be removed in a future version of the product.

However, in order for an instance of SQL to let you enable any of these compression mechanisms on any single object, it must be running the Enterprise edition (or a Developer Edition or an Evaluation Edition, both of which behave exactly like the Enterprise does. The only difference among them three is their licensing.). Not running an Enterprise edition and trying to enable row or page compression on any object, throws error 7738 “Cannot enable compression for object '%.*ls'. Only SQL Server Enterprise Edition supports compression.” Also trying to enable vardecimal storage format raises error 11407 “Vardecimal storage format can not be enabled for '%.*ls'. Only Enterprise edition of SQL Server supports vardecimal.”

Errors also occur if a non-Enterprise edition instance of SQL attempts to startup a database (from a backup or from a set of files attached to the instance) that contains at least one object configured to use any of these three data compression features.

Being that the case, during the database startup process it would fail to bring it online, the database will be marked suspect to indicate that something went wrong during startup, and will report error 909 in the Application Event Log and in SQL Server’s ERRORLOG. The descriptive message for that error is “Database '%.*ls' cannot be started in this edition of SQL Server because part or all of object '%.*ls' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.” That error will be followed by error 933 “Database '%.*ls' cannot be started because some of the database functionality is not available in the current edition of SQL Server.”

This post is part of the a series I decided to call “Understanding the value of the Enterprise Edition, one feature at a time” which I started in September 2011 and will grow on a weekly basis, having a new post incorporated to the family every Friday.