Voice of the DBA

Tag: SQLNewBlogger

Finding and Updating Duplicate IDs: #SQLNewBlogger
Finding duplicates was an interview question for me years ago, and I’ve never forgotten it. Recently I got asked how to easily do this and delete them, so I decided to write a couple of posts on the topic. This one looks at simple, single column IDs. The next one will look at more complex situations.

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

A Simple Scenario

Like many people, I like identity fields for primary keys. However, lots of people build tables like this:
```
CREATE TABLE PurchaseOrder
(
     poid INT IDENTITY(1, 1),
     purchaseordernumber VARCHAR(20),
     podate DATETIME,
     active INT
)
GO
```
No nullability, and no PK constraint. People think an identity prevents duplicates. Run the query above and then the script below.
```
INSERT INTO PurchaseOrder
(
     purchaseordernumber,
     podate,
     active
)
VALUES
('PO-2023-00001', '2023-01-15 09:30:00', 1),
('PO-2023-00002', '2023-01-22 11:45:00', 1),
('PO-2023-00003', '2023-02-05 14:20:00', 1),
('PO-2023-00004', '2023-02-18 10:15:00', 0),
('PO-2023-00005', '2023-03-03 16:30:00', 1),
('PO-2023-00006', '2023-03-17 08:45:00', 1),
('PO-2023-00007', '2023-04-02 13:10:00', 0),
('PO-2023-00008', '2023-04-15 15:25:00', 1),
('PO-2023-00009', '2023-05-01 09:50:00', 1),
('PO-2023-00010', '2023-05-14 12:05:00', 1),
('PO-2023-00011', '2023-06-01 14:40:00', 0),
('PO-2023-00012', '2023-06-15 10:35:00', 1),
('PO-2023-00013', '2023-07-02 16:55:00', 1),
('PO-2023-00014', '2023-07-17 08:20:00', 0),
('PO-2023-00015', '2023-08-03 11:30:00', 1),
('PO-2023-00016', '2023-08-18 13:45:00', 1),
('PO-2023-00017', '2023-09-04 15:10:00', 1),
('PO-2023-00018', '2023-09-19 09:25:00', 0),
('PO-2023-00019', '2023-10-05 12:40:00', 1),
('PO-2023-00020', '2023-10-20 14:15:00', 1)
GO
SET IDENTITY_INSERT dbo.PurchaseOrder ON
GO
INSERT INTO PurchaseOrder
( poid,
     purchaseordernumber,
     podate,
     active
)
VALUES
(19, 'PO-2023-00021', '2026-01-15 09:30:00', 1),
(14, 'PO-2023-00022', '2026-01-22 11:45:00', 1)
GO
SET IDENTITY_INSERT dbo.PurchaseOrder OFF
GO
```
Now if we select all the rows from this table, we might think things are fine. After all, all the purchaseordernumber fields are unique.

Checking Duplicates

I’ll run this query. Notice I use a GROUP BY on the poid to list these together with a count. In the image, we see some counts that are greater than 1, which indicates a duplicate. We are grouping, or putting all the rows with the same value together.

I often will add a HAVING clause to this, which lets me filter the grouped items. When I do that, I just see two items.

Notice if I change this to purchaseordernumber, I don’t get duplicates. This is because those are unique.

However, a lot of people often build software that edits using the underlying key, so they can edit the PO number. Let’s do that. I’ll change the PO number for id 19. First we’ll get the current values, and then re-query.

If we look below, we see separate purchase order numbers, but when we try to update one of them, we get two changed. Because we have duplicate hidden surrogate ID keys.

We want to fix this, so what can we do?

What we want to do is find the duplicate 14s and 19s (and others) and change them.

Fixing the Issue

While trying to fix this, I realized that one can’t update an identity field. That actually makes the fix really, really simple.

Since I want to give the rows new poid values, I need to find those rows which are duplicates and then re-insert them into the table. I also need a way to delete the old duplicate values as well.

This can be tricky, as the purpose of an identity (usually) is to ensure there are not duplicate rows. It’s possible every field in the row is duplicate, which could be an issue. In this case, I’d likely just copy the data back in and delete all the “old” rows, which were the same.

In my case, the purchaseordernumber is different, so we can use that with the date to decide which is a duplicate and which row we keep.

SQL New Blogger

