When a new version of SQL Server is released, you can typically see the benefits of any newly introduced T-SQL features fairly quickly, as well as how you can use them. But some features take longer to discover, and it can be quite a while before people understand their true value and what you can do with them. Such is the APPLY operator that was added in SQL Server 2005. Initially, this feature seemed like just an ordinary language element with a couple of uses—but as time went by, people found very creative and interesting uses for it. This article is the first in a two-part series. In Part 1, I describe the fundamentals of APPLY, using classic examples for demonstration purposes. In Part 2, I’ll cover a few more creative uses that go beyond the fundamentals.
I’d like to thank David Long, a SQL Server Magazine reader who suggested that I write an article about APPLY. Although I’ve touched on specific aspects and uses of APPLY in the past in my blog, this operator certainly deserves more extensive, focused coverage.
CROSS, OUTER, and Implicit APPLY
As I mentioned, I’ll start by describing the fundamentals of the APPLY operator. The examples in this section are against a sample database called InsideTSQL2008 that you can download. After you create the sample database, run the following code to add a shipper to the Shippers table and an index to the Orders table:
SET NOCOUNT ON;USE InsideTSQL2008;
SET IDENTITY_INSERT Sales.Shippers ON;
INSERT INTO Sales.Shippers(shipperid, companyname, phone)
VALUES(4, N'Shipper 00004', N'(243) 112-6091')
SET IDENTITY_INSERT Sales.Shippers OFF;
CREATE UNIQUE INDEX idx_sid_od_oid_i_eid_cid
ON Sales.Orders(shipperid, orderdate, orderid)
INCLUDE(empid, custid);
APPLY is a table operator. It applies a table expression you provide as its right input to each row from the table you provide as its left input. If this doesn’t make sense yet, don’t worry; often with such language elements, things make more sense when you see an example that demonstrates the use of the feature. But before I show an example, I want to mention that standard SQL supports a similar feature—however, this feature is called LATERAL correlation rather than APPLY. Back to APPLY and an example; suppose that you need to write a query that returns the two most recent orders for shipper 1. You write the following TOP query:
SELECT TOP (2) orderid, orderdate, empid, custidFROM Sales.Orders
WHERE shipperid = 1
ORDER BY orderdate DESC, orderid DESC;
But what if you need to return the two most recent orders for each of the shippers in the Shippers table? More generally, this problem is known as the TOP N per Group problem—and in our case, N is equal to 2, and the group, or partitioning, element is the shipper. The TOP filter doesn’t support a partitioning option. What you basically want to do is somehow invoke the TOP query that you wrote for a specific shipper against each of the shippers in the Shippers table. Of course, you could define a cursor against Shippers, iterating through the shippers one at a time, but that’s far from being an ideal solution. That’s exactly where the APPLY operator can help you. You provide the Shippers table as the left input to APPLY and a correlated derived table based on the previous TOP query as the right input, and instead of referring to a constant shipper ID in the query filter, you correlate the order’s shipper ID by the shipper’s shipper ID, like so:
SELECT S.shipperid, S.companyname, A.*FROM Sales.Shippers AS S
CROSS APPLY
(SELECT TOP (2) orderid, orderdate, empid, custid
FROM Sales.Orders AS O
WHERE O.shipperid = S.shipperid
ORDER BY orderdate DESC, orderid DESC) AS A;
Figure 1 shows the output of this query.
Figure 1: Output of CROSS APPLY query against shippers and orders
The left table (Shippers) is aliased as S and the inner table (Orders) is aliased as O. Therefore, to correlate orders to the current shipper, the inner query that’s used to define the correlated derived table A uses the predicate O.shipperid = S.shipperid. Observe in the query that in the SELECT list you’re allowed to refer to attributes from both the left side (identified by S), and the right side (identified by A). Also observe that this query uses the CROSS type of the APPLY operator. With CROSS APPLY, if a row from the left side doesn’t find a match in the right side, the left row isn’t returned—much like in an inner join. There are four shippers at the moment in the Shippers table, with IDs 1, 2, 3, and 4, but only the first three have related orders. Hence, the output of the query that Figure 1 shows doesn’t include shipper 4.