This month, Brent Ozar is hosting T-SQL Tuesday #136. He asks us to:
Blog About Your Favorite Data Type (Or Least Favorite)
I am not often one to do the bare minimum, unless it comes to looking for things around the house. After about 22 seconds I throw my hands in the air and exclaim, "I can't find it!" As my wife so kindly added: It usually turns out to be in a spot I already looked.
But when it revolves around SQL Server and opinions, I'm all over it. So I'm not going to talk about a data type today; I'm going to talk about two of them. One of them Brent already mentioned in his invite:
money
Ooh, I really dislike this data type. But it's not because of rounding errors when a calculation dips its toes in the "5 or more decimal places" pond. A lot of people have demonstrated this, and it's easy to fabricate an example, but it's harder to see how this could possibly apply in the real world.
DECLARE @m money = 12.75, @d decimal(11,4) = 12.75; SELECT [money] = (1000/@m)*12000, [decimal] = (1000/@d)*12000;
money decimal ----------- ------------------- 941175.6000 941176.470588228000
No, I dislike money more because it doesn't really offer anything over other numeric types, and it takes away flexibility. You aren't allowed to specify more or fewer decimal places; you're cornered into the equivalent of decimal(19,4) (or decimal(10,4) if we're talking about smallmoney). Which is useful if you sell both superyachts and penny stocks. In most businesses, you don't need that much scale or precision, and decimal can be a much more appropriate type. I say the same thing when people say they want to use float – just use decimal, unless you really want the properties of float.
datetimeoffset
"See, we support time zones now!" Except we don't. All of our servers, even the ones in Europe, are set to Boston time. So using this data type makes us feel like we've embraced time zones, but we haven't. We've embraced one time zone. And if you use this type, unless you're also applying AT TIME ZONE, you're not getting something that's DST-aware.
Personally, I would much rather use datetime2 with whatever precision makes sense, and store the data in UTC. For things like logs or events this makes sense from a storage point of view – no gaps or repeated events due to DST. Presentation is a separate issue and should be considered as such. Sure, people like Jon Skeet don't believe that that solution is perfect either, but no solution is. I keep going back to the fact that it's easier to convert from UTC to some other time zone than it is to convert from some time zone that may observe DST to some other time zone that may observe DST.