SQL Server Execution Plan – Clustered Index Scan with Nonclustered Index Hint

cardinality-estimatesmaterialized-viewnonclustered-indexsql serversql-server-2016

The Backstory:

I have a table with the following structure:

CREATE TABLE WideTable1
(
    BoringColumn1,
    BoringColumn2,
    CellPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see below
    PagerPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see below
    IsActive YN(CHAR(1)), -- User-defined types, see below
    BoringColumn3,
    ...
    BoringColumn350
)

The original query consuming this table was doing a few SQL functions on the CellPhoneNumberColumn and PagerPhoneNumber and then later using the output of those functions as predicates in another query. NULLIF(LTRIM(ISNULL(CellPhoneNumberColumn, PagerPhoneNumberColumn))) was one example of this.

The columns are also all user-defined types, in this case CellPhoneNumberColumn and PagerPhoneNumberColumn are defined as UDT Phone(VARCHAR(20)), so their underlying data type is VARCHAR(20). And IsActive is defined as YN(CHAR(1)) so it's actually a CHAR(1).

Additionally to complicate things further, the collation of this table / database is Latin1_General_BIN.

Long story short, the original consuming query was running into Cardinality Estimate issues.
In an attempt to alleviate the issue, I created an index view on the above columns and SQL functions being applied to them with the following definition (*note I didn't create this original logical, just trying to fix the performance of it):

CREATE VIEW PhoneNumbersNormalized WITH SCHEMABINDING AS

SELECT 
    NULLIF(LTRIM(ISNULL(CAST(CellPhoneNumberColumn AS VARCHAR(20)), CAST(PagerPhoneNumberColumn AS VARCHAR(20)))) AS Cell,
    SUM(CASE WHEN CAST(IsActive AS CHAR(1)) = 'Y'THEN 1 ELSE 0 END) AS IsActive 
FROM dbo.WideTable1
GROUP BY NULLIF(LTRIM(ISNULL(CAST(CellPhoneNumberColumn AS VARCHAR(20)), CAST(PagerPhoneNumberColumn AS VARCHAR(20))))

I also created the following indexes on the indexed view PhoneNumbersNormalized:

CREATE UNIQUE CLUSTERED INDEX IXV_PhoneNumbersNormalized_Cell ON dbo.PhoneNumbersNormalized(Cell)
CREATE NONCLUSTERED INDEX IXV_NC_PhoneNumbersNormalized_Cell_IsActive ON  dbo.PhoneNumbersNormalized(Cell, IsActive)

The Problem:
When I select from the indexed view PhoneNumbersNormalized with the Actual Execution Plan included, I noticed the execution plan specifcally mentions the original underlying table WideTable1 as where the data is coming from.

Furthermore if I select from indexed view PhoneNumbersNormalized with an indexed hint on the nonclustered index I created above IXV_NC_PhoneNumbersNormalized_PhoneNumber_IsActive the execution plan shows no mention of this nonclustered index being used, instead it says a clustered index scan is what it's doing instead (note I obfuscated the original table name, it's not actually called WideTable1 on my server):
Execution Plan

Pasted Execution Plan: https://www.brentozar.com/pastetheplan/?id=HJytxh2UP

Why is the execution plan always showing the original underlying table instead of the indexed view, and always using the clustered index on the underlying table even when my select query on the indexed view uses an index hint to force the use of the non-clustered index?

Best Answer

Why is the execution plan always showing the original underlying table instead of the indexed view, and always using the clustered index on the underlying table even when my select query on the indexed view uses an index hint to force the use of the non-clustered index?

Your view definition is being expanded. You'll need the NOEXPAND hint.

Can I use the NOEXPAND hint on an non-indexed view?

From the docs: The query optimizer treats the view like a table with clustered index. NOEXPAND applies only to indexed views.

Does schemabinding a view materialize it?

Being schema bound is an object property that can apply to things other than views, like functions. You can't index a view that isn't schema bound, though.

For an example with functions, check out this Q&A:

Related Question