Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect statement.

Students

SELECT studentID, first_name, last_name, SchoolID, ... FROM students

CREATE VIEW vw_eligible_student
AS 
SELECT * FROM students
WHERE enroll_this_year = 1

Teachers

SELECT TeacherID, first_name, last_name, SchoolID, ... FROM teachers

CREATE VIEW vw_eligible_teacher
AS 
SELECT * FROM teachers
WHERE HasCert = 1 AND enroll_this_year = 1

Schools

CREATE VIEW vw_eligible_school
AS 
SELECT TOP 100 PERCENT SchoolID, school_name 

FROM schools sh 
JOIN
     vw_eligible_student s 
     ON s.SchoolID = sh.SchoolID
JOIN 
     vw_eligible_teacher t
     ON s.SchoolID = t.SchoolID

At my workplace, I have investigated one of our in-house database application. I checked through the objects found out that there are two or three layers of the view stack each other. So that was remind me about what I read in the past. Can any one help explaining it?

If it is not OK to do so, I want to know that it is limited to just SQL Server or it is for database design in general.

Additional Info: I updated an example from my company. I change a bit to be more general without too many technical (too many columns in this example). Mostly the nested view we used is based on abstract or aggregated view. For example, we have a large student table with hundred of columns. Say, Eligible Student View is based on students who enrolls this year. And student eligible view could be use other places such as in stored-procedure.

share|improve this question
2  
I would submit that the same pros and cons would roughly equate regardless of the specific platform. –  Aaron Bertrand Sep 7 '11 at 18:47
    
Related StackOverflow post. –  Nick Chammas Sep 7 '11 at 18:49
add comment

3 Answers

up vote 22 down vote accepted

Regardless of platform, the following remarks apply.

(-) Nested views:

  • are harder to understand and debug

    e.g. What table column does this view column refer to? Lemme dig through 4 levels of view definitions...

  • make it harder for the query optimizer to come up with the most efficient query plan

    See this, this, this, and this for anecdotal evidence. Compare to this, which shows that the optimizer is often smart enough to correctly unpack nested views and select an optimal plan, but not without a compilation cost.

    You can measure the performance cost by comparing the view query to an equivalent one written against the base tables.

(+) On the other hand, nested views let you:

  • centralize and reuse aggregations or business rules
  • abstract away your underlying structure (say, from other database developers)

I've found that they are rarely necessary.


In your example you are using nested views to centralize and reuse certain business definitions (e.g. "What is an eligible student?"). This is a valid use for nested views. If you are maintaining or tuning this database, weigh the cost of keeping them against that of removing them.

  • Keep: By keeping the nested views you incur the advantages and disadvantages enumerated above.

  • Remove: To remove the nested views:

    1. You need to replace all occurrences of the views with their base queries.

    2. You must remember to update all relevant queries if your definition of eligible student/teacher/school changes, as opposed to just updating the relevant view definition.

share|improve this answer
1  
+1, except I would replace "harder" for the query optimizer, with "almost impossible". :) –  Jason Sep 7 '11 at 18:46
1  
@Jason - I agree, and I wish I could link to some concrete examples. Do you know of any references that explain or demonstrate why this is so? –  Nick Chammas Sep 7 '11 at 19:09
1  
All I can find is anecdotal evidence that when nested views get used, they suffer performance problems compared to the "flattened" SQL. sqlservercentral.com/blogs/2cents/archive/2010/04/05/… The problem appears to come down to the fact that the DB (SQL Server in this case) won't apply certain filters before it joins tables, and will therefore make the query take longer than it should. –  Jason Sep 7 '11 at 19:56
2  
I disagree on the query optimizer issue, as the resulting query after resolving all views will be the same no matter how many view transformations it has gone through (except some extra columns in intermediate result sets, which the optimizer can eliminate just fine). This leaves debugging; IMO it makes debugging easier to have nested views as I can look at intermediate results to see where it went wrong. –  Simon Richter Sep 8 '11 at 0:52
    
@Simon - These are great objections. With regards to the query optimization issue, for simple nested views the optimizer can indeed pick the appropriate plan. From past experience as well as anecdotal evidence, I gather that it fails to do this for more complex views. See Jason's comment as well as this and this. –  Nick Chammas Sep 8 '11 at 1:35
show 2 more comments

Sometimes nested views are used to prevent repeating aggregates. Let's say you have a view that counts messages and groups them by userid, you might have a view over that that counts the number of users that have > 100 messages, that kind of thing. This is most effective when the base view is an indexed view - you don't necessarily want to create yet another indexed view to represent the data with a slightly different grouping, because now you're paying for the index maintenance twice where performance is probably adequate against the original view.

If these are all just nested views where you're doing select * but changing the ordering or top, it seems this would be better encapsulated as a stored procedure with parameters (or inline table-valued functions) than a bunch of nested views. IMHO.

share|improve this answer
    
"This is most effective when the base view is an indexed view." Important point. –  Nick Chammas Sep 8 '11 at 19:46
add comment

Later versions of SQL (2005+) seem better at optimizing the use of views. Views are best for consolidating business rules. EG: where I work we have a telecom product database. Each product is assigned to a rateplan, and that rateplan can get swapped out, and rates on the rateplan can get activated/deacitvated as rates are increased or modified.

To make it easy, we can make nested views. 1st view just joins the rateplans to their rates using whatever tables are needed, and returning any necessary data the next levels of views would need. 2nd view(s) can isolate only active rateplans and their active rates. Or, just customer rates. Or employee rates (for employee discount). Or business vs. residential customer rates. (rateplans can get complicated). The point is, the foundation view ensures our overall business logic for rateplans and rates are joined together properly in one location. The next layer of views give us more focus on specific rateplans (types, active/inactive, etc).

I agree that views can make debugging messy if you're building queries and views at the same time. But, if you're using a tried-n-trusted view, it makes debugging easier. You know that view has already been through the ringer, so you know it's most likely not causing the problem.

Issues can come up with your views, though. "what if a product is associated only to an inactive rateplan?" or "what if a rateplan only has inactive rates on it?" Well, that can get caught at the front-end level with logic that catches user errors. "Error, product is on an inactive rateplan... please correct". We can also run query audits to double check it before a billing run. (select all plans and left join to active rateplan view, only return plans that don't get an active rateplan as problems that need to get addressed).

The good thing about this is the views let you greatly condense down queries for reporting, billing, etc. You can have a customer account view, then a 2nd-level view of just active customers. Team that with a view of customer address. Team that with a view of product(s) (joined on what product(s) customer has). Team that to view of product(s) rateplan. Team that with view of product features. View, view, view, each trial-n-errored to ensure integrity. Your end query using the views is very compact.

edit:

As an example of how the view would have been better than just a flat query of tables ... we had a temp contractor come in to make some changes. They told him there were views for things, but he decided to flatten all of his queries. Billing was running things off of some of his queries. They kept getting multiple rateplans and rates on things. Turns out his queries were missing criteria to only allow rates to bill if they were between the start & end dates the rate plan was supposed to use that/those rates during. Oops. If he had used the view, it would have already taken that logic into account.

Basically, you have to weigh performance vs. sanity. Maybe you can do all kinds of fancy stuff to increase the performance of a database. But, if it means it's a nightmare for a new person to take-over / maintain, is it really worth it? Is it really worth the new guy having to play whack-a-mole having to find all the queries that need to get their logic changed (and risk him forgetting / fat-fingering them) b/c someone decided views are "bad" and didn't consolidate some core business logic into one that could get used in 100's of other queries? It's really up to your business and your IT/IS/DB team. But, I'd prefer clarity and single-source consolidation over performance.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.