The Power of OUTER APPLY: A SQL Weapon to Expand Legacy Database

Introduction

In the world of SQL Server, maintaining and enhancing legacy stored procedures and views can be a daunting task, especially when you aren't 100% confident in your understanding of the legacy architecture. As systems evolve and businesses expand, the need to integrate new functionality while preserving existing logic becomes crucial. One powerful tool that can help in this endeavor is the OUTER/CROSS APPLY operator. This article explores how OUTER APPLY can serve as a valuable asset to expand and optimize legacy views.

Understanding OUTER APPLY

OUTER APPLY is a powerful SQL operator that allows you to join a table to a table-valued function (TVF) or a subquery, evaluated for each row of the outer table. It is particularly useful for scenarios where you need to return rows from the outer table even when there are no matching rows in the inner query.

Key Features of OUTER APPLY

1. Row-wise Operation:

OUTER APPLY enables you to join a table with a table-valued function (TVF) or a subquery that is evaluated for each row of the outer table. This allows for more dynamic and flexible queries compared to standard joins.

2. Integration with Legacy Systems:

For legacy stored procedures, OUTER APPLY can facilitate the integration of new functionality without requiring a complete rewrite of existing logic, thereby preserving the integrity of legacy systems.

3. Simplified Syntax for Complex Queries:

Using OUTER APPLY can simplify the syntax of complex queries that would otherwise require multiple JOINs or subqueries, enhancing readability and maintainability.

By leveraging these features, OUTER APPLY can significantly enhance the capabilities of SQL queries, particularly when working with legacy systems.

Real World Scenario for Expanding Legacy Views

Example:

Our company wanted to roll out some promotions earlier this year to increase sales, then we created tables "Promotions" and "OrderPromotions" which didn't exist in our legacy system before:

(If you wants to do this exercise, you could setup the data, tables and view according to my previous article first)

CREATE TABLE Promotions (
    PromotionID         BIGINT IDENTITY(1,1) NOT NULL,
    PromotionName       NVARCHAR(1000),
    PromotionCode       NVARCHAR(1000),
    ActiveFrom          DATETIME         NOT NULL DEFAULT '19000101',
    ActiveTo            DATETIME         NOT NULL DEFAULT '29991231',
    IsActive            BIT              NOT NULL DEFAULT 1,
    IsDeleted           BIT              NOT NULL DEFAULT 0,
    CreateBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    CreateDate          DATETIME         NOT NULL DEFAULT GETDATE(),
    ModifyBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    ModifyDate          DATETIME         NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY (PromotionID)
);

