Using vardecimal storage in SQL Server 2005 SP2 CTP2


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


Comments (2)

  1. Anonymous says:

    Been a while since my first post and slowly getting back into the real-world. Having spent the…

  2. Anonymous says:

    这个新的存储格式的主要目的是将decimalh和numberic的长度可变长。这样就可以节省一些存储空间。关于它的一些介绍,可以参考微软官方的文档 ms-help://MS.SQLCC.v9/MS.S…

Skip to main content