Birthday Brain Teasers

Tomorrow is my birthday! 🥳 I will be 80 feet underwater, where Teams cannot reach me and nothing deadlocks.

But before I go under, something fun. Four puzzles. They all look trivial, but they also all return results you weren't expecting. Junior DBA or seasoned MVP, I'd like to see your answers in the comments before you go into SSMS to run the queries -- that will make this fun.

Challenge 1: The Two Functions That Are Not the Same

Every report writer reaches for ISNULL and COALESCE to swap a blank for something friendlier. Most of us treat them as the same thing. Here they are, side by side, on the same value:

DECLARE @DiveNote VARCHAR(4) = NULL;

SELECT ISNULL(@DiveNote,   'Cancelled') AS Using_ISNULL,
       COALESCE(@DiveNote, 'Cancelled') AS Using_COALESCE;

Before you run it: both columns say 'Cancelled', right? Commit to an answer, then run it and see.

1. Explain why one column does not say what you expected.
2. Name one more way ISNULL and COALESCE quietly disagree.

Hint: one of these two functions cares very much about the first value it is handed.

Challenge 2: The Filter That Returns Everything

The sites I am hoping to dive are in #Dives, and the one I've already enjoyed is in #Logged:

-- sites I want to check out
IF OBJECT_ID('tempdb..#Dives') IS NOT NULL DROP TABLE #Dives;
CREATE TABLE #Dives (DiveSite VARCHAR(50));
INSERT INTO #Dives VALUES ('Yucab'), ('Santa Rosa Wall'), ('Columbia');

-- sites already logged 
IF OBJECT_ID('tempdb..#Logged') IS NOT NULL DROP TABLE #Logged;
CREATE TABLE #Logged (SiteName VARCHAR(50));
INSERT INTO #Logged VALUES ('Palancar Caves');

The question is simple. Which of my hoping-to dives have I already logged? 🤿

Before you run it: how many rows will come back, and which ones? Commit to an answer, then run it and see.

SELECT DiveSite
FROM #Dives
WHERE DiveSite IN (SELECT DiveSite FROM #Logged);

1. Explain why a filter for a single logged site behaves the way it does.
2. Change one thing so SQL Server throws a loud error here instead of quietly handing you the wrong answer.

Hint: look very closely at the column names in the two tables. SQL Server never did.

Challenge 3: The Running Total That Lies

Here is my dive log with a running total of Joy Points, ordered by date. Two dives on July 2, one on July 3:

IF OBJECT_ID('tempdb..#DiveLog') IS NOT NULL DROP TABLE #DiveLog;
CREATE TABLE #DiveLog (
    DiveDate DATE,
    Site VARCHAR(50),
    JoyPoints INT
);

INSERT INTO #DiveLog VALUES
('2026-07-02', 'Dalila', 100),
('2026-07-02', 'el Cedral', 100),
('2026-07-03', 'Palancar Horseshoe', 50);

-- running total
SELECT DiveDate, Site, JoyPoints,
       SUM(JoyPoints) OVER (ORDER BY DiveDate) AS RunningJoy
FROM #DiveLog;

A running total, so the obvious answer for RunningJoy is 100, then 200, then 250. Right?

Run it. Notice the first two rows show the same number, and the running total is already ahead of itself on dive one. Then tell me:

1. Why does the total leap forward before the second dive has happened?
2. What single clause makes it count the way a running total actually should?

Hint: the default window frame is not the one you think you asked for.

Challenge 4: Pin My Dive to the Top

No trick this time -- just a clever technique for ordering when you need to do something funky. It happens. Just read. You'll get what I mean.

IF OBJECT_ID('tempdb..#Wishlist') IS NOT NULL DROP TABLE #Wishlist;
CREATE TABLE #Wishlist (DiveSite VARCHAR(50));
INSERT INTO #Wishlist VALUES 
('Santa Rosa Wall'), ('Palancar Caves'), ('Columbia'), ('Yucab');

I want 'Palancar Caves', the site I have already logged, pinned to the very top of the list when I query my dives. Everything else should fall underneath alphabetically. One SELECT, no UNION.

Write the ORDER BY that does it in the comments -- and if you use something other than a CASE, I want to see it.

Hint: you are allowed more than one sort key, and the first one does not have to be a column.

Drop Your Answers Below! 👇

No peeking into SSMS until you've committed to your answers. Post your answers for each challenge in the comments, and tell me why.

This past year has been a big one for me at sqlfingers and I thank you all for being part of my journey. Cheers to another trip around the sun... ideally without too many deadlocks. 🥂