Voice of the DBA

Tag: T-SQL

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
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