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
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):
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?