A common usage pattern for common table expressions till SQL Server 2012 is to get the previous row values using a combination of ROW_NUMBER, CTE and self-join thus:
MyID INT PRIMARY KEY,
Filler NCHAR(3500) NOT NULL
);
GO
INSERT MyTable (MyID, Filler)
VALUES (11, N'A'), (22, N'B'), (33, N'C'), (44, N'D'), (55, N'E');
GO
– Test #1
WITH MyCTE
AS (
SELECT *, ROW_NUMBER()OVER(ORDER BY MyID) AS RowNum
FROM dbo.MyTable
)
SELECT *
FROM MyCTE crt
LEFT JOIN MyCTE prev ON crt.RowNum=prev.RowNum+1;
GO
In this case, for every row is displayed the previous row values:
MyID Filler RowNum MyID Filler RowNum
—- —— —— —- —— ——
11 A 1 NULL NULL NULL
22 B 2 11 A 1
33 C 3 22 B 2
44 D 4 33 C 3
55 E 5 44 D 4
Because a common table expression is a local view, the source code is expanded within the source of caller (ex. a INSERT, UPDATE, DELETE or a SELECT statement) and the execution plan for above SELECT statement looks like this:
As you can see from above execution plan, the common table expression (MyCTE) is expanded two times: crt and prev. If we want to see how many time is executed every “instance” of MyCTE then we can inspect the value of Number of Executions property for every operator. Bellow, you can see that for Number of Executions for Clustered Index Scan for crt is 1 and for prev is 5. Basically, in this example, the prev part is executed five times: one time for every row from crt part and overall the MyCTE is executed six times:
It’s possible to change this behavior ? Yes, it’s possible and the answer (at least one answer) comes from changing the physical type of JOIN which in this case is LOOP / NESTED LOOPS.
I’ve changed the physical join from LOOP to HASH and MERGE JOIN and the result was that the prev part is executed, in my example, just one time and overall the MyCTE is executed two times:
WITH MyCTE
AS (
SELECT *, ROW_NUMBER()OVER(ORDER BY MyID) AS RowNum
FROM dbo.MyTable
)
SELECT *
FROM MyCTE crt
LEFT HASH JOIN MyCTE prev ON crt.RowNum=prev.RowNum+1;
GO
– Test #3
WITH MyCTE
AS (
SELECT *, ROW_NUMBER()OVER(ORDER BY MyID) AS RowNum
FROM dbo.MyTable
)
SELECT *
FROM MyCTE crt
LEFT MERGE JOIN MyCTE prev ON crt.RowNum=prev.RowNum+1;
GO
Another aspect is, also, interesting: in this small test, the number of logical reads is smaller for HASH and MERGE JOIN than for LOOP JOIN:
Note #1: because of join hints (LOOP, MERGE, HASH) the join order is enforced. Don’t use these hints without proper testing your queries. The usage of these hints without carefully analyzing the execution plans can lead to suboptimal plans.
Note #2: Starting with SQL Server 2012 you can use LAG function to get previous row’s values. See also LEAD function.
