Something interesting would be the new challenge of keeping up with the many views that can now be created against a single table. Such as to display one set of columns to one department, another set to another, one for updating , another one with joins,...etc... Do you have a set naming convention that you use to distinguish the views at the least by what their purpose is? Thanks, and good article.

There is a general misconception of the following statement that you make.

"In this case SQL Server will still have to join all the tables in the view to return the data from a single table"

In the "Enterprise" edition, the query optimizer is smart enough to know that the other tables are not needed and does NOT include them in the query plan. You can easily verify this by generating the "Actual Execution Plan", where you see that the unused tables ar not included in the plan.

With this in mind, generating complex views with many table joins can be defined once (efficiently and correctly) and then used specifying only the desired field.For example.

The benefit is that this on ONE VIEW can serve many query situations efficiently and using the best query planwhile hiding the specific internals of the joins. If the underlying table structures cjange and some of the joins have to be changed, only this ONE view would change.

This is a very UNKNOWN feature that many people have misunderstood or had the misconception for a long time (myself included).

As a general rule I do not like views and I discourage developers from using them. My position is based on experience back to SQL 6.5. I find that developers adopt Views as a crutch for every problem rather than a tool for specific problems.As a system grows, morphs, and ages the number of, and nesting level of views tends to expand beyond any reasonable limit. The 6.5 system I mentioned had views nested more than 6 levels deep and often the lower level views had no where clause.I have recently joined a team whose in house application suite also uses many views. Again, they are often nested several levels (I have found 3 levels) and typically have significant, undocumented business logic embedded in the column specs.I have a list of "Why do you need that" questions I pose to developers. Select Distinct and Views are near the top of the list.

BTW, IMHO Code Reuse is not sufficient justification for choosing a tool with so much potential for trouble.

Like you, I did not like views for many years until I really knew what was going on under the hood and confirmed the the benefits for which they'r inteded. A lot has changed since SQL 6.5 so the topic deserves a seconf look into how it works now.

What you're describing has more to do with improper use of views by developers than the views themselves. Nesting views is a BAD PRACTICE and should not be done period.

Views should be looked at as "templates" that will tipically include all the possible fields and joins that could be related with the inderlying data. If you include everything in the complex view, there is no need for nesting, it's all included to begin with.

For example, let's assume that I don't want to use views. I could use the QUERY of the example view above as a starting poit, or as a "template". If I were to create a query for any of the scenarios I mentioned earlier, I could copy the code of the full query of the view and then ELIMINATE the fields and the JOINS that weren't necessary and end up with an efficient query that's properly done and no excess baggage.

Internally, that's EXACTLY what the query optimizer does giving the EXACT SAME result without having to copy and paste and most importantly, if this is done in multiple stored procedures, I don't need to repeat it multiple time. This becomes much more important in "Maintaining" the code later on.

I don't look at it as code reuse, I look at it as avoiding doing the same thing over and over again.

Hi Jay,It seems that I did not get my point across. I completely agree that Nested Views are a BAD THING. And I understand your comments about templates, etc. I guess we could debate the semantics of "reusable code" through several rounds at a good watering hole.

My point is that application developers tend to think in coding terms. Functions, methods, routines, loops, sequences, etc. They do not tend to think in terms of sets and set operations. I have had conversations with developers at several organizations including internal and third party. Most of them see no difference between creating a nested view and inheriting or overloading a method. And they have often and explicitly played the code reuse card to support their coding.

Triggers, Views, User Defined Functions, SQLCLR, etc. are all powerful tools that can contribute to the solution when used properly. Unfortunately many application coders (VB, C#, etc.) jump on one of these tools an apply it to all problems.

I did not mention it in my original reply but I have seen (and am dealing with) extensive use of triggers to enforce referential integrity and I have found triggers referencing nested views.

I think the problem is as much caused by inexperience and the pace of development and change. I made the same mistake when table variables were first introduced. I thought they were THE SOLUTION and immediately began removing classic temporary tables from all of my scripts. It did not take long to find out that it was not such a good idea in all cases. Yet today, after nearly 10 years I still encounter developers who are convinced that Table Variables are a better choice in ALL CASES.

Also as a post done here about 4-5 months ago they cover partitioned views, which as Jay posted contradicts the post you have done about querie all tables on the views, also I think you overlook the encryption of views as a feature.

enriarg (4/3/2014)Also as a post done here about 4-5 months ago they cover partitioned views, which as Jay posted contradicts the post you have done about querie all tables on the views, also I think you overlook the encryption of views as a feature.

Again, I did not say views are not a useful tool. I am currently implementing partitioned views. I also have created triggers and I use Cursors (a lot). I use Temp Tables, Table Variables, and any number of other specialized tools. I run SQL Profiler (both GUI and Scripted). I don't understand what there is in my comments that gives you the impression I am not aware of the capabilities and uses of views.

What I am saying, is that application developers (3GL and 4GL programmers VB, C#, C++) tend to jump on views and functions as quick, easy, Code ReUsing, solutions to problems. And then the views tend to proliferate and spawn new views. In that sense they are bad. And the disease is hard to cure once it is in place.

Here's another example. When I ask a developer why they used "Select Distinct" it is not because I don't think Distinct is a useful tool. It is because, over the years I have seen a number of very significant query logic, database design, data quality errors covered up by Select Distinct.

I posted a reply ONLY to clear up a comment that was made in the original article, and mainly because most people actually consider the comment in the article as accurate, when in fact its not.

I know that there are many places where developers are allowed too much freedom to make database decisions which completely go against "Best Practices". Any object (Table, index, View, stored procedure, trigger etc.) that is implemented in a database should at least be reviewed and authorized by a competent database developer before it's implemented in a database. Otherwise, the database will eventually experience the kinds of problems that Ray is mentioning.

I believe that the articles and forums should be to talk about the way things should be done right, following "Best Practices" and how the tools and techniques can help to improve when used properly. I think we have all seen plenty of "not so good" code and probably have done some ourselves before we discovered better ways to do things, its all part of the learning process and that's why we're here to begin with.