Microsoft® SQL Server® 2012 Bible

1037

Chapter 45: Indexing Strategies

45

SET ANSI_Padding ON;

SET ANSI_Warnings ON;

SET ArithAbort ON;

SET Concat_Null_Yields_Null ON;

SET Quoted_Identifier ON;

SET Numeric_RoundAbort OFF;

GO

CREATE VIEW vScrap

WITH SCHEMABINDING

AS

SELECT WorkOrderID, P.Name AS Product,

P.ProductNumber,

S.Name AS ScrappedReason, ScrappedQty

FROM Production.WorkOrder W

JOIN Production.Product P

ON P.ProductID = W.ProductID

JOIN Production.ScrapReason S

ON W.ScrapReasonID = S.ScrapReasonID

With the view in place, the index can now be created on the view, resulting in an indexed

view:

CREATE UNIQUE CLUSTERED INDEX ivScrap

ON vScrap (WorkOrderID, Product, ProductNumber,

ScrappedReason, ScrappedQty) ;

Indexed views can also be listed and created in Management Studio under the

Views ➪ Indexes node.

To drop an indexed view, the drop statement must refer to the view instead of to a table:

DROP INDEX ivscrap ON dbo.vScrap

Dropping the view automatically drops the indexed view created from the view.

Indexed Views and Queries

When SQL Server’s Query Optimizer develops the execution plan for a query, it includes the

indexed view’s clustered index as one of the indexes it can use for the query, even if the

query doesn’t explicitly reference the view. This happens only with the Enterprise Edition.

This means that the indexed view’s clustered index can serve to speed up queries. When the

Query Optimizer selects the indexed view’s clustered index, the query execution plan indi-

cates the index used. Both of the following queries use the indexed view:

SELECT WorkOrderID, P.Name AS Product,

P.ProductNumber,

S.Name AS ScrappedReason, ScrappedQty

c45.indd 1037c45.indd 1037 7/31/2012 10:16:44 AM7/31/2012 10:16:44 AM

http://www.it-ebooks.info