SQL Server Performance Forum – Threads Archive

Object Structure and Hierarchy

Hi, I’m relatively new to programming in T-SQL and would appreciate advice on the following… I have gotten into the practice of creating Views for each Table (usually providing enhanced formatting incorporating additional reference data). From the Views I build corresponding Functions, and from those Functions I build corresponding Procedures. I know it’s not good practice (though not too sure why), but in the Functions and Procedure I use wild cards to select the field data – this minimises the amount of cut’n’paste I have to do if I change the view, e.g. CREATE FUNCTION dbo.fxUser_SELECT() RETURNS table AS RETURN ( SELECT U.* FROM vwUser U ) and… CREATE PROCEDURE dbo.procUser_SELECT AS SELECT U.* FROM fxUser_SELECT() U In other words I build a complete relational hierarchy from the table->view->function-&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />rocedure. In this way, I can access the base view using the most convenient object type (View, Function or Proc) from the application code. From an overall development viewpoint, I like this approach as it gives me consistency and I know the depemdencies of each object, however… I have two question: 1) Is this poor practice? Would I, for example, be better replicating the Views ‘code’ in the relevant stored procedures directly (rather than referencing the View)? In other words, is it generally better to build ‘self-contained' objects with minimal dependency? 2) I’ve also found that if I change the base View, I need to manually edit each of the other objects (Functions and Procs) to get the changes to ‘wash through’ (I guess SQL server recompiles the object?). Is there a way of forcing the dependent objects to ‘recompile’? Thanks – sorry for the long post. Neil

Hi Neil, From a performance perspective this is not a very ideal solution. Since a stored procedure call has to go through 2 levels of indirection. also the procedure/function you’ve shown here don’t show an where clauses, which may be an intentional simplification. I’d suggest that from non TSQL code you always call stored procedures. This way the stored procedures can be tuned for maximum performance, by only returning the columns/rows that the client code wants. In some cases you may need to expose views to the client application (for generic searching mechanisms) insert/update/delete procs can easily be templated, in fact I use wscript to generate them for me based on a table name. Also selecting a single row based on the key is a proc that can be generated. All other select type procs really need to be hand-coded for best performance. If you change a view then you’d need to recompile any procs/functions. I’m not aware of there being any automatic way to achieve this. sp_recompile can help but only if the objects were created in the right order in the first place (pretty sure that it uses sysdepends) Cheers
Twan

]]>