View Single Post

   
  #1 (permalink)  
Old 02-29-2008, 07:30 AM
Matt
 
Posts: n/a
Default Design question: Nested views and functions?

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?

Examples:

CREATE VIEW vw2 AS SELECT * FROM tbl1 WHERE ...
CREATE VIEW vw3 AS SELECT * FROM vw2 WHERE ...
Application uses: SELECT * FROM vw3

-or-

CREATE FUNCTION udf2 AS SELECT * FROM tbl1 WHERE ...
CREATE FUNCTION udf3 AS SELECT * FROM udf2 WHERE ...
Application uses: SELECT udf3(param) AS value

Reply With Quote