Voice of the DBA

Category: Blog

T-SQL Tuesday #168 – Mature Window Functions
It’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
November 7, 2023
Creating a Self Referencing FK in a CREATE Statement–#SQLNewBlogger
I had written about a FK in a CREATE TABLE statement recently, but the second half of this was that after the original question, the person asked if this would also work for a self-referencing FK. It does, and I wrote this to show that.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Creating the FK

The last post showed how to create the FK, but this works within a table as well. Let’s say I want to have an Employee table that links back one employee to another, who is their manager. That type of structure looks like this:
```
CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
)
) ON [PRIMARY]
GO
```
I can add a link that makes MgrID a FK reference by altering the code like this:
```
CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
),
CONSTRAINT FK_MgrID_EmpID FOREIGN KEY (MgrID) REFERENCES dbo.Employee (EmpID)
) 
GO
```
Easy.

SQL New Blogger

This is a post that took me less than 10 minutes to write. I changed the code from the previous post and wrote this right after the other one. The search and replace was the longest code part, and then the writing was quick, 5 minutes.

This is a core skill for a DBA or developer. Write your own post to show how and why to build a self referencing FK for some scenario that you work with in your job, or in a project.
November 6, 2023
Friday Flyway Tips–Seeing Pending Migrations

I find that quite a few people using Flyway will end up with a lot of migration scripts over time. While you can certainly re-baseline and split scripts into separate folders, visualizing these over time can be hard.

The Flyway Desktop team added a nice little option that makes it easier to see new work as opposed to old work.We’ll look at that in this post.

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.

Lots of Migration Scripts

We might see a lot of migration scripts over time in a folder. Certainly I can see this in the file system for one of my projects.

In Flyway Desktop, here is my view.

That is a lot of scripts. Since these are ordered as they would apply, it can be a lot of scrolling to find the ones that haven’t been applied.

However, if I click an environment on the right, I get a different view. Now I see a checkbox above the migrations that says “Only show pending migrations”.

If I click that, I see a view of the few that haven’t been applied to this environment.

A quick way to see what work has been added to the project, but not applied to other environments.

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 3, 2023
A New Word: Zielschmerz

zielschmerz – n. the dread of pursuing a lifelong dream, which requires you to put your true abilities out there to be tested on the open savannah, no longer protected inside the terrerium of hopes and delusions that you started up in kindergarten and kept selaed as long as you could.

I don’t know I have any lifelong dreams, much less zielschmerz. I used to want to play professional sports, but that lifelong dream died a long time ago when I realized pursuing it wasn’t realistic.

I have enjoyed life and worked to grow my career, skills, and enjoyment in different ways. However, I am more a take things are they come and constantly re-evaluate what I want and need more than having a dream.

However, you can pursue a lifelong dream, which might not include being tested on the savannahs, but it might be tested in the real world. My wife changed careers to work with horses, taking a leap that she could actually earn enough to have that be a viable career. It’s been nearly 12 years now and she’s made it a success, though I know she had a lot of trepidation and anxiety in making the leap and growing her business.

Hopefully there is no more zielschmerz these days for her.

From the Dictionary of Obscure Sorrows

November 3, 2023