Nesting views?

OK say a user creates a view "A"...
Now another user comes along and creates a view "B" which joins view "A"
plus other tables.
Now yet another user creates a view "C" which uses "B"...
Supported yes but is this really wise? It really begins to be convoluded
quickly. Seems to me a view is to abstract data to the user not to use as a
crutch when writing procs. Is this just me being dumb or narrow minded? Or
is this not considered a good practice?Views can provide a well-defined client application interface as well as
horizontal and vertical partitioning functionality. Views can also be used
for query encapsulation, but you don't want to go overboard with nesting.
I once worked with a application (upsized from Access) that had views nested
up to 8 levels deep. Debugging functionality and performance was a real
challenge.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>
I try to structure a SQL Server application like this
---
| Business Processes |
|---
| Business Logic | The Application Tier
|---
| Database Transactions |
| *******************************
| * Stored Procedures |
************-- |
| Views and Table-Valued UDF's | | The Data Tier
---
| Tables |
---
So the tables are accessed via views, UDF's and Stored Procedures. Together
the views, UDF's and Stored Procedures provide the interface to the data
tier. The application tier defines transactions by composing elemetnts
exposed by the data tier.
The Database Transactions and even the Business Logic might be implemented
in TSQL, although they are more likely in .NET. The choice of language
there depends more on factors like the complexity of the applicaiton,
performance requirements, team skillset, the need for production debugging
and tuning, etc.
There's no real reason, however, to wrap tables in views and stored
procedures that don't do anything. Using views that pass through single
tables, and stored procedures that insert single rows acomplishes little.
If you're tempted there, just remember that there are worse things in life
than having a sub-optimal application architecture. Like failing to deliver
on time, or not delighting your users, or performing poorly, or spending all
your time learning technology rather than delivering business value.
David