title: Efficient way to compare for a NULL or value for a column in SQL
body: <p>What's the efficient way to check for a null or value for a column in SQL query. Consider a sql table <code>table</code> with integer column <code>column</code> which has an index. <code>@value</code> can be some integer or null ex: 16 or null. </p>
  
  <p>Query 1: Not sure, but it seems one should not rely on the short-circuit in SQL. However, below query always works correctly when <code>@value</code> is some integer or null. </p>
  
  <pre><code>select * from
  table
  where (@value is null or column = @value)
  </code></pre>
  
  <p>The below query is an expanded version of the above query. It works correctly too. </p>
  
  <pre><code>select * from 
  table 
  where ((@value is null) 
      or (@value is not null and column = @value))
  </code></pre>
  
  <p>Would the above 2 queries would take the advantage of the index?</p>
  
  <p>Query 2: The below query compares the column with non-null <code>@value</code> else compares the column <code>column</code> with itself which will always be true and returns everything. It works correctly too. Would this query take advantage of the index?</p>
  
  <pre><code>select * from
  table
  where (column = isnull(@value, column))
  </code></pre>
  
  <p>What's the best way? </p>
  
  <p>Note: If the answer varies with databases, I'm interested in MS-SQL. </p>