次の方法で共有

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 '<dbname>', '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 '<table-name>', '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 '<table-name>', '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('<table-name>’), 'TableHasVarDecimalStorageFormat')

or

select name, object_id, type_desc

from sys.objects

where objectproperty(object_id, N' TableHasVarDecimalStorageFormat ') = 1

 

 

I will discuss the boundary conditions for enabling vardecimal storage format and the implications of changing database version on backup/recovery and mirroring in next blogs.

Comments

  • Anonymous
    November 13, 2006
    Before enabling Vardecimal storage format, you may want to know the potential reduction in the size of

  • Anonymous
    December 28, 2006
    Disabling the vardecimal storage format for a database requires SIMPLE recovery model. Why? This can have implications on the backup/restore strategy for that database.

  • Anonymous
    December 29, 2006
    Please refer to the latest BLOG on this. Can you please elaborate on the backup/restore scenario that you have in mind. thanks Sunil

  • Anonymous
    December 29, 2006
    I understood the reason now. The log chain is broken when the database is moved to a less advanced version (removing vardecimal). Then switching to simple recover mode indicates the need for a new backup.

  • Anonymous
    February 06, 2007
    SQL Server 2005 SP2 では、decimal, numeric データ型利用時のディスクスペースが減る !? vardecimal とは.