We are here again with after another week and ready for another episode in this series.  Today we get to talk about chapter 5 in the book by Alex Kuznetsov (Blog).  You can find more on this series by looking here.

The title of the chapter this week is “Reusing T-SQL Code.”  In this chapter, Alex covers the topics of:

• copy and paste code
• proper code reuse
• View use to encapsulate simple queries
• UDFs and parameterized queries
• Performance issues with UDFs
• Business logic through filtered indexes, triggers and constraints

I am going to skip the discussion on copy and paste of code and the potential problems related to that.  I think that should be highly obvious.  I think code reuse should also bring up some obvious ideas of why to do it and how it can make your code better.  Simple queries in views is also pretty straight forward.  For these first three topics, check out what Alex has to say about them in his book.

UDFs and parameterized queries

When it comes to code reuse, it is typically easier to to reuse parameterized queries through an inline UDF.  That’s not to say that it cannot or should not be done through a stored procedure.  For many scenarios and requirements, it would be easier to do a parameterized query through a function.  Alex gives examples of some of the limitations such as sorting, Insert/Exec, and nesting.  There are of course limitations that should be considered with inline UDFs.  Alex has those listed in his book.

Performance issues with UDFs

Alex makes a very good point in this section.  When trying to reuse code, we have to pay attention to performance.  Poorly written UDFs could have serious impacts on the performance of the code and that should be a great concern.  To demonstrate this, Alex has listed out an example covering the bases.  Check out his code samples and test it.

There is also another great statement in this section.

Blanket statements do not belong in database programming.

Keep that in mind.  It is very difficult to make a blanket statement and have it apply in all cases.

The final section is covering the topic of when to use Filtered Indexes, Constraints, triggers and stored procedures.  Proper use of each of these can help to enforce business logic.  It should be noted that the use of these will depend on requirements and database design.

Having a single source of the code will help to prevent unexpected bugs.  It will also help to save time on development and documentation.