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.

  • 1
    I see only pros and no cons - with your requirements - but I'm sure you'll get more thorough answers. Jun 27, 2012 at 12:38
  • 1
    Agreeing with @ypercube I would push forward and consider adding week and month fields as well. Jun 27, 2012 at 12:47
  • One disadvantage of splitting is if anyone wants to map the same table in an ORM mapper, and if that mapper doesn't support one or both of those datatypes.
    – JM Hicks
    Dec 28, 2012 at 0:01
  • As far as the mention about narrower index width, you wouldn't consider that alone, but also whether dropping the unneeded time component from the index rows would perhaps greatly reduce the total number of unique index rows that need to be stored in the index. E.g., if the index is defined primarily by the timestamp data, and the 20 mil. rows are well enough spread over the time component, then eliminating the time component could reduce the number of index rows per day by a ratio of 1000's or 10's of 1000's to 1.
    – JM Hicks
    Dec 28, 2012 at 0:18

2 Answers 2


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.


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 MONTH(datetimecol>) and other commonly used expressions in your queries, which will permit using an index scan to find all rows with a matching month, if you want to trade the extra space for this index for elevated performance.

From a storage space POV, I doubt it matters compared to the size of the data tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.