I was a bit of a math nerd in high school and college. Some of you might have been as well, but I took advanced math all through high school, culminating with AP Calculus as a senior with 11 other kids (of about 320). In college, I started with Calculus III freshman year and went on to take 7 more semesters of various high level maths. One of those classes included analyzing data with linear regression, which we did with hand calculators and formulae.
At SQL Saturday Pittsburgh 2024, I watched a talk from Jeff Moden on linear regression. It was a trip down memory lane, with Jeff explaining how the process worked, the flaws, and how this technique could be used to do some predictions on data stored in SQL Server. It was a great session on the topic, but I liked that Jeff showed how you can use SQL Server to do various math calculations that might be useful to analyze data. I see applications sometimes programming various formulas, but I don’t often see people doing this in database queries.
This reminded me of some early programs I’d written in BASIC on an Apple II computer. We had to do labs and hand calculate out various results based on measurements. However, small mistakes in our notes sometimes created large errors in our lab reports, which would affect our grades. Sometimes we’d make a mistake in the chemistry process and sometimes we’d just write the wrong number down (or forget to do so). However, we couldn’t repeat the lab, so filling out reports at home was maddening and stressful.
I wrote programs that create a form and let me enter all my data. This would calculate the results, and I could “massage” the data to get a low error count. Maybe not the most ethical method, but for high school labs in which we were time and resource limited, and where our grades depended on a high level of accuracy, it seemed acceptable to me. I also learned the formulas better since I had to program them and debug my work.
Creating a simple system to do calculations can be very handy for many of us in our lives. While there is software available for many purposes, like examining mortgages or calculating a budget, doing some of that work yourself let’s you customize things, as well as ensure you understand how the program works. However, instead of C#, Python, or something else, I might use SQL these days as I use daily. I can store data across time, something that might help me better analyze my own data with SQL.
If you’ve never tried math formula in SQL Server, give it a try. You might have some new appreciation for the equations you solved in school, while building a little more familiarity and skill with the language you use in your daily work.
Steve Jones
Listen to the podcast at Libsyn, Spotify, or iTunes.
Note, podcasts are only available for a limited time online.
Voice of the DBA 