Data Partitioning - Pro SQL Server Internals - page 334

Potential Issues
Despite all of the benefits that data partitioning delivers, they do come at a cost. First, SQL Server requires a
partitioned column to be a part of the clustered index key in the partitioned table. This, in turn, adds that column to
the row-id and increases the row size in every nonclustered index. For example, in a table that stores 365 million rows,
a datetime -partitioned column adds 2.7GB per nonclustered index, not counting fragmentation overhead and
non-leaf level storage space.

■ always choose the most storage-efficient data type based on the business requirements. in the previous
example, you can use smalldatetime (4 bytes) or datetime2(0) (6 bytes) instead of datetime (8 bytes), if one minute
or one second precisions are acceptable.

Tip

Even though you can mitigate this space increase in some cases by implementing data compression on the
historical data, the row-id size increase can add new non-leaf levels to the indexes and extra reads when SQL Server
traverses index B-Trees.
Uniqueness support is another issue. You cannot create a unique constraint and/or index on a partitioned view.
With partitioned tables, SQL Server requires a partitioned column to be part of aligned unique nonclustered indexes.
This enforces uniqueness only in the single partition scope. Although you can define non-aligned unique indexes, it
prevents you from using a partition switch, which is one of the greatest benefits of partitioned tables.
Unfortunately, there is no easy solution for this problem. In cases where you need to support uniqueness across
multiple data partitions, you have to implement complex code, often using a SERIALIZEABLE transaction isolation
level, and this can introduce blocking issues in the system.

■ Note We will discuss transaction isolation levels in greater depth in Chapter 17, “Lock types.”

Ultimately, the biggest problem with data partitioning is that it changes the execution plans of the queries. It can
introduce suboptimal performance for some queries, which worked just fine when the data had not been partitioned.
Let's look at one such example and create a non-partitioned table and populate it with some random data, as
shown in Listing 15-29.

Listing 15-29. Potential issues with data partitioning: Creating a non-partitioned table

create table dbo.Data
(
Id int not null,
DateCreated datetime not null
constraint DEF_Data_DateCreated
default getutcdate(),
DateModified datetime not null
constraint DEF_Data_DateModified
default getutcdate(),
Placeholder char(500) null
);

create unique clustered index IDX_Data_Id
on dbo.Data(DateCreated, Id);