Challenge Your Code Design

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1800

    Comments posted to this topic are about the item Challenge Your Code Design

  • Eric M Russell

    SSC Guru

    Points: 125089

    The basic design principles of abstraction, encapsulation, polymorphism, and inheritence can be (in a limited way) achieved by T-SQL. It's not the same as C# or Java, but it helps to think in those terms when developing views and stored procedures.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1800

    Eric M Russell - Monday, September 11, 2017 8:19 AM

    The basic design principles of abstraction, encapsulation, polymorphism, and inheritence can be (in a limited way) achieved by T-SQL. It's not the same as C# or Java, but it helps to think in those terms when developing views and stored procedures.

    Thanks for your comment Eric. What's your opinion on the approach I prescribe here? I interpreted your comment as adding additional info but not really reflecting on the article.

    I was rather hoping for a little more engagement in the discussion forum from the community. Unusually quiet in here, people normally offer an opinion for or against, perhaps my piece wasn't provocative enough.

    I failed to ignite a debate 🙁

  • Eric M Russell

    SSC Guru

    Points: 125089

    gary.strange-sqlconsumer - Wednesday, September 13, 2017 9:22 AM

    Thanks for your comment Eric. What's your opinion on the approach I prescribe here? I interpreted your comment as adding additional info but not really reflecting on the article.

    I was rather hoping for a little more engagement in the discussion forum from the community. Unusually quiet in here, people normally offer an opinion for or against, perhaps my piece wasn't provocative enough.

    I failed to ignite a debate 🙁

    Hi Gary,
    You presented the case well for applying the principal of single responsibility when developing T-SQL queries, views and stored procedures. The code performs better and is easier to unit test, because it only retrieves those columns are required by a specific case usage. At the same time it provides more opportunity for reusability. It's not an object oriented programming language, but the same design principles still apply.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1800

    Eric M Russell - Wednesday, September 13, 2017 1:32 PM

    gary.strange-sqlconsumer - Wednesday, September 13, 2017 9:22 AM

    Thanks for your comment Eric. What's your opinion on the approach I prescribe here? I interpreted your comment as adding additional info but not really reflecting on the article.

    I was rather hoping for a little more engagement in the discussion forum from the community. Unusually quiet in here, people normally offer an opinion for or against, perhaps my piece wasn't provocative enough.

    I failed to ignite a debate 🙁

    Hi Gary,
    You presented the case well for applying the principal of single responsibility when developing T-SQL queries, views and stored procedures. The code performs better and is easier to unit test, because it only retrieves those columns are required by a specific case usage. At the same time it provides more opportunity for reusability. It's not an object oriented programming language, but the same design principles still apply.

    Thanks Eric,

    The feedback is much appreciated.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Bryant McClellan

    SSCarpal Tunnel

    Points: 4281

    Based on your conclusions about unit testing I'll ask if you've worked with tSQLt as a formal test framework? Without getting into the details of your UDF or stored procedure implementations, the breakdown you've used does indeed support robust unit testing. Using tSQLt for outcome based testing (given X input of data I expect Y output of data) you can end up with a bank of both functional and metadata tests. That bank will help you perform regression testing much more easily.

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

  • Mr. Brian Gale

    SSC-Insane

    Points: 22923

    Overall, I like what you did.  The code snippets are small and manageable.  The only downside to it is when it comes time to debug it.

    What I've seen where I work is that the bug is found in the application (C#) and from debugging that you narrow it down to a single stored procedure (hopefully).  Using your method, you then jump into the stored procedure to jump into a function to jump into 1 or more functions.  SSMS (as far as I am aware) doesn't have nice native ability like C#'s "go to definition".

    On the opposite spectrum, testing this is going to be a lot more simple and understanding the code is also a lot more simple.  Reading a lengthy stored procedure (we have some 8000+ line stored procedures here at work) is VERY easy to misread things and miss potential bugs and unit testing an 8000+ line stored procedure is impossible.  It also results in the code not being reused, or if it IS reused, it is copy-pasted to be reused which is not very efficient.

    My only gripe with the code is the lack of comments.  Having comments explaining what the code is doing and what the expected input and output are make it a lot easier to support and reuse.  That being said, your code is short and sweet, so not positive that comments would help; it is just part of our coding standards.  Much easier to have a rule of putting comments everywhere rather than have subjective rules like "comments only required for complex code".  I've seen that before and what is complex to me may be simple to you.  I know some developers really dislike the WITH syntax and would much rather use nested selects or store the results in temp tables or table variables.  I am liking the function approach and I actually have a report (SSRS) that I am working on now that could benefit from this approach.

    But after reading through the whole article, I think I am going to be more inclined to try to enforce your suggestions going forward here at work.  The code becomes reusable which is always a HUGE plus and if/when a bug is found, fixing it will fix it everywhere the code function is used.  Having copy-pasted code means you may (likely will) need to bug fix things in more than once place when a bug is found.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply