MSSQL: Recompiling views and stored procedures

Posted by Dan on Jul 11, 2007 @ 10:57 AM

Occassionally you come across something in a language you never knew existed, but always needed. Today I discovered a stored procedures included in MS SQL that I didn't know existed, but in the past have just used work around for.

Today I discovered the sp_refreshview stored procedure which updates "metadata for the specified non-schemabound view." In plane english that means if you've made changes to your tables that invalidates the view, it should refresh the view (this is problem if your view uses a select * from table—which should be avoid at all costs.) In the past I've always just done an ALTER VIEW to refresh the view—not very elegant, but it worked when I needed the view to pick up changes in the schema. This stored procedure provides to do the same thing.

For a simple explanation of the sp_refreshview see Vadivel's post on "Sp_refreshView explained..."

Another useful stored procedure in the same vain is sp_recompile. This stored procedure will recompile a stored procedure or table to optimize it's query plan. Over time if tables have had mass changes to them, the cached plans that your stored procedures use can become in effective. Using this stored procedure will force a recompile the next time the stored procedure runs. This can really crop up if you're doing a lot of bulk table importing (like via BCP or DTS.)

For a simple explanation of the sp_recompile see Pinal Dave's post on "SQL SERVER - Recompile All The Stored Procedure on Specific Table"

Categories: SQL, HTML/ColdFusion

2 Comments

  • Your post says sp_Recompile will recompile a stored procedure, trigger, table or view...I don't believe that's true, is it? Books Online says "Causes stored procedures and triggers to be recompiled the next time they are run". Also, if you look at the code within sp_Recompile, it does an explicit check to see if the 'IsTable' property = 0 or 'IsExecuted' property is 0...doesn't that kind of rule out anything other than a table or a SP/Trigger?
  • @Number2:

    You are correct, the sp_refreshview updates views--not the sp_recompile.

Add Comment

Leave this field empty