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.
>

Alter table changes optimizer query plan

I altered a table that had sever columns defined as float to decimal. Now f
or 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 els
e 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=...ublic.sqlserver
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...

quote:

---

> 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.

quote:

---

> 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...

quote:

---

> 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.

quote:

---

> 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...

quote:

---

> The columns that were altered are NOT part of the index nor are they used

---

in the criteria of the query.

quote:

---

>

---

Alter Index with REBUILD on master database?

SQL Server 2005:
We plan to use the alter index with rebuild syntax to rebuild our indexes
weekly in a job. Should master and msdb tables be included? I have no
interest in doing it manually a couple times per year if it needs it.
Thanks,
MarkI remember asking the same thing in a SQL 2000 forum a long time ago and the
consensus was you never need to include any of the system databases in
reindexing / update stats. I would gather the same is applicable to SQL
2005.
HTH,
Rubens
"Mark" <mark@.idonotlikespam.com> wrote in message
news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2005:
> We plan to use the alter index with rebuild syntax to rebuild our indexes
> weekly in a job. Should master and msdb tables be included? I have no
> interest in doing it manually a couple times per year if it needs it.
> Thanks,
> Mark
>|||Why not?
"Rubens" <rubensrose@.hotmail.com> wrote in message
news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
>I remember asking the same thing in a SQL 2000 forum a long time ago and
>the consensus was you never need to include any of the system databases in
>reindexing / update stats. I would gather the same is applicable to SQL
>2005.
> HTH,
> Rubens
> "Mark" <mark@.idonotlikespam.com> wrote in message
> news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2005:
>> We plan to use the alter index with rebuild syntax to rebuild our indexes
>> weekly in a job. Should master and msdb tables be included? I have no
>> interest in doing it manually a couple times per year if it needs it.
>> Thanks,
>> Mark|||Because in SQL2005 there shouldn't be any tables (of consequence) in the
master database that you can actually run UPDATE STATISTICS or rebuild
indexes.
Linchi
"Mark" wrote:
> Why not?
> "Rubens" <rubensrose@.hotmail.com> wrote in message
> news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
> >I remember asking the same thing in a SQL 2000 forum a long time ago and
> >the consensus was you never need to include any of the system databases in
> >reindexing / update stats. I would gather the same is applicable to SQL
> >2005.
> >
> > HTH,
> > Rubens
> >
> > "Mark" <mark@.idonotlikespam.com> wrote in message
> > news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> >> SQL Server 2005:
> >>
> >> We plan to use the alter index with rebuild syntax to rebuild our indexes
> >> weekly in a job. Should master and msdb tables be included? I have no
> >> interest in doing it manually a couple times per year if it needs it.
> >>
> >> Thanks,
> >> Mark
> >>
>
>

allround maintance plan for many small SQL2005 DB.

Hello
We got about 10 Std. SQL2005 server with maney small DB (from 256mb-2GB)
most are at 500mb
I would like to have a good allround maintance plan with a full backup to
.bak files each day.
But on some of the server we sometime got slow perf. what is best to rebuild
index or re org index in my maint plan ?CK
Run your backups at night when nobody is working. You have to identify
fragmented tables ( make sure that those tables have at least 1000 pages)
and rebuild indexes on them.
"CK" <fhf@.ggg.lo> wrote in message
news:15AE7194-875D-47C0-A066-945A515D0F8D@.microsoft.com...
> Hello
> We got about 10 Std. SQL2005 server with maney small DB (from 256mb-2GB)
> most are at 500mb
> I would like to have a good allround maintance plan with a full backup to
> .bak files each day.
> But on some of the server we sometime got slow perf. what is best to
> rebuild index or re org index in my maint plan ?
>

allround maintance plan for many small SQL2005 DB.

Hello
We got about 10 Std. SQL2005 server with maney small DB (from 256mb-2GB)
most are at 500mb
I would like to have a good allround maintance plan with a full backup to
.bak files each day.
But on some of the server we sometime got slow perf. what is best to rebuild
index or re org index in my maint plan ?CK
Run your backups at night when nobody is working. You have to identify
fragmented tables ( make sure that those tables have at least 1000 pages)
and rebuild indexes on them.
"CK" <fhf@.ggg.lo> wrote in message
news:15AE7194-875D-47C0-A066-945A515D0F8D@.microsoft.com...
> Hello
> We got about 10 Std. SQL2005 server with maney small DB (from 256mb-2GB)
> most are at 500mb
> I would like to have a good allround maintance plan with a full backup to
> .bak files each day.
> But on some of the server we sometime got slow perf. what is best to
> rebuild index or re org index in my maint plan ?
>

allround maintance plan for many small SQL2005 DB.

Hello
We got about 10 Std. SQL2005 server with maney small DB (from 256mb-2GB)
most are at 500mb
I would like to have a good allround maintance plan with a full backup to
..bak files each day.
But on some of the server we sometime got slow perf. what is best to rebuild
index or re org index in my maint plan ?
CK
Run your backups at night when nobody is working. You have to identify
fragmented tables ( make sure that those tables have at least 1000 pages)
and rebuild indexes on them.
"CK" <fhf@.ggg.lo> wrote in message
news:15AE7194-875D-47C0-A066-945A515D0F8D@.microsoft.com...
> Hello
> We got about 10 Std. SQL2005 server with maney small DB (from 256mb-2GB)
> most are at 500mb
> I would like to have a good allround maintance plan with a full backup to
> .bak files each day.
> But on some of the server we sometime got slow perf. what is best to
> rebuild index or re org index in my maint plan ?
>