ISNULL and COALESCE

Both the ISNULL and COALESCE functions will take values and return the first non-NULL value. ISNULL only takes two values, where COALESCE can take evaluate multiple values. Also, ISNULL is a T-SQL function, where COALESCE is part of the ANSI standard.
There are other differences as well. Here’s a short example:

CREATE TABLE dbo.CoalesceTest(
	Column1 varchar(10) NULL,
	Column2 int NULL,
	Column3 varchar(50) NULL
);

INSERT INTO dbo.CoalesceTest(Column1, Column2) 
VALUES (NULL, 2), ('A', 1);

INSERT INTO dbo.CoalesceTest(Column1, Column3) 
VALUES (NULL, 'String Longer Than 10 Characters');

We’ll run a query with both functions:

SELECT ISNULL(Column1, Column2) as IsNullResult 
    , COALESCE(Column1, Column2) as IsCoalesceResult
FROM dbo.CoalesceTest
WHERE Column2 IS NOT NULL;

This query will fail with the error “Conversion failed when converting the varchar value ‘A’ to data type int.”.
If we comment out the COALESCE statement, then the query will run.
The two columns are of different data types. ISNULL is able to work with that and select the first Non-Null value.
COALESCE will use data type precedence to determine the data type of the result. Since we’re mixing strings and integers, the integer has a higher precedence. The error occurs trying to convert the varchar to an integer.

A second query. We’ll compare two string columns of different lengths:

SELECT ISNULL(Column1, Column3) as IsNullResult 
    , COALESCE(Column1, Column3) as IsCoalesceResult
FROM dbo.CoalesceTest
WHERE Column3 IS NOT NULL;

The ISNULL returns ‘String Lon’, where the COALESCE returns the entire value. Since the first column in the ISNULL is varchar(10), that is being used as the return value. The longer string gets truncated by ISNULL, but COALESCE gives us the entire value.