LinksUp (9/23/2013)
Your code is indeed more efficient!
I just ran all 3 queries and checked the Execution plan on each one and I am only seeing 2 Table Scans on both my solution and the cte. Whereas your solution does only have 1. What are you looking at to determine 3 Table Scans?
(See attached jpg for execution plan)
When I run all three versions with SET STATISTICS IO ON, I get this:
=========================================
Prepare sample data:
(14 row(s) affected)
(1 row(s) affected)
=========================================
Subselect version:
(2 row(s) affected)
Table '#3B219CFC'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
=========================================
CTE version:
(2 row(s) affected)
Table '#3B219CFC'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
=========================================
CASE version:
(2 row(s) affected)
Table '#3B219CFC'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
Your subselect has to be performed once for each distinct value of party_code, even though you see only one table scan operator in the execution plan. If you look at the execution plan, you'll see that once branch of the logical tree shows a table scan of [party_payments] [t2] feeding a stream aggregate that computes the SUM in the subselect. If you look at the XML of the plan (by opening the attached .sqlplan with a text editor), you'll see that the table scan is actually performed twice, once for each distinct value of party_code. Here's is the RunTimeInformation node of the RelOp node for the stream aggregrate (NodeID = "15"):
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="0" ActualExecutions="2" />
</RunTimeInformation>
and the RunTimeInformation node of the RelOp node for the supporting table scan (NodeID = "16") - you see it's a child node of the stream aggregate RelOp:
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="2" ActualExecutions="2" />
</RunTimeInformation>
Note the value of the ActualExecutions attribute in each case: "2".
Add these two scans to the table scan of [party_payments] [t1] and you have 3 scans altogether.
You can test this by adding rows with additional distinct values of party_code - you'll see an additional scan for each additional distinct value.
If you're familiar with XPath notation, here are the XPaths to those nodes:
The stream aggregate:
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/ComputeScalar/RelOp/NestedLoops/RelOp[2]/ComputeScalar/RelOp
and the table scan:
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/ComputeScalar/RelOp/NestedLoops/RelOp[2]/ComputeScalar/RelOp/StreamAggregate/RelOp
Jason Wolfkill