Index seek with coalesce

Question

I have a table [MyTable] with a column [MyColumn] NVarchar(50). I have a nonclustered index on this column, now while running the below two queries:

SELECT  1
FROM    [MyTable] M
WHERE   M.[MyColumn] = @MyColumn

SELECT  1
FROM    [MyTable] M
WHERE   M.[MyColumn] = COALESCE(@MyColumn, M.[MyColumn] )   

I noticed the first query is using Index Seek (NonClustered) and the second one is using Index Scan (Non Clustered). May I know how will I make use of index seek with coalesce or isnull ?

Answer 1

May I know how will I make use of index seek with coalesce or isnull ?

Perhaps not an answer to your question but you can have two different queries. One for the case where @MyColumn is null and one for the case where you want to use @MyColumn in the where clause.

IF @MyColumn IS NULL
BEGIN
  SELECT  1
  FROM    [MyTable] M
END  
ELSE
BEGIN
  SELECT  1
  FROM    [MyTable] M
  WHERE   M.[MyColumn] = @MyColumn
END  

Answer 2

This isn't easy, since as Alex pointed out using the functions forces a scan, since the optimizer knows it needs to check every row.

What you CAN do is created a Computed Column for the result of your function, and index that column.

There's not really a prettier way to get a seek.

EDIT:

In rereading your question, this may not be an option for you unless you rethink your logic. You are integrating a variable into the function, and there is absolutely no way to index that.

EDIT 2:

Instead of your current logic, try something like:

...
WHERE (M.[MyColumn] = @MyColumn 
       OR @MyColumn IS NULL)