Sunday, December 21, 2008

Enabling vardecimal storage format

First, this feature is only available in EE and Dev SKU.

Enabling vardecimal storage format on a table is a two step process as follows:

First you need to enable database for Vardecimal storage format. This can be done using the stored procedure sp_db_vardecimal_storage_format. The exact command is as follows

exec sp_db_vardecimal_storage_format '', 'ON'

When the above command is executed, SQL Server internally bumps the database version number but no tables are enabled for Vardecimal storage format. The database version needs to be bumped to indicate that the data in this database can potentially have a different storage format (i.e. the Vardecimal storage format). This is used to prevent attaching a Vardecimal enabled database to earlier versions of SQL Server 2005 as those versions don’t know how to interpret the new storage format. You can only enable Vardecimal storage format on user databases.

To find out which database(s) is enabled for Vardecimal storage format, you can use the following command

exec sp_db_vardecimal_storage_format

Once you have enabled the database for Vardecimal storage format, you can now choose to enable one or more tables (based on the potential disk savings using the tool described earlier) with this new storage format as follows

sp_tableoption '', 'vardecimal storage format', 1

This command, potentially an expensive one (same order as creating an index), converts all the rows in the table containing columns of type decimal/numeric to Vardecimal storage format. During this conversion, the table is locked and is not available. If the table has no clustered index, then all non-clustered indexes are rebuilt because the RIDs of the rows will change due to storage format change. However, if you have clustered index on the table, then only the non-clustered indexes containing decimal numeric column as key or included column need to be rebuilt. Note, that you cannot enable vardecimal storage format on all tables. Before enabling vardecimal storage format, SQL Server needs to make sure that we can always revert back to static storage format for decimal data and that update of decimal/numeric data always succeeds. If these conditions are not satisfied, the conversion to vardecimal storage format is denied.

To disable Vardecimal storage format on the table, you can use the following command

sp_tableoption '', 'vardecimal storage format', 0

SQL Server guarantees that you can always revert back to ‘static’ storage format unless you run out of disk space during conversion. Note, that the space overhead, to enable/disable Vardecimal storage format, is of the same order as building an index and it is not an online operation.

You can use the following command to find out which tables(s) has been enabled for Vardecimal storage format

select objectproperty(object_id('’), 'TableHasVarDecimalStorageFormat')


select name, object_id, type_desc
from sys.objects
objectproperty(object_id, N'TableHasVarDecimalStorageFormat') = 1

No comments: