Views - Pro SQL Server Internals - page 184

■ Look at Books Online at: http://technet.microsoft.com/en-us/library/ms191432.aspx for a complete
list of requirements and restrictions.

Note

■ You can use the function OBJECTPROPERTY with parameter IsIndexable to determine if you can create the
clustered index on the view. the following select returns 1 if the view vPositions is indexable:

Tip

SELECT OBJECTPROPERTY (OBJECT_ID(N'dbo.vPositions','IsIndexable')

One instance where an indexed view is useful is for optimization of queries that include joins and aggregations
on large tables. Let's look at this situation, assuming that you have OrderLineItems and Products tables in the system.
The code that creates these tables is shown in Listing 9-15.

Listing 9-15. Indexed views: Table creation

create table dbo.Products
(
ProductID int not null identity(1,1),
Name nvarchar(100) not null,

constraint PK_Product
primary key clustered(ProductID)
);

create table dbo.OrderLineItems
(
OrderId int not null,
OrderLineItemId int not null identity(1,1),
Quantity decimal(9,3) not null,
Price smallmoney not null,
ProductId int not null,
constraint PK_OrderLineItems
primary key clustered(OrderId,OrderLineItemId),

constraint FK_OrderLineItems_Products
foreign key(ProductId)
references dbo.Products(ProductId)
);

create index IDX_OrderLineItems_ProductId on dbo.OrderLineItems(ProductId);

Now let's imagine a dashboard that displays information about the ten most popular products sold to date. The
dashboard can use the query shown in Listing 9-16.