Category: Syntax

Paul Randal takes us through one of my favorite operators:

Introduced by Microsoft in SQL Server 2005, SQL CROSS APPLY allows values to be passed from a table or view into a user-defined function or subquery. This tutorial will cover the incredibly useful and flexible APPLY operator, such as how the CROSS APPLY and OUTER APPLY operators work, how they’re like the INNER and LEFT OUTER JOIN, and give you some examples of both. All the examples use the AdventureWorks example database.

Later in the article, I’ll also discuss a highly pervasive SQL Server performance problem—one I still encounter with customers on a weekly basis. This problem is related to using the APPLY operator against a specific type of user-defined function. It’s so problematic and can overwhelm tempdb, bringing your SQL Server instance to a crawl!

If you’re not too familiar with APPLY in its two forms, read the whole thing.

Comments closed

Michael J. Swart has a function:

Just like PIVOT syntax, UNPIVOT syntax is hard to remember.
When I can, I prefer to pivot and unpivot in the application, but here’s a function I use sometimes when I want don’t want to scroll horizontally in SSMS.

Click through for the function. This is an area where I wish there was built-in * logic for PIVOT and UNPIVOT. Or at least a “Select all columns but the following” as that would make things easier.

Comments closed

Erik Darling is not amused:

I went looking for other docs examples from vendors who have had the syntax around for 10+ years, and there wasn’t anything all that much more interesting.

Mostly case expressions and whatnot.

Big deal.

Check out the comments for more.

Comments closed

Erik Darling notes some improvements:

With the release of CTP 2.1, the problems that we saw the first time around are all gone. But there are still a couple small caveats that you should be aware of.

There’s also been a change in the way you call the function, too. You not longer need the START and STOP identifiers in the function.

There are still some limitations but it does look like the function is considerably better in CTP 2.1.

Comments closed

Itzik Ben-Gan looks at ways recent T-SQL improvements:

One of the complexities that is of a specific importance to this section is comparisons that potentially involve NULL comparands, such as ones that you use in filter and join predicates. Most operators that you use in such comparisons, including the equals (=) and different than (<>) operators, use three-valued logic. This means that there are three possible truth values as a result of a predicate that uses such operators: true, false and unknown. When both comparands are non-NULL, such operators return true or false as you would intuitively expect. When any of the comparands is NULL, including when both are NULL, such comparisons return the unknown truth value. Both filter and join predicates consider unknown as a non-match, and sometimes that’s not the behavior that you’re after.

The distinct predicate (IS [NOT] DISTINCT FROM) should simplify a lot of code in the wild.

Comments closed

Hasan Savran peeks through the windows:

WINDOW Operations in SQL Server can be hard to understand.  I believe one of the main reasons for that is the long and repeated code it needs. SELECT…WINDOW Clause will help us to remove repeated code and hopefully it will make the WINDOW Operations more user-friendly.

I am really happy about this syntax change, as often times, we have to re-use the same window frame (PARTITION BY and ORDER BY) or a base frame with a minor change (add one more column to the end). This removes the kind of repetition which makes queries harder to read and introduces subtle bugs.

Comments closed

Daniel Hutmacher is making a list and checking it twice:

If you’ve worked with reporting, you’ve probably come across the following problem. You have a list of values, say “A, B, C, D, K, L, M, N, R, S, T, U, Z” that you want to display in a more user-friendly, condensed manner, “A-D, K-N, R-U, Z”.

Today, we’re going to look at how you can accomplish this in T-SQL, and what this has to do with window functions and gaps and islands.

Read on for a really good demonstration of how powerful window functions and STRING_AGG() can be.

Comments closed