I am trying to evaluate performance of indexed views. So far results are very negative. Here is one of the examples: I have 3 tables: Tenant(Id, Name, etc), Service(Id, Name, etc.) and intermediate ServiceTenant(Id, Tenant_Id, Sercice_Id) for m:m between Service and Tenant. 10000 records in Tenant, 200 in Service, 20000 in ServiceTenant. Id column is PK for ServiceTenant. I deliberately don't have index on other columns in ServiceTenant. Here is my view and clustered index: create view [dbo].[vwTenantService] with schemabinding as select t.id tenant_id, s.id service_id, t.name tenant_name, s.name service_name from dbo.[Service] s, dbo.serviceTenant st, dbo.tenant t where t.id = st.tenant_id and st.service_id = s.id CREATE UNIQUE CLUSTERED INDEX ixuvwTenantService ON vwTenantService(tenant_id, service_id) I run 3 queries: select * from vwTenantService where tenant_id = 72057594037934470 -- indexed view is not used -- CPU is 15 select * from vwTenantService with (NOEXPAND) where tenant_id = 72057594037934470 Ã¢â‚¬â€œ indexed view is used, index seek Ã¢â‚¬â€œ CPU is 0 select * from vwTenantService where service_id = 72057594037928102 Ã¢â‚¬â€œ- indexed view is used - index scan First query doesn't use indexed view. It uses index scan on ServiceTenant, than inner join nested loops with IndexSeek on Service. Index scan cost is 0.17 + other operations. Second query is forced to use index view, it's index seek. Cost is 0.003 Third query is using index scan on indexed view. Why indexed view is not used for first query? It's much cheaper. I run enterprise edition (you can see that third query uses indexed view w/o NOEXPAND.