Thanks for this article, sknox. It's disconcerning to see that, apparently, such grave misconceptions about indexed views exist.

In the introduction, you write that someone told you that NOEXPAND is not availabla as of SQL Server 2008R2. I don't know who wrote that and where he/she got this information, but I can tell you that this is completely wrong (trying hard to avoid stronger language here). The NOEXPAND hint is fully supported in all versions, including SQL Server 2008R2 and SQL Server 2012. This hint is also not documented as being deprecated, so there are no plans to remove support in a future edition. And to the best of my knowledge, there are no plans to deprecate this hint in a future version.

I also want to provide some feedback on a few minor points that I noticed in your article.

At one point, you write: "the percentages aren't definitive because of the random nature of the data I'm using" - I just wanted to point out that the percentages are NOT a measure of the actual cost of queries. The percentages shown in the execution plan are always based on the estimated execution plan (even if you are running the query with the option to include the actual execution plan). You can see some of the differences between estimates and reality by hovering your mouse over various parts of the execution plan, but neither total cost of an iterator or entire query, nor query cost relative to the batch support that - the most important measures that you can get an actual vs estimate on are row counts for all of the data streams (arrows), and both row counts and numbers of executions for iterators.

The percentages on the execution plan are often a good measure, but the caveats are that (1) if the optimizer miscalculates because of incorrect assumptions or outdated stats, you won't see it; and (2) they are based on query cost, which is based on assumptions that might no hold for you (e.g. when your database is already in cache - the query cost calculation is based on the assumption that most I/O will be non-cached; or when elapsed time is the only thing that's important to you, even at the cost of increased I/O; or ......)

When I compare perfomance of queries, I always use SET STATISTICS TIME to check elapsed time (or CPU time if that's what I want to minimize), and SET STATISTICS IO to check how much I/O the query used. Or I use extra code to put the datetime at the start of the query in a variable, then calculate the DATEDIFF in milliseconds at the end of the query and return it to the client or save it in a table for later analysis. (The latter method is very useful when I let the computer run overnight to run lots of long-running tests, or to repeat the same test multiple time to average out random spikes).

In your conclusion, you mention some conditions where an indexed view will not be used. This is absolutely true for queries with no hint running on Enterprise Edition. But when you use the NOEXPAND hint, then (in ANY edition!) SQL Server no longer has any option - the term hint is extremely misleading; it is the same kind of hint parents give to their children to clean up their rooms: "you might want to ..." - but if you don't, they'll just ground you for a week. If you use the NOEXPAND hint, the optimizer WILL choose a plan that uses at least one of the indexes defined on the view - even if other plans would have been cheaper.

Finally - I just saw the link to where you found the misinformation about the NOEXPAND hint. I tried to comment on the article, but I was forced to log in to some site I've never heard of and I am still contemplating if this is worth getting yet another account for. I also checked a few other articles on that site by the same author, and I can only conclude that this man is dangerous. He has no idea what he's talking about, and yet dares to write in an authorative tone, happily spreading misinformation all over the world. Please, whenever you rean an article by him, double check other sources before assuming that he's right.