Voice of the DBA

Tag: SQLNewBlogger

Adding Row Numbers to a Query: #SQLNewBlogger
I realized that I hadn’t done much blogging on Window functions in T-SQL, and I’ve done a few presentations, so I decided to round out my blog a bit. This post will start with the ROW_NUMBER() function as a gentle intro to window functions.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also part of a series on Window Functions.

A Basic Set of Data

I’m going to use some fun data for me. I’ve been tracking my travels, since I’m on the road a lot. I’m a data person and part of tracking is trying to ensure I’m not doing too much. Just looking at the data helps me keep perspective and sometimes cancel (or decline) a trip.

In any case, you don’t care, but I essentially have this data in a table. As you can see, I have the date of travel, the city, area, etc. I also have a few flags as to whether I was traveling that day, if I spent a night away from home, and how far I was.

I have a travelID in here, which is a sequence, but what if I wanted to the trips I took in August 2024. I’d want a distance > 0 (not at home) and filters by dates. Adding that to my query, I’d run this:
```
SELECT
   TravelID

, TravelDate

, TravelCity

, Area

, Province

FROM travel

WHERE
   TravelDate     > '2024/07/31'
   AND TravelDate < '2024/09/01'
   AND Distance > 0

ORDER BY TravelDate;
```
This gives me these results:

There are 9 rows in here, but they have a weird ID number, plus these are different trips. I can just add a row_number to this data, and I’d see this result. Ignore the OVER and track I used, but you can see an incrementing number added to each row. The second column in the result set matches with the number added by SSMS on the side.

What if I wanted to see the separate trips with some row number for the day in each city?

That’s where a row_number() can help.

Creating a Window

The window comes from the OVER() clause, which is added to a number of functions, including Row_number(). The OVER() clause lets me set a window or rows on which the function works. I can set a partition and an order.

The partition is a column where we are essentially grouping data. For me, this would be the city. When I change city, I want to reset the number. Looking at the data above, I’d expect to see 1, 2 for the first 2 days in Minneapolis, then a 1 for a day in Fort Collins, and another 1 for day in Aurora

The ordering is what order is the data in the partition. In this case, I want to have the data in the window ordered by traveldate, so I’ll use that. I now have this code:

SELECT
   TravelID
, ROW_NUMBER () OVER (PARTITION BY TravelCity
                       ORDER BY TravelDate)
, TravelDate
, TravelCity
, Area
, Province
FROM travel
WHERE
   TravelDate     > ‘2024/07/31’
   AND TravelDate < ‘2024/09/01’
   AND Distance > 0
ORDER BY TravelDate;

And I get these results, where I can see that I essentially had 4 trips (all with number 1s), and these were the trips:
- Minn – 2 days
- Fort Collins – 1 day
- Aurora – 1 day
- New York City – 5 days
This shows how row_number() gives me a sequence based on the partition. The select null part in the earlier query just ignores the order by, which is required for row_number(). With no order, how do we know what sequence?

Let’s change this slightly. What if I partition by Province? Then I see this:

We put the data in date order, and ran through each province. In this case, my two 1 day trips around Colorado are bucketed (partitioned) together and I see one less trip. If I did this by country, I’d see all of this as one sequential list, since all my trips were in one country.

However, if I did countries for June, I’d see this, with the raw data on the left and the row_number() on the right. There’s a weird sequence in here; can you see it?

The weirdness is that my trips to England were broken up by a trip to Italy. So while my sequence looks good for the first part of the trip to English for 6 days, when I returned a week later, we get numbers 7, 8, 9. That’s because the data is grouped first by country, and the sequence added. The ordering of the sequence is by date, so the later days (June 13,-15) are marked with the higher sequence that continues on.

Hopefully this gives you a basic look at row_number() and some of the possibilities. I’ll examine it further in another post, along with various other window functions.

SQL New Blogger

Complex coding and finding weird situations are things employers want you to be able to do. If you work on algorithms or you’re learning new language elements, blog about them. That will impress people.

This post took me about 30 minutes, plus about 15 minutes or playing with code to set things up. You could likely do this in an hour if you’ve never blogged, though let someone proof things for you.
October 9, 2024
Comparing an Old Running Total to Window Functions
Often I see running totals that are written in SQL using a variety of techniques. Many pieces of code were written in pre-2012 techniques, prior to window functions being introduced.

After SQL Server 2012, we had better ways to write a total. In this case, let’s see how much better. This is based on an article showing how you might convert code from the first query to the second. This is a performance analysis of the two techniques are different scales..

Pre SQL Server 2012

