Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

COALESCE and ISNULL in SQL Server - Part 2

, 5 Aug 2014
Rate this:
Please Sign up or sign in to vote.
COALESCE and ISNULL in SQL Server - Part 2

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:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Uppuluri Aditya
Software Developer (Junior)
India India
I have started my IT career in April 2013. Currently working as C#, SSIS and SQL Server developer in a multinational company. I am a Microsoft Certified Professional in SQL Server 2012.
Follow on   LinkedIn

Comments and Discussions

 
QuestionGood Start Pinprofessionalcoded0076-Aug-14 2:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140922.1 | Last Updated 5 Aug 2014
Article Copyright 2014 by Uppuluri Aditya
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid