Tuesday, August 30, 2011

SQL Code refactoring

Code refactoring (wiki:  "disciplined technique for restructuring an existing body of code, altering its internal structure without changing its external behavior") is a very common method of self-improving our existing code.

Doing this is extremely important and there are many resources, articles and forums discussing this topic.
Our SQL Code (procedures, functions etc…) is no different than any other regular code
And yet, we don’t see this common practice popular enough among SQL Developers.

It is important to distinct between query refactoring, which would usually aim at reformatting the query to better readability and in some cases – better performance, and actual code refactoring.

Frequently refactoring your code helps you make sure you (and other developers in your team) don’t get lost in long, spaghetti, unstructured code as your project grows.
In addition, you’ll be surprise of how much potential bugs you may find, fix and prevent during this process.

So, here are the common items you should be aiming towards:
-          Renaming (at least the internals that are not exposed to the outside application)
o   There are many tools, including Visual Studio, which will help you detect any rename any references to the renamed object. I strongly advise to use those
-          Keeping your procedures and functions small.
o   I personally saw too many of those 1000+ lines procedures. This is always a bad practice
o   And by the way, the solution is not to split a long procedure into [n] pieces J
-          Keeping the same structure/pattern. I’m referring to formatting, variable declaration, naming convention and more
-          Using schema prefix for different DALs. (this is a trick we’re using to divide the code into sub-systems)  For example:
o   Common.BatchDelete()
o   CallsDAL.StartTraceCall()
o   Etc…

Last but not least – during and after refactoring, always make sure that your project:
-          Compiles and builds with no errors
-          Deploys successfully
-          Passes all relevant unit-tests (to make sure backward compatibility and logic are not harmed)
 This is another big topic which I’ll cover in the near future.