posted on Wednesday, December 27, 2006 1:04 AM
by
admin
VarDecimal in SP2 (December CTP)
I've been experimenting with the new VarDecimal data type in SP2.
In a virtual PC named vpcPeter (there's a vpcRay and vpcEgon nearby), I deployed the December CTP of SP2. I then created a database named StorageTests and a table inside named dbo.VarDecimalTest.
I wrote a routine that populated a decimal field in dbo.VarDecimalTest with 10,000 rows of decimal(18,5) data type.
I next enabled the vardecimal datatype for the database using exec sp_db_vardecimal_storage_format 'StorageTests', 'on' and executed the test sproc (exec sys.sp_estimated_rowsize_reduction_for_vardecimal 'dbo.VarDecimalTest') to estimate row_len savings:
29.47 / 32.87 ~= 0.8966 equating to a roughly 10% reduction in data table size.
I then enabled the varDecimal data type on the table with the following command: sp_tableoption 'dbo.VarDecimalTest', 'vardecimal storage format', 1. Note that this does not add a new data type per se (executing select * from sys.types still returns only 27 rows...) but it converts existing decimal / numeric rows in the specified table to the new data type - saving space:
Before:
After:
The script I used to generate these results is available
here.
My original table is 352 KB, my VarDecimal table table is 312 KB. 352 * .8966 = 315. Pretty darn close.
:{> Andy
Technorati Tags: SQL Server VarDecimal 2005 SP2 December CTP