Coalesce Vs Isnull

• arrjay

  Hall of Fame

  Points: 3455

  More actions

  August 7, 2015 at 12:50 am

  #320314

  Hi all - why does this query return;

  12345

  1234567890

  SELECT ISNULL ( CONVERT(varchar(5), NULL), CONVERT(varchar(10), '1234567890') )

  , COALESCE( CONVERT(varchar(5), NULL), CONVERT(varchar(10), '1234567890') )

  Thanks!

  --------------------------------------------

  Laughing in the face of contention...

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 7, 2015 at 1:50 am

  #1818028

  arrjay (8/7/2015)

  ---

  Hi all - why does this query return;

  12345

  1234567890

  SELECT ISNULL ( CONVERT(varchar(5), NULL), CONVERT(varchar(10), '1234567890') )

  , COALESCE( CONVERT(varchar(5), NULL), CONVERT(varchar(10), '1234567890') )

  Thanks!

  https://msdn.microsoft.com/en-us/library/ms190349.aspx

  Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden