#TSQL2sday 136 Wrap-Up: Your Favorite (and Least Favorite) Data Types

For this month’s T-SQL Tuesday, I asked you to blog about your most-loved and least-loved data types.

Crazy, right? How could people possibly love or hate data types? Well, if you’ve been working with them for a while, I figured you’d have built up an array of tips or pain points, and y’all delivered with 29 interesting blog posts.

Secretly, when I picked the topic, I hoped we’d end up with a couple of data types where people disagreed so much that it was both a favorite AND a least favorite. Indeed, y’all delivered on that too!

Here’s what you wrote, grouped by data type, each in lower case as Aaron Bertrand intended:

bigint

• Bigint is built for the future by Arun Sirpal – “Of course you should use INT where possible (especially for clustered index keys – but that’s a different topic to this one) but always think of the future.”

bit

• Bit versus other data types by Kevin Chant – “So, as you can see in this slightly biased post the bit data type wins here.” (I laughed pretty hard throughout this one because it was not what I was expecting.)

date

• Beware the Width of the Covering Range by Rob Farley – “…although we often refer casually to the idea that a datetime column cast to a date column is sargable, it’s actually not quite, and you’re better off handling your ranges yourself.”
• Date and Time as Separate Columns by Koen Verbeeck – “Another advantage of having dates stored in two columns – date and time – is they compress much better in the Vertipaq engine, because there are less unique values in the columns.”
• Comparing the precision & storage of date options by Jess Pomfret – “Let’s talk about accuracy and precision, and how much of it you actually need.  The rest of this post is going to focus on datetime datatypes, but these thoughts could easily apply elsewhere (for example tinyint vs int vs bigint).”
• Converting dates to binary by Deborah Melkin – “The fun really starts when you realize that what SQL Server stores internally isn’t what we see.”
• My Least Favorite Datatype: Date by Camila Henrique – “It works great, the real issue is that we as humanity never agreed on a single date format. Sigh.”
• If you could all start using date, that’d be great by Alex Stuart – “I wonder how many GB/TB (/PB??) on the planet is wasted by datetimes with 00:00:00.000 time components right now.”

datetimeoffset

• Datetimeoffset Uniqueness by Rob Farley – “the time zone thing isn’t about whether they are actually identical or not. It’s about whether they are considered the same based on the things that the SQL engine uses to consider them equal.”
• My two least favorite data types by Aaron Bertrand – “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.”

float

• I’ve Got 99.0991 Problems But Floating Point Precision ‘Aint One by Barney Lawrence – “Why worry about all that precision and scale nonsense when you can just use a data type that will hold literally* any number!” (Everyone else can stop using the 99 Problems meme now, because this wins.)
• Float Does Not Suck by Hugo Kornelis – “To explain why float is not a bad data type at all, I’ll need to take you back to your high school days. To science class.”

int

• 4 Things I Love About Integers by Brent Ozar – “Integers are clean, elegant datatypes that do exactly what they’re supposed to. They’re the Golden Retrievers of the database world: they have a few shortcomings, but so, so few.”

money

• My two least favorite data types by Aaron Bertrand – “…it doesn’t really offer anything over other numeric types, and it takes away flexibility.”

smallint

• Use the smallest integer that you can by Glenn Berry – “In reality, int is often used when you could actually get away with using a smallint or even a tinyint data type. I think many people either don’t know or simply forget that there are other choices beside int.”
• Right-sizing ints by Kenneth Fisher – “Now try that with 10 or 20 lookup tables. The savings can add up pretty dramatically.”

spatial

• Mapping visualizations and geo-spatial coding by Tom Huguelet – “the fact that we got so much for nothing, and the fact that these data types behave like objects with methods, and the fact that mapping is such an important type of visualization for analytics, make SQL Server Spatial Geography Types my favorite.”
• Spatial is My Favourite, Yet Most Pointless Datatype by Greg Dodd – “I’d rather store Latitude and Longitude as decimal fields and have an application layer do the hard lifting, where I can send things parallel, where I can do some maths to work out the locations I want and index them if I need to.”

sql_variant

• Most and Least Favorite Data Type by Eitan Blumin – “And then there’s this a**hole.” (I laughed pretty hard at that.)
• Not the Best Idea by Mikey Bronowski – “Although it looks very tempting to use sql_variant more often it may not be the best idea…”

text

• Text served its purpose, but time to move on by Mike Scalise – “Sure, it sounds like it would fit the bill for…well…anywhere you need to store large amounts of text….but…”

timestamp

• The Datatype Blog by Steve Jones – “It has been deprecated, but the timestamp type is still around. It’s not in the list, but it is mentioned as a synonym for rowversion. This is a unique binary number in each database, which is often used to detect changes in a row.”

uniqueidentifier

• Bad as a Clustering Key By Chad Callahan – “You wouldn’t use VARCHAR(MAX) for a middle initial of a name or use BIGINT for an area code, right?”

varbinary

• Like the Useful Wera Koloss Ratchet by John Q. Martin – “From storing blob data in our databases either directly (please don’t do this), via filestream, or with FileTable, varbinary is there helping us meet our goals.”
• Using varbinary for encryption by Justin Bird – “Some time ago I fell down a rabbit hole exploring the inner workings of the ENCRYPTBYKEY function in SQL as part of some work exploring options for securing sensitive information.”
• The Bludgeoning It Deserves by Travis Page – “Designs that seem innocuous at first conception can grow up to be a monster and be hard to undo.”

varchar

• The complications of varchar(max) by Deepthi Goguri – “Limiting the length of the strings is really important for designing any database. Using VARCHAR(n) is recommended over VARCHAR(MAX) when ever possible.”

xml

• Most and Least Favorite Data Type by Eitan Blumin – “Speaking of which, SQL Server Radio Episode 122 focuses on the XML data type, its history, purpose, and real strengths. Check it out, if you haven’t yet!”

They’re all good datatypes, Brent

• Favorite: any datatype that’s used correctly by Lina Kovacheva – “I cannot choose only one magical universal data type as I believe each of them can be magical when used in the right circumstances.”
• Least favorite: any datatype that’s used INcorrectly by Reitse Eskens – “…every datatype will have its use at some point. But the datatype I despise is the one that’s not used correctly.”

My thoughts overall

In my training classes, I often compare our work to carpentry.

When you just get started with carpentry, you only have a tool or two. The longer you work in the field, the more you amass a collection of tools, and the better you understand how to use those tools.

This is a discussion we could have had 20 years ago, or 10 years ago. The answers change, too: just because a tool had a valid purpose once doesn’t mean it still does, because better options arise. Sometimes, new options arise, but…they’re not actually better. (I’m looking at you, JSON.)

We’ll still be having this discussion 20 years into the future. I’m celebrating the 20th anniversary of BrentOzar.com, and I can’t think of a better T-SQL Tuesday topic: I could have published this in 2001, 2011, 2021, 2031, or 2041. We’re still facing the same carpentry projects, but the tools we use and the ways we use them gradually change over time.

If you had fun writing (or reading) about this, and you’ve got an idea for a future T-SQL Tuesday episode that you’d like to host, tweet @way0utwest or leave a comment over here.