When each wins, and how your prompt affects which one AI picks.
Intro
This demo is based on something that happened at my work. A T-SQL UPDATE had a logical bug. Our developers used Claude to rewrite it. The bug was fixed, but the query ran slowly and caused blocking on the server. The longer an UPDATE runs, the longer it holds its locks, and the longer other sessions wait on them.
The rewrite was logically correct. It just picked a way of getting the latest row per group that didn't fit our query.
In this demo, I will show you the performance difference between CROSS APPLY TOP (1) and ROW_NUMBER. Neither one is always better, so it is important to pick the pattern that matches your query. I will also show you how small changes in your prompt can push AI toward one pattern or the other.
I ran this demo on SQL Server 2022.
Setup
First, let's create a demo database:
------------------------------------------------------------------------------
-- 1. Create a demo database
------------------------------------------------------------------------------
USE master;
GO
IF DB_ID(N'Demo10062026') IS NOT NULL
BEGIN
ALTER DATABASE Demo10062026 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE Demo10062026;
END;
GO
CREATE DATABASE Demo10062026;
GO
Next, create four tables: AppUser, Task, TaskAssignment, and Message.
- An app user can be assigned to one or more tasks. An assigned user is a task taker.
- Each task has a dedicated chat, and users (including task givers and task takers) send messages to it.
------------------------------------------------------------------------------
-- 2. Create tables
------------------------------------------------------------------------------
USE Demo10062026;
GO
CREATE TABLE dbo.AppUser
(
UserID int NOT NULL CONSTRAINT PK_AppUser PRIMARY KEY CLUSTERED,
DisplayName nvarchar(50) NOT NULL
);
CREATE TABLE dbo.Task
(
TaskID int NOT NULL CONSTRAINT PK_Task PRIMARY KEY CLUSTERED,
Title nvarchar(100) NOT NULL
);
CREATE TABLE dbo.TaskAssignment
(
TaskAssignmentID int IDENTITY(1,1) NOT NULL
CONSTRAINT PK_TaskAssignment PRIMARY KEY CLUSTERED,
TaskID int NOT NULL CONSTRAINT FK_TaskAssignment_Task REFERENCES dbo.Task (TaskID),
UserID int NOT NULL CONSTRAINT FK_TaskAssignment_AppUser REFERENCES dbo.AppUser (UserID),
AssignedAt datetime2(0) NOT NULL CONSTRAINT DF_TaskAssignment_AssignedAt DEFAULT SYSUTCDATETIME(),
LastMessageBody nvarchar(400) NULL,
LastMessageSender nvarchar(50) NULL
);
CREATE TABLE dbo.Message
(
MessageID int IDENTITY(1,1) NOT NULL
CONSTRAINT PK_Message PRIMARY KEY CLUSTERED,
SenderID int NOT NULL CONSTRAINT FK_Message_AppUser REFERENCES dbo.AppUser (UserID),
TaskID int NOT NULL CONSTRAINT FK_Message_Task REFERENCES dbo.Task (TaskID),
SentAt datetime2(0) NOT NULL,
Body nvarchar(400) NOT NULL -- wide column so the clustered index is realistically large
);
GO
CREATE NONCLUSTERED INDEX IX_Message_TaskID_MessageID
ON dbo.Message (TaskID, MessageID)
GO The nonclustered index on Message (TaskID, MessageID) lets a query seek directly to a task's newest message. It doesn't cover SenderID, SentAt, or Body, so a key lookup to the clustered index may be needed if the query needs these additional fields.
In this demo, "latest" means the highest MessageID. SentAt increases with MessageID, so ordering by either gives the same result. MessageID is unique, which avoids ties.
Finally, let's populate the tables. The data has 200,000 users, 10,000 tasks, 2 million messages (200 per task), and 50,000 assignments (5 per task).
------------------------------------------------------------------------------
-- 3. Populate the data
------------------------------------------------------------------------------
-- Users 1..200,000
WITH n AS
(
SELECT TOP (200000) rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b
)
INSERT dbo.AppUser WITH (TABLOCK) (UserID, DisplayName)
SELECT rn, N'User ' + CAST(rn AS nvarchar(10))
FROM n;
-- Tasks 100000..109999
WITH n AS
(
SELECT TOP (10000) rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b
)
INSERT dbo.Task WITH (TABLOCK) (TaskID, Title)
SELECT 99999 + rn, N'Task ' + CAST(99999 + rn AS nvarchar(10))
FROM n;
GO
-- Messages: rn 1..2,000,000.
WITH n AS
(
SELECT TOP (2000000) rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b
)
INSERT dbo.Message WITH (TABLOCK) (SenderID, TaskID, SentAt, Body)
SELECT SenderID = ((rn - 1) % 200000) + 1,
TaskID = 100000 + ((rn - 1) % 200000) / 20,
SentAt = DATEADD(SECOND, rn, CAST('2025-01-01' AS datetime2(0))),
Body = N'Message ' + CAST(rn AS nvarchar(10)) + N' ' + REPLICATE(N'x', 150)
FROM n
ORDER BY rn; -- makes IDENTITY assignment follow rn
GO
-- Assignments: 5 users per task. UserID = rn * 4, so only every 4th user is
-- assigned, each to exactly one task. Task 109947 = rn 49736..49740,
-- which is UserIDs 198944, 198948, 198952, 198956, 198960.
WITH n AS
(
SELECT TOP (50000) rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b
)
INSERT dbo.TaskAssignment WITH (TABLOCK) (TaskID, UserID)
SELECT TaskID = 100000 + (rn - 1) / 5,
UserID = rn * 4
FROM n
ORDER BY rn;
GO
When CROSS APPLY top (1) performs better than ROW_NUMBER
Enable the actual execution plan (Ctrl+M in SSMS) before running these queries so you can see how each one reads the data.
SET STATISTICS IO, TIME ON;
PRINT '===== Query A: ROW_NUMBER derived table =====';
SELECT ta.TaskID, lm.Body, lm.SenderID, lm.SentAt
FROM dbo.AppUser AS taskTaker
JOIN dbo.TaskAssignment AS ta
ON taskTaker.UserID = ta.UserID
JOIN (
SELECT TaskID, SenderID, Body, SentAt
FROM (
SELECT m.TaskID,
m.SenderID,
m.Body,
m.SentAt,
rn = ROW_NUMBER() OVER (PARTITION BY m.TaskID
ORDER BY m.MessageID DESC)
FROM dbo.Message AS m
) AS latest
WHERE latest.rn = 1
) AS lm
ON ta.TaskID = lm.TaskID
WHERE taskTaker.UserID = 198960;
PRINT '===== Query B: CROSS APPLY TOP (1) =====';
SELECT ta.TaskID, lm.Body, lm.SenderID, lm.SentAt
FROM dbo.AppUser AS taskTaker
JOIN dbo.TaskAssignment AS ta
ON taskTaker.UserID = ta.UserID
CROSS APPLY (
SELECT TOP (1)
m.SenderID,
m.Body,
m.SentAt
FROM dbo.Message AS m
WHERE m.TaskID = ta.TaskID
ORDER BY m.MessageID DESC
) AS lm
WHERE taskTaker.UserID = 198960;
SET STATISTICS IO, TIME OFF;
GO
CROSS APPLY runs the TOP (1) subquery once for each task the user is assigned to, and user 198960 has only one. SQL Server seeks the (TaskID, MessageID) index straight to the task's newest message, then does one key lookup for SenderID, SentAt, and Body.
Query A has to number the messages before it can keep rn = 1. Because the filter is on the user rather than on TaskID, SQL Server can't apply it inside the derived table. It reads all 2 million messages across all 10,000 tasks, keeps the latest message per task, and only then joins to TaskAssignment, which throws away all but one task.
In short, CROSS APPLY TOP (1) performs better when the outer query processes only a few rows and an index lets each lookup seek straight to the latest row.
When ROW_NUMBER performs better than CROSS APPLY top (1)
SET STATISTICS IO, TIME ON;
PRINT '===== Query A: ROW_NUMBER derived table =====';
SELECT
ta.TaskID, lm.Body, lm.SenderID, lm.SentAt
FROM dbo.TaskAssignment AS ta
JOIN (
SELECT TaskID, SenderID, Body, SentAt
FROM (
SELECT m.TaskID,
m.SenderID,
m.Body,
m.SentAt,
rn = ROW_NUMBER() OVER (PARTITION BY m.TaskID
ORDER BY m.MessageID DESC)
FROM dbo.Message AS m
) AS latest
WHERE latest.rn = 1
) AS lm
ON ta.TaskID = lm.TaskID
WHERE ta.AssignedAt >= DATEADD(year, -1, SYSDATETIME())
PRINT '===== Query B: CROSS APPLY TOP (1) =====';
SELECT ta.TaskID, lm.Body, lm.SenderID, lm.SentAt
FROM dbo.TaskAssignment AS ta
CROSS APPLY (
SELECT TOP (1)
m.SenderID,
m.Body,
m.SentAt
FROM dbo.Message AS m
WHERE m.TaskID = ta.TaskID
ORDER BY m.MessageID DESC
) AS lm
WHERE ta.AssignedAt >= DATEADD(year, -1, SYSDATETIME())
SET STATISTICS IO, TIME OFF;
GO
CROSS APPLY still runs the TOP (1) subquery once per outer row, but there are now 50,000 of them. Each run is a separate index seek plus a key lookup for SenderID, SentAt, and Body.
Query A still reads all 2 million messages, but only once. It computes the latest message for each of the 10,000 tasks, then joins those rows to the 50,000 assignments. One pass over the table costs less than 50,000 separate seeks and lookups.
In short, a ROW_NUMBER derived table performs better when the outer query processes many rows, because one pass over the table is cheaper than one seek per row.
Returning 50,000 rows isn't realistic for most applications. However, you can still see this pattern in reporting, where the outer query processes many rows that are then aggregated before the result is returned.
Bug Scenario
Here is the bug we want Claude to help us fix. When a task has several messages, the JOIN to Message matches multiple rows, and the UPDATE writes whichever one SQL Server happens to pick. We want each task assignment to get exactly one message: its task's newest one.
UPDATE ta
SET ta.LastMessageBody = lm.Body
FROM dbo.TaskAssignment AS ta
JOIN dbo.AppUser AS taskTaker
ON taskTaker.UserID = ta.UserID
JOIN (
SELECT TaskID,
Body
FROM dbo.Message AS m
GROUP BY TaskID, Body
) AS lm
ON ta.TaskID = Lm.TaskID
WHERE taskTaker.UserID = 198960; CROSS APPLY TOP (1) is the best fix here. A given user is assigned to only a few tasks, so, as we saw earlier, CROSS APPLY seeks the latest message for each of them instead of reading all 2 million messages.
UPDATE ta
SET ta.LastMessageBody = lm.Body
FROM dbo.TaskAssignment AS ta
JOIN dbo.AppUser AS taskTaker
ON taskTaker.UserID = ta.UserID
CROSS APPLY (
SELECT TOP (1) m.Body
FROM dbo.Message AS m
WHERE m.TaskID = ta.TaskID
ORDER BY m.MessageID DESC
) AS lm
WHERE taskTaker.UserID = 198960How the prompt changes what AI chooses
To test this, I used three prompts with slight differences in wording. I used Claude Opus 5.5 with memory disabled and started a new chat for each run. I ran each prompt 3 times. mssql2 is the name of the MCP server connected to the demo database.
All three prompts start the same way: "Connect to Demo10062026 database using mssql2. When a task has several messages, the JOIN to Message matches multiple rows, and the UPDATE writes whichever one SQL Server happens to pick." Each ends with "The original query is ..." The differences are in bold.
| Prompt | Request | Pattern Claude chose |
|---|---|---|
| 1 | Fix the query so each assignment gets exactly one message: the task’s newest one. | CROSS APPLY TOP (1) in 3 runs |
| 2 | Fix the lm clause so each assignment gets exactly one message: the task’s newest one. | ROW_NUMBER in 2 runs |
| 3 | Fix the lm clause so each assignment gets exactly one message: the task’s newest one. Double check the query for performance. | CROSS APPLY TOP (1) in 3 runs |
What do these results tell us?
In Prompt 1, I asked Claude to fix the query. That left it free to restructure the whole UPDATE, and it chose CROSS APPLY TOP (1), the better fit when updating a single user's assignments.
In Prompt 2, the only change was "the query" → "the lm clause." My guess is that this narrowed the scope of the fix. Claude kept the JOIN to the derived table and fixed the problem inside it, which leads naturally to ROW_NUMBER. Switching to CROSS APPLY would mean replacing the JOIN itself, which is outside the lm clause.
Prompt 3 keeps the narrow "lm clause" wording but adds one sentence: "Double check the query for performance." That was enough for Claude to step outside the lm clause and choose CROSS APPLY TOP (1).
When you narrow the scope of a fix, AI may stay inside that scope even at the cost of performance. If performance matters, and on a busy server it usually does, say so in the prompt. AI output also varies between runs, so treat these results as an illustration, not a guarantee.
Key takeaways
- Neither pattern is always better. CROSS APPLY TOP (1) performs better when the outer query processes only a few rows, because it seeks the latest row for each one. A ROW_NUMBER derived table performs better when the outer query processes many rows, such as in reporting queries that aggregate the results, because one pass over the table is cheaper than thousands of seeks.
- CROSS APPLY TOP (1) depends on the right index. Without an index on the grouping and ordering columns, here (TaskID, MessageID), each CROSS APPLY call may have to scan instead of seek, and it loses its advantage.
- Small changes in the prompt can change what AI chooses. Asking Claude to fix "the lm clause" instead of "the query" was enough to steer it toward ROW_NUMBER. Adding "Double check the query for performance" steered it back.
- Writing a good prompt is now an important skill. When AI does what you asked instead of what you wanted, the gap is often in the prompt.
USE master;
ALTER DATABASE Demo10062026 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE Demo10062026;
