Many of us have started working on SQL 2008 or SQL 2008 R2 and as advised, we are using datetime2, date, time and DateTimeOffset datatypes. There are many benefits of using this over Datetime. But this post is not about that.
I have moved to my current project before six months and Initially, I was looking into existing database design. We are using SQL server 2008 R2. When I looked into the tables, I found that all the date and time columns are using Datetime2, date or time datatype as required. So, that’s nice start.
After taking to analysts, I figured out, In time part, we only need precision upto Seconds not more than that. But, in database fractional seconds are added nowhere.
If we run following statements, we can find out the impact
DECLARE @d7 datetime2 = sysdatetime()
DECLARE @d0 datetime2(0) = sysdatetime()
DECLARE @d1 datetime2(1) = sysdatetime()
DECLARE @d2 datetime2(2) = sysdatetime()
DECLARE @d3 datetime2(3) = sysdatetime()
DECLARE @d4 datetime2(4) = sysdatetime()
SELECT @d7 AS NoPrecision
, @d0 AS ZeroFractionPrecision
, @d1 AS OneFractionPrecision
, @d2 AS TwoFractionPrecision
, @d3 AS ThreeFractionPrecision
, @d4 AS FourFractionPrecision
SELECT datalength(@d7) AS NoPrecisionSize
,DATALENGTH(@d0) AS ZeroFractionPrecisionSize
,DATALENGTH(@d1) AS OneFractionPrecisionSize
,DATALENGTH(@d2) AS TwoFractionPrecisionSize
,DATALENGTH(@d3) AS ThreeFractionPrecisionSize
,DATALENGTH(@d4) AS FourFractionPrecisionSize
Results:-
So, even if we use fractional level up to two, six bytes are used. But, if we do not specify fractional seconds, it takes seven as default.
Same goes true for Time datatype.
Even if we think normal size application with not more then 100 tables, with on an average 10000 rows and two such fields in table, it can become big savings in the end.
Below query can be used to findout such columns in database.
SELECT TABLE_NAME
,COLUMN_NAME
,DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('Datetime2','Time')
AND DATETIME_PRECISION > 2
Also, if we think storage is cheap and this not much difference, then we it may not be wise thinking due to reasons mentioned below.
- Page need to be filled in Buffer Cache, so it will require more memory
- More I/O will be needed to fetch broader columns
- Index size will be heigher and it will take more time in rebuild
- Disaster Recovery will take more time
Summary:- If more precision is not needed in time part of datatime2 columns or time column, we should consider specifing presicion.
Update(15 Jan, 2011) Note:- Internallly SQL server users 7 bytes even if we specify datetime2(0) and server use 9 bytes if datatype is datetime2(7), one more byte is required for svaing the precision.
Rererences:- http://technet.microsoft.com/en-us/library/bb677335.aspx