I did not know SQL Server Views are Static

16 Comments

• That's bitten me before, too.
  
  
  
  Whenever I add column to a table that a view should now include, I just edit the view then save it, forcing it to recompile what columns are included in the "*".
  
  
  
  But the question remains, what's up with that behavior? It's reasonable to say that for performance reasons it needs to compile the query (leaving it static). It's also reasonable to say that for a small percentage of the cases that it is too computationally expensive to cascade a change in a table all the way down to all compiled views. Just my guesses.
  

• Isn't this just an issue with enterprise manager?
  
  
  
  Does the samething happen in Query Analyzer when altering a view?
  

• Creating my view in Query Analyzer as "SELECT *" will still take a static snapshot of the fields, instead of using "SELECT *" as I intended. And the system tables will still reflect that I created my view as "SELECT *" -- which isn't really what I have once I add a new field. But if you mean that Enterprise Manager can lull you into thinking you've updated it when you press the OK button then you are correct.
  

• I've seen the same issue in SQL Server and Sybase. It affects stored procs as well - try adding a test column to a table, create a proc that references that column and then go back and delete it - the delete of the column doesn't complain that there are other "objects" referencing the column, but it will fail when you call it of course.
  
  
  
  I don't know for sure, but it looks like it doesn't do any type of checks for referring objects... as for your issue I would think the view got compiled and it replaced your * with a real column list which I think would be more efficient and since it doesn't do any type of reference checks it didn't recompile the view. Only options are to not use select *'s or to always remember to look at the dependencies on a table and "recompile" as necessary... neither of which are as intuitive or as easy as a select *...
  
  
  
  As a side note there is a similar issue if you refresh a table from say a production instance to a development instance (at least on Sybase) where it will appear to work and the table will be there with data, but none of the referring procs/views will be updated with the "new" table object so they'll all fail until you recompile them with no changes...
  

• It is so good to know I am not crazy (still sorry for your pain). This happened to me a couple of years back and I've never used a View since.
  

• Cached execution plan?
  
  
  
  "sp_recompile targetObject" will invalidate all execution plans referencing the target, and cause them to be rebuilt.
  
  
  
  So "sp_recompile myModifiedTable" would rebuild (on the next execution) the plan for the View, any referencing Stored Procedures, and cached Queries.
  
  
  
  I'm not positive it's a problem with the execution plan, but trying sp_recompile would verify that one way or the other.
  

• Sam's post reminded me - if you don't need it to cache the execution plan, you can include "WITH RECOMPILE" in your proc and it will recompile it each time... should make it handle the table changing...
  
  

• When you save a view, I believe it adds entries to syscolumns for the fields at the time of view compilation. Like a stored procedure, it also stores the text version of the script that actually created the view in the syscomments table. sp_recompile will cause all cached execution plans to be dumped, and stored procedures will get recompiled on next use, but I am not sure if views get cached in this manner also.. as they really do not contain a set execution plan (different SARGs and joins on a view will produce differring plans). I have also seen cases of sp_recompile not being able to really locate all dependencies. There are some situations where it just cannot do so.
  
  
  
  I am pretty sure its just the metadata for the view being saved at compile time that causes this behavior. It would be horribly inefficient if the server had to recompile the metadata every time a view is referenced just to see if any fields were added (the idea behind a view is that it is static enough of a query component that you can store it server-side and use a synonym for the underlying complexity - it's not supposed to change).
  
  
  
  P.S. It's just not good habit to use "select *" in any permanent SQL. I weaned myself off of that years ago, and rarely even use it when doing ad-hoc queries in QA.
  
  

• Yeah, I've found that the SQL behind a view needs to be recompiled. You can do this in Query Analyzer by choosing to alter the view in the object browser and execute it. This is much better than dropping and re-creating a view as you don't lose the permissions associated with it.
  

• I have a better one: try doing this: Add a field IN FRONT of existing fields, and pick a totally different data type.
  
  
  
  Any of the views that use SELECT * FROM [Table] exhibit the odd behavior of returning the original column's data transposed into the new column's data.
  

• Views are created, well, when they are created. Specifying all fields (*) results all fields at creation time.
  

• You might try compiling the View with SCHEMABINDING. This will at least remind you what views need to be recompiled when you change the schema of the underlying tables.
  

• When I change the underlying tables I use the "sp_refreshview" system sproc.
  

• What's up with developers using SELECT *? Do you guys have lazy habits in the language that you code in? Why do it in T-SQL? SELECT * is a performance hit. Regardless of how minimal it is, it still is a hit.
  

• Good question Matthew -- I don't usually do this, and certainly my mapper never does this, but it seemed like a simple and quick thing to do at the time, and I knew the underlying table would very likely change so it seemed "safer" to use SELECT * since I thought that would automatically work in the future too. Oh well, you're right, there's apparently no excuse for laziness! :)
  

• "SElect *" is jsut shorthand. A lookup is necessary to execute that statement to determine what columns are to be included. That's why it is a fraction slower to do an ad hoc query like that.
  
  
  
  Like others have alluded to a view is compiled the first time and since nothing changed it didn't recompile.