title: What is the performance impact with using IS NULL OR vs. ISNULL with LIKE?
body: <p>I am writing a parameterised query where there is a requirement for the <code>LIKE</code> clause to include <code>NULL</code> values when <code>field LIKE '%'</code>.</p>
  
  <p>I have multiple solutions; I ran these queries side-by-side and they return the similar execution plans with the same summary and the query times are similar. I worry about how these queries scale-up in performance for tables with millions of records.</p>
  
  <pre><code>-- Where @PartId is typically '%' or could be 'Part123%'.
  SELECT *
  FROM dbo.OrderItems AS oi
  WHERE oi.PartId IS NULL OR oi.PartId LIKE @PartId
  
  SELECT *
  FROM dbo.OrderItems AS oi
  WHERE ISNULL(oi.PartId, '') LIKE @PartId
  
  SELECT *
  FROM dbo.OrderItems AS oi
  WHERE COALESCE(oi.PartId, '') LIKE @PartId
  </code></pre>
  
  <p>This <a href="https://stackoverflow.com/questions/8924913/is-there-is-any-performance-issue-while-using-isnull-in-sql-server">question</a> suggests that there is very little difference between <code>ISNULL</code> and <code>COALESCE</code>. Grant Fritchey demonstrates in this <a href="https://www.red-gate.com/simple-talk/sql/performance/the-seven-sins-against-tsql-performance/" rel="nofollow noreferrer">article</a> that applying functions on <code>WHERE</code> clauses causes a bigger performance hindrance than using <code>LIKE</code>, however I've read that using logical <code>OR</code> can cause performance issues in queries. What is the performance impact with using <code>IS NULL OR</code> vs. the alternative of <code>ISNULL</code>?</p>
  
  <p>Edit: This is the execution plan summary for the queries:
  <a href="https://i.stack.imgur.com/sHy2d.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sHy2d.png" alt="enter image description here"></a></p>