Database Reference
In-Depth Information
-- Getting number of boundary values in partition function
select @BoundaryValuesCount = max(boundary_id)
from sys.partition_functions pf join
sys.partition_range_values prf on
pf.function_id = prf.function_id
where pf.name = 'pfData'
;with Partitions(PartitionNum)
as
(
select 1
union all
select PartitionNum + 1
from Partitions
where PartitionNum <= @BoundaryValuesCount
)
insert into #Partitions(PartitionNum)
select PartitionNum from Partitions;
;with Steps1and2(Id, DateCreated, DateModified)
as
(
select top 100 PartData.ID, PartData.DateCreated, PartData.DateModified
from #Partitions p
cross apply
(
select top 100 Id, DateCreated, DateModified
from dbo.Data
where
DateModified > @LastDateModified and
$Partition.pfData(DateCreated) =
p.PartitionNum
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
Alternatively, if the number of partitions is static and predefined, you can hardcode it in
Partitions
CTE,
as shown in Listing 15-36.
