Voice of the DBA

Category: Blog

FIRST_VALUE vs. Min: #SQLNewBlogger
I had mentioned some new T-SQL functions for SQL Server 2022 and a commenter asked about the difference between Min() and First_value. This post looks at a few cases.

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

The Scenario

Let’s set up some data and examine these functions. First a table and some data:
```
SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON

GO
CREATE TABLE [dbo].[MonthlySales](
     [TransactionDate] [date] NULL,
     [SalesAmount] [decimal](10, 2) NULL

) ON [PRIMARY]

GO
```
Some data for 2024:
```
insert MonthlySales (transactiondate, SalesAmount)
  values
  ('2024-01-30', 100),
  ('2024-02-28', 200),
  ('2024-03-30', 300),
  ('2024-04-30', 400),
  ('2024-05-30', 500),
  ('2024-06-30', 600),
  ('2024-07-30', 700)
```
Now, a query and some results. This query runs the same over() clause for first_value, min, and sum. The sum just shows totals.

It seems that first_value and min do the same thing. Let’s add one item to this. I’ll add two columns that remove the first_value and min from the total sales. Let’s assume we want to get rid of the first month’s sales for some reason (they lag).

OK, this looks good.

The Difference

Now, I’ll add some 2023 sales with this code:
```
insert MonthlySales (transactiondate, SalesAmount)
  values
  ('2023-01-30', 10),
  ('2023-02-28', 20),
  ('2023-03-30', 5),
  ('2023-04-30', 40),
  ('2023-05-30', 50),
  ('2023-06-30', 60),
  ('2023-07-30', 70)
```
When we re-run the query, we see different results for lines 3-7. This is because above, we had increasing sales in every line, when ordered by date. In 2023, we have a dip in sales, which happens, so the min value after Mar is 5, but the first_value is still 10 (from Jan).

The same sort of issue can come with last_value and max, as the ordering might not match the sorting of values.

At first glance, these might seem like duplicate functions, but that really depends on the use cases you have for windowing functions and your data. You might often order by a numeric used in an aggregate, in which case they can be the same. However, there are plenty of cases where these work differently.

Another quick example: in my baseball database we can see Barry Bonds HR counts by year, and the min differs from the first year in SFN.

SQL New Blogger

This post took me about 15 minutes to setup and write. I’ve practiced telling a story, but I bet most of you could produce this in 30-45 minutes of work. Easy if you spend 1 hour a week on your career branding.

Showcase your skills and set up a blog today.
December 4, 2024
Grinding Away: Iris Classon

Iris Classon is a developer and Microsoft MVP. She has had a lot of success across the last decade plus. This is her second career, deciding in 2011 to leave her profession as a registered clinical dietician to learn to program. Here’s an excerpt of an interview:

“Well, the first few days sucked. I mean—yes, you might want to beep that out. Because our teachers are doing, counting, talking about binaries, and I had forgotten all the math. They’re talking about binary numbers and doing all the calculations. They’re talking about hardware as well, which I didn’t know anything about. I know now, but not then.

I was so frustrated. On the second day, I was like, “I want to go home.” I was kind of upset and stuff, so my teacher drove me home and we had a long chat, and he said, “Iris, come back. We kind of need a personality like that. You just come back. Give it the rest of the week and you’ll see.”

On the third day, when I came back—I did come back—we started programming. We did our first little console application, and I just realized I just got it. I would turn around and look at my classmates. Some of them just seemed confused and I was like, “What is the problem? I mean, this is logic.”

For the first time in my life, I understood what was written.”

Her first year of programming is documented on her site, and it’s a neat read to me. The important thing is to remember she documented this daily during that year. It’s summarized now, but she was grinding away.

What I like about this

This is a story I think shows practically how to improve yourself and get better at something new. I have remembered this story as I tried to learn to coach over the last ten years, and as I continue to work on trying to improve my skills.

This is also in tech, and it gives you a way to look at yourself and try to improve your skills to find a better position, or maybe the position you really want.

What I don’t like about this

Not everyone can take time off to improve for a year, though this is something most of you could do across 3-4 years.

