April 15, 2015 at 5:52 am
I have an index on a date field but, the datatype is nvarchar. If I convert to datetime in my where clause, will the index still be used? For example
select MRN, Name, AppointmentDate
from DATA
where convert(datetime, AppointmentDate, 121) between '2015-04-01' and '2015-04-30'
April 15, 2015 at 6:13 am
No, it will not (well, maybe for a scan, but not for a seek).
Aside, why are you storing a date in a string column?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2015 at 6:16 am
Thanx. The database is out of my hands. I just get stuck writing queries out of it.
April 15, 2015 at 6:20 am
Can you suggest to those who are responsible for it that storing dates in anything other than one of the date/datetime data types is... not ideal?
In the mean time, for a predicate to be SARGable (used as a search operation in an index seek), it must be of the form <column> <operator> <expression>. Hence if you can re-write your where clause as something like WHERE AppointmentDate BETWEEN <expression> AND <expression>, then the index will be usable. Be careful of implicit conversions as well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2015 at 6:23 am
Thanx.
April 15, 2015 at 2:14 pm
You want to use the current index where possible, so do this:
select MRN, Name, AppointmentDate
from DATA
where datetime >= '2015-04-01 00:00:00.000'
and datetime < '2015-05-01 00:00:00.000'
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 16, 2015 at 4:02 am
Thanx.
April 16, 2015 at 8:18 am
You're welcome. The really great thing is that that code still works perfectly if/when you convert the column itself to datetime (or date) ... nice!
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply