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"
2 Comments
Comments for this entry have been disabled.