It’s almost Thanksgiving time again! Let’s see, what am I thankful for? T-SQL Tuesday! Someone else get’s to pick a blog topic for me! In this case it’s the always fun Mickey Stuewe(b/t) and her topic is Data Modeling Gone Wrong. So let’s see .. a Data Model is a small toy data right? Ok, so maybe it’s not. Definitions are not my strong suit. So what is Data Modeling? The simplest definition I could find for my purposes is this:

Data models define how the logical structure of a database is modeled.

So basically, if I understand it correctly, data modeling is in part the database design. And as it happens I’ve had a question about database design (gone wrong) for a while.

Why is everyone still using the DateTime datatype exclusively?

Back in SQL 2008 we gained a whole new range of date/time datatypes. Isn’t it about time we started to use them? Don’t get me wrong, datetime is still useful, if you actually need accuracy to three thousandths of a second and you aren’t going too far back into the past (earlier than 1753). Oh, and you only care about one time zone. Frequently though we don’t really care about the time (date of birth, credit card activation date, etc) in which case we can use the Date datatype. This has some major advantages. DateColumn = ‘1/1/2012’ will actually give you accurate answers for example. Not to mention the 5 byte size savings (8 byte datetime – 3 byte date). But what if the time is really important (shift or time of day calculations)? If so use the Time data type. This way you can put an index on the time specifically. Need to work with time zones? DateTimeOffset. Need the date with a lower accuracy time (seconds)? SmallDateTime, and that one’s been around since 2005. And last but not least, what if you need really high precision time? DateTime2 can take you down to a ten millionth of a second.

So let me ask again. Why is that column a datetime?

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tuesday Tagged: database design, microsoft sql server, T-SQL Tuesday