Best Practices For Coding (Re-Usability)

Good afternoon everyone!

Today marks the finale of my mini series of blog posts on the best practices for coding. If you haven't done so already, I strongly recommend that you read my previous blog posts to catch up, which can be found here:

Best Practices For Coding (Intro)
Best Practices For Coding (Readability)
Best Practices For Coding (Accessibility)

What is meant by "Code Re-Usability"?

Code re-usability means that certain parts of the code has been written in such a generic way that it can be reused in multiple places within your application. As the term goes, why reinvent the wheel if you have a perfectly good subroutine or function available for your use just waiting for you to plug some variables into it? Repeating code unnecessarily can:

1. Take up memory usage unnecessarily.
2. Take additional time to copy, paste and incorporate into the new function.

Code re-usability is key, particularly when it comes to creating larger and more complicated programs. Why start off complicated as when solutions expand, it only becomes more and more complicated. Plan ahead, look at possible scenarios where code may need to be repeated (such as for delete or save commands) and then code appropriately.

What has to be noted is that SQL Server isn't as flexible with code re-usability compared to other programming languages such as C++ and C#. When you think of code re-usability in SQL, you will probably think of using the following:

• Stored Procedures that can cope with many different tables and inputs.
• One very large re-usable view which brings back rows from a variety of different tables and joins them all together.
• User Defined Functions that can be executed by many different queries.

Whilst these are all methods of code re-usability, they also have one other thing in common; a relatively negative impact on performance, especially when many instances are used.

So how would you get around this problem? 

• Stored Procedures can be used but try not to make them too generic, otherwise it'll slow things down instead of speed them up. 
• Use views but don't overcomplicate them unnecessarily. 
• In-line code and logic has much better performance than User Defined Fucntions, so try to use it as much as you possibly can.

The key really is all about balance, and weighing up the pros and the cons sensibly. You need to consider the project scale as if you have multiple users who could be possibly accessing the same data at the same time (which is the most likely scenario), you will definitely want to make performance your priority.

Conclusion

Overall, you could argue that SQL could do more in order to promote code re-usability, however realistically this probably would never happen. In the mean time, you just have to be careful when using Stored Procedures, Views and User Defined Functions as yes, they do reduce the chances of you repeating yourself but they can come at a performance cost!

This is the conclusion to my mini series on the best Practices For Coding. I hope you have found the blogs in this series useful.

What's Next...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there. Thanks! :)