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.
JOYOUS ADDENDUM: Now that I got it working, I successfully tested that it even works in cases where the query contains a left join in those cases where the left join can in fact be optimized away (ie. the on-clause covers a unique index in the joined table).
That is really awesome, because it means that even in cases of a query with left joins one can design the schema in such a way that getting a total row count of everything or specific groupings is fast.