Database Reference
In-Depth Information
Listing 15-36.
Hardcoding partition numbers
declare
@LastDateModified datetime = '2014-05-25'
;with Partitions(PartitionNum)
as
(
select v.V
from (values(1),(2),(3),(4),(5),(6),(7),(8)) v(V)
)
,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
To repeat, data partitioning changes the execution plans of the queries. You should carefully test systems in a
staging environment using databases of similar size and data distribution to that of production. This will help to avoid
unpleasant surprises when changes are implemented on production servers.
Summary
Management of a large amount of data is a challenging task, especially when the data is not partitioned. Keeping
a large amount of data in the same place is not efficient for several different reasons. It increases storage costs and
introduces overhead due to the different workload and index management requirements for the various parts of the
data. Moreover, it prevents piecemeal database restore, which complicates availability SLA compliance.
There are two main data partitioning techniques available in SQL Server. Partitioned tables are available in
the Enterprise Edition of SQL Server. They allow you to partition table data into separate internal tables/partitions,
which is transparent to client applications. Each partition can be placed in its own filegroup and have its own data
compression. However, the database schema, indexes, and statistics are the same across all partitions.
Alternatively, you can partition the data by separating it between multiple tables, combining all of them through
a partitioned view using the
union all
operator. Every table can have its own schema and set of indexes and maintain
its own statistics. Partitioned views are supported in all editions of SQL Server.
