Using vardecimal storage in SQL Server 2005 SP2 CTP2

  • Article

As you probably know, Service Pack 2 for SQL Server 2005 introduces the vardecimal storage format.  Below is a step-by-step example using the AdventureWorks database.  There are a few incorrections in BOL SP2 CTP2 although these are not major problems and can be easily resolved; I imagine discussions are ongoing about naming conventions for the vardecimal options. 

USE master ;
GO

-- Enable vardecimal on database
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

-- Enable vardecimal compression at the table level
USE AdventureWorks
GO
-- Note: The BOL example incorrectly references 'decimal data compression'
EXEC sp_tableoption 'Sales.SalesOrderDetail', 'vardecimal storage format', 1
GO

-- Does not show vardecimal properties
EXEC sp_help 'Sales.SalesOrderDetail'

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

-- Under the covers, this uses sys.dm_db_index_physical_stats to calculate the stats
-- Documented in BOL CTP2 as sp_estimatedecimalcompression
EXEC sp_estimated_rowsize_reduction_for_vardecimal 'Sales.SalesOrderDetail' ;

-- Clean-up / disable vardecimal storage format
USE AdventureWorks
GO

-- Disable table-level storage format
EXEC sp_tableoption 'Sales.SalesOrderDetail', 'vardecimal storage format', 0
GO
USE master;
GO

-- Disable database property
EXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'OFF' ;
GO