In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

• Can indexed views still help me in this scenario somehow?
• Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

EDIT: On the duplicate question - I'm more interested in the GROUP BY and aggregate aspect of indexed views. The answer helped me find a stupid mistake I made, it's now working for me as well.