Reducing the Size of your Database in SQL Server 2005/SP2

An exciting new feature in SQL Server 2005/SP2 is Vardecimal Storage Format. This storage format lets you reduce the size of your table significantly if the table has one of more columns of type decimal or numeric without requiring any changes to your application.

Up until now, the decimal and numeric types are stored as fixed length data in SQL Server. Both of these types are functionally equivalent and have a format of (p, s) where p is the precision (number of decimal digits) and s is the scale representing number of digits after the decimal. Depending on the precision (it can be declared in the range from 1 to 38), the decimal value can take anywhere from 5 bytes to 17 bytes. This can bloat the size of the table, especially when you have small decimal values for a column declared with high precision requirement. This issue is similar to char (17) vs. varchar(17). In this case, if most of your character data is 1 or 2 characters long but the max value is 17 characters long, you can reduce the size of the table by declaring the column to be of type varchar(17) instead of char(17).

The new vardecimal storage format stores the decimal/numeric values in a variable length storage format. It provides efficient storage of decimal/numeric data by eliminating the leading/trailing zeros and only storing the minimum required bytes. Using this format, you can get significant space savings (depending on your data distribution) in the space required to store decimal/numeric data. You can enable vardecimal storage format at a table level.

In our in-house testing, we have seen significant reduction in the size of the FACT table(s) that has large number of decimal columns. FACT tables are typically the largest table in a Data Warehouse. Here are some the numbers from our testing.

Best case reduction in the size of the table

57%

69%

51%

In my next post http://blogs.msdn.com/sqlserverstorageengine/archive/2006/11/13/estimating-the-space-savings-with-vardecimal-storage-format.aspx , I will describe when and how to enable Vardecimal storage format on one or more tables in your database.

SQL SERVER – SQL SERVER – UDF – Get the Day of the Week Function – Part 2

I have written article about SQL SERVER – UDF – Get the Day of the Week Function. I have received good modified script from reader Mihir Popat has suggested another code where Sunday does not have to be necessary the first day of the week.

CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
DECLARE @weekDay INT
-- Here I have subtracted 7 For keeping Sunday as the First day
-- like wise for Monday we need to subtract 2 and so on
SET @weekDay = ((DATEPART(dw,GETDATE())+@@DATEFIRST-7)%7)
SELECT @rtDayofWeek = CASE @weekDay
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END
RETURN (@rtDayofWeek)
END
GO
SELECT dbo.udf_DayOfWeek(GETDATE())

Source:YM vannetsoft

Skype:vancvcafe