In Service Pack 2, SQL Server 2005 adds a new storage format for numeric and decimal datatypes called vardecimal. Vardecimal is a variable-length representation for decimal types that can save unused bytes in every instance of the row. The biggest amount of savings come from cases where the decimal definition is large (like decimal(38,6)) but the values stored are small (like a value of 0.0) or there is a large number of repeated values or data is sparsely populated.
SQL Server 2005 also includes a stored procedure that can estimate the savings before you enable the new storage format.
To enable vardecimal storage format, you need to first allow vardecimal storage on the database;
exec sys.sp_db_vardecimal_storage_format N'databasename', N'ON'
Once the database option is enabled, you can then turn on vardecimal storage at a table level using the following procedure;
exec sp_tableoption 'tablename', 'vardecimal storage format', 1
Vardecimal storage format presents an overhead due to the complexity inherent in variable length data processing. However in IO bound workloads, savings on IO bandwidth due to efficient storage can far exceed this processing overhead.
If you would like more information on this topic, updated SQL Server 2005 Books Online for Service Pack 2 contains extensive information on the new vardecimal format.
Things borrower must know about Pre Closing Home loan - Most people tend to take a home loan for 15 to 20 years as this usually offers the lowest home loan interest rates. Now to prepay the loan, you have to pa...