Sql-server – Addition With Null Values

coalesceisnullsql serversql-server-2008-r2

I have inherited a table that has NULL values for float fields, instead of a 0 value. I am doing some basic testing for querying, and in my tests both ISNULL() and COALESCE() present the same accurate output. In my testing (a table with roughly 5K records) time difference is very negligible as well.

Question being with the sample DDL below, is there a benefit to use ISNULL() over COALESCE() or vice-versa? Are there circumstances that I have yet to see that could provide an inaccurate calculation?

Create Table #Test
( 
  blue float
  ,red float
  ,green float
)

Insert Into #Test VALUES 
('14', NULL, '12')
,(NULL, '12', '10')
,(NULL, NULL, '8')
,('10', '2', NULL)

Select 
ISNULL(blue,0)+ISNULL(red,0)+ISNULL(green,0)
FROM #Test

SELECT 
COALESCE(blue,0)+COALESCE(red,0)+COALESCE(green,0)
FROM #Test

Best Answer

COALESCE can take a whole list of values to check for NULL where ISNULL takes one check value and would require nesting to check more values. 

SELECT 
COALESCE(col1, col2, col3, col4, col5, 0) AS value_returned
FROM myTable

vs

SELECT 
ISNULL(col1, ISNULL(col2, ISNULL(col3, ISNULL(col4, 0)))) AS value_returned 
FROM myTable

Since your case is using float values, there should not be a difference in results from calculations. The only benefit I can really see if code readability and minimizing the amount of nesting needed.

Related Question