My database is extremely large and growing at a rate of ~20m rows/day. I have timestamp data that is important but most of the reporting is based on date ranges and week over week or month over month comparisons. Time is displayed in the result sets occasionally but never used as a criteria. Given this, I'm thinking that I'd save considerable storage space with an index on date alone vs a combined datetime field. I'm not sure if I would also see performance gains in my selects or if there are any disadvantages to splitting into 2 fields.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
For reporting purposes splitting the field out into date and time has some benefits. Some possible benefits you could realise include:
For your application you might get a win from a date reference table (make a clustered PK on date for efficient lookup), which will probably be more efficient than de-normalising the week and month onto your large table.
The index performance should not change.
In a sorted array or tree structure (i.e. the index), asking for "all entries where is equal to " requires a lookup of the first and last entries in the range, same thing as when asking for "all entries where is greater or equal midnight on and smaller than midnight on ".
What might be interesting is an index on
From a storage space POV, I doubt it matters compared to the size of the data tables.