SQL Server date vs smalldatetime

Question

I have a bunch of tables which consolidate data on different levels: quarterly, hourly, daily and monthly. Apart from that there's a base table which contains the "raw" data as well, this one contains some extra columns. The consolidated tables all have the same columns.

The base, quarterly and hourly tables make use of a column of type smalldatetime to timestamp the data. This column will also be available in the hourly and daily tables, but of course I won't need the time aspect here.

For the sake of simplicity I want to use the smalldatetime data type here also, but maybe it's better for performance to use a date data type for the column?

Is there a really big difference between the types when it comes down to performance?

Answer 1

It's usually a good idea to use the smallest data type that you need. You shouldn't use varchar(max) or even varchar(10) to store a 2 character state abbreviation.

In the same sense, if you just need the Date (e.g. 4/11/2001) then use the Date type and not the DateTime type.

While it might not be a huge gain in performance (DateTime is 5 bytes larger than Date.)It can start adding up if you have multiple fields and/or multiple rows.

Answer 2

Various data types have various size which affects size of data/indexes, which is more important than query performance (I/O cost).

On the other hand converting between data types could be costly. Moreover implicit conversion between various data types can be wrong/unexpected.

I would keep one type of date (4 bytes smalldatetime in this case) if you JOIN between tables on date columns or if not keep the smallest possible date type -- smalldatetime for storing hours and quarters and date for pure dates.