Introduction
Please read COALESCE and ISNULL in SQL Server - Part 1 before reading this.
In order to explain the impact of transactions on COALESCE and ISNULL, open two new query editor windows in SQL Server Management Studio namely Query1 and Query2. I presume that, default isolation level of your SQL Server instance is READ COMMITTED.
Run the following code in Query1:
USE tempdb
GO
CREATE TABLE TEST (Col INT)
INSERT INTO TEST VALUES (12)
BEGIN TRAN
INSERT INTO TEST VALUES (23)
In the above query, a table is being created with one column and a value of 12 is inserted into that column. After that, an explicit transaction is being initiated and a value of 23 is inserted. Note that, the transaction has not yet been committed or rolled back.
Run the following code in Query2:
USE tempdb
GO
SELECT COALESCE ((SELECT SUM (col) FROM TEST), 0)
Now, the above code in Query2 will be in waiting state, until the transaction in Query1 is either committed or rolled back.
Back in Query1, run the following code:
DELETE FROM TEST
COMMIT TRAN;
DROP TABLE TEST
Upon running the above query in Query1, the code in Query2 will also be executed and here is the result of the Query2?
COALESCE and ISNULL in Transactions
COALESCE in Transactions
In order to explain what went wrong, please consider the actual execution plan of Query2:
The query in Query2 is internally translated into the following expression:
CASE
WHEN (SELECT SUM (col) FROM TEST) IS NOT NULL
THEN (SELECT SUM (col) FROM TEST)
ELSE 0
END
The highlighted part of the above query calculates the result of the query in the WHEN clause of the CASE expression. It reads the row with the value 12 and then blocks, because the row with the value 23 is exclusively locked. Then, Query1 deletes all the rows from the table and commits, allowing the query to complete. The resulting sum 12 is assigned to a variable expr1004.
CASE
WHEN (SELECT SUM (col) FROM TEST) IS NOT NULL
THEN (SELECT SUM (col) FROM TEST)
ELSE 0
END
Next, the above highlighted part of the query will be executed. The query returns a NULL because there are no rows in the table at this point, and the NULL result is assigned to the variable Expr1010. Now the case expression looks like this:
Logical Expression
CASE WHEN 12 IS NOT NULL THEN NULL ELSE 0 END
Actual Expression
CASE WHEN Expr1004 IS NOT NULL THEN Expr1010 ELSE 0 END
Under the default isolation level read committed, there’s no guarantee that the code will interact with the current state of the data.
COALESCE in SERIALIZABLE ISOLATION LEVEL
Now, repeat the same test starting from Query1 but modify the Query2 as the following:
USE tempdb
GO
SELECT COALESCE ((SELECT SUM (col) FROM TEST WITH (SERIALIZABLE)), 0)
The output will be:
This time the query in WHEN clause blocks before any rows are scanned waiting for the Transaction in Query1 to complete. So both the branches will return NULL and now the expression looks like this:
CASE WHEN NULL IS NOT NULL THEN NULL ELSE 0 END
ISNULL in Transactions
Now, rewrite Query2 by replacing the COALESCE with ISNULL as follows and repeat the test:
USE tempdb
GO
SELECT ISNULL ((SELECT SUM (col) FROM TEST), 0)
This time, the query will return value 12 and the execution plan is as follows:
The query will be executed only once. It will be blocked after reading the value 12. After the transaction in query1 committed, there were no more rows to read. Hence the sum will be 12.
Conclusion
Though COALESCE and ISNULL functions work similarly, there are lots of differences between them. I have just written only few of them. I hope this tip will be helpful for you to decide between COALESCE and ISNULL depending on your application needs.
Don’t forget that, COALESCE is ANSI-standard and can accept multiple parameters where as ISNULL isn’t.
For more information on COALESCE and ISNULL, please follow the MSDN link below:
