Voice of the DBA

Category: Editorial

  • Computer Algebra

    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.

  • New SQL Server 2022 Functions

    At the recent 2024 PASS Data Community Summit, I saw a lightning talk from Mikey Bronowski on the New T-SQL Functions in SQL Server 2022.  Before the talk, I made a joke with him that none of these were new because it’s 2024. They’d been out for 2 years.

    Mikey did a nice job, given that he only had 10 minutes, but it was enough to give attendees an idea of some new things they might investigate to use in their own code. If you missed the talk, or you don’t have access to the recordings, we have a series on SQL Server Central that covers these (Part 1, Part 2, Part 3) and helps you understand the new options. The list of language changes is also in the MS Docs under What’s New in the Language.

    I’m curious if any of you are using these new functions? There are a few time series ones, though I think GENERATE_SERIES is the one many of you might have used. Are any of you using DATE_BUCKET? That looks interesting, but I have to admit I haven’t played with it. STRING_SPLIT with the ordinal is my guess for the most popular function people use.

    There are also some new JSON and bit manipulation functions, which might be of use in some situations. GREATEST/LEAST are there, but I’ll have to say these functions haven’t come to mind as solutions in any of my queries or answers I write for questions. I do use the trim functions, though still only with spaces. I guess that some of you might find ways to incorporate trimming with other characters and possibly change old code.

    I do think that many of you can likely refactor code and make it cleaner with these functions, but you should test them extensively. As we’ve seen with some language changes, performance isn’t always better, and some changes (like FORMAT), can cause you resource issues. At the same time, if the code performs well, using cleaner code is a good way to perhaps update your codebase and gain some skill with new techniques.

    One warning. While I like refactoring code, make sure you do some testing, preferably automated, to be sure you aren’t introducing bugs or missing edge cases that your old code covers well. A cleaner codebase is nice, but having working code is more important.

    If you’re using any language features, leave a comment today.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Republish: Boring or Scripting

    I’m off to Live 360 today in Orlando, flying early (I should be somewhere over Texas or Louisiana by now), and busy at the event.

    So you get to re-read Boring or Scripting, which describes a bit about my career. I don’t get bored, I find ways to keep busy and make my job better.

  • How Do You Patch 100 Database Servers?

    In most of the organizations I’ve worked for or consulted with, patching was always a challenge. Patching hasn’t usually been given a priority and is often skipped when operations staff is busy. This has resulted in lots of un-patched, or slowly patched systems. I assume this is one reason Microsoft continues to release RTM-GDR patches because some people won’t patch at all unless there are critical fixes.

    I also know that much of IT management sees patching systems like patching parking lots. Needs to be done, but tomorrow, after we do other important work today.

    Patching isn’t easy, in fact, Allan Hirt says it was never easy, but these days we don’t get the downtime over a weekend to patch, and there is a desire to patch security issues immediately because of the potential reputational (more likely) or regulatory (less likely) risks. Also, we often need to patch dozens, if not hundreds or thousands, of systems.

    So is there a way that most organizations do this? It’s interesting in the piece above that Allan notes that most of us have technical debt, and this debt consists of more than just code and systems. It’s also people, budget, politics, and more. This is even more of an issue if you didn’t write the software. Applications often limit patches or upgrades, and it can be hard enough to get applications certified on new platforms when you control everything. If you purchased something from a vendor, you might be bound by their timelines not your own.

    So how do you patch a lot of systems? There are lots of ideas and potential solutions. I’m sure Allan covered some recently in his session at the PASS Data Community Summit. For me, this boils down to building part of a process, using it, evaluating it, and then rolling it out wider. This might mean I need two processes because some systems will lag behind others for various reasons. I’d probably spend a year or two slowly adding to this process and getting it better, all the while ensuring I used automation as much as humanly possible to process notifications, approvals, and actually deploy code.

    Start small, experiment, test, evaluate, make changes. Always codifying things that I can. It’s a method that has worked for a long time.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.