Development & Middleware
Friday, 15. October 2021., 10:20
Developers familiar with procedural languages often like to treat a database as a bit bucket and pull the data into the familiar procedural environment for processing. Doing the same processing in SQL can be unfamiliar and daunting. Let me compare the two and show you that SQL is not scary at all.I'll hazard the guess that you (like me) started your developer career by learning a procedural language like Basic, Pascal, C, C++, C#, Java or something else depending on your generation. There's something about a procedural language that makes it relatively easy to understand what is happening - you can “single step debug” in your head and walk through your code piece by piece.In SQL that is not quite as easy. You need to think about dealing with sets of data instead of a single row at a time, and the language has a declarative nature where you specify what you want the done and let the database figure out the details of how exactly to get the individual rows pieced together to form the final result. You can rarely follow along what is happening, but you just execute the statement, witness the result, and then need to figure out from that result what actually happened, so you can make changes and try again. You can't "single step" your SQL code - it is often more of an iterative trial-and-error development process.So SQL has aquired a reputation - in my opinion unjustified - of being a hard programming language to learn. There’s a gazillion frameworks and tools out there that promise that you can write your applications without SQL. The problem is that you cannot really work with your data without SQL - the frameworks simply generate SQL for you. This can be OK in maybe 80% of the cases, but for the last 20% a human is very likely to be able to write SQL that by far outperforms what the framework generates. And for a human that knows SQL, I believe it is even easier and faster to write the SQL than use the framework.I will take a single use case - picking items by FIFO (first-in-first-out) in a warehouse - and demonstrate coding this in a procedural language versus doing it in SQL, along the way showing differences and similarities in thought processes of the developer of each method. Ultimately I will show that it is not really harder writing a single 100-line SELECT statement than a procedural application doing the same thing.