SQL Server – Is Nested View a Good Database Design?

database-designperformancesql serverview

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.

Best Answer

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.

Related Question