title: Threats / drawbacks behind ISNULL(), NULLIF(), and COALESCE()
body: <p>I am working on a Microsoft SQL Server 2005 with Transact-SQL.</p>
  
  <p>I am trying to concatenate string values coming from different columns of the same table dealing with NULL values.</p>
  
  <p>Say for example the table is <code>Person</code> and the columns are <code>FirstName</code>, <code>SurnamePrefix</code>, <code>LegalSurname</code></p>
  
  <p>It happened that concatenating a string value with a NULL value (coming from two different columns) returns in output a NULL value.
  I tried different scenarios to prevent NULL values in output:</p>
  
  <ul>
  <li>Starting from: 
  <ul>
  <li><code>Person.FirstName + ' ' + COALESCE(RTRIM(LTRIM(Person.SurnamePrefix)) + ' ', '') + Person.LegalSurname</code></li>
  </ul></li>
  <li>I changed my statement to:
  <ul>
  <li><code>COALESCE(Person.FirstName + ' ', '') + COALESCE(Person.SurnamePrefix, '') + COALESCE(' ' + Person.LegalSurname, '')</code></li>
  </ul></li>
  </ul>
  
  <p>Then I came across functions like <code>ISNULL()</code>, <code>NULLIF()</code> etc.</p>
  
  <p>Which is the best and efficient approach to show empty strings values in output rather than NULL values?
  Is the solution affected by the version of the SQL Server? (i.e. 2005, 2008, etc.)</p>