CODEWITHSUNDEEP
sqlsql-serverindexing
ohad mesika
ohad mesika

Reputation: 79

SQL Server : why convert to date use index and convert to nvarchar not?

I have the following 2 queries with function on the index, one do index seek and one index scan:

This uses the index:

select num_of_row 
from test0
where convert(date, index_date) = '2017-06-13'

This does not use the index:

select num_of_row 
from test0
where convert(nvarchar, index_date, 103) = '2017-06-13'

Why is the index used when I convert to date, but not when I convert to nvarchar?

Upvotes: 0

Views: 256

Answers (1)

James Z
James Z

Reputation: 12317

Usually you can't use any functions for a field if you want it to be SARGable (=to use index), but datetime -> date is one of the exceptions, most likely because the order can be guaranteed to be the same (in dates and datetimes), but not in datetimes and (n)varchars.

Upvotes: 1

Related Questions