Voice of the DBA

Tag: T-SQL Tuesday

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

  • T-SQL Tuesday #168 – Mature Window Functions

    tsqltuesdayIt’s time for T-SQL Tuesday and I’m hosting this month. I usually do one a year, just because I can and being responsible for a month keeps me engaged in the party.

    This month my invitation is on Window functions and is described below.

    If you’d like to host T-SQL Tuesday, let me know. I have lots of openings in 2024 and I’m looking for someone with a blog, some creativity, and an idea for a technical topic that you’d like to see other people write about.

    Mature Window Functions

    We’ve had window functions in SQL Server for a decade now, since SQL Server 2012.

    This month I’m asking you to write on how window functions have made your life easier. A few ideas for you:

    • What problems have you solved with a window function? Bonus points for lead/lag/first_value/last_value
    • Have you used the SQL Server 2022 enhancements in any queries?
    • How has performance improved for you with a window function
    • Draw a picture of a window with a spatial function – more extra points

    Give us some specifics, with real world problems. Obfuscate the data, at least if you have my name in your dev system, but help others understand how they might solve a complex aggregate using a Window function. The more specific examples, the more others might get help from one of the posts.

    The Rules

    Not many rules, but a few of them.

    • Post between 00:00:00 and 23:59:59 on 2023-11-14
    • Include the logo above in your post
    • Link that logo to this post
    • Leave me a trackback or comment on this post (double check if you have this automated)
    • Post you URL on Twitter, LinkedIn, etc. with the hashtag #tsql2sday
    • Have fun

  • T-SQL Tuesday #167 – Encryption and Data Protection

    T-SQL-Tuesday-LogoI missed September since I was gone half the month in Europe and busy with a roadshow.

    And, I missed October, since I was busy this month and lost track of this during my travels. However, I can still write, and I love the invitation from Matthew McGiffen. It’s on Encryption and Data Protection, two things I care about and about which I’ve delivered a few presentations.

    As always, T-SQL Tuesday is a monthly blog party where you can write on a particular topic. It’s a great way to stimulate some learning and writing, but it’s also something you can catch up on later. I’ll likely write something on #166 in the future.

    If you want to host, send me your interest on Twitter (@way0utwest), LinkedIn (/in/way0utwest), or email (sjones using the sqlservercentral domain).

    Protecting Encrypted Data

    One of the things I deal with regularly is how do you deal with encryption in your software pipeline. There are two concerns here:

    1. How do we deploy encryption keys to production from development?
    2. How do we refresh development environments with encrypted data?

    It should go without saying, but I’ll say it. You can’t use the same keys in development and production because most auditors and regulatory authorities won’t see this as secure. If you have really secured development, and you are bonding developers, them maybe, but most of the time you need different keys/certificates/etc. in production than development.

    This means you can’t have the key definitions (SQL Server) or asymmetric key files/certificates in version control. That makes deployment a challenge.

    What I usually recommend here is that you have a separate pipeline for privileged, DBA, users who can store some of this in a separate repository and secure that.

    At the same time, how do we get this data down to dev? Honestly, I wouldn’t. I’d let it come down encrypted, without the keys (those can be deleted as part of the refresh) and then add in known, secure, PII data with a dev key. The mechanics here get complex, but suffice it to say that protecting keys and keeping them out of dev is important.