I have the following version of SQL Server:
Microsoft SQL Server 2014 (SP3) (KB4022619) – 12.0.6024.0 (X64)
Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
I created the following view
CREATE VIEW [dbo].[vwGroupsOfficesDependencies] WITH SCHEMABINDING AS SELECT GC.IdGroup, COUNT_BIG(*) AS countBig, OD.IdOffice FROM dbo.Group AS GC INNER JOIN dbo.GroupDependencies AS GD ON GC.IdGroup = GD.IdGroup INNER JOIN dbo.OfficeDependencies AS OD ON OD.IdDependency = GD.IdDependency INNER JOIN dbo.Dependencies AS D ON D.IdDependency = GD.IdDependency WHERE (D.Active = 1) GROUP BY GC.IdGroup, OD.IdOffice
Then I created the clustered index
CREATE UNIQUE CLUSTERED INDEX [IX_vwGroupsOfficesDependencies_IdOficeIdGroup] ON [dbo].[vwGroupsOfficesDependencies] ( [IdGroup] ASC, [IdOffice] 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)
But when I run the query
SELECT IdGroup, IdOffice FROM dbo.[vwGroupsOfficesDependencies]
I see in the execution plan that it does not use the indexed view, it performs the joins with the tables inside the view. But if I include the hint WITH(NOEXPAND) it uses the indexed view.
I read that I must include WITH(NOEXPAND) for non Enterprise version, but in my case it is not necesary. Why it is not using the indexex view?