Is nested view a good database design?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.