sp_db_vardecimal_storage_format (Transact-SQL)

New: 12 December 2006

Returns the current vardecimal storage format state of a database, or enables or disables a database for vardecimal storage format. Requires SQL Server 2005 Service Pack 2 or later versions. Vardecimal storage format is available only in SQL Server 2005 Enterprise, Developer, and Evaluation editions.

Important

Changing the vardecimal storage format state of a database can affect backup and recovery, database mirroring, sp_attach_db, log shipping, and replication. For information about the vardecimal storage format, see Storing Decimal Data As Variable Length.

Syntax

sp_db_vardecimal_storage_format [ [ @dbname = ] 'database' ] 
    [ , [ @vardecimal_storage_format = ] {'ON' | 'OFF' }] [ ; ]

Arguments

  • [ @dbname= ] 'database'
    Is the name of the database for which the storage format is to be changed. database is sysname, with no default. If the database name is omitted, the vardecimal storage format states of all the databases in the instance of SQL Server are returned.
  • [ @vardecimal_storage_format = ] {'ON'|'OFF'}
    Specifies whether vardecimal storarge format is enabled. @vardecimal_storage_format can be ON or OFF. The parameter is varchar(3), with no default. If a database name is provided but @vardecimal_storage_format is omitted, the current setting of the specified database is returned.

Return Code Values

0 (success) or 1 (failure)

Result Sets

If the database storage format cannot be changed, sp_db_vardecimal_storage_format returns an error. If the database is already in the specified state, the stored procedure has no effect.

Remarks

sp_db_vardecimal_storage_format will fail in the following circumstances:

  • There are active users in the database.
  • The database is enabled for mirroring.
  • The edition of SQL Server does not support vardecimal storage format.

To change the vardecimal storage format state to OFF, a database must be set to simple recovery mode. When a database is set to simple recovery mode, the log chain is broken. Perform a full database backup after you set the vardecimal storage format state to OFF.

Changing the state to OFF will fail if there are tables using vardecimal database compression. To change the storage format of a table, use sp_tableoption. To determine which tables in a database are using vardecimal storage format, use the OBJECTPROPERTY function and search for the TableHasVarDecimalStorageFormat property, as shown in the following example.

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

Permissions

Requires ALTER DATABASE permission.

Examples

The following example enables compression in the AdventureWorks database, confirms the state, and then compresses decimal and numeric columns in the Sales.SalesOrderDetail table.

USE master ;
GO

EXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'ON' ;
GO

-- Check the vardecimal storage format state for
-- all databases in the instance.
EXEC sp_db_vardecimal_storage_format ;
GO

USE AdventureWorks ;
GO

EXEC sp_tableoption 'Sales.SalesOrderDetail', 'vardecimal storage format', 1 ;
GO

See Also

Reference

Database Engine Stored Procedures (Transact-SQL)

Other Resources

Storing Decimal Data As Variable Length

Help and Information

Getting SQL Server 2005 Assistance