T-SQL Tuesday #136: Blog About Your Favorite Data Type (Or Least Favorite)

T-SQL Tuesday

This month’s T-SQL Tuesday is hosted by Brent Ozar (b|t) and the topic is “Blog About Your Favorite Data Type (Or Least Favorite)”

A Little Background

For those of you who don’t know, T-SQL Tuesday is the brainchild of SQL Server Guru Adam Machancic (t). It is a monthly blog party where a person is chosen to be the blog “host” for the month, chooses a topic (can be technical or non-technical), and charges IT professionals to write about the topic on their respective blogs, then link back to the host blog.

Once the month has concluded, the host makes a roundup post comprised of links to each participant’s post. It’s a great way for people to learn a lot about a topic from incredibly knowledgeable people, and it’s wonderful for the participants as they get to contribute to the community and the greater good.

I’m sorry but you’re not my type.

I was planning to kick this T-SQL Tuesday off by writing about my favorite SQL Server data type–the VARCHAR. Yes, it has many good qualities, not the least of which is its fairly high data type precedence, allowing it to be implicitly converted to several lower-precedence data types. A real forgiving type…

But instead I’ve decided to focus on a data type that I really, really dislike–TEXT. Sure, it sounds like it would fit the bill for…well…anywhere you need to store large amounts of text….but did you know that this fun data type has been deprecated since SQL Server 2005? That’s 16 years ago. 7 SQL Server versions ago. Yet it still finds its way into modern versions of SQL Server. I mean, sure, Microsoft has announced that it will be removed in a future version (in favor of VARCHAR(MAX)), but until then, here are some fun facts about the TEXT data type:

  1. You can’t use it with the = operator. You read that correctly. The most common operator in the WHERE clause. Can’t use it. You have to use LIKE or convert the field beforehand.
  2. You can’t use it in a UNION or UNION ALL because it’s not comparable.
  3. INTERSECT? Nope.
  4. EXCEPT? No.
  5. Well surely you can sort by it, right? Haha no.

See a pattern here?

It’s not me. It’s you.

It’s easy to see the myriad limitations of the TEXT data type–which is probably why Microsoft has encouraged developers to use VARCHAR(MAX) instead. It has served its purpose for earlier versions of SQL Server, but it’s time to move on.

And honestly, I don’t like focusing on the negative as there’s enough of that going around in this world, but I can make an exception for a data type that has no place in our beloved SQL Server databases.

Leave a Reply

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