Index does not apply to indexed view

Question

I have an indexed view, but when I run queries in that view, the index that is built on the view is not applied, and the query is executed without an index. Below is my dummy script: Tables + View + Pointer in view

CREATE TABLE P_Test ( [PID] INT IDENTITY, [TID] INT, [StatusID] INT ) CREATE TABLE T_Test ( [TID] INT IDENTITY, [FID] INT, ) CREATE TABLE F_Test ( [FID] INT IDENTITY, [StatusID] INT ) GO INSERT INTO F_Test SELECT TOP 1000 ABS(CAST(NEWID() AS BINARY(6)) %10) --below 100 FROM master..spt_values INSERT INTO T_Test SELECT TOP 10000 ABS(CAST(NEWID() AS BINARY(6)) %1000) --below 1000 FROM master..spt_values, master..spt_values v2 INSERT INTO P_Test SELECT TOP 100000 ABS(CAST(NEWID() AS BINARY(6)) %10000) --below 10000 , ABS(CAST(NEWID() AS BINARY(6)) %10)--below 10 FROM master..spt_values, master..spt_values v2 GO CREATE VIEW [TestView] WITH SCHEMABINDING AS SELECT P.StatusID AS PStatusID, F.StatusID AS FStatusID, P.PID FROM dbo.P_Test P INNER JOIN dbo.T_Test T ON T.TID = P.TID INNER JOIN dbo.F_Test F ON T.FID = F.FID GO CREATE UNIQUE CLUSTERED INDEX [PK_TestView] ON [dbo].[TestView] ( [PStatusID] ASC, [FStatusID] ASC, [PID] 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 run the following queries, the index [PK_TestView] is not applied:

  SELECT PStatusID , FStatusID , PID FROM [TestView] SELECT PStatusID , FStatusID , PID FROM [TestView] WHERE [PStatusID]=1 SELECT COUNT(PStatusID) FROM [TestView] WHERE [PStatusID]=1 

Can you help me fix this?

Answer 1

You need to use the NOEXPAND hint. SQL Server will not consider mapped indexed views without this (even if the query name is specified in the query) unless you use the Enterprise Edition engine.

 SELECT COUNT(PStatusID) FROM [TestView] WITH (NOEXPAND) -- this line WHERE [PStatusID]=1 

This should give you the first, much cheaper plan.