Voice of the DBA

Tag: T-SQL Tuesday

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

  • T-SQL Tuesday #165–Job Titles

    tsqltuesdayThis  T-SQL Tuesday is from a new host, Josephine Bush, leader of the Boulder group just North of me. It’s an interesting invitation, asking what our job titles really mean. I like this as the titles do affect how our industry advances and what opportunities are available to us.

    I manage the tsqltuesday party, so if you want to host, ping me.

    The DBA

    When I was a software developer, I worked with a DBA that made a lot of $$$. That was very attractive to me. So, I pursued that title. There were few of them in any company, and I needed to learn SQL, modeling, performance, etc. but once I got to that point, I found it was a good job.

    These days I see some companies with DBAs, but lots of the customers I work with don’t have DBAs as a title and have some sysadmin or developer doing those jobs.

    New Titles

    The invitation shows other titles, but often what I see is titles are for HR and pay levels. The job often is very similar. I see DBAs/Database Engineers/Database Reliability Engineer, often doing very similar work. They might watch over servers with admin stuff (backup/security, etc). They might do DevOps, IaaC, deployments, they might also do modeling and consulting with devs.

    Database Developer is something I see often, sometimes with BI developer, and often they do the same work. The former might do more T-SQL on OLTP systems and the latter might only want to work with BI/OLAP/warehouse systems. However, I find people who want to limit themselves are limiting opportunities and might be those that don’t hold onto a job for a long time. That being said, I do find some BI developers might not have access to all development databases, but database developers often do.

    I’ve seen a few data scientists. They’ve been around for years, there has been a big push to get them to work on ML/AI systems, and I see less of that in the last few years as there AI tech grows and gets more commoditized. It doesn’t seem that as many companies want to pay highly for this role. I have no idea what they do other than lots of ETL and experiments to try and get a computer to make better decisions from data.

    Architects – I know a few people that have this role, but I don’t see a lot of them. These are usually people that evaluate tech, set standards, and try to oversee a common direction for database designs or tech, however, I also find these people need to be doing other work to justify their existence. They often fill in where work is needed.

    In general, I think titles sometimes reflect a specialization of a portion of technology, but aren’t necessarily needed. I’ve been a DBA and done all the things the invitation lists for various roles. However, I find that if I wanted to be a database engineer rather than a database administator, I could likely justify a higher salary.

    My advice is pick the title that has a good salary and aim for it. Learn all the skills, since the title might not matter when someone needs work done.