RSS
 

Posts Tagged ‘Functions’

ISNULL() VS. COALESCE()

11 Feb

There are a lot of arguments about which of these to use.  In my opinion, they both have their place.  Here are a few facts about the two functions.

  • ISNULL can accept only two arguments where as COALESCE can accept any number of input parameters
  • ISNULL is not ANSI standard and is proprietary to TSQL.
  • With ISNULL, the datatype of the second argument is converted to equal the data type of the first argument where as COALESCE converts according to data type precedence. 
  • ISNULL is generally accepted to be faster than COALESCE.
  • COALESCE is a much cooler word and will usually earn you either an impressed glance or a blank confused stare when slipped into casual conversation. 

Here are a few examples that demonstrate some of the functional differences between the two conversion methods. 

declare @a varchar(5)
select ISNULL(@a, 'ISNULL Test 1')        -- Result: ISNUL
SELECT COALESCE(@a, 'COALESCE Test 1')    -- Result: COALESCE Test 1

declare @b tinyint
SELECT ISNULL(@b, 99999)                -- Result: **Error**
SELECT COALESCE(@b, 99999)                -- Result: 9999

declare @c char(5)
SELECT ISNULL(@c, 'a') + 'B'            -- Result: a    B
SELECT COALESCE(@c, 'a') + 'B'            -- Result: aB
 
2 Comments

Posted in All