Voice of the DBA

Tag: T-SQL

RANK() vs DENSE_RANK(): #SQLNewBlogger

I haven’t done one of these in awhile, but I saw an article recently about this and decided to explain it to myself, but in a slightly different way. You’ll see how I checked on RANK() vs DENSE_RANK() below.

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

Getting Started

Imagine you’ve never used these functions, but you need to rank some data. Let’s say that you have a series of things that measure sale and you want to know who has done the best job this week. I’ll start with AdventureWorks, and we’ll assume it’s June 2011. If I look at some data from that database, I see this. Order Headers and Order Details from that range.

Who are the top 5 salespeople?

I put this into SQL Prompt AI: who are the top 5 salespeople with ranking for the first week of june 2011. Give me the salespersonid, the rank and sales total

I got this code, though with 2011-06-01 as the first date, which didn’t work as there were no orders that week. I adjusted this back to May 31. You can see this uses RANK() as the aggregate.

I then asked about DENSE_RANK, because I heard about it and might want to use it.

Prompt adjusted the query to add DENSE_RANK, but I then clicked the “add explanation” next prompt prompt (is that a thing now?)

I now see an explanation, though in this example, this doesn’t help.

I then asked Prompt to help me understand.

I got back this code. You can see in the results, that both show me the ties for 2nd with people that have the same sales totals. However, with RANK, we jump to 5, since there are 3 people tied at 2 (presumbly taking 2,3,4th place). The dense ranking goes from the ties at 2 to 3.

I asked for an explanation:

I got this. This is the text from SQL Prompt AI (in italics):

# RANK vs DENSE_RANK: When to Use Each

The key difference between RANK and DENSE_RANK is how they handle ties:

## RANK
– Assigns the same rank to ties
– Skips the next rank(s) after ties
– Creates “gaps” in the ranking sequence
– Example: 1, 2, 2, 4, 5 (note the missing 3)

## DENSE_RANK
– Also assigns the same rank to ties
– Does NOT skip ranks after ties
– Creates a “dense” or consecutive ranking without gaps
– Example: 1, 2, 2, 3, 4 (no gaps)

## When to use DENSE_RANK:
1. **Consecutive Ranking Systems**: When you need a continuous sequence without gaps, such as in competitions where tied participants should receive the same position, but the next participant should be ranked immediately after.

2. **Top N Analysis**: When identifying items in the “top 10” or similar groups, DENSE_RANK ensures you get exactly 10 distinct ranks regardless of ties.

3. **Percentile Calculations**: When calculating percentiles or quartiles where a continuous ranking is needed.

4. **Educational Grading**: When multiple students with the same score should receive the same rank, but the next student should be ranked immediately after.

## When to use RANK:
1. **Traditional Competition Standings**: Olympic medals and many sports competitions use this approach – if two athletes tie for first place, the next finisher gets bronze (3rd place, not 2nd).

2. **Statistical Analysis**: When you need to preserve the mathematical properties of ranks where the sum of all ranks should equal the sum of consecutive integers.

3. **Scenario Where “Skipped” Positions Matter**: When it’s important to account for how many entities are ahead of each entity.

Your example query perfectly demonstrates this difference:
– Employees with $145,000 all get rank 2 with DENSE_RANK
– With RANK, they get position 2, but the next rank is 5 (skipping 3 and 4) because there are three employees sharing rank 2

Each function has its appropriate use cases depending on whether you want a continuous ranking sequence or one that accounts for the number of entities at each rank level.

What I Learned Here

I knew most of this, but in terms of trying to understand better where/why to use each, the explanation made sense. I hadn’t thought about the Educational Grading for DENSE_RANK, but that makes sense. I knew the others, and often that the TOP N needs the correct number of rankings.

For RANK, we use the competition ranking with volleyball, so I see that all the time, but I don’t do a lot of statistical analysis where this has come up, but it’s good to keep it in mind.

To me, I often go back to the client, or think about both of these when I rank things. I will do what Prompt AI did and put both in a query, see the differences and then decide (or let someone else decide) how to present the ranking data.

SQL New Blogger

