How to resolve Space issue in SQL server 2005 TIP #93

Hello friends,
Many times we face space issues with our database. To resolve this problem SQL Server 2005 provided one more solution which is VARDECIMAL feature.

** VARDECIMAL feature exist in SQL SERVER’s Developer & Enterprise edition.

** This feature is not available in SQL Server 2005’s Standard edition.
I hope by the name it is clear that what VARDECIMAL functionality is.
If not then please read below line VAR + Decimal = VARDECIMAL
It is just like VARCHAR features means it will consume space which is actual require to store the decimal value. It ignores null and zero value to save space
. To understand it more lets implement it.
I am taking an example here
First we will check the database size without applying the VARDECIMAL Feature .
To check the current space use below command
    Use Your Database name;
    GO
    Sp_spaceused;
The above command provides you current space used by the database. Now we will check whether the VARDECIMAL feature is enabled or not to check this we can use two way go to database property and check it as shown in below fig

Or
use below command
   exec sp_db_vardecimal_storage_format
Now to enable the property on particular database either make the property true in above fig
or use following command
    exec sp_db_vardecimal_storage_format ‘Your DatabaseName’, ‘ON’
Once the database has VARDECIMAL storage format.
We need to apply VARDECIMAL storage property on actual table. Before applying the feature first check the space of existing table which can be determine by following command.
   Sp_spaceused table Name;
Now check whether the table has VARDECIMAL feature enable or not. To check this feature just right click on table and check table property.
Now to enable this property use following command
   sp_tableoption ‘YourTableName’, ‘vardecimal storage format’, 1

Once you run this option on database you will find the property on the table is now true.
Now to check how much space gain you got use again
    Sp_spaceUsed table name ;
command.
Now compare the space with existing result and check how much you gain in terms of space.
I hope this feature will help you in some where if you are using SQL Server 2005 Enterprise edition.
Enjoy!!
RJ