I also don’t want to make it seem like anyone can do this. Everyone has certain genetic gifts, and they are drawn to certain things. Iris clearly is drawn to very logical work and has a gift for programming. Your experience, raw talent, and understanding might not be as good.

You can get better, but you walk your path and you have to remember that. Reading other stories can sometimes feel like I can’t do that. You won’t likely do the same thing, but you can improve yourself a lot of you grind away.

December 2, 2024
A New Word: Nachlophobia

nachlophobia – n. the fear that your deepest connections with people are ultimately pretty shallow, that although your relationships feel congenial in the moment, an audit of your life would reveal a smattering of low-interest holdings and uninvested windfall profits, which will indicate you were never really at risk of joy, sacrifice, or loss.

I don’t have nachlophobia. Maybe in my younger years, but I deeply value connections with those I choose to spend time with. I have learned to make an effort at events to spend significant time with a few people, even if this means I don’t get time with others.

I have no fear that I’m not risking anything; I am. I am truly saddened when my close friends experience pain and joyful for their success.

A few weeks ago at the PASS Summit, I spent an hour sitting in a mostly empty room with a friend, talking about life. We chat periodically, but we don’t see each other enough. It was worth every minute. I also had to go offsite to an event and another friend asked to ride with me. I was grateful for the company and conversation, something I don’t get enough of.

I hope none of you have nachlophobia. If you do, spend time building strong relationships, which are defined by you. It doesn’t matter how others feel about me, it’s about how I feel about them.

From the Dictionary of Obscure Sorrows

November 29, 2024
Adding Test data to msdb.sysjobhistory
One of our internal people was looking to test some process in (I assume) Redgate Monitor and needed more job history than they had in msdb.sysjobhistory. They wanted to use SQL Data Generator to help, but couldn’t make it work.

This gives the solution I sketched for them. It can work with some other system tables, but not all. Many system tables do not allow user data to be inserted. Some do.

The Main Problem

The main problem here is that SQL Data Generator doesn’t see system tables. If I open a project in msdb, I see this:

That matches what I see in SSMS. Only user tables.

However, I know I can do this and it works.
```
INSERT INTO dbo.sysjobhistory
  (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server)
VALUES
  ('EA6B3BC3-D358-4B0C-A793-8C3C558098AB', 0, 'mystep', 0, 0, 'Executed as user: NT Service\SQLAgent$SQL2022. The job script encountered the following errors.'
  , 0, 20241111, 175438, 0, 0, 0, 0, 0, 'ARISTOTLE\SQL2022')
```
Now, how to get SQL Data Generator to help?

A Little System Table ETL

Since I know I can insert data into the table, how can I generate data? Apparently SQL Data Generator cannot read these tables, but I can use a trick that I’ve used in the past.

First, I’ll run this in msdb:
```
SELECT *
  INTO mysysjobhistory
  FROM dbo.sysjobhistory AS s
  WHERE 1 = 0
```
This code will make a copy of dbo.sysjobhistory with no data. However, this is a user table. Once I do this, now I can refresh SQL Data Generator and I can see my table.

Now I can use the settings to get the type of data I want. Here’s a preview of some data I set, using the data in my existing table.

Now, I can click generate data and I have data added to my user table. If I query this table, I see data:

The last step is to move this data. I’ll use this query:
```
INSERT dbo.sysjobhistory
   (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server)

SELECT job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server
  FROM dbo.mysysjobhistory AS m
```
Once this runs, I can see the data in msdb.

Of course, I’d also have to populate sysjobs if I wanted this linked to a job and shown in the Agent Job History Viewer.

Summary

This post showed how I’ve sometimes worked in situations where I couldn’t directly access a table from an application. In this case, I want to get data into sysjobhistory, but SQL Data Generator doesn’t support that directly. My solution:
1. make a copy of the table
2. insert data into the copy
3. move the data into the original
This has worked in a few situations as well where I might need to move/stage data before it gets into an application table. In this case, we wanted to generate some random history for Redgate Monitor to read.

This can work for other tables as well, as long as you can insert..select into them.

SQL Data Generator is a neat tool to generate data quickly for a variety of purposes in SQL Server. Give it a try, especially if you already have the Toolbelt Essentials.
November 27, 2024