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.
9 Comments. Leave new
Good post. In the sprit of using the right tool for the job, I would point out the picture is of woodworking tools, not carpentry tools. Having attempted both, I can faithfully report they are two very different things.
You wouldn’t (or shouldn’t anyway) hire a woodworker to build a house and you probably shouldn’t hire a carpenter to build a roll-top desk.
Look at you! Neato. Out of curiosity, you said you attempted both – do you still do ’em as hobbies? I ask because I found woodworking really therapeutic even though I sucked (terribly) at it. When you spend all day in the virtual realm, there’s something neat about building something with your hands.
You want therapeutic? Get a pilots license. I can assure you flying around in small plane is truly therapy for the mind, body, and soul. And it has the added benefit of making what can be done in a 3 day weekend so much bigger and better. Check out my email and you will see flying is my first love.
Hahaha, noooo, I’ve flown on small planes, and I’m a big fan of reading FAA accident reports. I’ll leave those to you, heh.
I love this type of article. Everyone has an opinion and they summary is good. Each of the datatypes have a used. Does this imply there is no bad SQL code, just bad SQL programmers? LOL
The worst example of SQL code I had ever seen was someone using SQL Bit masks to implement status. Why would someone use Bit Masking to implement 20+ statuses I do not know. But I have seen it.
Perhaps someone can show me a reasonable use for Bit Masking in SQL.
First one that springs to mind is the schedules of SQL Agent which uses them. I have used bit masking before, can be elegant, can be a royal pain…
What a great aggregate of opinions and wisdom. I have a feeling I’ll be referring back to this post in years to come. Nice post Brent. And I agree, its really good to have a hands on hobby (like woodworking) when you spend your days on the virtual realm. I’ve relied on adrenaline sports like mountain biking and rock climbing as therapeutics but lately learning the ukulele has been a fun (and safe) brain exercise for me. But I can see woodworking (and probabaly fly fishing) as future pursuits.
Thanks for creating a post with the summary of all our posts Brent, appreciate it!
I love the comments about tinyint, smallest, int, and bigint. Spoken like true DB nerds. Programmers hate them and go with the storage is cheap argument all the time. Just use int unless you need the humongousness (yeah, I made that up) of a bigint.