Alter table changes optimizer query plan

I altered a table that had sever columns defined as float to decimal. Now for some reason instead of using the index for it is using a sequential scan. I have updated the statistics, rebuilt the indexes and about everthing else I can think of. It simply refuses to use the index it did prior to the alter
Anyone have a clue as to what is going on?Is the comparison done against a variable or another column which is of the
float datatype? Float has higher datatype precedence, so the decimal need to
first be converted to float before that comparison can be performed which
prohibits the usage of index. If you code the code, or preferable a
simplified example that displays the behavior we might be able to comment...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...
> I altered a table that had sever columns defined as float to decimal. Now
for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.
> Anyone have a clue as to what is going on?|||Hi Redmund,
Are you comparing the column with a variable of data type float?
In that case, due to the rules of data type precedence, the decimal will be
implicitly converted into a float, and the implicit convert prevents the use
of an index on the column.
Change the variable to decimal as well.
--
Jacco Schalkwijk
SQL Server MVP
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...
> I altered a table that had sever columns defined as float to decimal. Now
for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.
> Anyone have a clue as to what is going on?|||The columns that were altered are NOT part of the index nor are they used in the criteria of the query.|||Hi,
Can you posts your table(s), indexes and query, so that we can study that?
--
Jacco Schalkwijk
SQL Server MVP
"RedMud" <anonymous@.discussions.microsoft.com> wrote in message
news:810E5CE2-29CE-490B-BFFD-5A58EA99048B@.microsoft.com...
> The columns that were altered are NOT part of the index nor are they used
in the criteria of the query.
>