Storing Decimal Data As Variable Length

Artikkeli
12/03/2008

New: 12 December 2006

The decimal and numeric data types are typically stored on the disk as fixed-length data. The numeric data type is functionally equivalent to the decimal data type. In SQL Server 2005 Service Pack 2 (SP2) and later versions, decimal and numeric data types can be stored as a variable-length column by using the vardecimal storage format. The vardecimal storage format is available only in SQL Server 2005 Enterprise, Developer, and Evaluation editions.

Note

Vardecimal is a storage format, not a data type.

The vardecimal storage format might significantly reduce the storage size of the data, but does add a small amount of CPU overhead. The vardecimal storage format is applied at the table level. This means that you cannot store some decimal columns of a table in the vardecimal storage format and not other columns. The decimal data remains an exact data type.

When the vardecimal storage format is enabled on a table, the decimal data is stored in the data, index, and log pages in the vardecimal storage format. Changing the storage format is an offline operation. The table that is being modified is locked exclusively for the duration of operation, and is unavailable for concurrent read or write access.

Implementation of the Vardecimal Storage Format

Depending on the precision of the column (1 to 38), storage of a decimal value consumes between 5 and 17 bytes. When a table is not using the vardecimal storage format, every entry in the table consumes the same number of bytes for each defined decimal column, even if the value of a row is 0, NULL, or some value that could be expressed in a smaller number of bytes, such as the number 3. When a table is stored in the vardecimal storage format, the decimal columns of each row consume only the space that is required to contain the provided number, plus 2 bytes of overhead. The result is always between 5 and 20 bytes. This includes the 2 bytes of overhead to store the offset to the value. However, null values and zeros are treated specially and take only 2 bytes.

If the table does not have any variable length columns, there is an additional overhead of 2 bytes per row to store the number of variable length columns. If the table already has at least one variable length column, there is no additional overhead.

The following table shows the number of bytes that are required to store decimal data in the regular fixed format, and the maximum number of bytes that are required to store decimal data in the vardecimal storage format. When a table is stored in the fixed format, the value that is listed will be used for every row. When a table is stored in vardecimal storage format, many values will require less than the number of bytes that is listed.

Column precision	Original fixed decimal size (bytes)	Maximum vardecimal data area (bytes)	Overhead to store offset (bytes)	Maximum vardecimal storage used (bytes)
1-3	5	3	2	5
4-6	5	4	2	6
7-9	5	5	2	7
10-12	9	6	2	8
13-15	9	8	2	10
16-18	9	9	2	11
19	9	10	2	12
20-21	13	10	2	12
22-24	13	11	2	13
25-27	13	13	2	15
28	13	14	2	16
29-30	17	14	2	16
31-33	17	15	2	17
34-36	17	16	2	18
37-38	17	18	2	20

Using the Vardecimal Storage Format

You can use the vardecimal storage format when you are trying to resolve the following issues:

There is a shortage of disk space.
Disk access (I/O) is a bottleneck on system performance.
You must have a high level of precision for some of the data, even though many of the values are small, NULL, or 0 (such as a table in a data warehouse with a decimal column that has many rows that contain 0 or integer values).

Changing the storage format of a table requires the storage of the table (either clustered index or heap) to be rebuilt. If the clustered index of a table contains a decimal column, all nonclustered indexes must also be rebuilt because they contain the clustered key values. If the clustered index does not have a decimal column, nonclustered indexes are rebuilt if they contain a decimal column. If the table is a heap (that is, does not have a clustered index), all nonclustered indexes must be rebuilt to point to the new row locations in the heap.

Rebuilding a table to enable or disable vardecimal storage format can require more than double the total storage space of the original table. If the table does not contain decimal or numeric columns, enabling vardecimal storage format is a metadata operation only. Expect lots of log activity when the table and indexes are rebuilt.

Vardecimal storage format can be used for both read only and read/write tables. The storage savings must be balanced against the additional CPU usage that is required to convert the storage format of the rows every time that they are accessed. Additionally, writing to a table that is using vardecimal storage format can slow performance because of an increased number of page splits.

Limitations of the Vardecimal Storage Format

The following restrictions apply:

