Lets see what are the relevant differences:
1. COALESCE is ANSI standard and ISNULL is T-SQL proprietary.
2. You can work with only one value at a time with ISNULL, but COALESCE can deal with multiple values. as in
SELECT ISNULL(NULL, ‘SomeValue’)
GO
SELECT COALESCE(NULL, NULL, NULL, ‘SomeValue’)
GO
3. With ISNULL, the Alternate value you specify is limited to the length of the first parameter. In case of COALESCE, you dont have such restrictions.
See the example below.
DECLARE @somestring CHAR(4)
SET @somestring = NULL
SELECT ISNULL(@somestring, ‘Roji Thomas’)
–Returns ‘Roji’
SELECT COALESCE(@somestring, ‘Roji Thomas’)
–Returns ‘Roji Thomas’
4. When using COALESCE All expressions must be of the same type or must be implicitly convertible to the same type. If you are not careful, this can give you incorrect results. See the examples below.
SELECT COALESCE(NULL, GetDate())
–Returns : 2004-07-26 13:47:15.937
–Correct
SELECT COALESCE(1, GetDate())
–Returns 1900-01-02 00:00:00.000
–Incorrect Value
SELECT COALESCE(1,’abcd’)
–Returns 1
SELECT COALESCE(‘abcd’,1)
–Returns Error
– Server: Msg 245, Level 16, State 1, Line 1
– Syntax error converting the varchar value ‘abcd’ to a column of data type int.
SELECT COALESCE(NULL,435,’abcd’, CURRENT_TIMESTAMP,’xyzzz’,435)
–Returns 1901-03-12 00:00:00.000
–Incorrect
SELECT COALESCE(NULL,GetDate(),9999999999)
–Returns : 2004-07-26 13:47:15.937
–Correct
SELECT COALESCE(NULL,9999999999, GetDate())
–Returs Error
– Server: Msg 8115, Level 16, State 2, Line 1
– Arithmetic overflow error converting expression to data type datetime.
5. Eventhough COALESCE is generally the preferred way, there are some performance consideration especially when you have a select statement as an arguument to COALESCE.
