Voice of the DBA

Author: way0utwest

  • 2021 Advent of Code–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I’ll do a post on how to easily get started here as a new blogger, but following the Advent of Code, even some random problems, is a good way to show off some T-SQL skills.

    This won’t be a goal for me, but I did start working on the 2021 Advent of Code, taking a few minutes across some days to break from other work and solve a programming problem.

    My aim this time is not to get stuck on a problem. If I can’t solve it, or don’t see a way, I leave it and move on. This post has a few thoughts on the first few days.

    Day 1

    The first problem dealt with loading a set of data and then counting how many times the number increases from the previous number.

    Since numbers in a SQL table don’t have a guaranteed order, this was a bit of a hack from me. I created a table and added a clustered index, and then bulk inserted the data. I then moved this in the same order (I hope) to a table with an identity column. From here, simple LEAD to find the differences between consecutive rows and counting these.

    The second part changed the calculation slightly to use groups of three rows. I copied my LEAD formula to include 3 rows instead of just 1 in each side of the calculation.

    Day 2

    We’re in a submarine, moving forward or up/down. The input was something I needed to evaluate in order again, so I repeated a similar load. Then I used SUBSTRING and CASE to decide what type of instruction was needed and sum the results.

    Part 2 was tricky. I bailed initially, as I couldn’t quite get the math down in my head. I eventually set up a small test data set using the values on the site and then used that to calculate things. I had a series of CTEs that I used to extract the values, then get changes, then perform the math.

    Letting part 2 sit for a day in my head helped me focus better.

    Day 3

    Day 3 was fairly easy binary counting. The test data doesn’t depend on order, so I just loaded it up. Then I need to extract the values into the bits, so SUBSTRING each of these out in a CTE. Not dynamic, but it was easy to extract all 12 bits, then count up the number of 1s and 0s, deciding which was more prevalent.

    From there, a simple calc to assemble back the counts into a binary number and convert to decimal.

    Part 2 is really about counting the 1s and 0s in each position, then creating a final binary number from this and converting back to decimal. I had to read carefully here, as you need to reduce your input set each time. I ended up looping here, as I couldn’t find an easy way to do this otherwise. I could have added some flag to ignore rows, but ended up with a temp table and deletes to get this done.

    So far, easy, harder, then easier.

  • Daily Coping 10 Dec 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

    Today’s tip is to support a charity, cause, or campaign you care about.

    I tend to concern myself with charities that deal with kids, food, and education. I support Habitat, mostly because this helps families and kids get into stable situations.

    This month is one to think about others. I’m volunteering next week for Habitat, giving a day of time to help build a new house.

    I’ve also purchased a few extra items at the grocery for local food banks, giving them donations to help with their work.

  • Daily Coping 9 Dec 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

    Today’s tip is to give kind comments to as many people as possible today.

    Today I’m in Las Vegas at the SQL Server and Azure SQL Conference. I’ll run into lots of people, and I’m making a note to myself in my calendar to complement people many times today. Here’s the view:

    2021-12-03 09_33_25-DkRanch - Calendar - Thursday, December 9, 2021

    I have other appointments around these, so this is intended to keep the idea in my mind today as I walk around the MGM and see people.

  • Worse Before Better

    There was a thread on Twitter on career longevity and fixing issues inside an organization. IT notes that often there is a problem and someone is assigned or hired to fix them. Often when someone starts to dig in, they can fix some things and reduce the pain.  That’s good, as things get better.

    However, as you gain more knowledge of the issue, sometimes you realize there are more fundamental problems. If you try to fix them, you might make things worse. This could result in a repeat of the process of worsening and bettering of the problems over time. You might not make much progress if new people are constantly assigned when things get worse. If you stick with the person that is working on underlying problems, things get better over the long term. I don’t know how often I find management willing to stick with the same person.

    I think my experience has often been that organizations engage in short-term thinking. They just try to patch an issue and don’t necessarily make things better over time. I certainly saw this with supporting Windows systems in the 90s. We often rebooted machines first, which “fixed” the problem. At least for then. Often the issue would repeat, but customers could reboot their own machines.

    Is that the right approach? It could be, if a long-term fix requires lots of resources, perhaps a regular set of reboots from people is a better tradeoff. In a more complex system, usually, we find someone fixing a short-term software issue with code that is less reliable, or even less upgradable in the future. How many times do we see code that has a comment to “not touch this” or a note that the last developer didn’t understand this, but it worked. We end up with spaghetti code that no one maintains, doesn’t want to touch, and it inhibits our ability to grow and react to new requirements.

    Good fundamentals make a difference in how we build and maintain systems. This is especially true in databases, as we have to live with their design for a long time. We need to be agile, but we should apply good database modeling and integrity practices as we do so. Making quick decisions on how to add something to a schema is fine, but do so in line with best practices and an eye on the future.

    Steve Jones

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