Efficient way to compare for a NULL or value for a column in SQL

Question

What's the efficient way to check for a null or value for a column in SQL query. Consider a sql table table with integer column column which has an index. @value can be some integer or null ex: 16 or null.

Query 1: Note sure, but it seems one should not rely on the short-circuit in SQL. However, below query always works correctly when @value is some integer or null.

select * from
table
where (@value is null or column = @value)

The below query is an expanded version of the above query. It works correctly too.

select * from 
table 
where ((@value is null) 
    or (@value is not null and column = @value))

Would the above 2 queries would take the advantage of the index?

Query 2: The below query compares the column with non-null @value else compares the column column with itself which will always be true and returns everything. It works correctly too. Would this query take advantage of the index?

select * from
table
where (column = isnull(@value, column)

What's the best way?

Note: If the answer varies with databases, I'm interested in MS-SQL.

Answer 1

Variations on this question have come up several times in the past couple of days (why do these things always happen in groups?). The short answer is that yes, SQL Server will short-circuit the logic IF it creates the query plan with known values. So, if you have that code in a script where the variables are set then I believe it should short-circuit the logic (test to be sure). However, if it's in a stored procedure then SQL Server will create a query plan ahead of time and it won't know whether or not it can short-circuit the query, because it doesn't know the parameter values at the time of generating the query plan.

Regardless of whether it is short-circuited or not, SQL Server should be able to use the index if that's the only part of your query. If the variable is NULL though, then you probably don't want SQL Server using the index because it will be useless.

If you're in a stored procedure then your best bet is to use OPTION (RECOMPILE) on your query. This will cause SQL Server to create a new query plan each time. This is a little bit of overhead, but the gains typically outweigh that by a lot. This is ONLY good for SQL 2008 and even then only for some of the later service packs. There was a bug with RECOMPILE before that rendering it useless. For more information check out Erland Sommarskog's great article on the subject. Specifically you'll want to look under the Static SQL sections.

Answer 2

I've answered this and various variations many many times, try: http://stackoverflow.com/search?q=user%3A65223+%22dynamic+search%22

you don't have to ask me if an index is used or not, do it yourself. only you have the tables and the data. run: SET SHOWPLAN_ALL ON then your query, look at the execution plan, is an index used?