Requires SQL Server 2005 SP2 or later versions.
Vardecimal storage format cannot be enabled in the system databases: master, model, msdb, tempdb, or distribution. When a query sorts data that is stored in vardecimal storage format, the data is sorted in tempdb in a fixed decimal state. Typically, the data will require significantly more space in tempdb than the space occupied by the vardecimal storage format source table in the source database.
Vardecimal storage format cannot be applied to views, indexed views, XML indexes, and full-text indexes. However, the tables that underlie those objects can use vardecimal storage format.
Internal tables, such as metadata and notification tables, cannot use vardecimal storage format.
Table-valued functions cannot use vardecimal storage format.
The numeric column that is stored in a vardecimal storage format table cannot also be encrypted.
Heterogeneous partitions (that is, fixed decimal format and vardecimal storage format partitions) are not supported.
New tables that are created from a vardecimal storage format table by using the Transact-SQL SELECT … INTO… syntax do not inherit the vardecimal storage format.
Databases that are enabled for database mirroring cannot have their vardecimal storage format states changed. You must remove database mirroring to enable vardecimal storage format on the database. However, you do not have to remove database mirroring when individual tables are enabled or disabled for vardecimal storage format.
SQL Server must be able to guarantee that all updates will succeed and that the table can always be returned to the fixed decimal format. Therefore, a table cannot be changed to vardecimal storage format if the additional overhead causes an existing row to exceed 8060 bytes, or an existing index value to exceed 900 bytes.

Note

Vardecimal storage format differs from variable text (varchar) storage in that SQL Server lets you create a row that could exceed 8060 bytes if all variable columns are at the maximum size. SQL Server enforces the 8060-byte limit when text data is inserted or updated. SQL Server does not let you create a set of decimal columns that could ever exceed the 8060 byte limit for a row. The 8060-byte limit is enforced when the table format is changed to vardecimal storage.
When you are transferring a database by using the detach-and-attach method of the Copy Database Wizard, the attach operation will fail if the destination Database Engine is not SQL Server 2005 SP2 or later. The SQL Server Management Objects method will create the new database and tables without using the vardecimal storage format. The database and tables can be changed to the vardecimal format after the transfer if the Database Engine is SQL Server 2005 SP2 or later.

Backup and Recovery, Database Mirroring, sp_attach_db, and Log Shipping

Backup and recovery, database mirroring, sp_attach_db, and log shipping work correctly with the vardecimal storage format; however, to include a database that is using the vardecimal storage format, each instance of SQL Server must be upgraded to at least SQL Server 2005 SP2. For example, you cannot restore a log backup of a database enabled for the vardecimal storage format to a database that is not enabled, or mirror from a database enabled for the vardecimal storage format to a database that is not enabled, or attach a database enabled for the vardecimal storage format enabled from SQL Server 2005 SP2 to an earlier version of SQL Server. If you restore a full backup of a database enabled for the vardecimal storage format to a database that is not enabled for the vardecimal storage format, the database will become enabled for the vardecimal storage format.

When a table is changed to the vardecimal storage format, the log backup chain remains valid, and the database can be restored by applying the last full backup plus a valid log chain. To prevent creating backups that are not valid, you must change the database to the simple recovery model before you modify any table to remove the vardecimal storage format. After you remove the vardecimal storage format of any table, you should create a full database backup.

Using Vardecimal Storage Format with Database Mirroring

The following procedures provide the steps for using the vardecimal storage format with database mirroring.

To use the vardecimal storage format with database mirroring

Bring both the principal and the mirror partner instances to at least SQL Server 2005 SP2.
If you are currently using database mirroring, remove database mirroring and delete the mirror partner. For more information, see How to: Remove Database Mirroring (Transact-SQL).
Enable vardecimal storage format on the principal database, and make sure that the principal database is in the full recovery model.
Establish database mirroring by using full and log backups from the principal database. For more information, see How to: Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL).
Modify individual tables to use vardecimal storage format.

Note

You do not have to remove database mirroring to change the storage format of individual tables.

To remove the vardecimal storage format

Modify tables in the principal database to remove the vardecimal storage format.
Remove database mirroring.
Set the principal database to simple recovery mode. This breaks the log chain.
Disable the vardecimal storage format on the principal database.
Drop the mirror partner database.
Change the principal database back to the full recovery mode.
Back up the principal database and reestablish database mirroring.

Effects of the Vardecimal Storage Format on Replication Operations

Replication works as usual on tables that use the vardecimal storage format, but with the following considerations:

decimal data types stored in the vardecimal storage format are converted to the fixed decimal format for transfer during replication. The distribution database cannot be enabled for vardecimal storage format. Therefore, data is not stored in the vardecimal storage format when it is stored in the replication tables in the distribution database. At the subscriber, the log records are applied ordinarily.
A vardecimal storage format table can be replicated to a fixed decimal format table, and a fixed decimal format table can be replicated to a vardecimal storage format table.
The create-table process related to a new subscription does not create tables using the vardecimal storage format. This enables replication to succeed, regardless of the service-pack level of the Database Engine, or the vardecimal storage format enabled state of the subscribing database. The subscribing table can be enabled for the vardecimal storage format at the subscriber after the table is created or by modifying the creation scripts before they are applied.

