New year, same issues and some tips.

Most folks working with SQL DBs for a while will likely come to see how beginners in this area struggle with “declaritive code” and “tell the DB what you want not how to do it”

In my experience, this issue stems partly from the way SQL is tought – examples are often way too simplistic to cover “advanced” topics – partly this is because it is very different thinking required when one comes from Python, JAVA or the whole notion of “telling the computer what to do”.

This often leads to horrendous SQL code and data models, that neither give correct results nor deliver them fast.

Despite all of this, I see many questions around tiny optimization options, command variations and “optimizer flags” from beginners all the time.

Astonishingly, this approach of looking for workarounds for problems with the data model understanding occurs regardless of the DBMS platform used.

– Lars Breddemann

What’s often missing in those questions is the broader context, WHY they want the DB to do something and WHAT eventually should be achived.

Basically, the core question “WHAT should be the outcome, the result?” remains unanswered both on top- and bottom-level.

Maybe this is because it is hard to express this desired outcome in a good way?

I recently read a really good presentation slide deck from Markus Winand that, besides explaining an interesting new SQL standard feature (row pattern matching), makes use of a nice way to illustrate what data should be selected and returned.

Screenshot of page 19 from the presentation "Row Pattern Matching" by Markus Winand.

The example used is non-trivial and certainly beyond what most SQL 101 courses cover and that’s another positive!
The visualization he uses gives a really good grasp on the kind of data and what the result should be.

Maybe this could be a pointer for how to improve your SQL writing in 2019:
Draw a picture of the data you have and what aspects of it that you want and then work build your SQL statement step by step based on this.
This is what I do in many cases when I try to understand what a SQL statement does, so this technique works both for constructing a new statement as well as for deciphering existing ones.

To finish this slightly ranting post of here are some links to presentations and twitter accounts that think are worthwhile to read or follow (not exhaustive at all).
If you do SQL programming as part of your daily struggle for bread work, take some time and check these resources.

There you go, now you know!

With that, have yourself a Happy New Year 2019.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.