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 AS SELECT * FROM students WHERE enroll_this_year = 1
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
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.
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.