Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #171–Helping a Customer Fix Something

    t-sql tuesday logoIt’s that time of the month for a new blog party at T-SQL Tuesday. This month we have Brent Ozar hosing, and it’s a good topic. I wondered what he might ask when he agreed to host and he has surprised me.

    If you haven’t participated in this, start a blog, start building your brand, and write a post that answers Brent’s invitation. If you enjoy this and want to host, ping me for a date.

    Closing Tickets

    I don’t get tickets per se. I get copied on a few of them from customers, but often the things that I fix are things I’ve broken at SQL Saturday or T-SQL Tuesday or one of the sites I run.

    However, I do consult with customers and our Solutions Engineers at Redgate on architectures and challenges that customers are facing. Sometimes I even come up with a solution. This was the most recent one, which I found interesting.

    I walked into a conversation at a Redgate office one day. A few engineers were talking about a requirement that a customer had to stop a deployment if a particular configuration file had changed. They allow changes, but by few people, however, the configuration file is in the repository with other code and controls some of the deployment parameters.

    Our engineers were looking at git hooks or  moving the file to another repo, or rearchitecting the pipeline to change the configuration. There were other solutions, but all of them were adding complexity to the situation.

    Having grown up in the era of limited RAM and unreliable networks, I immediately thought about detecting changes with a file checksum. It’s a computer science technique used in lots of places, including many database drivers. I thought the simple thing is to store the checksum in a variable in the pipeline, and then add a step that runs something like Get-FileHash and compares that to the variable. Then fail the pipeline if it’s wrong.

    If the file needs to change, the privileged users can change the file, run this, and update the variable with the results.

    A simple way to detect changes and limit them inside the repo, from outside it.

    I have to say I haven’t seen this particular issue before, but I have seen lots of people worry about how to ensure deployments don’t happen in prod if they haven’t happened in test. That’s another whole issue, and a complex solution that I saw a customer implement. It worked great, but that’s for another day.

  • T-SQL Tuesday #170–Abandoned Projects

    It’s the first T-SQL Tuesday of the new year. As we move forward, this month’s invitation is neat in that it’s looking back to learn how to move forward. I think while many people do look back, they don’t always use that information to move forward. This month’s host, Reitse Eskens, asks us about abandoned projects.

    As always, if you’re interested in hosting, blog, and then send me a note about hosting. I still have space in 2024.

    Abandoning a Project

    I’m sure that many of my employers started something and then abandoned it, and there are no shortage of projects I’ve abandoned myself, but nothing big springs to mind. Mostly I find my employers muddle through very poorly written, poorly performing projects and are very hesitant to abandon them.

    However, I will say that I have a couple things on my mind personally. This past December I completed the Advent of Cyber, which I wrote a bit about.  I actually got a certificate (of sorts).

    2023-12-28 16_08_10-Window

    That wasn’t abandoned, and I was glad I went through this, learning about a few tools.

    However.

    In the past, I’ve worked on the Advent of Code, and I’ve never finished it. I even built a repo, where I separated out puzzles by year. I invariably get a week in and then don’t have the time, and I struggle with coding out the puzzles. They get hard, and since this is a spare time thing, I run out of energy and interest.

    I do think, however, I learn some things. A few years I’ve tried to solve puzzles in PowerShell, Python, and SQL, which makes me translate logic, and think about the issues. SQL is hard, as a number of the puzzles are really iterative, and SQL isn’t great at iteration.

    I do learn.

    Even when I abandon projects, I get better at writing code and solving problems. It requires me to think, and it also teaches me things about how to look at a requirement and then write code. I also love the skill of building in tests to verify my code from the samples, which is a good skill.

    It’s an abandoned projects, and even as I write this, I feel like I should work on the 2023 puzzles a bit. I probably won’t because of time, but I do think starting and abandoning this project teaches you something.

  • T-SQL Tuesday #168–Roundup

    Last week was the 168th T-SQL Tuesday, which I hosted. The invitation is here.

    I didn’t get much of a chance to check out the posts as I was at the PASS Data Community Summit, but I came home and started to work through them.

    This was the 8th one I’ve hosted, which makes sense as I’ve taken over managing the party from Adam Machanic and there have been a few places I’ve had to fill in for missing hosts. In any case, here’s the roundup. I’m going in order of the comments as I see them on the blog.

    Rod writes about using LAG to rewrite older cursor code that summarizes data by period. for a 30x reduction in runtime. Plus, one less cursor in the world.

    Aaron Bertrand has probably done most, if not all, of the T-SQL Tuesday parties. He’s been an expert in many aspects of T-SQL and I always look forward to reading his posts. In this one, he writes about a few different problems he’s solved with different window functions on the Stack Overflow database.

    Deb the DBA was in Seattle with me last week, but she found time to give us a way she gets visibility into long running processes from an audit table. In this case she wraps a few window functions inside of a set of MAX() queries of the data.

    Andy Brownsword a few relative queries that perform better with window functions. A good set of examples you might use in your work.

    Hugo warned me he was going to write a long post. He did. Worth a read as he delves into the execution plans behind window functions.

    My own post was on a change in SQL Server 2022 that makes it easy to re-use window definitions and not have to copy/paste them.

    I believe Rob Farley has done every T-SQL Tuesday, and this month is no exception. In this case, he shows how to look at temporal table data.

    Chad Callihan looks at Stack Overflow and how Lead() can find gaps.

    And from Twitter, I caught Barney Lawrence’s post on using FIRST_VALUE, LAST_VALUE and NULLs. I don’t often see too many people looking at first_value() and last_value(), so I liked this one.

    That’s it. If you want to host in 2024, I’ve still got some spots near the end of the year. Ping me and participate in a few of the other parties on your blog.

  • T-SQL Tuesday #168–Using Window Functions

    tsqltuesdayI am the host for T-SQL Tuesday this month, and I hope that a lot of people like the topic. This idea actually came to me earlier this year when I happened to see someone ask about a T-SQL problem and get an answer using a Window function. This person mentioned they hadn’t used the window function before, and I wondered how many people haven’t even tried using the OVER() clause with a window function.

    I also saved the idea of window functions just in case I didn’t have a host, and I realized a few months ago November was blank. So I created an invitation for technical solutions using window functions, hopefully mature solutions you’ve use many times.

    If you want to host, contact me, and send me your blog link and I’ll get you scheduled. FYI, I’m looking for people in the second half of 2024, so it’s not an immediate need.

    Cleaning Up Window Functions

    I don’t write a ton of code, and I don’t have any really cool solutions, but I did want to highlight one thing from SQL Server 2022: the WINDOW clause.

    In the past we’ve often had code like this:

    WITH    HRCTE
           AS ( SELECT   hrorder = ROW_NUMBER() OVER ( PARTITION BY p.franchName ORDER BY HR DESC )
                       , p.nameFirst
                       , p.nameLast
                       , p.franchName
                       , p.HR
                FROM     dbo.Players p
              )
     SELECT  hrdenserank = DENSE_RANK() OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
           , hrrank = RANK() OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
           , playercound = COUNT(p.nameLast) OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
           , hrsum = sum(p.HR) OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
     FROM    HRCTE
     WHERE   HRCTE.hrorder <= 5;

    That’s not bad, but I’ve written a bunch of repeating code in the OVER() clauses. In SQL Server 2022, I can do something more like this (just the outer query):

        SELECT  hrdenserank = DENSE_RANK() OVER frname
           , hrrank = RANK() OVER frname
           , playercound = COUNT(p.nameLast) OVER frname
           , hrsum = sum(p.HR) OVER frname
     FROM    HRCTE
     WHERE   HRCTE.hrorder <= 5
     WINDOW frname AS ( PARTITION BY HRCTE.franchName ORDER BY HR DESC );

    That, to me, is a cool maturity of the Windowing function capability in T-SQL. I can alias a window and reuse it in my code. This also makes I can make a few different windows and easily see which one is used with which aggregate.