As I was reading that, I decided that it was time to create an Official Ranking. So here it is for the date and time data types:
DATETIME2(0). If you’re using UTC everywhere, this is a no-brainer best data type. You get to choose whether you want your
DATETIME2to be smaller than
DATETIMEor more precise than
DATE. A large percentage of the time, I just need the date and don’t care about the time. This three-byte beauty gives me that, but with zero risk that I miss a record because instead of
2020-01-01 00:00:00they enter something that rounds to
DATETIMEOFFSET. It’s a hefty data type, the largest in the bunch. But if you aren’t dealing with UTC everywhere, it does something no other date or time data type can: it keeps track of your time zone. Therefore, if you for some reason hate storing times in UTC, flip this with
DATETIME2and use it everywhere. Note that you also need to use the
AT TIME ZONEsyntax to specify what the time zone is or have your user correctly specify the time zone by passing in a pre-created
DATETIMEOFFSET. It’s like getting a puppy: you wanted it, now you deal with the extra work taking care of it.
TIME. This is where the utility curve really starts to fall off. I rarely use the
TIMEdata type. However, I do appreciate that it’s just 3 bytes and can be useful for process scheduling.
DATETIME. It’s really not that great of a data type. If you care about size, three of the four have it beat. If you care about precision, three of the four (assuming you bump up the precision on
DATETIME2) have it beat. If you care about not messing things up, three of the four have it beat (
TIMEif you use one but not both elements, and
DATETIMEOFFSETif you don’t want to mess up time zones).
SMALLDATETIME. If you’re really that desperate about two bytes, just use
DATETIME2. This was a lot more useful prior to SQL Server 2008, but we can say the same thing about Kurt Warner. That doesn’t make me want to plug him into my fantasy team today.