MSSql: Using the APPLY operator in TSql

Recently, implementing some data access code, I was faced with the task of selecting the last N records for each entity. User kuda78 prompted to use the SelectMany method instead of a multi-story sample.

Exploring what SQL code LinqToSQL creates, I came across an interesting SQL statement called APPLY.





As MSDN says, this command does the following:

http://technet.microsoft.com/en-us/library/ms175156.aspx

The APPLY statement allows you to call a table-valued function for each row returned by an external table query expression. The table-valued function acts as the right input, and the outer table expression as the left input. The right input is evaluated for each line from the left input, and the generated lines are combined for final output. The list of columns created by the APPLY statement is the set of columns in the left entry, followed by the list of columns returned by the right entry.



As it turned out, APPLY is very well suited to solving the problem.



Let's look at an example:

Task: Select the last 10 orders for each customer.



Let us have the following simple database structure:

CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, CustomerName NVARCHAR(30) NOT NULL ) CREATE TABLE Nomenclature ( NomenclatureID INT PRIMARY KEY, NomenclatureName NVARCHAR(30) NOT NULL, Price MONEY NOT NULL ) CREATE TABLE Deal ( DealID INT IDENTITY(1, 1) PRIMARY KEY, CustomerID INT NOT NULL, NomenclatureID INT NOT NULL, [Count] DECIMAL(8,2) NOT NULL, DealDate DATETIME NOT NULL ) 

')

Now we need to select the last 10 orders for each customer. Previously, we used the following approach: first, for each customer, select the date from which he had 10 orders, and then select all orders from that date.

 SELECT d.DealDate, c.CustomerName, n.NomenclatureName, n.Price, d.Count FROM Customer c JOIN Deal d ON d.CustomerID = c.CustomerID JOIN (SELECT c.CustomerID, (SELECT MIN(lastDeals.DealDate) FROM (SELECT TOP 10 d1.DealDate FROM Deal d1 WHERE d1.CustomerID = c.CustomerID ORDER BY d1.DealDate DESC) LastDeals) LastDealDate FROM Customer c) ld ON ld.CustomerID = c.CustomerID JOIN Nomenclature n ON n.NomenclatureID = d.NomenclatureID WHERE d.DealDate >= ld.LastDealDate ORDER BY c.CustomerName, d.DealDate DESC 

* For simplicity, I specifically made the assumption that 2 orders at the same time cannot be.



Using APPLY, the SQL code has gained greater readability:

 SELECT d.DealDate, c.CustomerName, n.NomenclatureName, n.Price, d.Count FROM Customer c OUTER APPLY (SELECT TOP 10 d1.* FROM Deal d1 Where d1.CustomerID = c.CustomerID ORDER BY d1.DealDate DESC) d INNER JOIN Nomenclature n ON n.NomenclatureID = d.NomenclatureID ORDER BY c.CustomerName, d.DealDate DESC 

The plan and time of the request with all the necessary indexes also inspire confidence that this function will come in handy more than once.



Database creation file with indexes: CreateDB.txt

SQL query file: Queries.txt