SQLdep

SQL code checker: review and validation

28 November, 2016 16:40:34 PM · Martin Masarik

Whenever you use SQL on daily basis you should make sure that everybody on the team uses the best SQL coding practises. Couple of well placed rules goes a great length when you need to analyse SQL code written by somebody else.

Examples of the best SQL coding practises

I have never understood how somebody can omit using a fully qualified column name. It just takes 2 seconds to place table alias in front of the column name. And your SQL query analysis is not slowed down by artificial problem. Otherwise you have to stop and and go find out to what table the columns belong. It is very frustrating because you know that the guy who coded the script had the information back then.

 

SQLdep - SQL code review and check

 

Another example, which especially DBAs are very sensitive to, is nesting a query into a SELECT clause. Your database engine is then forced to execute the nested query for every row in the dataset. What a way to create a performance problem (and get a tough of love from your DBA!). Generally speaking you should avoid using such subselect at all costs. And only on a very rare occasions you can justify that.

How to enforce rules for SQL coding

Regardless the to team size you should establish the basic rules which anybody has to be compliant with. It saves you a headache down the road. How to enforce this? Even without additional tooling you can perform at least random checks and verify manually that the scripts are aligned with the best SQL coding practises.

Automated SQL code review and its validation

The other option is to use automated tool. As a side feature to our main product QueryFlow (which visualises data lineage in SQL scripts) we are developing SQL query validator. Even for 10,000 SQL queries we will swiftly run checks for each and every one of those. The result -- list of SQL queries breaking a rule along with the concrete information what rule and on what line number the rule was broken.

Along with those generally applicable rules we are working on a custom rule creator. You will have the option to create your own rules. For example how to enforce naming conventions. We are ready to discuss this further, shoot us a message through the contact form.

Read a case study

SQLdep saved GE Money twelve months of work. GE Money was searching for ways to make the development and administration of its 20TB data warehouse faster and more efficient.

Download