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!
Top comments (1)
Leave your comments here if you love SQL ;)