Difference between ISNULL and COALESCE (SQL Server)

Though ISNULL and COALESCE can be interchangeably used in common scenarios basic functionality of ISNULL and COALESCE are different.

ISNULL:
ISNULL Accepts 2 parameters, if the first parameter is NULL, second parameter value will be returned.

COALESCE:
COALESCE accepts multiple parameters and returns first non-NULL value.

Differences with simple examples:

-- 1. COALESCE accepts multiple parameters  , ISNULL Accepts only 2 parameters  
SELECT ISNULL(1,2) as ISNULL_1_2, ISNULL(NULL,2) AS ISNULL_NULL_2
SELECT COALESCE(1,2) as COALESCE_1_2, COALESCE(NULL,NULL, 4,5) AS COALESCE_NULL_2_4 -- Accepts multiple Parametrs 


-- 2. If both parameters are NULL with no data type defined, ISNULL Returns INT but COALESCE errors 
select ISNULL(NULL, NULL) 
select COALESCE(NULL, NULL) -- ERROR 

select ISNULL(NULL,NULL) as col into temptable

SELECT name, t = TYPE_NAME(system_type_id), max_length, is_nullable
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID('temptable');

drop table temptable


-- 3. ISNULL tries to implicitly converts all other columns to first column data type, COALESCE tries to convert the data to highest data type precedence baseed on http://msdn.microsoft.com/en-us/library/ms190309.aspx.
declare @mydate date, @myvar varchar(6)
select ISNULL(@mydate, getdate())
select COALESCE(@mydate, getdate())
-- NOTE: DATETIME getdate() has higher precedence compare to DATE datatype

select ISNULL(@myvar, 'vijredblog') -- returns vijred
select COALESCE(@myvar, 'vijredblog') -- returns vijredblog


-- 4. ISNULL is not expected to be NULLABLE Column but COALESCE expected to be NULLABLE 
declare @myvar varchar(6) 
select COALESCE(@myvar,'vijredblog') as col_COALESCE, ISNULL(@myvar,'vijredblog') as col_ISNULL into temptable

SELECT name, TYPE_NAME(system_type_id) as datatype, max_length, is_nullable
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID('temptable');

drop table temptable
-- -- RESULTS:
--name			datatype	max_length	is_nullable
--col_COALESCE	varchar		10			1
--col_ISNULL	varchar		6			0

-vijred

Advertisements
This entry was posted in Productivity, Script, SQL, SQL Query and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s