Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #173–The AI Job Helper

    tsqltuesdayThis month I had a new host, Pinal Dave. I was surprised to see he hadn’t hosted, but I didn’t see him in the list. His invite is interesting, as AI has been on his mind. I pinged him after listening to him on a webinar, and his invite reflects the topic: Has AI Helped with Your SQL Server Job?

    It seems that our topics stray a bit further from core SQL topics as the world becomes more complex, and honestly, as more and more of us do things beyond just managing a server.

    In any case, that’s the topic, and if you want to host, ping me.

    AI Assistance

    Has AI helped me? I don’t do a lot of SQL Server work, but I continue to learn about the platform and I do find myself writing code or helping customers find ways to build database software. I also started a short series on AI stuff as I experiment with tools and techniques.

    In general, I don’t find Copilot or ChatGPT (or other tools) that helpful with SQL Server. I think I haven’t spent enough time, but honestly, I struggle to create prompts and work with things. However, I know there is something. If you watch Pinal’s webinar, AI isn’t perfect, but it’s like a coworker making some suggestions of things you might not have thought of.

    Where I have found it useful is doing a question of an AI, often in Bing/Edge chat, and using that as a first search engine for what I want to look for rather than getting a list of results and clicking through them. In that sense, it’s saved some time.

    However, I know I still need to evaluate what comes back and make an informed decision if the suggestion or code will actually work.

    And of course, I need to test it.

  • 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.