Database Reference
In-Depth Information
Figure 15-3.
Execution plans of the queries against a partitioned view
You should always add predicates, which reduce the number of tables to be processed by the queries. Let's look
at a practical example and, as the first step, create another entity called
OrderLineItems
. Obviously, you would like to
partition it in the same way as the
Orders
entity; that is, on a monthly basis.
■
You should partition related entities and place them in filegroups in a way that supports piecemeal restore and
which allows you to bring entities online together.
Tip
Listing 15-6 shows the code that creates the set of tables and the partitioned view. Even though the
OrderDate
column is redundant in the
OrderLineItems
table, you need to add it to all of the tables in order to create a consistent
partitioning layout with the
Orders
tables.
Listing 15-6.
OrderLineItems partition view
create table dbo.OrderLineItems2012_01
(
OrderId int not null,
OrderLineItemId int not null,
OrderDate datetime2(0) not null,
ArticleId int not null,
Quantity decimal(9,3) not null,
Price money not null,
/* Other Columns */
constraint CHK_OrderLineItems2012_01
check (OrderDate >= '2012-01-01' and OrderDate < '2012-02-01'),
constraint FK_OrderLineItems_Orders_2012_01
foreign key(OrderId)
references dbo.Orders2012_01(OrderId),
