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.


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

CREATE VIEW vw_eligible_student
SELECT * FROM students
WHERE enroll_this_year = 1


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

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


CREATE VIEW vw_eligible_school
SELECT TOP 100 PERCENT SchoolID, school_name 

FROM schools sh 
     vw_eligible_student s 
     ON s.SchoolID = sh.SchoolID
     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.


5 Answers 5


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.

  • 1
    +1, except I would replace "harder" for the query optimizer, with "almost impossible". :)
    – Jason
    Sep 7, 2011 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? Sep 7, 2011 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, 2011 at 19:56
  • 9
    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. Sep 8, 2011 at 0:52
  • 3
    I've written an embedded database server, and to me, resolving views first and then optimizing the resulting query was the obvious route, as it is actually quite unlikely that all queries on views will return all columns. I cannot even think of a reason why realizing view data in the middle of a query would gain something, so that was a no-brainer for me. Sep 8, 2011 at 11:17

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.

  • 5
    "This is most effective when the base view is an indexed view." Important point. Sep 8, 2011 at 19:46

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.


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.


The real issue is not nested views in themselves. The real issue is the proliferation of nested views as developers layer additional tweaks on existing views. I have found queries with a nested view 4 layers that actually joined to one of the views in it's definition. Our tendency to take the easy way out rather than analyze and solve a problem is the root of the issue.


In my environment, we replicate a lot of tables from production server to the reports server. On reports server we have plenty of views that are based on replicated production tables AND are nested. Before replication starts, we have to remove all views to make replication possible (we use drop and create because tables structure often change in production). After replication ends, we have to rebuild all the views.

Now here's the fun part: Because many of the views are nested, we have to rebuild them in a specific order. While making any changes in the views definition we must pay attention to keep the correct rebuild order. It's a total mess. I strongly discourage using nested views if you use replication or simply drop and rebuild your tables, that are source for views.

Performance is another thing. Views that are based on other views are nothing else but multiple queries to execute. It's easier to put the larger query together, create a job and make a table out of it. Easier and improves the performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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