Database Space issue in SQL server 2005 (New feature VARDECIMAL)

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.

In the next session I will explain what new feature available in SQL Server 2008 R2 for same space problem.

Till than

Thanks & Best Regards,

Rajat Jaiswal

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s