Voice of the DBA

Category: Blog

T-SQL Tuesday #168–Using Window Functions
I 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.
November 14, 2023
Data Community Summit Week

I’m up in Seattle this week for the PASS Data Community Summit 2023. This is almost an annual event for me. I’ve missed a few since 1999, but not many.

This is my last trip of the year. It’s been quite a year of travel, and I’m glad not to have anything booked in the foreseeable future. I have one conference in late January, but I haven’t submitted for others or planned anything else for now.

In any case, I have a DevOps in a Day tomorrow and then a few panels at the Summit, but mostly I’m free to enjoy the time with people. I am hoping to do some livestreaming at the event, but we’ll see if that works. If they give me access to the Summit channels

We’ll see if I can get some time to post pictures and thoughts here as well.

November 13, 2023
Friday Flyway Tips – Adding the Type of Database Project

There was an update to Flyway Desktop which lets you see the type of database your project is associated with, and this post shows how to get this in your list of projects.

As an example, you can see below my first project is a “SQL Server” project.

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

Two Simple Steps

The first step to seeing the type of database project is to upgrade Flyway Desktop. The team is releasing basically every week. My version, upgraded before this post, is 6.9.3, so anything after this should have this capability.

The second step is you need to open your project. If I open the “DBCode” project above, it starts the comparison.

I don’t have to wait for the comparison, I can just close the project. Once I do, the type of project appears on the right side.

If you’ve been working with Flyway Desktop for awhile, you might have noticed an “Upgrade project” in the upper right. This is to upgrade the project from a JSON format to a TOML format, which doesn’t matter for you, but it does make the management of the internals of Flyway and Flyway Desktop easier for the developers.

In any case, you don’t need to upgrade the project. If I open and close a PostgreSQL project, I see this:

If I reopen the FWPoC_PostgreSQL project (named before this feature appeared), I see the upgrade is still there.

Try it out today. If you haven’t worked with Flyway Desktop, download it today. There is a free version that organizes migrations and paid versions with many more features.

Video Walkthrough

I made a quick video showing this as well. You can watch it below, or check out all the Flyway videos I’ve added:

November 10, 2023
A New Word: Heartspur

heartspur – n. an unexpected surge of emotion in response to a seemingly innocuous trigger – the distinctive squeal of a rusty fence, a key change in an old pop song, the hint of a certain perfume – which feels all the more intense because you can’t quite pin it down

I certainly feel heartspur at times. I can be emotional, and I find that there are lots of things that trigger memories. Often I know why or when the memory occurred, but not always. Especially as I get older.

Recently I heard Waterfalls on a playlist. I remember listening to this with my wife in the car, and it’s a great song. But I know that I heard this before I met her, and evoked a strong emotion of sadness. I can’t quite remember why, and still don’t.

If only I’d had a smartphone in 1994 and had taken a lot of pictures. Maybe I’d know why this one trigger something.

From the Dictionary of Obscure Sorrows

November 10, 2023