CODEWITHSUNDEEP
sqlsql-server
MarkD
MarkD

Reputation: 1063

Coalesce(), ISNULL() clarification

I am currently studying for my MCSA Data Platform, I got the following question wrong and I was looking for an explanation as to why my answer was wrong as the in test explanation did not make much sense.

The Hovercraft Wages table records salaries paid to workers at Contoso. Workers get either a daily rate or a yearly salary. The table conatains the following columns:

EmpID, Daily_Rate, Yearly_Salary

Workers only get one type of income rate and the other column in their record has a value of NULL. You want to run a query calculating each employees total salary based on the assumption that people work 5 days a week 52 weeks per year.

Below are two options the right answer and the answer i chose

SELECT EmpID, CAST(COALESCE(Daily_Rate*5*52, Yearly_Salary) AS money) AS 'Total Salary'
FROM Hovercraft.Wages;

SELECT EMPID, CAST(ISNULL(Daily_Rate*5*52, Yearly_Salary)AS money)AS 'Total Salary'
FROM Hovercraft.Wages;

I selected the second choice as there were only two possible pay fields but was marked as incorrect for the coalesce, Can anybody clarify why an ISNULL is not a valid choice in this example as I do not want to make this mistake in the future

Many Thanks

Upvotes: 2

Views: 252

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726499

The biggest difference is that ISNULL is proprietary, while COALESCE is part of SQL standard. Certification course may be teaching to maximum portability of knowledge, so when you have several choices, the course prefers a standard way of solving the problem.

The other difference that may be important in this situation is the data type determination. ISNULL uses the type of the first argument, while COALESCE follows the same rules as CASE, and picks the type with higher precedence. This may be important when Daily_Rate is stored in a column with narrower range.

For completeness, here is a list of other differences between the two (taken from Microsoft SQL Server Blog):

  • The NULLability of result expression is different,
  • Validations for ISNULL and COALESCE is different, because NULL value for ISNULL is converted to int, but triggers an error with COALESCE
  • ISNULL takes only two parameters whereas COALESCE takes variable number of parameters
  • You may get different query plans for the two functions.

EDIT : From the way the answer is worded I think that the authors want you to use ISNULL in situations when the second argument is guaranteed to be non-NULL, e.g. a non-nullable field, or a constant. While generally this idea is sound, their choice of question to test it is not ideal: the issue is that the problem guarantees that the value of the second ISNULL parameter is non-NULL in situations when it matters, making the two choices logically equivalent.

Upvotes: 3

dean
dean

Reputation: 10098

Besides all the very well known differences, not many people know that COALESCE is just a shorthand for CASE, and inherits all of it's +s and -s -- a list of parameters, proper casting of the result and so on.

But it also can be detrimonial to performance for an unsuspecting developer.

To demonstrate my point, pls run these three queries (on AdventureWorks2012 database) and check the execution plans for them:

SELECT COALESCE((SELECT CustomerID FROM Sales.SalesOrderHeader WHERE SalesOrderId = 57418), 0)

SELECT ISNULL((SELECT CustomerID FROM Sales.SalesOrderHeader WHERE SalesOrderId = 57418), 0)

SELECT CASE WHEN (SELECT CustomerID FROM Sales.SalesOrderHeader WHERE SalesOrderId = 57418) IS NULL 
    THEN 0
    ELSE (SELECT CustomerID FROM Sales.SalesOrderHeader WHERE SalesOrderId = 57418)
END

You see that the first and the third one have the identicall execution plans (because the COALESCE is just a short form of CASE). Also you see that in first and third queries the SalesOrderHeader table is accessed twice, as oposed to just once with ISNULL.

If you also enable SET STATISTICS IO ON for the session, you'll notice that the number of logical reads is double for these two queries.

In this case, COALESCE executed the inner SELECT statement twice, as opposed to ISNULL, which executed it only once. This could make a huge difference.

Upvotes: 0

Related Questions