Recently I talked to someone about a view calling another view, a practice which is known as nested views. TL;DR Do not write Nested Views. Give this person a little credit, he had heard it was a bad idea, but he had a really good reason. There is no such thing. There is also no reason to take my word for it. Here are a number of other very good posts which detail why nested views are bad from a performance perspective.
7 sins against TSQL Performance
Nested Views Causing Query Performance Woes
Poor Little Misunderstood Views
Proving Views Can Hurt Performance
T-SQL Anti-Patterns: Nested Views
I understand that on the internet, no matter how cockamamie your idea is, you can find someone or some post which will agree with you. That is why I trust sources who I know and trust rather than the random chatter of places like Stack Overflow.
Non-Performance Reasons Not to Use Nested Views
In addition to the performance reasons, there are other reasons not to use nested views. Supporting and maintaining nested views can be a nightmare. If there is an issue with the accuracy of the data, finding the problem is just that much harder. And what about when one needs to go investigate or fix a problem? Then someone has to test all of the places the view is called. I worked with some code recently where a view was created on a set of data just to create 3 new values based up 3 case statements. That view was nested many levels below where the field was actually called. I know people from a development background look at nested views as modular development. Don’t. TSQL is not the same as object oriented code.
Good Use Cases for Creating Views
Views are not always a bad idea. If you are creating an Analysis Services or Power BI project, it is a good idea to create views of the tables you are going to be using, even if there is no difference between the view and the table. Why? That way if the table is changed, your project will still work. Exposing a single view to users who are going to use self-service BI against it can also be a good idea. None of these are reasons to nest views, ensuring that the source data is anything other than a table.
If all you have is a Hammer Everything Looks like a Nail
It takes a while to learn how to write good SQL and I have seen people rely on a view because it takes a while longer to write good SQL. Relying on a view though is like having one tool in the toolbox. If you don’t have a screwdriver, one may be tempted to use a Hammer instead. Too often nested views are written because sometimes writing SQL is hard and it is easier to just write a query and save it as a view rather than use a CTE or a derived table. Do not succumb to the temptation. There is a lot of code written where someone as in a hurry and they were planning on going back and fixing it someday. Someday appears with the same frequency as a lottery. Make it easier on the person who has to figure out or maintain the code left in your wake. Don’t write nested views. Your replacement will thank you for it.
Yours Always
Ginger Grant
Data aficionado et SQL Raconteur
Nice post! If I may make a shameless plug, I hate nested views so much that I created a free community tool to help unravel them!
https://sqlbek.wordpress.com/tag/sp_helpexpandview/?order=ASC
If you’ve never checked it out, I’d love to hear what you think and any feedback you may have on how I can improve it. Cheers!
Andy —
I can’t believe that I hadn’t see your post and view unravel tool. This is a cool piece of work on your part. I hope this helps other people find your link as I wish I had known about it the last time I had to unravel views. Thanks so much for sharing.
–Ginger
Pingback: Nested Views – Curated SQL
I don’t see any of those links supporting such a broad statement as views calling views being a bad idea. Several of the links specifically say it’s only an issue if used wrongly and the other examples seem to depend on the database missing its foreign keys so table elimination can’t come in to play.
You obviously need to know what your doing and avoid certain types of views but there’s not a lot that can go wrong when your views are on a single table (e.g. to filter out logically deleted or archived date), you’re running simple joins across foreign key relationships or the joins in the view are fundamental to its purpose so can’t be avoided.
Barney —
The links provided show that nested views do not perform well. Looking at the execution plans of nested views in particular in this post T-SQL Anti-Patterns: Nested Views
shows just that.
Nesting views causes issues with troubleshooting and maintenance over time, which is why they are not a best practice and should be avoided. There are some very good reasons to write views, there are not very good reasons to write nested views. Its not about what can go wrong, it is about writing code that is easy to maintain, execute and understand by not just the person who wrote it, but everyone later on who needs to maintain it. Much of the time nested views are created because it is easier to write at the time, not because it is a good idea for any other reason.
–Ginger
Barney – Interestingly enough, there isn’t that much written out there, discussing in depth why nested views are a poor design pattern. That is one of the reasons why I have developed a new SQL Saturday session, that explores this in depth.
One critical performance detail that isn’t well documented, is how cardinality estimation becomes horrifically inaccurate, with each additional layer of nesting that is introduced. And poor cardinality estimation leads to terrible execution plans. I also discuss internals of the Query Optimizer and discuss how it is an optimizer whose goal is to generate a plan that is “good enough,” not necessarily the “best” plan.
The long and short of it, is that there are some very vicious performance pitfalls of employing nested views that dramatically outweigh the “code reuse” benefit. It is a non-trivial trade-off.
I inherited a nested SQL View situation once and it was indeed very painful, both to understand and from a performance perspective.
Tim —
Thanks for your feedback and the links. I hope that other people can benefit from your pain.
–Ginger