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.

T-SQL Tuesday #123: Life Hacks to Make Your Day Easier

T-SQL Tuesday

This month’s T-SQL Tuesday is hosted by Jess Pomfret (b|t) and the topic is “Life hacks to make your day easier.”

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.

This 123rd T-SQL Tuesday is particularly special to me because it’s the first one I’ve participated in.

Life Hack

A life hack I learned about relatively recently is the Apple “Speak Screen” option on iPhones. This feature, which has apparently been around since the iOS 6 days, allows iPhones to speak the contents of web pages to you. How did I not know this existed?

When I first used this feature, I was blown away. My backlog of websites, blogs, and articles to read had been growing, and it seemed I would never make my way through all of the material. Now, when I’m in the shower, in the car, heck–even when I’m cleaning the house I can bring up a page I’ve been wanting to read and simply have Siri dictate it to me.

Here’s how to turn it on:

Settings –>Accessibility–>Spoken Content–>Toggle “Speak Screen” On

Or, just go to a web page ask Siri to “Speak Screen.” If it’s not enabled, it’ll give you the option to go to the aforementioned settings area and enable it; if it is enabled, it’ll speak the page to you.

There are several other related options you can set in this area, like whether text gets highlighted as it’s spoken, the rate at which text is spoken, etc.

As we all know, time is one of the few resources we simply cannot make more of, so our next best option is to be as efficient as we can with the time that we have available to us. “Speak Screen” has allowed me to significantly improve my efficiency and overall productivity. I highly recommend exploring this to see if it can help you too. If you want to do more reading on it, head over to Apple.

How to Increase SQL Server Query Performance When You Have No Control Over the Query — A Unique Approach

Last week, at my local PASS chapter (Rochester), I gave a presentation titled “Magical Query Tuning.” In the presentation, I described a query-tuning technique that can be used when you think you’re out of options. I first learned of this method from Kendra Little at LittleKendra.com and I think it’s an incredibly interesting and not widely-known method, which is why I chose to share it with everyone.

Continue reading “How to Increase SQL Server Query Performance When You Have No Control Over the Query — A Unique Approach”

Function Friday

I’m a huge proponent of SQL Server. I think it’s a great database engine and I find that I’m always looking for ways to expand my knowledge about the product and help others expand theirs. In that vein, starting in October of this year I’ve begun tweeting one SQL Server function and a link to its page on microsoft.com, each Friday. I call it Function Friday.

Continue reading “Function Friday”