When I started to explain this, I first opened the DOC pages and was going to use those to write this and thought, this is a good place to test AI models and see. I took a different tact and incorporated some AI into my work, because that’s where the world is going. Like it or not.

This went faster with AI, and less cognitive load from me. I wrote this post, but I used AI to help set things up, generate code, and get me there quicker. You could do the same thing and use a blog to showcase that you’re learning how AI is a tool you can use.

SQL Prompt can help you learn more about your code, in addition to all the cool time saving features. Give it a try today.

FWIW, I asked CoPilot the same query and got an answer (0 people), without code. When I asked for code, I did get it, but not quite what I wanted.

February 18, 2026
Aging Code for T-SQL Tuesday #195

It’s that time of the month again, with T-SQL Tuesday coming along. I managed to not forget about this and checked with the host. He had an issue, but fortunately I got a friend to step up.

This month Pat Wright has an interesting question, asking how your code has aged. He and I have had a few conversations lately about getting older and when I asked him to host, this was a perfect choice.

I’m definitely getting older, but what about my code?

25 Years Later

Actually a little more, but I wrote a series called “Tame Those Strings” for Swynk a long time ago. That became Database Journal, but during the switch, they stopped paying us authors. A few of us started SQL Server Central and we went live 25 years ago.

In that piece, I referenced the oldest article, which is Tame Those Strings Part 4 – Numeric Conversions. There’s also a part 3, but in those pieces, is that code still useful?

A bit.

These are basic articles looking at string functions that are still heavily in use today. The idea of cleaning phone numbers using REPLACE is still something we might do today. If we are using SQL Server 2025, there are additional functions, but I still see a lot of code that still use multiple CHARINDEX+SUBSTRING or REPLACE functions.

I asked the Prompt AI if it could do better.

It gave me two pieces of code. The first is nested REPLACE() statements. This works, but I find this hard to read. I’d rather have separate statements, for ease of maintenance.

The second is a single statement, using a CASE and STUFF and XML to clean things. I like this, thought it’s a semi-complex way of doing things. However, it works.

Has the code aged well? I think it’s OK. There are better ways, as shown with the STUFF/XML version, which wouldn’t have worked in SQL Server 2000. Still, the use of REPLACE is a common technique still used today.

For part 4, with the use of LTRIM and STR(), today we have FORMAT, which is cleaner. However, it’s likely less performant. In a simple test of 500,000 values, the FORMAT takes over 600ms to return the results while my LTRIM/STR combination consistently runs in the 460ms or quicker range.

I think my code aged well.

February 10, 2026
Learning from Mistakes: T-SQL Tuesday #194

We’re a week late, once again my fault. I was still coming out of the holidays and forgot to check on my host. Luckily, Louis Davidson (who did have Feb) agreed to go early. He has a nice invite, and I am glad to answer.

This is the monthly blog party on something SQL Server/T-SQL/etc. related. I have about half of 2026 covered, but if you would like to host, I’d love to have you. Ping me on X/LinkedIn/BlueSky.

A Mistake

Since we aim for T-SQL, I decided to ping something I’ve done a number of times in T-SQL, and sometimes still break. However, a little testing has helped me (mostly) keep this from getting to production.

Always have testing in place.

I am good at T-SQL, but not amazing. I learn things from others all the time, and these days, take help from AIs, though I do test and double check what they do.

One of the places I’ve struggled with is with outer joins. Usually left/right outer joins where I am trying to get a list of things from a join, but filter out some of the missing items. Here’s an example from Northwind. I want a list of customers joined to orders, but I might have a way where customers filter out those who haven’t been charged freight. There’s likely some business reason, but it escapes me now.

If I run this query, I get lots of stuff.

That doesn’t seem right. If I check, I see this:

What’s the problem here? Well, the main issue is one I keep doing, fortunately, I catch this. If I move the Freight IS NULL to WHERE instead of the ON, it works. You can see this below.

If I see too much data, which can be hard to catch in large result sets, I can ask Prompt AI.

I get the response I’d expect from most AIs.

How do I test for this? Well, the best way is to have test coverage for queries. For example, I might build a test like this:

