Date Efficient Query ?

I have a table that has a date and time column. I need to do a search on the table by days and will eventually need to do it by hours as well.

I wanted to ask the question that will the performance get better if I create two additional columns one stateing the "Day of Week" and the other stating " Hour of Week". These will have numerical values prepopulated i.e. for Saturday 7, sunday 1, Monday 2 etc etc etc. And for the time , I will have 1 for 1pm-159pm 2 for 2-2:59pm pm 3 for 3-3:59pm etc etc etc

The total number of rows in the table could total half a million, with filtered to by weekf of day may be reduce to being 80,000 or so.

Is the above criteria to add two numeric columns to the table and putting indexes on those two numeric fields is a good solution? and efficinet or just using the datepart functionality with the actual date column and using the week of day and time parameters as the case may be.

Thanks fro your help.

Thanks

Does your table have a Date column and a Time Column? From your description, this is not clear. More likely it has a single DateTime column. There is no advantage in splitting out parts of a DateTime value into separate components since the DateTime SQLType has all of them in one.

Consider indexing the column for faster searching.

|||

If you're going to want to query something like

select * from sometable where DayOfWeek=wednesday

then I think you should create a seperate column for day of week

Otherwise, you'll have to include a function call in your WHERE statements, eg,

select * from sometable DATEPART(weekday,datefield)=4

but this will NOT optimize well, even if you have an index on datefield because such an index won't be able to get to the "day part" by itself

Same goes for time, but SQL Server does not have a time datatype, just datetime, so you would need to store it as eg hour of day

Adding 2 numeric fields is trivial (you can even make them of type tinyint)

If you will always be querying using both criteria then put them both into one index, or you can create 2 seperate indexes

|||

The reason a query on a datepart (like day of week) won't optimize is because it's "in the middle" of the field Let me draw an analogy, suppose you have an indexed field thats char(7) with this data:

AAABCCC
DDDBKKK
ZZZCCCC
DDDDDDD
ZZZBXXX

Now, a search where the 4th character is "B" can't use the index on this field because it's in the middle.

Datetime values are actually stored as big numbers in a BINARY(8) field (http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx) -- it represents time since a basedate of 19000101, not a character string. But the idea is the same, if you want to pick all the Wednesdays in a group of datetime fields, no index will help, you need to store and index day of week seperately.