Category: Syntax

I have a new video:

In this video, I explain the history behind the APPLY operator in T-SQL. I also demonstrate one use case for it: executing table-valued functions.

As I mention at the end of the video, this is by far the least interesting use case for the APPLY operator. Subsequent videos in the series get a lot more exciting. But we have to start at the beginning, unless we’re going full Pulp Fiction.

Shane O’Neill tries something out:

Now, SQL Server doesn’t have the filter option, but we can do some pretty weird things, like a SELECT...WHERE statement with no FROM clause.

SELECT

    a,b,c,

    [filter?] = (SELECTb WHEREb > 11)

FROMz;

GO

It turns out that this works, but when you try to aggregate the results, it doesn’t work the way Shane expected. Using the APPLY operator does help here, so click through to see how that works.

Louis Davidson chooses the form of our destroyer:

I preface a lot of what I write with whether or not it is for a “practical” use. One of the Simple Talk authors used this function in a forthcoming article. and I realized I hadn’t heard of it before (or I forgot about it… which is not completely unlikely.) The practical use was to generate some data and have at least a little variety to the values.

The CHOOSE function has this syntax.

CHOOSE(item_to_choose, item1, item2 [,item3]…[itemN])

In fairness to Louis, CHOOSE() was one of those additions to SQL Server 2012 that we mostly forgot about. It’s similar in that vein to IIF() if you weren’t living in Excel at the time. In any event, Louis takes CHOOSE() through its paces, showing some useful scenarios and stretching the limits to see what happens.

Andy Brownsword takes a look at one quality of life improvement in SQL Server 2022:

Window functions allow us to perform a function across a set of rows in a result set, rather than how we might typically group them. In SQL Server 2022 we have a new clause available for our queries which can help tidy up how these are defined.

You can do some neat things with the WINDOW clause, including partial matches. For example, you could define a window r1 AS (PARTITION BY x.MyColumn) and then another window r2 AS (r1 ORDER BY x.SomeOtherColumn). I don’t think there are a huge number of scenarios in which this is helpful, especially because performance typically depends upon minimizing the unique number of window functions in your query, but every once in a while it’s a really neat thing.