Category: Syntax

Kiana Bergsma shows us a tried-and-true method to confuse people:

Often times I have told developers, here is how you do it, and if you Google on it you will find some great samples.  Now it is time that I provide my own sample. I call this the FOR XML hack since it used the FOR XML command, without actually involving any XML at all.

I’m quite happy that STRING_AGG() is around as of SQL Server 2017, as it is a much clearer representation of how to solve this problem. If I had a dollar for every time somebody needed me to explain why I used FOR XML PATH() when I clearly wasn’t building XML, I’d have several dollars. Probably not a fistful of dollars, though.

Comments closed

Barney Lawrence shows off how to build XML from data in SQL Server:

In my experience creating XML is a less common task than reading it with one semi-exception (that being the old trick of using FOR XML PATH(”) to concatenate strings) but it can be an important one. The biggest piece of XML I’ve built was for a government mandated return, it was the only XML I built on that job but a sizeable proportion of the entire department’s work culminated in it. The switch from a set of Excel macros which took over eight hours to run to something that could rebuilt in minutes had a huge impact on our efficiency, particularly when there would be multiple rounds of validation failures against initial attempts at submission.

There are a few variants when it comes to converting queries into XML but in my opinion the only one you really need to know is the XML PATH approach, the others being either fiddly or not providing enough control.

Click through to learn more about the process. FOR XML PATH(): it’s not just for concatenating strings…

Comments closed

Barney Lawrence continues a series on XML processing in SQL Server:

This post looks at a problem that can come up in XML messages that have perhaps not been thought out as well as we’d like and where we can’t uniquely identify one instance of a repeating element. The example we’ve used so far avoids this problem so we’ll switch to a sample that illustrates this problem.

Read on for the crux of the problem, as well as solutions.

Comments closed

Greg Dodd unleashes a monster:

We now have all of the data, but the question is, how do we stop the Person’s name coming back if the HireDate is after 1 Jan 2000?

We can’t simply put it in a where clause, because then the Course Title won’t come back. We could put it as part of the join condition to Instructor, but that will only limit the hire date coming back. We could use that to build a case statement around the FirstName and LastName fields, but what other options are there?

What we want to do is limit the join to Person to only return if Instructor returns, and then we could put the join condition on Instructor.

I’ll admit that whenever I see this syntax, I tend to remove it and replace with a subquery or APPLY operation. I don’t like multi-joins at all for the reasons Greg mentions at the end: it’s uncommon and difficult for a T-SQL developer to parse mentally.

Comments closed

Steve Jones explains how the OFFSET-FETCH process works:

The other day I saw an article on the OFFSET clause in a SELECT. I had seen this come out and looked at it briefly in SQL Server 2012, but hadn’t done much with it.

NOTE: if you use this, be sure you read about potential performance problems and solutions.

Read on for more info. This is something I really wanted to work better than it does, as paging is really awkward in SQL Server. OFFSET-FETCH is great syntactically, but doesn’t do much for performance. The best solution I’ve seen is to take the results of a paging operation and store them in a dedicated paging table, allowing the user to query by page in that small table quickly. But at that point, OFFSET-FETCH isn’t really much less complicated than TOP(NumberOfRows) WHERE NumberOfRows >= NumberOfRows * (NumberOfPages – 1) ORDER BY NumberOfRows.

Comments closed

Mike Scalise has an alternative to using the GREATEST() and LEAST() functions in SQL Server:

As of 4/14/21, Microsoft has officially announced that the GREATEST and LEAST functions are in Azure SQL Database and SQL Managed Instance. Unofficially, it seems they had silently included these functions in at least (no pun intended) SQL Managed Instance several months prior. In any case, here we are today with official Microsoft documentation on GREATEST and LEAST. This is all great news. What’s also great is that, in their statement, Microsoft stated they would be including these two functions in the next version of SQL Server.

But what about all of us on SQL Server 2019 and prior? Fortunately, there’s a way to mimic these two functions in your queries using a correlated subquery in the SELECT clause.

Click through for examples. This is a bit different from getting the largest or smallest value in a window, which you can do with MIN(val) OVER () or MAX(val) OVER (). But I’m looking forward to seeing GREATEST() and LEAST() in the box product.

Comments closed

Edwin Sanchez takes us through a few issues you might run into with CASE expressions:

If you are trying to scan the subheadings to see what the issues are, I can’t blame you. Readers, including me, are impatient.

I trust that you already know the basics of SQL CASE, so, I won’t bore you with long introductions. Let’s dig into a deeper understanding of what’s happening under the hood.

Click through for the list.

Comments closed

Cyndi Johnson has a warning for us:

The bugs alone are reasons enough to avoid using it, and also to refactor any existing MERGE statements you have in your code base. Those bugs include ones that have performance implications as well as ones that could lead to incorrect results. Long story short, Microsoft refuses to fix several of them, and some of the other major issues are “by design”!

If you still feel the desire to use them, please keep reading, and I hope by the end of this blog you will be convinced that

1. Less statements does not always lead to better code or better performance.
2. MERGE is like a VAMPIRE and once it is in your codebase, it’s just a matter of time before it drains your servers of their lifeblood.

The worst part about the second point is all the sparkling.

The worst part about the first point is that I was really excited with MERGE came out in 2008, as I wanted UPSERT for SQL Server. It, uh, didn’t work out so well.

Comments closed

David Alcock tries an experiment:

The question whether to use COUNT(*) or COUNT(1) has been doing the rounds on the internet again, the question this time is whether or not COUNT(*) forces the optimiser to scan all rows or not.

I’m going to query Sales.Orders table of the WideWorldImporters sample database and use a where clause that should take advantage of a non-clustered index on CustomerID. 

David looks at COUNT(*) versus COUNT(1). The story’s slightly different with COUNT(ColumnName) when there are NULL values afoot.

Comments closed