The following table describes the script requirements for various subscribers.

Subscriber	Script
SQL Server 2000 or SQL Server version 7.0	Create-table scripts can be used without modification.
SQL Server 2005, database is not marked for vardecimal storage format.	Create-table scripts can be used without modification.
SQL Server 2005, database is marked for the vardecimal storage format, but you do not want the subscriber table to have the vardecimal storage format enabled.	Create-table scripts can be used without modification.
SQL Server 2005, database is marked for the vardecimal storage format, and you want the SQL Server 2005 subscriber table to have the vardecimal storage format enabled.	Create-table scripts can be modified to enable the vardecimal storage format in the database and to enable the vardecimal storage format of the tables. Or the subscriber database and tables can be enabled by using the stored procedures that are described in the section "Enabling the Vardecimal Storage Format" that follows.

Additional Considerations

The following is a list of additional considerations to keep in mind when you are working the vardecimal storage format:

The vardecimal storage format has no effect bulk import and export (bcp) operations.
The DATALENGTH function does not detect the vardecimal storage format and returns the number of bytes that would be stored in the fixed decimal format.
In rare occasions, the vardecimal storage format will prevent SQL Server from using a query plan that was optimal for fixed decimal data.
The vardecimal storage format can be used with any database compatibility level.
If a table has no columns of the decimal or numeric data type when sp_tableoption is executed, the table metadata is changed to indicate that the table uses the vardecimal storage format. When new decimal columns are added later, they will be stored in the vardecimal storage format. No special techniques are needed to add or remove columns from a table that is using the vardecimal storage format.

Enabling the Vardecimal Storage Format

To enable or change the vardecimal storage format requires the following permissions:

To enable vardecimal storage format in a database requires ALTER DATABASE on the server.
To change a table to vardecimal storage format requires ALTER permission on the table.

Before you enable the vardecimal storage format, you should first verify that the table will shrink when the vardecimal storage format is enabled. When the defined column precision is required for most rows, the overhead that is associated with vardecimal storage format could be more than the savings and could result in a larger table. To estimate the row size reduction before you modify the table, use the sp_estimated_rowsize_reduction_for_vardecimal stored procedure. If you decide to change the storage format of the table, enable the database for vardecimal storage format, and then enable individual tables for the vardecimal storage format.

You can enable the vardecimal storage format for decimal data types by using stored procedures or SQL Server Management Studio:

Run sp_db_vardecimal_storage_format to enable the vardecimal storage format in the database, and then run sp_tableoption, to enable the vardecimal storage format in the appropriate tables.
In Management Studio, use the Database Properties Options Page to enable the vardecimal storage format in the database. You must use sp_tableoption to change a table to the vardecimal storage format.

Identifying the Vardecimal Storage Format Tables

To determine which tables in a database are using the vardecimal storage format, use the OBJECTPROPERTY function and search for the TableHasVarDecimalStorageFormat property.

The following example returns 1 if the Production.WorkOrderRouting table uses the vardecimal storage format, and 0 if it does not.

USE AdventureWorks ;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('Production.WorkOrderRouting'), 
   'TableHasVarDecimalStorageFormat') ;
GO

The following example searches the AdventureWorks database for all tables that use the vardecimal storage format.

USE AdventureWorks ;
GO
SELECT name, object_id, type_desc
FROM sys.objects 
 WHERE OBJECTPROPERTY(object_id, 
   N'TableHasVarDecimalStorageFormat') = 1 ;
GO

Problems Removing the Vardecimal Storage Format

To remove the vardecimal storage format from a table, the table must be rebuilt in the fixed decimal format. This could significantly increase the disk space that is used by the table. If enough disk space is not available, the operation will fail. In this case, to disable the vardecimal storage format, you must make sure that enough disk space is available to SQL Server. The expansion operation also requires temporary space to store data in both vardecimal and regular format. If the expanded table would fit on the available disk space after the expansion but is failing to expand only because of the lack of temporary disk space, you can expand the data incrementally by copying rows of the table to a new unexpanded table.

To remove the vardecimal storage format from a database immediately after it has been modified, drop the database, and then restore the database from a backup that was made before the vardecimal storage format the database enabled.

When you are moving a database from a SQL Server 2005 Enterprise, Developer, or Evaluation edition to another edition or to an earlier version of SQL Server, you must first open the database by using one of the required editions, remove the vardecimal storage format, and then migrate the database. Attaching a database that contains the vardecimal storage format to an ineligible server will fail.

Jaa