EXEC tsqlt.NewTestClass @ClassName = N’QueryTests’ — nvarchar(max)
go

CREATE OR ALTER PROCEDURE [QueryTests].[TestCustomersWithoutOrders]
AS
BEGIN
— Arrange
— Create temporary table to hold expected results
DECLARE @Expected TABLE
(
CustomerID nchar(5)
)

— Insert the expected result – customers with no orders
INSERT INTO @Expected
SELECT CustomerID
FROM dbo.Customers
WHERE CustomerID NOT IN
(
SELECT DISTINCT CustomerID FROM dbo.Orders WHERE CustomerID IS NOT NULL
)

— Act
— Create temporary table to hold actual results
DECLARE @Actual TABLE
(
CustomerID nchar(5)
)

— This should be the query that’s being tested
INSERT INTO @Actual
SELECT DISTINCT
Customers.CustomerID
FROM dbo.Customers
LEFT OUTER JOIN dbo.Orders
ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.CustomerID IS NULL

— Assert
— Check that we have exactly 2 results
DECLARE @ActualCount INT =
(
SELECT COUNT(*)FROM @Actual
)

IF @ActualCount <> 2
BEGIN
EXEC tSQLt.Fail ‘Expected exactly 2 customers without orders, but got ‘,
@ActualCount;
RETURN;
END

— Check that we got the expected customers
IF EXISTS
(
SELECT 1
FROM @Expected e
WHERE NOT EXISTS
(
SELECT 1
FROM @Actual a
WHERE a.CustomerID = e.CustomerID
)
)
OR EXISTS
(
SELECT 1
FROM @Actual a
WHERE NOT EXISTS
(
SELECT 1
FROM @Expected e
WHERE e.CustomerID = a.CustomerID
)
)
BEGIN
EXEC tSQLt.Fail ‘The actual set of customers without orders does not match the expected set.’;
END
END;

That’s a lot of code, but I can see it works. I get two customers back, which is what I expect. Lines 53-58 have my query being tested above. If I run the test, it passes.

If I change those lines to put the filter in the ON clause (and remove WHERE), it fails.

Ideally I’d have this in a proc so I can change/tune this and compare plans, run tests easily, etc.

This is a mistake I still make at times today, albeit rarely. Now I write some tests to look for my mistake. Maybe that’s the thing I’ve learned the most: have tests for my code.

January 20, 2026
Refactoring SQL Code

One of the things I see software developers often talking about is how they refactor code. As they touch a class, method, etc., they may take the time to refactor the code to make it cleaner, perform better, or just add some documentation. It seems that a regular part of a software developer’s job is refactoring code in the codebase.

That is unless they see a “don’t touch this, no idea how it works” comment. There are plenty of those, and often everyone leaves that code alone.

I was thinking about this when I saw this article on strategies to refactor sql code. The article seems written more for PostgreSQL, but there are items that relate to T-SQL as well. The main thrust of the article is about trying to rewrite code to DRY (don’t repeat yourself). The more changes you can make to shrink code, either to make it easier to read or avoid repeating those copy/paste items, the better off your team will be. It’s easy to think those copies aren’t a big deal, but it’s easy to update code in one place because that solves the problem you were given, and forget to fix all the copies.

I don’t know that anyone should implement all the techniques listed, but they are things to think about. Using CTEs, Views, APPLY, the WINDOW clause, and more can help improve the health of your codebase and make it easier for all the members of your team to understand how the system works.

I wonder how many of you have a refactor mentality when you touch code, or do you tend to leave things alone and add new queries/objects/etc. to your database. I wonder if the fear of breaking something that might be used by other code is on your mind. Or maybe you suffer from “not invented here” (NIH) and just add your own code.

If you refactor code, then what things do you look to change or improve? Any tips/tricks/guidelines you’d share with others? If you don’t refactor code, why not?

I think testing is a big part of refactoring. If you have tests, then you can be less worried about your changes breaking something. There is a great video on practical refactoring. It’s from the software engineering view, and it’s long, but it’s worth a watch if you have a few moments.

I wish more people tested their SQL code and refactored poorly written (or poorly performing) code on a regular basis.

Steve Jones

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

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

December 12, 2025