Can there be situations where using (noexpand) on an indexed view is undesirable? From the past experience using the schema bound and indexed view in a query with (noexpand) hint usually dramatically improves performance if the view is designed well.
Apparently using the indexed views is not implicit, as it requires a table hint.
But can there be any scenarios where supplying (noexpand) hint may be undesirable while the view remains schema bound and indexed?
In other words, should schema bound indexed views be &quot;not expanded&quot; implicitly at all times instead of a developer having to remember to supply the hint to turn off view expansion?
The way I read BOL it states that Enterprise is supposed to imply (noexpand). But I know for certain that it does not, as I am on Enterprise and adding (noexpand) provides dramatic performance improvement, it never failed to. So it is not correct to state that noexpand is an Enterprise feature.
Firstly, NOEXPAND is an enterprise edition feature of SQL Server, that is why you have to specify it manually if you are not on this edition. 

Based on the optimizer's prediction there may be a situation, where NOEXPAND would be harmful. But it's pretty rare and most likely NOEXPAND isn't the issue in such cases. Most likely the issue is poorly chosen indexes. For instance, an index on a column with low cardinality. 

Additionally, have a look at this noexpand hint for non-enterpise edition and performance  post as it answers the rest of your questions. And also provides you with a workaround.