May 18, 2007 at 8:22 am
Ok, I've recently run into a conendrum (or however it's spelled). I have a table for setting up Schedules. I've always used DateTime or SmallDateTime depending on what the needs were, however, looking at the MSDB Database, the JobSchedules do not use Dates but instead integers (Int). I can see the advantage to this but I'm wondering if one way is more efficient than the other?
If I'm using SmallDateTime for most of my dates, an Int & SmallDateTime both take up 4 bytes. So I don't see a space advantage. The ints are still easy to read and parse (they are stored as "yyyymmdd" or for a time "hhmmssms").
I can think of one thing that would probably be bad for performance. I would have to constantly convert any Dates to Ints in order to lookup values based on a date range.
Any thoughts on this?
May 20, 2007 at 1:36 pm
Hi Tymberwyld,
An int is 4 bytes and a datetime is 8 bytes. The smaller your datatype, the more you can get on a page and the better use of indexes and RAM. But if you can get around with smalldatetime, this would also be only 4 bytes.
You probably could convert everything to numbers of days after e.g. 1/1/1900. In order to have an efficient search then, you would convert your search argument before selecting, so that it can make use of the index. But of course at least your display result would have to be converted with dateadd.
So in fact I don't see any real benefit using int. Using smalldatetime is in fact an additional constraint (only valid dates) and simpler.
Has anybody else seen other arguments?
Jan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply