December 9, 2005 at 3:53 am
Hi,
Anyone know of a better way to optimize
my query below so that the optimizer will
do a clustered index seek insteads of the
clustered index scan. Those commented out
code are those code that I test with.
In addition, is there any function to
convert datetime to numeric value
and vice versa. I am thinking of
using it to convert my datetime
to numeric for my query use instead.
-- index on PD_INDEX pd_security(product_id)
select * from pd_security
where
--cast(exp_dt as datetime) = '9999-12-31'
--convert(char(10),exp_dt,21) = '9999-12-31'
exp_dt = cast('9999-12-31' as datetime)
December 9, 2005 at 12:34 pm
exp_dt = cast('9999-12-31' as datetime) would be preferable, since sql server only has to convert once and can use an index on exp_dt.
Otherwise it has to convert each value and possibly loses the index search ability.
Is there an index on exp_dt ? (since product_id is of little relevance in the seek)
December 11, 2005 at 12:18 pm
What is the data-type for the exp_dt column ? And what kind of values do you have in that column ?
exp_dt = cast('9999-12-31' as datetime)
maynot yield right results at all if you are storing datetime values in a string data-type field and if those values have time portions as well. Same is true if that is a datetime data-type and if the values have time portions in it.
On a different note, what Jo said above is correct. If you apply functions on indexed columns then indexes do not get used (Oracle has a concept of Function Based Indexes but SQL Server does not - unless you use a computed column and index it), so stay clear of applying functions on indexed columns in searches.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply