1

I created two views:

  • vTest1
  • vTest2

The create procedure for each is:

CREATE VIEW [dbo].[vTest1] WITH SCHEMABINDING
AS
SELECT        Person.EmailAddress.EmailAddress, Person.Person.Title, Person.Person.FirstName, Person.Person.MiddleName, Person.Person.LastName
FROM            Person.Person 
    INNER JOIN  Person.EmailAddress 
    ON Person.Person.BusinessEntityID = Person.EmailAddress.BusinessEntityID

and

CREATE VIEW [dbo].[vTest1] WITH SCHEMABINDING
AS
SELECT        Person.EmailAddress.EmailAddress, Person.Person.Title, Person.Person.FirstName, Person.Person.MiddleName, Person.Person.LastName
FROM            Person.Person 
    INNER JOIN Person.EmailAddress 
    ON Person.Person.BusinessEntityID = Person.EmailAddress.BusinessEntityID

Then I run a SELECT * from each I get the expected execution plan.

Non-indexed view execution plans

Now, I add an index to vTest2:

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20161025-205534] ON [dbo].[vTest2]
(
    [EmailAddress] ASC,
    [Title] ASC,
    [FirstName] ASC,
    [MiddleName] ASC,
    [LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Now when I the select again I find that the added index is being used for both views.

Clustered index view execution plans

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.5" Build="13.0.1711.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="19936.1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.209696" StatementText="SELECT * FROM dbo.vTest1" StatementType="SELECT" QueryHash="0x95D05E881A8B7175" QueryPlanHash="0x16D51AFE3D4CCA61" RetrievedFromCache="true" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="581" CompileCPU="4" CompileMemory="288">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="69408" EstimatedPagesCached="52056" EstimatedAvailableDegreeOfParallelism="6" />
            <RelOp AvgRowSize="227" EstimateCPU="0.0221262" EstimateIO="0.187569" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="19972" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.209696" TableCardinality="19972">
              <OutputList>
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="EmailAddress" />
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="Title" />
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="FirstName" />
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="MiddleName" />
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="LastName" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="19972" ActualRowsRead="19972" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="31" ActualCPUms="5" ActualScans="1" ActualLogicalReads="256" ActualPhysicalReads="1" ActualReadAheads="250" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="true" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="EmailAddress" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="Title" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="FirstName" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="MiddleName" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="LastName" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Index="[ClusteredIndex-20161025-205534]" IndexKind="ViewClustered" Storage="RowStore" />
                <IndexedViewInfo>
                  <Object Database="[AdventureWorks2008R2]" Schema="[Person]" Table="[Person]" />
                  <Object Database="[AdventureWorks2008R2]" Schema="[Person]" Table="[EmailAddress]" />
                </IndexedViewInfo>
              </IndexScan>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="2" StatementEstRows="19936.1" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.209696" StatementText="SELECT * FROM dbo.vTest2" StatementType="SELECT" QueryHash="0x95D05E881A8B7175" QueryPlanHash="0x16D51AFE3D4CCA61" RetrievedFromCache="true" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="240">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="69408" EstimatedPagesCached="52056" EstimatedAvailableDegreeOfParallelism="6" />
            <RelOp AvgRowSize="227" EstimateCPU="0.0221262" EstimateIO="0.187569" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="19972" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.209696" TableCardinality="19972">
              <OutputList>
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="EmailAddress" />
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="Title" />
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="FirstName" />
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="MiddleName" />
                <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="LastName" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="19972" ActualRowsRead="19972" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="5" ActualCPUms="5" ActualScans="1" ActualLogicalReads="256" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="true" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="EmailAddress" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="Title" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="FirstName" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="MiddleName" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Column="LastName" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[AdventureWorks2008R2]" Schema="[dbo]" Table="[vTest2]" Index="[ClusteredIndex-20161025-205534]" IndexKind="ViewClustered" Storage="RowStore" />
                <IndexedViewInfo>
                  <Object Database="[AdventureWorks2008R2]" Schema="[Person]" Table="[Person]" />
                  <Object Database="[AdventureWorks2008R2]" Schema="[Person]" Table="[EmailAddress]" />
                </IndexedViewInfo>
              </IndexScan>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

In the Plan XML I can see that this is actually using the index on vTest2 for both queries.

Why is the SELECT * FROM dbo.vTest1 using the index defined on dbo.vTest2?

PS. The queries are:

USE [AdventureWorks2008R2]
GO

SELECT * FROM dbo.vTest1
SELECT * FROM dbo.vTest2
4
  • 1
    Any queries that the indexed view can satisfy can also be chosen by the optimiser. Cool, eh?
    – Philᵀᴹ
    Commented Oct 25, 2016 at 20:30
  • Is that what it's doing? If so, that is indeed cool... also means that adding an index to a table could affect execution of sprocs which have no relation to that table! Hope I never have to track a bug caused by that down.
    – BanksySan
    Commented Oct 25, 2016 at 20:31
  • Underlaying SQL Server engine converts views into their definitions and he's smart enough to discover that you have two identical views. Commented Oct 25, 2016 at 20:50
  • What version of SQL Server are you on (2012,2014,2016)? Also is it Enterprise or Standard?
    – Greg
    Commented Oct 26, 2016 at 3:37

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.