What is the affect of CONVERT() on INDEX while searching?

Question

I am using SQL Server 2008. I have a Non-Unique Non-clustered index on a DateTime Column "DateFrom". I am searching the table based on this column. I just wanted to know the affect of CONVERT() function on INDEX see below:

Query1:
SELECT  *
FROM    myTable
WHERE   CONVERT(VARCHAR(10),DateFrom,23) >= '2011-01-01'

Query2:
SELECT  *
FROM    myTable
WHERE   DateFrom >= '2011-01-01 00:00:00.000'

I have checked & found no difference. But i was thinking that since the column is CONVERTED, so the index may not be used by the SQL Server, is it correct?

Please forgive me, if this is not a proper question.

Answer 1

Generally, when you have a function on the left side of the comparison in a WHERE clause, the server will be unable to utilize the index on the referenced column.

In your first example, there is no comparison to the DateFrom column directly. Instead, you are using a function on the column. When the server expands this, it must perform the function on each column value, and the resulting value is not indexed. Therefore, no index can be used to improve the query.

Also, in your first example, you indicated that the DateFrom column is a datetime column. Yet, you're converting the column to a string and doing a string comparison. Hence, the server will not use your datetime index.

In your second example, you are comparing the constant value with the date column directly, so the server may utilize the index. The server will convert the string constant on the right side of the comparison into a datetime value. However, it won't use the index in all cases. For example, if you have a very few number of rows, the server may decide not to use the index and just scan the few rows.

Both queries may yield the same result set, but they are still very different.