In the world of SQL Server, we are often taught that COALESCE is just a fancier, standard-compliant version of ISNULL.

Official Syntax

According to the official Microsoft documentation, the definition is straightforward. It evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

COALESCE ( expression [ ,...n ] ) 
COALESCE (Transact-SQL) - SQL Server
Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
LEARN.MICROSOFT.COM

Under the hood, however, SQL Server translates this simple function into a more complex CASE expression:

CASE
    WHEN (expression1 IS NOT NULL) THEN expression1
    WHEN (expression2 IS NOT NULL) THEN expression2
    ...
    ELSE expressionN
END

This definition hides a dangerous implementation detail. If you run the query below enough times in SQL Server, you will eventually get a NULL result, bypassing your hardcoded safety net of 2.

SELECT COALESCE(CASE WHEN RAND() <= 0.5 THEN 1 END, 2);

The Smoking Gun: SSMS Execution Results

If you execute this query repeatedly in SQL Server Management Studio, you would expect to see only 1 or 2. However, because of the "Double Evaluation" bug, here is what happens after a few runs:

Execution #1: Success (RAND roll: 0.2)
00:00:00
(No column name)
1 1
Query executed successfully. Rows: 1
Execution #2: Success (RAND roll: 0.8)
00:00:00
(No column name)
1 2
Query executed successfully. Rows: 1
Execution #3: THE VOLATILITY TRAP (Double Evaluation)
Unexpected NULL
(No column name)
1 NULL
⚠️ Execution result differs from deterministic logic. Rows: 1

The "Double Dip" Execution

The issue arises because the expression A appears twice in the expanded logic: once in the WHEN check, and again in the THEN return.

If expression A is deterministic (like a column value or a static number), this doesn't matter. But in our query, A is non-deterministic: Every time you touch it, RAND() re-rolls the dice.

The Gotcha! SQL Server evaluates the random expression once to check if it exists (unlocking the door), and then evaluates it again to return the result (walking through the door). If the dice roll changes between these two steps, you get a NULL.

Visualizing the Failure

Here is the logic flow that leads to the impossible NULL:

STEP 1: INITIAL CHECK Evaluating RAND() <= 0.5 STEP 2: THE RETURN RE-ROLLING NEW VALUE SAFE FALLBACK Returns 2 FALSE (0.8) TRUE (0.3) 1 STILL < 0.5 NULL NOW > 0.5!

The "Volatility Trap" Logic Flow: One check, one return, two evaluations.

How to Fix It

To prevent this "Volatility Trap," you must force SQL Server to evaluate the random logic only once.

Option 1: Use ISNULL (T-SQL Specific)

ISNULL guarantees the expression is evaluated only once.

SELECT ISNULL(CASE WHEN RAND() <= 0.5 THEN 1 END, 2);

Option 2: Subqueries (The Standard Way)

Materialize the random value in a derived table or Common Table Expression (CTE) first, then coalesce the result.

SELECT COALESCE(Val, 2)
FROM (SELECT CASE WHEN RAND() <= 0.5 THEN 1 END AS Val) AS T;

Reference & Further Learning

For a detailed breakdown of the performance and behavior differences, I highly recommend this video by Erik Darling:

Erik Darling SQL Video
A Difference Between ISNULL And COALESCE in SQL Server
Erik Darling • YouTube