sp_db_vardecimal_storage_format (Transact-SQL)
Applies to: SQL Server
Returns the current vardecimal storage format state of a database or enables a database for vardecimal storage format. Starting with SQL Server 2008 (10.0.x), user databases are always enabled. Enabling databases for the vardecimal storage format is only necessary in SQL Server 2005 (9.x).
Note
SQL Server 2019 (15.x) supports the vardecimal storage format; however, because row-level compression achieves the same goals, the vardecimal storage format is deprecated. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Important
Changing the vardecimal storage format state of a database can affect backup and recovery, database mirroring, sp_attach_db, log shipping, and replication.
Syntax
sp_db_vardecimal_storage_format [ [ @dbname = ] 'database_name']
[ , [ @vardecimal_storage_format = ] { 'ON' | 'OFF' } ]
[;]
Arguments
[ @dbname= ] 'database_name'
Is the name of the database for which the storage format is to be changed. database_name is sysname, with no default. If the database name is omitted, the vardecimal storage format status of all the databases in the instance of SQL Server are returned.
[ @vardecimal_storage_format= ] {'ON'|'OFF'}
Specifies whether the vardecimal storage 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.
Important
This argument has no effect on SQL Server 2008 (10.0.x) or later versions.
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.
If the @vardecimal_storage_format argument is not provided, returns the columns Database Name and the Vardecimal State.
Remarks
sp_db_vardecimal_storage_format returns the vardecimal state but cannot change the vardecimal state.
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 model. When a database is set to the simple recovery model, 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 AdventureWorks2022;
GO
SELECT name, object_id, type_desc
FROM sys.objects
WHERE OBJECTPROPERTY(object_id,
N'TableHasVarDecimalStorageFormat') = 1 ;
GO
Examples
The following code enables compression in the AdventureWorks2022
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 'AdventureWorks2022', 'ON' ;
GO
-- Check the vardecimal storage format state for
-- all databases in the instance.
EXEC sp_db_vardecimal_storage_format ;
GO
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'Sales.SalesOrderDetail', 'vardecimal storage format', 1 ;
GO
See Also
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for