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:
    
    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

    Also popular now: