Category: T-SQL

Eric Blinn probably exists:

I’ve seen the EXISTS keyword in Microsoft SQL Server T-SQL code and don’t understand it well. What does it do? How do I use it? Are there best practices around SQL EXISTS?

This SQL tutorial will explain what the keyword EXISTS does and show several different use cases.

Read on to see how you can use EXISTS and its complement, NOT EXISTS, in a variety of use cases. One important part of why EXISTS can be useful compared to other ways of writing a particular query is that the performance profile of an EXISTS clause is a semi-join: we proceed until we find the first result matching our clause. If that happens to be in the first row, we can stop there as we’ve fulfilled the requirement. By contrast, an alternative using IN or something else like using SELECT COUNT(*) would likely need to read more pages of data than EXISTS.

Kevin Wilkie has fun with an unexpected character:

The query ran just fine so I happily completed my work for the day. I sent it to my QA team and had them check it out before pushing it to Production. They sent it back with a note saying “We expect a lot more “Insert Garbage Data Here” to show.

Being a good data developer – or someone who just wants to show QA up (take your pick) – decided to dig deep into what FieldName really shows. I found a lot of the following:

In Kevin’s case, he tried to use TRIM() and it didn’t fix anything. That’s because TRIM(), by default, only removes the space character (up to the first non-space character), not all whitespace and not the null character CHAR(0) that Kevin found.

An alternative version of TRIM() that would have worked in this case, plus adding in tabstops as well, would be:

SELECT
    TRIM(' '+char(0)+char(9) FROM FieldName) AS TrimmedFieldName
FROM dbo.SomeTable;
GO

And you could also extend that to include newlines, line feeds, vertical tabs, the line separator character, and whatever else you need.

Andy Brownsword is looking for a discount:

Performing a wildcard search by throwing a % into a LIKE expression is bread and butter. How do we handle this when we actually want to search for the wildcard though?

This was an issue I first saw early in my career before I was even building database solutions. The business had a back office solution where you could search through offers on the UI. Unfortunately it threw up some strange results.

Searching for ‘30%’ offers would return more than expected.

Read on for an example of the problem, as well as how you can resolve it.

Aaron Bertrand says, four days of the week, she thinks I’m the enemy:

I recently had to help support synchronization and distribution of workloads between multiple servers. Some of this work involves Task Scheduler and, be honest, who knew Task Scheduler was this complicated? On different servers, we wanted jobs to trigger on different days of the week. The way that Task Scheduler handles this programmatically is through a numeric property called WeeklyTrigger.DaysOfWeek, where the following coefficients are packed together into a single value:

Read on for the kind of pain that you can find within Task Scheduler. Any time I see bitwise storage like that in SQL Server, it hurts my head and not in a fun way. Aaron does help bring some sanity to the process, at least.

Chad Baldwin re-enacts the astronaut meme:

Well…not really, but a handful of functions in T-SQL are simply just syntactic sugar for plain ol’ CASE statements and I thought it would be fun to talk about them for a bit because I remember being completely surprised when I learned this. I’ve also run into a couple weird scenarios directly because of this.

For those who don’t know what the term “syntactic sugar” means…It’s just a nerdy way to say that the language feature you’re using is simply a shortcut for another typically longer and more complicated way of writing that same code and it’s not unique to SQL.

Here’s where I push up my no-longer-existent glasses and say, “Well, actually, it’s a CASE expression rather than a statement because it always returns a value–or a dreaded NULL non-value–of some explicit data type.” But Chad is absolutely right about several T-SQL functions and operators being adaptations of the CASE expression syntax under the covers and I’m just being annoyingly pedantic for the fun of it.