Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
    
Is this reproducible in a small example that you could post here? Or does it only happen with this database/view/sproc/etc? –  Jon Seigel Oct 9 '13 at 1:09
    
@JonSeigel - I'll see if I can reproduce a small example when I get a chance. I've only ever seen this twice in over 5 years, and both times someone removed the index from a view (different views), so there may be other factors helping to cause it. –  Paul Kroon Oct 9 '13 at 11:50

1 Answer 1

This is because the indexed view is schema bound . You should recreate the view.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.