What’s in Enterprise only? (Partitioned Tables and Indexes)

  • Article

Starting with version 2005, SQL Server introduces the concept of partitioned tables and indexes. Partitioning can make large tables and indexes more manageable and scalable. By using partitioning, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes in earlier versions of SQL Server. Maintenance operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required, instead of the whole table.

However, to be able to use this feature in a database requires the instance of SQL Server to 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 create the partition function that maps the rows of a table or index into partitions based on the values of a specified column (through the CREATE PARTITION FUNCTION DDL statement) will raise error 7736 “Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition of SQL Server supports partitioning.”

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) which has one or more partition functions defined in it.

In that case, the database startup process would fail to bring it online, the database will be marked suspect to indicate that something went wrong during startup, and will report error 905 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 it contains a partition function '%.*ls'. Only Enterprise edition of SQL Server supports partitioning.” 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.