--1 Order could apply more than 1 promotions
CREATE TABLE OrderPromotions (
    OrderPromotionID    BIGINT IDENTITY(1,1) NOT NULL,
    OrderID             BIGINT,
    PromotionID         BIGINT,
    IsDeleted           BIT              NOT NULL DEFAULT 0,
    CreateBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    CreateDate          DATETIME         NOT NULL DEFAULT GETDATE(),
    ModifyBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    ModifyDate          DATETIME         NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY (OrderPromotionID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (PromotionID) REFERENCES Promotions(PromotionID),
);

--Create promotions
INSERT INTO Promotions (PromotionName, PromotionCode) VALUES
('25% Off Discount', '25OFF')
, ('Buy 1 Get 1', 'B1G1')
, ('New Customer', 'NEW')
, ('Black Friday', 'BLACK')
, ('Loyalty Discount', 'LOYALTY')

A new customer, Elon, joined and placed an order that bought 100 iPhones and 100 iPads, and he was the first one to enjoy our new customer promotion:

INSERT INTO Customers (LastName, FirstName, DOB) VALUES
('M.', 'Elon', '19701231')

INSERT INTO Orders (CustomerID, OrderNumber, OrderDate) VALUES
((SELECT TOP 1 CustomerID FROM Customers WHERE FirstName = 'Elon' AND IsDeleted = 0)
, 'ORD0004'
, DATEADD(MONTH, 2,GETDATE()))

INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES
((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0004' AND IsDeleted = 0)
, (SELECT TOP 1 ProductID FROM Products WHERE ProductCode = 'I0001' AND IsDeleted = 0)
, 100)
, ((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0004' AND IsDeleted = 0)
, (SELECT TOP 1 ProductID FROM Products WHERE ProductCode = 'I0002' AND IsDeleted = 0)
, 100)

INSERT INTO OrderPromotions (OrderID, PromotionID) VALUES
((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0004' AND IsDeleted = 0)
, (SELECT TOP 1 PromotionID FROM Promotions WHERE PromotionCode = 'NEW' AND IsActive = 1 AND IsDeleted = 0))

An existing customer, David, bought 1 iPad Mini and also got loyalty discount:

INSERT INTO Orders (CustomerID, OrderNumber, OrderDate) VALUES
((SELECT TOP 1 CustomerID FROM Customers WHERE FirstName = 'David' AND IsDeleted = 0)
, 'ORD0005'
, DATEADD(MONTH, 2,GETDATE()))

INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES
((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0005' AND IsDeleted = 0)
, (SELECT TOP 1 ProductID FROM Products WHERE ProductCode = 'I0003' AND IsDeleted = 0)
, 1)

INSERT INTO OrderPromotions (OrderID, PromotionID) VALUES
((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0005' AND IsDeleted = 0)
, (SELECT TOP 1 PromotionID FROM Promotions WHERE PromotionCode = 'LOYALTY' AND IsActive = 1 AND IsDeleted = 0))

A few months later, marketing department wanted to understand which promotions are most attractive to customers. By analyzing customer orders and the promotions applied, the department aimed to identify the effectiveness of various promotional strategies, such as discounts, loyalty rewards, and new customer incentives. So, they requested to update the view [v_Product_Top_1] that we have provided before.

For expanding the columns for analysis, we created a new function which returns the top 2 applied promotion for each product in a specific time arrange:

CREATE FUNCTION GetTop2AppliedPromotionByProductID (@product_id BIGINT, @target_date DATETIME)
RETURNS TABLE
AS
RETURN (
    SELECT TOP 2
        pm.PromotionCode
        , COUNT(1) AS NoOfPromotionUsed
    FROM Products pd
    INNER JOIN OrderItems oi ON
        oi.ProductID = pd.ProductID
        AND oi.IsDeleted = 0
    INNER JOIN Orders o ON
        o.OrderID = oi.OrderID
        AND o.IsDeleted = 0
    INNER JOIN OrderPromotions op ON
        op.OrderID = o.OrderID
        AND op.IsDeleted = 0
    INNER JOIN Promotions pm ON
        pm.PromotionID = op.PromotionID
        AND pm.IsDeleted = 0
    WHERE pd.ProductID = @product_id
        AND YEAR(o.OrderDate) = YEAR(@target_date)
        AND MONTH(o.OrderDate) = MONTH(@target_date)
    GROUP BY pm.PromotionCode, pd.ProductID
    ORDER BY COUNT(1) 
);

(I added "ProductName" to make it easier for everyone to understand, which is not necessary.)

After verifying the new function, we applied this function to the existing view:

Before:

CREATE VIEW [v_Product_Top_1] 
AS
WITH cte AS (
    SELECT
        YEAR(o.OrderDate) AS SalesYear
        , MONTH(o.OrderDate) AS SalesMonth
        , ProductName
        , SUM(oi.Quantity) AS TotalSales
        , RANK() OVER(PARTITION BY YEAR(o.OrderDate), MoNTH(o.OrderDate) ORDER BY SUM(oi.Quantity) DESC) AS rn
    FROM OrderItems oi
    INNER JOIN Orders o ON
        o.OrderID = oi.OrderID
        AND o.IsDeleted = 0
    INNER JOIN Products p ON
        p.ProductID = oi.ProductID
        AND p.IsDeleted = 0
    WHERE oi.IsDeleted = 0
    GROUP BY p.ProductName, YEAR(o.OrderDate), MONTH(o.OrderDate)
    HAVING SUM(oi.Quantity) > 0
)
SELECT SalesYear, SalesMonth, ProductName, TotalSales
FROM cte
WHERE rn = 1

After:

ALTER VIEW [v_Product_Top_1] 
AS
WITH cte AS (
    SELECT
        YEAR(o.OrderDate) AS SalesYear
        , MONTH(o.OrderDate) AS SalesMonth
        , ProductName
        , SUM(oi.Quantity) AS TotalSales
        , RANK() OVER(PARTITION BY YEAR(o.OrderDate), MoNTH(o.OrderDate) ORDER BY SUM(oi.Quantity) DESC) AS rn
        , pmTop2.PromotionCode, pmTop2.NoOfPromotionUsed
    FROM OrderItems oi
    INNER JOIN Orders o ON
        o.OrderID = oi.OrderID
        AND o.IsDeleted = 0
    INNER JOIN Products p ON
        p.ProductID = oi.ProductID
        AND p.IsDeleted = 0 
    OUTER APPLY dbo.GetTop2AppliedPromotionByProductID (p.ProductID, o.OrderDate) pmTop2
    WHERE oi.IsDeleted = 0
    GROUP BY p.ProductName, YEAR(o.OrderDate), MONTH(o.OrderDate), pmTop2.PromotionCode, pmTop2.NoOfPromotionUsed
    HAVING SUM(oi.Quantity) > 0
)
SELECT SalesYear, SalesMonth, ProductName, TotalSales, PromotionCode, NoOfPromotionUsed
FROM cte
WHERE rn = 1

Please note that you could rename the view name if necessary:

EXEC sp_rename 'dbo.v_Product_Top_1', 'v_Product_Top_1_with_Promotions'

Compare the view data BEFORE and AFTER

Before:

After:

(indicated that the new customer promotion was the most attractive campaign in terms of number of sales.)

Conclusion

The OUTER APPLY operator is a potent tool that can bring a new life to legacy databases. By enabling more dynamic data retrieval, simplifying complex queries, OUTER APPLY serves as a useful weapon for developers looking to enhance their SQL Server applications. Embracing this feature can lead to more efficient, maintainable, and scalable database solutions.

Moreover, you can think of OUTER APPLY as a left join and CROSS APPLY as an inner join to help distinguish between the two.

As you start on expanding your legacy stored procedures/views, consider the power of OUTER/CROSS APPLY!

Comments

[David Au Yeung]

Leave your comments here if you love SQL ;)