Data Partitioning - Pro SQL Server Internals - page 310

■ Note We will discuss schema locks in greater detail in Chapter 23, “Schema Locks.”

Sometimes you can abstract schema changes at the partition view level, which allows you to avoid altering some
tables. Think about adding a NOT NULL column with a default constraint as an example. In SQL Server 2005-2008R2,
this operation would modify every data row in the table and keep the schema modification (SCH-M) lock held for the
duration of the operation. It also generates a large amount of transaction log activity.
In the case of partitioned views, you can alter operational data tables only by using a constant with historical
data tables in the view. Listing 15-9 illustrates such an approach. Keep in mind that such an approach prevents a
partitioned view from being updateable.

Listing 15-9. Abstracting schema changes in the partitioned view

alter table dbo.Orders2014_06
add IsReviewed bit not null
constraint DEF_Orders2014_06_IsReviewed
default 0;

alter view dbo.Orders(OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId, IsReviewed)
with schemabinding
as
select OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId, 0 as [IsReviewed]
from dbo.Orders2012_01
/* union all -- Other tables */

union all
select OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId, IsReviewed
from dbo.Orders2014_06

Using Partitioned Tables and Views Together
You can improve the supportability of a system and reduce the number of required tables by using partitioned tables
and partitioned views together. With such an approach, you are storing historical data in one or more partitioned
tables and operational data in regular table(s), combining all of them into partitioned view.
Listing 15-10 shows such an example. There are three partitioned tables: Orders2012 , Orders2013 , and
Orders2014 , which store historical data partitioned on a monthly basis. There are also two regular tables storing
operational data: Orders2014_05 and Orders2014_06 .

Listing 15-10. Using partitioned tables and views together

create partition function pfOrders2012(datetime2(0))
as range right for values
('2012-02-01', '2012-03-01','2012-04-01','2012-05-01','2012-06-01'
,'2012-07-01','2012-08-01','2012-09-01','2012-10-01','2012-11-01'
,'2012-12-01');