index on date column vs index on integer

Question

index on date column vs index on integer

Anamika

SSCertifiable

Points: 6202
More actions
January 8, 2012 at 8:27 pm

#251076

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

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.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 2

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/19/join-performance-implicit-conversions-and-residuals.aspx

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Anamika SSCertifiable Points: 6202 More actions · Answer 3

sorry guys,

I'm using sql server 2005.

and there is only datetime which requires 8 bytes.... 🙂

Paul White SSC Guru Points: 150467 More actions · Answer 4

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.... 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

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

Anamika SSCertifiable Points: 6202 More actions · Answer 6

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

Paul White SSC Guru Points: 150467 More actions · Answer 7

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).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 8

Steve Jones - SSC Editor

SSC Guru

Points: 736252

January 9, 2012 at 8:35 am

#1430517

Moved to 2005 forum.