This is a little longer post, and it somewhat got away from me, but this isn’t an easy thing to write about, nor is it short. Easy to mess this up.

This post took me about 45 minutes to write. The code part wasn’t long, but I had to think about how to frame the issue with test code and explain that. SQL Prompt made the coding easy once I knew what I wanted. I built this over 3-4 days, working on it at 5-10 minutes at a time.

That’s a great way to tackle complex topics.

You could do this and impress an interviewer. Highlight this post in your resume/LinkedIn/etc.
March 18, 2026
Identity Columns Can’t Be Updated: #SQLNewBlogger
I’m not sure I knew identity column values could not be updated. I ran into this while trying to solve a problem recently and had to check the error I was getting. This post shows what happened.

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

Setup

A quick setup for you. I need to go to the store soon, so hence, here is my sample table (created and filled by SQL Prompt).
```
CREATE TABLE Vodka
( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  brandname VARCHAR(100) NOT NULL
  , rating TINYINT
);INSERT INTO dbo.Vodka
(
    brandname,
    rating
)
VALUES
('Grey Goose', 9),
('Belvedere', 8),
('Absolut', 7),
('Smirnoff', 6),
('Stolichnaya', 8),
('Ketel One', 9),
('Tito''s', 8),
('Ciroc', 7),
('Skyy', 6),
('Russian Standard', 7););
```
I then tried this:

OK, what about IDENTITY_INSERT. I know this isn’t an insert, but I thought this “unlocked” the identity column. It doesn’t work.

I searched on MS Learn and found the UPDATE statement documentation. In here, you can see what it says below. I can’t do this.

The error reference provides no info, but apparently this isn’t a thing.

What’s amazing to me is that in 30 years either I’ve never done this, or I’ve rarely encountered it and forgotten. Either is possible.

In any case, if I want to change this, I likely need to “re-insert” the row with a new value (either take the seed or use identity_insert) and then delete the old one.

Crazy.

SQL New Blogger

I was testing something else and ran across this. I decided it’s a great showcase of me learning something and giving a workaround. I’ll show the workaround in another post, which is actually about the thing I was doing.

Of course, that post needs to change.

This took about 10 minutes to write.
February 25, 2026
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
Database Collation Matters for Unicode: #SQLNewBlogger
While trying to work with Unicode data, I found some issues with collation. This post showcases what I’ve seen, with probably not enough answers. The collation/UTF stuff is still slightly confusing to me.

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

Noticing Problems

I was doing some testing with Unicode data and noticed this sentence in the docs for UNISTR() (image below): “The database collation must be a UTF-8 collation if the input is of char or varchar data types.”

I started experimenting with SQL 2022 with a default, US database. I ran this code:
```
SELECT N'Denver ' + NCHAR(0x1F601), DATABASEPROPERTYEX('sandbox', 'Collation')
```
That gave me unexpected results. The inputs aren’t char or varchar. They are NCHAR.

Strange. I’d have expected this to work. Let’s try the COLLATE clause. That should help.

It doesn’t.

One Solution

I decided to create a new database to test things. First, I ran this code to create a database using a UTF-8 collation:
```
CREATE DATABASE UnicodeTest COLLATE Latin1_General_100_CI_AS_SC_UTF8
```
Next, I tried my test. Same code as above, different database.

This works. I see my Unicode characters.

Why, I’m not sure. I would think that my requesting a collation for a query would work, but I see this in the docs, which notes this is for ORDER BY.

In the Write International T-SQL Statements doc, there is this:

I’m not sure what UCS-2 means when I’m querying in memory only, but apparently this matters.

An Explanation

The real answer is found in the NCHAR() docs. In here, the arguments section notes this:

The key is the Unicode value. NCHAR() handles up to 0xFFFF (4 Fs). My value is 0x1F40E (5 characters), so it’s out of range for the values that are handled with a non SC collation.

If I return to my Sandbox, non SC collation database, I can get Unicode characters, as long as they are below the FFFF threshhold.

A fun little experiment, where I learned something.

SQL New Blogger

This is a great example of my finding a problem, digging in, and solving it. Around some other work, this probably took me about 30 minutes to figure out with some reading and experimenting. Then about 15 minutes to write this post.

This is something you could easily do and showcase your knowledge as someone looking to learn and grow.
December 3, 2025