January 8, 2012 at 8:27 pm
Hi,
what could be the performance gain on integer column than indexing on date column when in comes to composite primary keys.
let me explain,
i have a fact table with date, varchar and integer columns as primary keys.
i hope when you convert the date to integer like 12-12-2011 into 12122011 and using the same for composite primary key will increase the performance in queries.
same way, when i can do this for varchar by assigning integers in underlying dimension tables.
but i need to understand how SQL behave when i do this. and how the performance will be increased?
any links / white papers on this are highly appreciated.
Thanks,
regards,
ami
January 8, 2012 at 11:36 pm
This is something I've seen in the past for warehousing, but I've also heard it's a bad practice because you don't gain anything and it takes away date manipulations (such as dateadd). I don't really see a point. If you just want the date (and not hours) just use the DATA data type for 3 bytes instead of an INT at 4 and thus maintain a slightly more narrow index and keep the functionality of data functions.
Now, that's my understanding anyway but it's also possible that there is a valid reason to convert that I'm unaware of.
January 8, 2012 at 11:41 pm
Anamika (1/8/2012)
i hope when you convert the date to integer like 12-12-2011 into 12122011 and using the same for composite primary key will increase the performance in queries.
The DATE data type requires 3 bytes, see http://msdn.microsoft.com/en-us/library/bb630352.aspx
An INT requires 4 bytes, see http://msdn.microsoft.com/en-us/library/ms187745.aspx
Despite that, there are times where using an INTEGER can perform faster, though this generally applies only to single-key indexes.
http://sqlblog.com/blogs/paul_white/archive/2011/07/29/enforcing-uniqueness-for-performance.aspx
http://sqlblog.com/blogs/paul_white/archive/2011/08/04/avoiding-uniqueness-for-performance.aspx
http://sqlblog.com/blogs/paul_white/archive/2011/07/07/bitmap-magic.aspx
http://sqlblog.com/blogs/paul_white/archive/2011/08/09/sql-server-seeks-and-binary-search.aspx
January 9, 2012 at 3:32 am
sorry guys,
I'm using sql server 2005.
and there is only datetime which requires 8 bytes.... 🙂
January 9, 2012 at 3:36 am
Anamika (1/9/2012)
I'm using sql server 2005.and there is only datetime which requires 8 bytes.... 🙂
So the 2008 forums is probably not the best place for your question, is it.... 🙂
January 9, 2012 at 3:45 am
If you change to int, what's to stop invalid dates such as 13132011 getting into your data? Also, if you index on integers like that, you're not going to have your dates in chronological order. For example, 12122011 will come before 13112011. And, as bt pointed out, you lose the ability to do date arithmetic.
John
January 9, 2012 at 4:55 am
sry, paul...
it my mistake i posted in 2008
but i believe i can continue the discussion...
i want to know the advantages you get while doing indexing on a composite key when you have integer instead of datetime/ varchar
thanks,
regards,
ami
January 9, 2012 at 5:27 am
Anamika (1/9/2012)
i want to know the advantages you get while doing indexing on a composite key when you have integer instead of datetime/ varchar
The integer will be half the size of the datetime, saving four bytes per row. Assuming the composite primary key is clustered, this might (or might not!) mean each clustered index page could store an extra row or two. It would also save a minimum of 4 bytes per row in every non-clustered index, since the whole clustering key is included in the non-clustered index, at least at the leaf level. The impact on the non-clustered indexes will generally be greater than on the clustered index (because 4 bytes is normally a greater fraction of the per-row size of a non-clustered index).
The other responses to your question pointed out some potential drawbacks (some of which you could work around by ensuring correct constraints are in place, admittedly).
January 9, 2012 at 8:35 am
Moved to 2005 forum.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply