Voice of the DBA

Tag: T-SQL

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
Enabling an Index: #SQLNewBlogger
I don’t do a lot of work with disabled index, but I learned how to re-enable one today, which was a surprise to me. This short post covers how this works.

The Scenario

Imagine that you have an index on a table. In my case, I created this index:
```
CREATE INDEX LoggerNCI ON dbo.Logger (LogID)
```
I can then disable this index with the following code:
```
ALTER INDEX LoggerNCI ON dbo.Logger DISABLE
```
I had assumed that ENABLE would be the opposite, but SQL Prompt taught me this wasn’t an option. I checked the docs, and sure enough, it’s not ENABLE.

It’s resume. This code turns the index back on and updates it.
```
ALTER INDEX LoggerCI ON dbo.Logger REBUILD
```
I can also use either of these items:
```
CREATE INDEX LoggerNCI ON dbo.Logger (logid) WITH DROP_EXISTING

DBCC DBREINDEX(Logger, LoggerNCI)
```
Interesting short moment the other day as I realized there are a few options here.

SQL New Blogger

While playing with this, I realized that I didn’t know all the ways this worked, so I spent 10 minutes after I’d worked with the code to put this together.

A nice short way to showcase some learning.
October 2, 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
Bad Stored Procedures

I don’t see a lot of SQL at The Daily WTF, but this one was great. It’s a stored procedure that was likely just converted from embedded code, as noted by the poster. It’s a strange set of code, that doesn’t quite make sense to me, and I can’t imagine why someone wrote it. Arguably, this is no better than having this code in a C# or ASP.NET application.

Or is it?

I think it is better. If I saw this code in a review or even in a production database, I could work on cleaning it up, adding protection against SQL Injection, and even tuning how it works to reduce the load on the database. I could likely wrap testing around this and get it deployed way quicker than if I were trying to update the source code for an app. More importantly, this is centralized code. If this is called from multiple places in the app code, I’ve fixed it once, not requiring an app developer, who has other work being piled on them, to spend time updating repeated sections of the code.

Even better, I could refactor some of the schema behind this stored procedure and easily find that my changes might affect this code. I could add a feature flag to this procedure and slowly migrate my schema in the background, without disturbing the user, again because the code is centralized. That’s a technique that most developers use in C#/Java/Python/etc., so why not in SQL?

I find it very interesting that a lot of developers refactor their classes and methods to better adhere to SOLID or some other practice, and they are happy to remove repeated code in their language. Yet, they don’t want to implement a stored procedure or function into their calls, essentially creating a database method for the things they need.

The more I work with legacy systems, the more value I see in using stored procedures. Every developer ought to know how to build them, and every developer ought to be able to create them in dev systems so they can easily deploy database and code changes together. More importantly, they can also share the load of tuning queries with operations staff, who may notice things in a production environment that are not apparent in development ones.

The big challenge in all of this is that database tooling is immature. Capturing your database code in source control is hard, and often it is a separate process from the one you follow for application code. I see some companies (including my employer) trying to make this easier, but there is a long way to go, and a lot of habits to change for developers.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

September 20, 2024