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

vw_CIScan_QueryPlan

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)

vw_ExpandView_QueryPlan

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

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *