Object Structure and Hierarchy | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Object Structure and Hierarchy

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