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

Development
9 Comments

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

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

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

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

smallint

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

text

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

varbinary

varchar

xml

They’re all good datatypes, Brent

Photo by Barn Images on Unsplash

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.

Previous Post
How I Worked Toward GDPR Compliance: The Really Long Story
Next Post
What Does a Database Administrator Actually Do?

9 Comments. Leave new

  • Dexter Jones
    March 15, 2021 4:15 pm

    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.

    Reply
    • 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.

      Reply
      • Edward Miller
        March 16, 2021 5:18 pm

        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.

        Reply
  • Steven F Lange
    March 15, 2021 4:44 pm

    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.

    Reply
    • Kevin S Lewis
      June 15, 2021 9:05 pm

      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…

      Reply
  • Jeff Mergler
    March 15, 2021 5:59 pm

    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.

    Reply
  • Camila Henrique
    March 15, 2021 9:57 pm

    Thanks for creating a post with the summary of all our posts Brent, appreciate it!

    Reply
  • 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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.