The short answer to "will it make use of the index?" is NO. The answer to
"how do you get it to use the index" isn't short :).
BUT - what I don't understand is why you're passing in a string that looks
like a date and then converting to varchar? If it's a date then keep it as a
date and SQL Server will use the index? BUT if you really want to convert to
something other than a date and get SQL Server to use indexes then there are
a couple of options:
1) You could store only the converted version of the column (and have a
trigger, stored procedure or the application convert the data to the version
you really want to index/lookup) BUT the problem with this approach is that
you'll need to convert back to a datetime datatype when you want to do
2) You could look into using a computed column and then indexing that (there
are numerous requirements for this though - see the BOL topic "Set Options
that Affect Results". This works well when queries are highly selective.
3) You could consider using an indexed view IF the queries are NOT highly
There are some good resources out there - especially the white paper on
Indexed Views. Check out the
Please reply only on the newsgroups! Include dml/ddl, when possible.Thanks
Kimberly L. Tripp
President, SYSolutions, Inc. [url]www.SQLSkills.com[/url]
Principal Mentor, Solid Quality Learning [url]www.SolidQualityLearning.com[/url]
"Shamim" <shamim.abdulrailamerica.com> wrote in message
news:%23zabDVXRDHA.2240TK2MSFTNGP11.phx.gbl...not> SQL 2K
> I understand if we use a CONVERT function in a datetime column, it willin_startdatetime,101)> make use of index attached to the column.
> I have a SP with 2 datetime arguments, if I use the argument in the below
> format , will it make use of index ??
> Arguments :- in_startdatetime, in_enddatetime
> declare in_cvt_startdatetime varchar(10), in_cvt_enddatetime varchar(10)
> select in_cvt_startdatetime = CONVERT(varchar(10,> select in_cvt_enddatetime = CONVERT(varchar(10, in_enddatetime,101)
> select * from mytable where movedate between in_cvt_startdatetime and
> ** What are all the ways to compare a datetime column to make use of index