SQL Server expand views hint and execution plan

Hi Geeks,

Today I will be talking about SQL Server expand views hint and its impact on execution plan. We will create an indexed view first then dive into the topic.

--USE [AdventureWorks2012]

--Step 1
CREATE VIEW VW_PurcOrdDtCnt WITH SCHEMABINDING
AS
SELECT PurchaseOrderDetail.PurchaseOrderID, COUNT_BIG(*) AS 'COUNT'
FROM Purchasing.PurchaseOrderDetail
GROUP BY PurchaseOrderDetail.PurchaseOrderID
GO

--Step 2
CREATE UNIQUE CLUSTERED INDEX IX_PurcOrdDtCnt ON VW_PurcOrdDtCnt(PurchaseOrderID)

Now, let us run following SQL statement and see the execution plan.

USE [AdventureWorks2012]

SELECT PurchaseOrderDetail.PurchaseOrderID, COUNT(*) AS 'COUNT'
FROM Purchasing.PurchaseOrderDetail
GROUP BY PurchaseOrderDetail.PurchaseOrderID

Above plan uses indexed view instead of query. Now let us use EXPAND VIEWS hint then find out if execution plan looks something different from the above.

USE [AdventureWorks2012]

SELECT PurchaseOrderDetail.PurchaseOrderID, COUNT(*) AS 'COUNT'
FROM Purchasing.PurchaseOrderDetail
GROUP BY PurchaseOrderDetail.PurchaseOrderID
OPTION (EXPAND VIEWS)

What makes it different? Answer is, SQL Server engine expand views in early steps of the query optimization process to be specific during binding. When a view definition is expanded, it include tables used in the view and SQL engine will match any query to the indexed view and in our example VW_PurcOrdDtCnt. The EXPAND VIEWS query hint removes this step. So, it won’t consider any indexes defined on views for similar SQL statements and hence the plan is different with the hint.

We can safely drop the view now.

USE [AdventureWorks2012]

DROP VIEW VW_PurcOrdDtCnt

You can find index to the execution plan series here and click on One operator a day to visit exclusive page for this series.

Happy Learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook