SQL Server 2005 introduced two powerful features: Common Table Expressions (CTEs) and the Row_number() function. In this article, we will explore how these two features can be combined to solve a common problem in a more elegant way.
Common Table Expressions
A CTE is a temporary result set that is valid for the scope of a single Select, Update, Insert, or Delete statement. While some argue that the limited scope of CTEs restricts their usefulness, they offer more than just improved SQL readability. One key advantage is that the result set can be referenced multiple times within the same statement.
Row_number()
The Row_number() function is a long-awaited addition to SQL Server. It assigns a sequential row number to each record in a result set. You can specify an ordering to determine the basis for the numbering, and optionally partition the result set to create separate numbering sequences.
The Price History Example
Let’s consider a scenario where a retail client wants to generate a report showing the item name, old price, new price, and the date range for which the new price was applied. Traditionally, this type of question has been challenging for SQL developers, often requiring messy subqueries or storing both start and end dates in the table. However, we can leverage CTEs and the Row_number() function to provide a more elegant solution.
The Solution
The first step is to construct a CTE that numbers the rows of the data table. We partition the numbering by the item ID and order it by the price start date. This allows us to link each row to its previous and next rows.
;WITH PriceCompare AS (
SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (PARTITION BY ph.ItemId ORDER BY PriceStartDate) AS rownum
FROM Items i
INNER JOIN PriceHistory ph ON i.ItemId = ph.ItemId
)
SELECT * FROM PriceCompare
Next, we enhance the SELECT statement by joining the CTE with itself to retrieve the previous and next rows for each item. We use left joins to handle the first and last rows, which have no previous or next rows.
SELECT currow.Item, prevrow.Price AS OldPrice, currow.Price AS RangePrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate
FROM PriceCompare currow
LEFT JOIN PriceCompare nextrow
ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemId
LEFT JOIN PriceCompare prevrow
ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemId
By running this query, we obtain the desired result set for the Price History report. Additionally, we can encapsulate the entire CTE in a view for easier reuse.
CREATE VIEW [dbo].[PriceCompare] AS
WITH PriceCompare AS
(
SELECT i.Item, ph.ItemId, ph.PriceStartDate, ph.Price,
ROW_NUMBER() OVER (PARTITION BY ph.ItemId ORDER BY PriceStartDate) AS rownum
FROM Items i
INNER JOIN PriceHistory ph ON i.ItemId = ph.ItemId
)
SELECT currow.Item, prevrow.Price AS OldPrice, currow.Price AS RangePrice, currow.PriceStartDate AS StartDate, nextrow.PriceStartDate AS EndDate
FROM PriceCompare currow
LEFT JOIN PriceCompare nextrow
ON currow.rownum = nextrow.rownum - 1 AND currow.ItemId = nextrow.ItemId
LEFT JOIN PriceCompare prevrow
ON currow.rownum = prevrow.rownum + 1 AND currow.ItemId = prevrow.ItemId
In conclusion, CTEs and the Row_number() function offer a powerful combination for solving complex problems in SQL Server. By leveraging these features, you can achieve more elegant and efficient solutions, improving both the readability and performance of your SQL code.