Experiment: Does sp_recompile on a table update associated views.

2

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.

2 thoughts on “Experiment: Does sp_recompile on a table update associated views.

  1. […] Kenneth Fisher puts on a lab coat and safety goggles: […]

  2. jeffmoden says:

    Really good article, Kenneth. I hope people “get it”. I’d also like to add a bit to it because it’s so very important.

    The principle of this nice simple article cannot be stated enough. It’s one of those things that has driven my life in SQL Server, databases, and software development in general. It has also saved me many times in areas that have nothing to do with software.

    The concept expressed in this article is also something I learned from my dear ol’ Dad long before the internet became a reality. He told me that “Half of all that is written is wrong and the other half is written in such a fashion that you can’t tell”. This is especially true on the internet. As someone else once said decades ago but is still very true, “The internet is a well paved on-ramp to a dirt road”.

    All that brings us to the final paragraph is this article and, again, it is a foundational principle behind all good development that everyone should learn especially since there are so many “experts” to “learn” from on the internet (words quoted to emphasize the sarcasm in that statement)…

    “One good test result is worth a thousand expert opinions”.

    That’s also brings us to what I call “Holy Grail” articles that include concept, descriptions, readily consumable test data, a test harness, and test results. Be careful in accepting what they say and the conclusions the seem to come to because not all tests are actually good tests and the reason is that the test data itself can be faulty.

    A good example of this is the people that supposed “prove” that XML string splitters are faster than Tally-based splitters. They absolutely are not but the test data makes it appear so. A lot of people don’t actually know how to make test data and so they’ll create 1 to 5 or so rows and, using one method or another (GO N seems to a a favorite), they’ll duplicate those 5 rows until they have what seems like enough rows to do a performance test with.

    What they don’t realize is that the cardinality of the test data is somewhere between only 1 and 5 and XML can and does extreme advantage of that fact where a Tally-based splitter does not. So, when you do the same testing as in the “Holy Grail” article, it looks like you’ve found a great solution and then you put the code into production and BOOM! The code suddenly takes dozens of times longer than it did in test.

    So, yeah… I agree. Running YOUR OWN experiments should be the normal but you also need to be very aware of the “Devils in the DATA” that can seriously lead you astray whether it’s based on someone else’s tests or your own.

    Learn how to test and learn how to create oodles of test data that actually and accurately simulate the data you have in production.

    Thanks again for writing this simple but super important article, Kenneth. Well done!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,569 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: