On a SQL Server 2005 Enterprise edition server, an index has been dropped from a view. When running a select * query that includes this view, no results are shown. In addition, the execution plan references this index on the view (that no longer exists). By adding the
EXPAND VIEWS option to this query, the correct results are returned.
We have tried to clear the cache plans with
DBCC freeproccache, but the problem persists (the fresh cache plan still refers to this non-existent index). We have seen this problem before, and a restart of the SQL service corrected it, but I would like to find out the actual cause before we attempt that again.
Edit: I have tried sp_refreshview, with no change. The SQL build version is 9.00.3042 (SP2).