Database Reference
In-Depth Information
order by DateModified, ID
) PartData
order by PartData.DateModified, PartData.Id
)
-- Step 3 - CI seek as Key Lookup operation
select s.Id, s.DateCreated, s.DateModified, d.Placeholder
from Steps1and2 s join dbo.Data d on
d.Id = s.Id and s.DateCreated = d.DateCreated
order by s.DateModified, s.Id
The outer select statement in Steps1and2 CTE sorts the data returned by the CROSS APPLY operator across all
partitions, which is the second step in the algorithm.
Finally, the last select outside of CTE is the third step in the algorithm described above.
You can see the execution plan of the query in Figure 15-20 . The plan is almost as efficient as the one against
non-partitioned tables.
Figure 15-20. Execution plan of the optimized query
Unfortunately, SQL Server underestimates the number of executions and rows returned by recursive CTE. It can
lead to further cardinality estimation errors and subefficient execution plans in some cases. You can avoid this error
by using a temporary table to store partition numbers, as shown in Listing 15-35.
Listing 15-35. Storing partition numbers in a temporary table
declare
@LastDateModified datetime = '2014-05-25',
@BoundaryValuesCount int
create table #Partitions(PartitionNum smallint not null);
 
Search WWH ::




Custom Search