Database Reference
In-Depth Information
Figure 35-12.
Execution plan of test query in SQL Server 2012
It is still possible to force batch-mode execution with SQL Server 2012; however, it requires cumbersome query
refactoring with subselects and/or common table expressions. Listing 35-8 shows you how to refactor the test query
by introducing the
group by
aggregate.
Listing 35-8.
Batch-mode execution: Refactoring of test query to utilize batch-mode execution in SQL Server 2012
;with CTE(BranchId, TotalSales)
as
(
select BranchId, Sum(Amount)
from dbo.FactSales
group by BranchId
)
select sum(TotalSales) as TotalSales
from CTE
Figure
35-13
shows the execution plan of the query after refactoring.
Figure 35-13.
Execution plan of test query after refactoring in SQL Server 2012
As you can see, query refactoring is not always trivial and requires a deep understanding of SQL Server query
processing. This prevents the widespread adoption of such techniques within the SQL Server 2012 community.
Fortunately, SQL Server 2014 makes query refactoring unnecessary in a large number of cases. Those improvements
alone make upgrading from SQL Server 2012 to 2014 in Data Warehouse environments worth the effort.
