Database Reference
In-Depth Information
constraint FK_OrderLineItems2012_01_Articles
foreign key(ArticleId)
references dbo.Articles(ArticleId)
);
create unique clustered index IDX_Orders2012_01_OrderId_OrderLineItemId
on dbo.OrderLineItems2012_01(OrderId, OrderLineItemId)
on [FG2012];
create nonclustered index IDX_Orders2012_01_ArticleId
on dbo.OrderLineItems2012_01(ArticleId)
on [FG2012];
/* Other tables */
create view dbo.OrderLineItems(OrderId, OrderLineItemId
,OrderDate, ArticleId, Quantity, Price)
with schemabinding
as
select OrderId, OrderLineItemId, OrderDate
,ArticleId, Quantity, Price
from dbo.OrderLineItems2012_01
/*union all other tables*/
union all
select OrderId, OrderLineItemId, OrderDate
,ArticleId, Quantity, Price
from dbo.OrderLineItems2014_06;
Let's assume that you have a query that returns a list of orders that includes a particular item bought by a specific
customer in January 2014. The typical implementation of the query is shown in Listing 15-7.
Listing 15-7.
Selecting a list of customer orders with a specific item: Non-optimized version
select o.OrderId, o.OrderNum, o.OrderDate, i.Quantity, i.Price
from dbo.Orders o join dbo.OrderLineItems i on
o.OrderId = i.OrderId
where
o.OrderDate >= '2014-01-01' and
o.OrderDate < '2014-02-01' and
o.CustomerId = @CustomerId and
i.ArticleId = @ArticleId
As you can see in Figure
15-4
, SQL Server has to perform an index seek in every
OrderLineItems
table
while searching for line item records. Query Optimizer is not aware that all required rows are stored in the
OrderLineItems2014_01
table.
