May 11, 2011 at 7:25 am
Hi guys,
the date-datatypes have always been an big discussion point in SQL Server < 2008 while there was no "just date"-Type (just small/datetime) to represent just a "day".
The Kimballgroup recommends to proceed with date in the same way then other attributes -> surrogate key to a dimension.
As the date-type was introduced by M$ in 2008 I would like to know if anyone had made performance-testing if the new date type equals the int counterpart (e.g. YYYYMMDD).
It is quite likeable/convinient to use the date-type as you can operate on the column directly and do not have to join to a date-dimension or convert the int-value to a date.
However if the date type is still much slower than the int-counterpart I will have to measure how much my comfort is worth in performance 😉
Can anyone tell me something about it?
May 11, 2011 at 11:03 am
I have not tested them side-by-side however DATE has an edge in terms of its size...the storage size of an INT is 4 bytes while the storage size of the new DATE type is only 3 bytes.
All things being equal I am in favor of choosing the correct data type to store the data. Go with DATE unless there is a compelling reason (like wide variances in performance) not too.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2011 at 2:38 pm
don't get me wrong. I am in favour of using the datetype for dates,
I just want to know if anyone made bigger test with those types before I move in to a performance-threat.
I tried a few test, but my queries didn't get expensive enought to direct in one direction.
Even if I tried to get the year and month for a group by
YEAR(date_date), MONTH(date_date) vs int_date/10000 AS year, int_date/100%100 AS month on a few 100ks of rows the results differed just a 1-2 seconds but no side winning while repeating the test
May 11, 2011 at 2:46 pm
mitch.fh (5/11/2011)
don't get me wrong. I am in favour of using the datetype for dates,I just want to know if anyone made bigger test with those types before I move in to a performance-threat.
I tried a few test, but my queries didn't get expensive enought to direct in one direction.
Even if I tried to get the year and month for a group by
YEAR(date_date), MONTH(date_date) vs int_date/10000 AS year, int_date/100%100 AS month on a few 100ks of rows the results differed just a 1-2 seconds but no side winning while repeating the test
If you're planning on using date functions like MONTH and YEAR then you should use definitely the DATE type. I'll refer to my previous comment starting with "all things being equal" 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 12, 2011 at 1:19 am
YEAR and MONTH are not my main query parts, just gave an example how I tried to make the querie more expensive.
But you are right about that I will use quite a lot of queries that involve the date column.
So if nobody rules against it, I will stick to date 😉
Thx for your comments opc.three
May 12, 2011 at 7:08 am
No problem...thanks for bringing out the conversation. If you have a moment please post back if there are further findings or developments in the data type choice.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply