Matt (bsg075@gmail.com) writes:
> I am working in a project where the business model is complex enough
> that many common retrieval functions become difficult to develop and
> maintain in single query statements or functions.
>
> I have found the logic is easier to implement (and later modify when
> the code is no longer freshly remembered), by implementing the
> processing layers in nested views, and functions that call
> sub-functions (UDFs), not too unlike object based programming or
> non-DBMS procedural languages. In some cases, the views and functions
> are nested three deep.
>
> So far, from a design standpoint, this practice is working very well.
> Code that would be a recusive mess is reduced to two or three simpler
> code blocks. With the proper indexing and query structure, performance
> seems to be satisfactory (time will tell).
>
> In MS SQL Server, is there anything which makes this practice unsound?

The performance of sclar UDFs are not always the best. There is quite
an overhead in using them (this appears to improve in SQL2005). A
UDF in the wrong place can kill a query.

This problem does not appear with table-valued functions.

One reason I'm a little skeptic to this approach, is that you develop
a set of views to use, programmers tend to choice the most complex and
versatile view, even if they need only one or two values from it. This
can bring in tables to the query that do not belong there, giving quite
an undesired overhead.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp