My initial question was How does someone determine if it is a good idea to index a view?.
Update: My underlying assumption was that indexing views speeds them up. This is apparently not sensible, since views just execute queries, as the comments below point out.
What I'm doing right now is very similar to what this guy here is doing: I'm finding views that are selecting about 2K rows out of 2 million rows, take far too long to fetch rows, and I thought that a schema bound indexed view might help.
The operation that I was trying to optimize is:
select * from vwSample where TYPE_CODE = 'X';
TYPE_CODE is a 10-character string, there are about 2K unique
TYPE_CODE values in
vwSample, and there are about 2-5 million rows in
vwSample, and there are between 2K and 10K rows for each unique value of
TYPE_CODE. I didn't post my execution plan because I was trying to ask a "general practice" question.
Update: it looks like my question is wrong-headed, I should just write and optimize queries, and ignore the fact that there's a view, maybe even just write it as a regular query and ignore this view. And there are no "turbo buttons" available in views, that can make my view faster, by magic as the accepted answer suggests. My question appears to boil down to "I'm a SQL Server
n00b and I know nothing about optimizing views, how do I speed up
select * from vwSomething where KEY=X where
vwSomething is a view?".