Vladimir Oselsky hits on something I dislike:

My first gut reaction was that this code is broken and would not run. To my amazement code ran just fine. Now came the hard part, which was to figure out what the code was doing because I have never seen this syntax before. Since I did not understand what I was looking at I could not BING “weird join syntax” to get an answer. As a developer, I learned long time ago to break down code into smallest possible chunks to get the answer.

After I have figured out the relationship between tables, I was able to understand what query was doing. To be able to read query better it can be rewritten in the following way.

Do read Vlad’s post.  I’ve seen terrible misuse of this plus right outer joins, and I don’t know if I’ve ever seen a case where using this syntax made the code easier to understand.

Denis Gobo ran into a problem with a linked server query he ran:

This past week I needed to run some queries on production to verify there were indexes added on a table. There were several scripts that needed to be run and the last one was the addition of the indexes.  The query given to me was something like the following

SELECT *
FROM LinkedServerName.DatabaseName.sys.indexes
WHERE object_id =(OBJECT_ID('TableName'))

So I ran the query..nothing. Aha maybe they are still running the scripts before that, setting up replication, snapshotting the table etc etc. I will check again in a bit I thought.

Click through for the full reason and how to fix your code in this situation.

Kenneth Fisher looks at a few methods for dynamic searches in T-SQL:

Multiple Queries

Pro: We get a separate query plan for each combination of parameters so performance is great. (Well, as good as can be anyway.)
Con: Maintance stinks. We need 16 different queries when we have 4 parameters and the numbers increase dramatically as we add additional parameters. So any change we make to the base query will have to be changed 16 times, and/or adding a new parameter means careful logic and adding a bunch of new queries.

I’d consider this a gateway for Erland Sommarskog’s article on the topic.

Shane O’Neill looks at different ways to match patterns with the LIKE operator:

So let’s test out this bad boy using the WideWorldImporters database, see if we can find everyone with the first name of Leyla.

Simple right? And because [Sales].[Customers] uses the full name, we have to use LIKE.

Now a developer comes along and says “Wait a second, my sister is Leila”. So we try to cheat and add a wildcard in there.

Leonardo!? Well I suppose he does count in this situation, but there’s 2 characters between the ‘e’ and the ‘a’ and I only wanted one.

Click through for a couple pattern matching tricks and look for ways to avoid Lejla in your life.

Dave Mason has to unpivot a particularly wide table:

Today’s less-than-ugent challenge was to un-pivot the output of RESTORE HEADERONLY. I thought for certain someone else, somewhere, at at some time must have wanted to do the same thing. So I asked the Twitterverse, but no one responded. I guess I’ll have to make do myself without the easy button. No worries, though. We can do this!

Metadata tables are good friends at times like these.

Kathy Kellenberger solves an issue with tracking contiguous calls within a call center:

To come up with a solution, I first thought about how to solve it with a traditional cursor. I won’t provide a cursor solution here, but it would involve looping through the rows in order and finding the first and last employee rows. When the last EmpID doesn’t match the next EmpID, then the previous row is the last call for the previous EmpID and the current row is the first call for the next EmpID.

NOTE: The solution to this problem assumes that there are at least two calls per shift.

In 2012, four T-SQL window functions became available that let you look at expressions from different rows: LAG, LEAD, FIRST_VALUE, and LAST_VALUE. For this puzzle, LAG and LEAD seemed to be the answer.  In this case, I used the optional default parameter to replace any NULLs with -1.

This is a particular solution for the Gaps and Islands problem; here’s Itzik Ben-Gan on the topic.