Using ISNULL vs using COALESCE for checking a specific condition?

by

This problem reported on Microsoft Connect reveals some differences between COALESCE and ISNULL:

an early part of our processing rewrites COALESCE( expression1, expression2 ) as CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. In [this example]:

COALESCE ( ( SELECT Nullable
             FROM Demo
             WHERE SomeCol = 1 ), 1 )

we generate: 

SELECT CASE
          WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL
          THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1)
          ELSE 1
       END

Later stages of query processing don’t understand that the two subqueries were originally the same expression, so they execute the subquery twice…

One workaround, though I hate to suggest it, is to change COALESCE to ISNULL, since the latter doesn’t duplicate the subquery.

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. to select all the employees who have joined most recently under king
  4. Simple way to transpose columns and rows in SQL?
  5. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  6. Only inserting a row if it’s not already there
  7. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  8. SQL JOIN vs IN performance?
  9. Most efficient way in SQL Server to get date from date+time?
  10. Convert varchar into datetime in SQL Server
  11. TSQL Pivot without aggregate function
  12. How to generate a range of dates in SQL Server
  13. SQL RANK() versus ROW_NUMBER()
  14. Generate SQL Create Scripts for existing tables with Query
  15. Check if a row exists, otherwise insert
  16. Partition Function COUNT() OVER possible using DISTINCT
  17. How do I create a foreign key in SQL Server?
  18. How do I group on continuous ranges
  19. join comma delimited data column
  20. Sql PIVOT and string concatenation aggregate
  21. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  22. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  23. Alter user defined type in SQL Server
  24. Store multiple bit values in a single table column
  25. SQL Server equivalent to MySQL enum data type?
  26. Grant SELECT permission on a view, but not on underlying objects
  27. SQL – Call Stored Procedure for each record
  28. SQL – How do I get only the numbers after the decimal?
  29. How to retrieve records for last 30 minutes in MS SQL?
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment