Our Architecture team is proposing a framework that would see our SQL queries mo

Question

Our Architecture team is proposing a framework that would see our SQL queries moved from coded strings within our applications, into a file based system where we would invoke them with function calls. Our application makes heavy use of SQL queries ranging from the mundane, to very complex. This is a .NET solution.

The idea is that each query would be written to text files, and we would be able to tag fields, joins, and conditions with attributes. When calling our SQL functions, we could pass arguments to toggle these attributes on/off based on our business logic, allowing for only the portions of the SQL we need to be executed.

Although I like the idea of abstracting away our SQL, I am skeptical that this system will result in any tangible benefits. The major driver of this is our past experience where many coders created some massively bungled dynamic SQL functions which were impossible to understand. The idea is that this will make it easier to define your queries, will make all queries more easily testable, and clearly define the business logic involved. I am unsure if this will survive the test of reality.

I

Explanation / Answer

If your problem is "impossible to understand" SQL, focus on that problem. Can not imagine that SQL with attributes does anything to make it easier to understand.

Instead it will create a maintenance burden on parsing attributes etc on an already complex language like sql. Sooner or later your extensions will prevent you from using some nice database feature. How much training is needed to teach new employees/contractors your version of sql? Extra files create a more complicated deployment process and possible support problems. How do you know the version of the file that is in use or if it has been altered by the customer or your own support to solve another issue? Everything can be solved of course but only you knows if it is worth it.

Spend your time setting up strict guidelines for writing readable/maintainable SQL first. Once that is in place and everyone agrees on the rules you can move to the next problem.

What is readable and maintainable sql varies but here is a subset of our rules. (For sql-server)

you must be able to easily copy/paste code to/from database and run it I.e. No string concatenations of variables in code. Use parameters or string.Replace where parameters are not allowed in sql.
one column per line pefixed with table/alias
explicit AS
ansi joins
descriptive aliases (not t1, t2 etc)
use CTE/CROSS APPLY to reduce complexity
use parameters with descriptive name for both variables and "magic" numbers.

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.