Thank you for an interesting article!
The company I work for is still using SQL Server 2000. I tried the example and it seem like the NOEXPAND just gave an extra overhead to the query. It didn't expand without the tag, and with the tag it took a little bit more resources. Without NOEXPAND total cost was 0.0634, with NOEXPAND it was 0.0642.
My question is if anyone knows the best practice for SQL Server 2000? Shall I use NOEXPAND to be prepared for a possible migration to SQL Server 2005?
All of my testing was on SQL Server 2005 and 2008 (and all my references are specifically for 2005), but I believe the same things apply to 2000, and if you are using something other than Enterprise Edition you likely want to use noexpand where it is appropriate.
As for the time trials, make certain you are testing them in separately and not in a batch, otherwise the caching, execution plan generation, etc will cause one portion to affect the others. When I try running them together in one batch, my results are inconsistent with it coming out faster with the noexpand sometimes and without other times. When I run them separately the one with noexpand comes out slightly, but consistently better. Of course, I kept these sample sizes small, if you increase the table sizes the difference becomes much more marked.
To provide an anecdote, I once reduced a query that was being run routinely from taking over an hour to taking less than a minute by indexing the view and adding with (noexpand). But that was a very large query which needed several joins against large tables.
(edited to correct a typo)