SQL Server View Problem

Question

We have a View (call it X) that is the base view called by 2 other views(call them Y and Z).

Today we made a change to view X, after that view Y and Z started bringing back data that was incorrect. When we were in Management Studio and ran Select * from Y(which is exactly how the view is called in in code) it would get back data that was incorrect. However, when we ran the actual SQL that the view contained it was fine. We tried a number of things until a colleague suggested adding a space to view X and Z and then running Alter, which worked. Everything returned to normal and ran fine.

My question is: Does MSSQL cache its views? and if so how do you force them not to OR force them to re-compile?

Also, any additional reading about this would be helpful.

Answer 1

SQL Server does not cache view data (at least, not in the way you are referring to it).

If a view definition contains 'SELECT *', then the actual column list is defined when the view is created, i.e. the 'SELECT *' is replaced by the actual column list than exists at the time you create the view. That means if you add columns to underlying tables referenced by that view, that won't appear in the view.

What I've done in the past is write some T-SQL that automatically refreshes all views. I don't have it to hand but I'll see if I can dig it up.

Answer 2

See the sp_refreshview command.