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.
Sql-server – What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field
datatypesoptimizationperformancesql-server-2008
Related Question
- Sql-server – Storing date as integer (numeric), what are the advantages
- Sql-server – Average, between specific date/time, of the difference between current field and previous field
- SQL Server Performance – How to Split a Large Table to Improve Performance
- SQL Server – Calculate Time Difference Between Datetime and Current Date & Time
- PostgreSQL – Optimal Data Type for an MD5 Field
- Mysql – What are the cons of implementing faceted search with WHEREs and JOINs
Best Answer
For reporting purposes splitting the field out into date and time has some benefits. Some possible benefits you could realise include:
You can make a date reference table (much the same as a date dimension in a data warehouse) with your breakdown into weeks, months etc. This can be keyed on the date and used with a join.
Analysis by time of day is easier with a separate time field. You can also round the time to an appropriate grain and make a reference table.
The index would be slightly narrower although each leaf row still has a (IIRC) 6 byte page reference, so it's not such a great saving overall.
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.