August 17, 2021 by Kenneth Fisher
tldr: No. You have to use sp_refreshview.
One of the only good things to come out of all of the lockdowns this last year is the opportunity to attend presentations at user groups that aren’t in your area without traveling. So recently I “went” to Stored Procedure Optimization Techniques presented by Kimberly Tripp (blog|twitter) at the Ohio North Database Training group. It was a great session and at one point she mentioned using sp_recompile. Which in case you didn’t know can be run against a table. Obviously a table doesn’t get compiled, but when you do this every stored procedure associated with the table is marked for recompile. Now, another fact you may not know, if you put SELECT * in a view, and change any of the tables associated with that * the changes won’t take effect until you run sp_refreshview. If you want some detail I wrote about this here.
So, now the question comes in. If I run sp_recompile against a table, will that also cause the associated views to be updated?. So quick experement.
-- Create test objects. CREATE TABLE test_table (id int, col1 varchar(10)); GO CREATE VIEW test_view AS SELECT * FROM test_table; GO SELECT * FROM test_view; GO
-- Change the table and see that the view hasn't changed. ALTER TABLE test_table ADD col2 varchar(10); GO SELECT * FROM test_view; GO
-- Run the test and see that still, no change in the view. EXEC sp_recompile test_table; GO SELECT * FROM test_view; GO
-- And to actually change the view we run sp_refreshview. EXEC sp_refreshview test_view; GO SELECT * FROM test_view; GO
That was a nice and simple test right? And I should point out that with the exception of some formatting cleanup this is the exact test code I ran that night. So you might be wondering, given that it came up negative why am I sharing?
Running your own experiments should be normal!
A while back a friend of mine pointed out that those of us who regularly share technical content should be careful about the code (and ideas) that we share. What we share can, and does, become part of what others use.
I’ve decided to share more of these tests, successful or not. Hopefully this can help show to others that while asking questions is perfectly ok, so is running your own simple tests. In this case, I actually did both. I asked Kimberly, and then when she wasn’t sure herself, I generated and ran my own simple test.