Data Partitioning - Pro SQL Server Internals - page 303

■ SQL Server allows you to define partitioned views combining data from multiple databases or even SQL Server
instances. the latter case is called Distributed Partitioned Views. the coverage of such scenarios is outside of the scope
of the topic. however, they behave similarly to partitioned views defined in a single database scope.

Note

Listing 15-4 shows an example of data partitioning of the Orders entity using a partitioned view approach.

Listing 15-4. Creating partitioned views

create table dbo.Orders2012_01
(
OrderId int not null,
OrderDate datetime2(0) not null,
OrderNum varchar(32) not null,
OrderTotal money not null,
CustomerId int not null,
/* Other Columns */
constraint PK_Orders2012_01
primary key clustered(OrderId),

constraint CHK_Orders2012_01
check (OrderDate >= '2012-01-01' and OrderDate < '2012-02-01')
) on [FG2012];

create nonclustered index IDX_Orders2012_01_CustomerId
on dbo.Orders2012_01(CustomerId)
on [FG2012];

create table dbo.Orders2012_02
(
OrderId int not null,
OrderDate datetime2(0) not null,
OrderNum varchar(32) not null,
OrderTotal money not null,
CustomerId int not null,
/* Other Columns */
constraint PK_Orders2012_02
primary key clustered(OrderId)
with (data_compression=page),

constraint CHK_Orders2012_02
check (OrderDate >= '2012-02-01' and OrderDate < '2012-03-01')
) on [FG2012];

create nonclustered index IDX_Orders2012_02_CustomerId
on dbo.Orders2012_02(CustomerId)
with (data_compression=page)
on [FG2012];

/* Other tables */