Database Reference
In-Depth Information
Figure 15-18.
Execution plan with index hint
There is no easy way to fix the problem. You can use non-aligned nonclustered indexes, which are not
partitioned. Unfortunately, you cannot use a partition switch in such cases nor perform piecemeal database restore,
making subsets of the data available to customers. Thus the only option you have is code refactoring.
■
Tip
You can drop a non-aligned nonclustered index before a partition switch and recreate it after the switch is done.
The
$PARTITION
system function returns a partition number for the value provided as a parameter. You can use
this function in a
where
clause in the query, which eliminates other partitions and produces execution plans similar
to the queries against non-partitioned tables. You can see the query, which reads modified rows from partition 5, in
Listing 15-33.
Listing 15-33.
Potential issues with data partitioning: Selecting data from the single partition
declare
@LastDateModified datetime = '2014-05-25'
select top 100 Id, DateCreated, DateModified, PlaceHolder
from dbo.Data with (index=IDX_Data_DateModified_Id_DateCreated)
where
DateModified > @LastDateModified and
$partition.pfData(DateCreated) = 5
order by DateModified, Id
As you can see in Figure
15-19
, the execution plan is very similar to the query that read modified data from the
non-partitioned table.
Figure 15-19.
Execution plan for the query: selecting data from the single partition
