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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply