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. The user kuda78 suggested using the SelectMany method instead of multi-storey selection.
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-returning function for each row returned by an external table expression query. The function that returns the table value acts as the right input, and the external table expression acts as the left input. The right input is evaluated for each line from the left input, and the generated lines are combined for the final output. The list of columns created by the APPLY statement is the set of columns in the left input, followed by the list of columns returned by the right input.
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:
Now we need to select the last 10 orders for each customer. Previously, we used the following approach: first, for each customer, we selected a date starting from which he had 10 orders, and then he selected all orders from this date.
* For simplicity, I specifically made the assumption that 2 orders at the same time cannot be.
Using APPLY SQL, the code has become more readable:
The plan and execution time of the request with all the necessary indexes also inspire confidence that this function will come in handy more than once.
Index creation database
file : CreateDB.txt SQL query file: Queries.txt
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-returning function for each row returned by an external table expression query. The function that returns the table value acts as the right input, and the external table expression acts as the left input. The right input is evaluated for each line from the left input, and the generated lines are combined for the final output. The list of columns created by the APPLY statement is the set of columns in the left input, followed by the list of columns returned by the right input.
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, we selected a date starting from which he had 10 orders, and then he selected all orders from this 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 SQL, the code has become more readable:
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 execution time of the request with all the necessary indexes also inspire confidence that this function will come in handy more than once.
Index creation database
file : CreateDB.txt SQL query file: Queries.txt