The old way:
```
SELECT Acc.ID,CONVERT(varchar(50),TransactionDate,101) AS TransactionDate
  , Balance, isnull(RunningTotal,'') AS RunningTotal 
 FROM Accounts Acc  
   LEFT OUTER JOIN (SELECT ID,sum(Balance) AS RunningTotal 
                    FROM (SELECT A.ID AS ID,B.ID AS BID, B.Balance 
                           FROM Accounts A 
                             cross JOIN Accounts B 
                           WHERE B.ID BETWEEN A.ID-4 
                           AND A.ID AND A.ID>4 
                          )T
                    GROUP BY ID ) Bal 
     ON Acc.ID=Bal.ID
```
What were the statistics on this? After running a few times, with STATISTICS IO ON, I get this:

Table ‘Accounts’. Scan count 37, logical reads 37, physical reads 0

Not bad. I’ve truncated out the other values as they were all 0.

Window Functions

Here is the same query written with a Window function.

SELECT
id
, TransactionDate
, Balance
, CASE WHEN LAG(TransactionDate, 4, null) OVER (ORDER BY TransactionDate) IS NOT NULL
THEN SUM (Balance) OVER (ORDER BY TransactionDate ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
ELSE 0
END AS runningotal
FROM dbo.accounts

The statistics?

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0
Table ‘Accounts’. Scan count 1, logical reads 1, physical reads 0

The window function definitely does less work. A lot less. But how does this scale?

Performance Testing

There are numerous ways to create some test data for this. Since I have Redgate SQL Data Generator, I decided to use that. It’s simple and easy, and I added 100,000 rows first.

My results of the first query:

Lots of reads and scans. Let’s compare this to the window function.

Hmmm, both took essentially zero time less than a second. That might lead some developers to think either method is quick enough.

Let’s add 1mm more rows.

Now compare. The first takes about 15s with these results.

The window function? 3 sec, with these stats.

The comparison looks like this. First, let’s look at SSMS time

Old, Cross Join Window Function

20 rows 0 sec 0 sec

100,020 rows 0 sec 0 sec

1,100,020 rows 15 sec 3 sec

If we look at CPU time, then we see this:

Old, Cross Join Window Function

20 rows 0 ms 0 ms

100,020 rows 748 ms 250 ms

1,100,020 rows 5845 ms 2919 ms

If we look at the logical reads in total, we see this

Old, Cross Join Window Function

20 rows 37 1

100,020 rows 403,998 359

1,100,020 rows 6,450,895 3945

Clearly the window function is better and the better grows as the size of data grows.

Summary

This post looks at two queries and compares the performance across a few queries. These aren’t the only ones, and you might choose other types of queries, but these are both examples of how you might approach a problem using old tech and new tech.

The window function is not slightly more efficient, but extremely efficient compared to the older style method of using a cross join. As the data scales up, the difference is pronounced. While 1mm rows might not be a great test here, and you may prefer to test at 10mm or 100mm rows to get an idea of load, the fact is the Window function is much quicker and uses less resources.

If you are using older style code to perform T-SQL calculations, make some time to refactor that code (and test it) to use modern window functions.

Setup Code

Here’s the initial setup code:
```
CREATE TABLE Accounts
(
ID int IDENTITY(1,1),
TransactionDate datetime,
Balance float
)
go
insert into Accounts(TransactionDate,Balance) values ('1/1/2000',100)
insert into Accounts(TransactionDate,Balance) values ('1/2/2000',101)
insert into Accounts(TransactionDate,Balance) values ('1/3/2000',102)
insert into Accounts(TransactionDate,Balance) values ('1/4/2000',103)
insert into Accounts(TransactionDate,Balance) values ('1/5/2000',104)
insert into Accounts(TransactionDate,Balance) values ('1/6/2000',105)
insert into Accounts(TransactionDate,Balance) values ('1/7/2000',106)
insert into Accounts(TransactionDate,Balance) values ('1/8/2000',107)
insert into Accounts(TransactionDate,Balance) values ('1/9/2000',108)
insert into Accounts(TransactionDate,Balance) values ('1/10/2000',109)
insert into Accounts(TransactionDate,Balance) values ('1/11/2000',200)
insert into Accounts(TransactionDate,Balance) values ('1/12/2000',201)
insert into Accounts(TransactionDate,Balance) values ('1/13/2000',202)
insert into Accounts(TransactionDate,Balance) values ('1/14/2000',203)
insert into Accounts(TransactionDate,Balance) values ('1/15/2000',204)
insert into Accounts(TransactionDate,Balance) values ('1/16/2000',205)
insert into Accounts(TransactionDate,Balance) values ('1/17/2000',206)
insert into Accounts(TransactionDate,Balance) values ('1/18/2000',207)
insert into Accounts(TransactionDate,Balance) values ('1/19/2000',208)
insert into Accounts(TransactionDate,Balance) values ('1/20/2000',209)
go
```
September 30, 2024
Moving One File Across Git Branches: #SQLNewBlogger
I was working on some branching and merging with a customer and they wanted to move a file from one branch to another without taking the entire commit. I had to dig in a bit and see how to cherry pick a file, and not a commit. This post looks at how this can work.

I’ll do this in the git CLI. I’m sure it works in many clients, but when I do something strange or new, I like looking at the CLI. Mostly because when I make a mistake, the clients send me to the CLI often anyway, so I get comfortable there.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. You can see all posts on Git as well.

A Simple Setup

First, grab a repo. In this case, I had a branching/merging repo I use with customers to show some things. I’ll use the repo at: https://github.com/way0utwest/BranchMerge

For me, I have a main branch, as well as a dev and qa branches. There are likely some feature ones as well.

To start with, let’s check main. Everything is up to date (I pulled already):

Let’s also check QA. Same thing.

I’ll now make some changes on QA, adding a file and changing two others. Once I do this, here is my status, pre-commit.

If I don’t commit, these files don’t exist, and I could change branches and add them there. However, once I commit, I might want to move them in a certain way. I’ll commit and my status is clean.

Now, I could switch to main and do this to move one file:
```
git checkout qa -- README.md
```
However, I’m just doing that without review. So, I wouldn’t do that and you shouldn’t either. Instead, let’s create a PR.

Using a Branch for Peer Review

A PR is a pull request, but it also means we ask for some peer review. In my case, I’ll use this code to create a new branch and then pull in two changes: my modified readme and one of the other changes.
```
git checkout -b r9-qa

git checkout qa -- README.md

git checkout qa -- "V6__create proc gettwo.sql"
```
Ignore my typos, but I’ve run 4 commands: checkout 3 times and status once.

Now I’ll commit and push these changes to my r9-qa branch.

Once I do that, Github detects a push and asks me to create PR. I do, and I see the PR with these changes.

Now I can proceed with my flow, and my cherry picked changes are captured.

There is a git cherrypick command, but often I find I need random files from multiple commits, while ignoring others in the commit. This works well for database releases.

A few references:
SQL New Blogger

This post took my about 20 minutes to write. However the learning and experimentation took over an hour as I read various links and dug into the docs and various posts.

This is a very useful skill, and one you can discuss in an interview. Write a similar post and you’ll be prepared for this type of question in an interview.
August 28, 2024
Deleting Old Local Git Branches–#SQLNewBlogger
I had a lot of local branches for a repo (actually a few repos). I know these are old and not used anymore, so how do I delete them? This post shows how to do that on Windows.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. You can see all posts on Git as well.

The Problem

As I’ve been making changes for various SQL Saturday events

I saw this SO post, which was a good starting point. I grabbed this code, which I’ll explain below.
```
git fetch -p && git branch -vv | awk '/: gone]/{print $1}' | xargs git branch -d
```
The problem is this doesn’t work on Windows.

Running This on Windows

I assume most of you installed Git and have Git Bash. The xargs and awk commands are Unix/Linux ones, so you need a bask shell to tun them

The solution for me, was to open a bash shell in the repo with the right click menu on Windows.

Then run the code:

Local branches removed. Well, almost; read the next section.

Additions

Note that in the first execution, I had two errors noting that there were some unmerged branches. When I look at these, I see they were old branches, ones that haven’t been used in years. I’m guessing either I was fixing something for someone, or they fixed something in another branch.

So, I forced delete by re-running the command a capital D.

How this Works

This code uses some Unix based utilities that I haven’t used in a long time. The flow of this is similar to how PowerShell, or even VBScript works, but on a single line. In this case, this code:
- Gets a list of branches from the remote with git fetch after pruning the references for local branches that don’t exist on te remote.
- Run the branch command with the verbose output. Could be –verbose as well
- Take the output if the previous step and pipe that through awk. This command will parse text, looking for “gone” in a line and then printing the branch name.
- This text is then taking with xargs and passing it to the git branch command with the delete option.
Note this doesn’t force delete branches.

SQL New Blogger

This post took about 20 minutes to write. I spent about 5 minutes checking a few code examples online, and then tried one after I’d killed branches from GitHub. I don’t have a great solution there, but I don’t do this often and I can click a few buttons to manage this.

I then structured this post with a few screenshots and spent 15 minutes working on it. I’d actually sketched it in 5 minutes with the major sections and a sentence in each and realized this would be quick to write, so I just filled it in on a Sunday morning.

You could do this as well and give an interviewer something to ask you in the next interview. This might catch their eye. I’d also suggest (and I will) do a few posts on awk and xargs. Those are good skills to have and you might spent 20 minutes experimenting and having fun with them.
July 8, 2024