how to force a table scan in query

I have a query thats using a particular index and I would like to force the
query to do a table scan instead. How can i do so ?
Heres a sample query
select * from tableA where col1= 5Geeze why would you want to?!
I think your only way would be to drop the index. I don't think you can
force the optimiser not to use an index...
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I have a query thats using a particular index and I would like to force
the
> query to do a table scan instead. How can i do so ?
> Heres a sample query
> select * from tableA where col1= 5
>|||Remove the index.
>--Original Message--
>I have a query thats using a particular index and I would
like to force the
>query to do a table scan instead. How can i do so ?
>Heres a sample query
>select * from tableA where col1= 5
>
>.
>|||Table hint WITH INDEX(0)
BOL: If a clustered index exists, INDEX(0) forces a clustered index scan
and INDEX(1) forces a clustered index scan or seek. If no clustered index
exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an
error.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I have a query thats using a particular index and I would like to force
the
> query to do a table scan instead. How can i do so ?
> Heres a sample query
> select * from tableA where col1= 5
>|||There are definitely reasons why you wouldn't want to use an index, but most
of them can be solved by making sure your statistics are updated. If the
optimizer is incorrectly choosing to use a nonclustered index, you can far
far more reads than a simple table scan would take. You might also just want
to run the query without the index for testing and comparison purposes, to
find out how much the index is really saving you, to see if it's worth the
cost of its maintenance.
As Russell pointed out, you can use WITH INDEX(0) to force no index to be
used on a particular table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"London Developer" <dev@.nowhere.com> wrote in message
news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> Geeze why would you want to?!
> I think your only way would be to drop the index. I don't think you can
> force the optimiser not to use an index...
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I have a query thats using a particular index and I would like to force
> the
> > query to do a table scan instead. How can i do so ?
> >
> > Heres a sample query
> >
> > select * from tableA where col1= 5
> >
> >
>|||btw , Kalen, in your latest article in SQL Mag about the optimiser, listing
2 did take an index scan when you mentioned that it would take a table scan
bcos of less reads. My optimiser is not smart enough I guess as yours :-)
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> There are definitely reasons why you wouldn't want to use an index, but
most
> of them can be solved by making sure your statistics are updated. If the
> optimizer is incorrectly choosing to use a nonclustered index, you can far
> far more reads than a simple table scan would take. You might also just
want
> to run the query without the index for testing and comparison purposes, to
> find out how much the index is really saving you, to see if it's worth the
> cost of its maintenance.
> As Russell pointed out, you can use WITH INDEX(0) to force no index to be
> used on a particular table.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "London Developer" <dev@.nowhere.com> wrote in message
> news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > Geeze why would you want to?!
> > I think your only way would be to drop the index. I don't think you can
> > force the optimiser not to use an index...
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > I have a query thats using a particular index and I would like to
force
> > the
> > > query to do a table scan instead. How can i do so ?
> > >
> > > Heres a sample query
> > >
> > > select * from tableA where col1= 5
> > >
> > >
> >
> >
>|||Hi Hassan
Can you be more specific? I usually write my articles about 3 months in
advance (I just submitted February's article yesterday), so which is the
latest? October or November?
I'll take a look at it.
Thanks!
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> btw , Kalen, in your latest article in SQL Mag about the optimiser,
listing
> 2 did take an index scan when you mentioned that it would take a table
scan
> bcos of less reads. My optimiser is not smart enough I guess as yours :-)
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > There are definitely reasons why you wouldn't want to use an index, but
> most
> > of them can be solved by making sure your statistics are updated. If the
> > optimizer is incorrectly choosing to use a nonclustered index, you can
far
> > far more reads than a simple table scan would take. You might also just
> want
> > to run the query without the index for testing and comparison purposes,
to
> > find out how much the index is really saving you, to see if it's worth
the
> > cost of its maintenance.
> >
> > As Russell pointed out, you can use WITH INDEX(0) to force no index to
be
> > used on a particular table.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "London Developer" <dev@.nowhere.com> wrote in message
> > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > Geeze why would you want to?!
> > > I think your only way would be to drop the index. I don't think you
can
> > > force the optimiser not to use an index...
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > I have a query thats using a particular index and I would like to
> force
> > > the
> > > > query to do a table scan instead. How can i do so ?
> > > >
> > > > Heres a sample query
> > > >
> > > > select * from tableA where col1= 5
> > > >
> > > >
> > >
> > >
> >
> >
>|||I think it was November..the latest issue on the newstand... that had Yukon
on the cover plus the listing that had examples of orderdetails table
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39906&
These lines in particular
"If you run the command SET STATISTICS IO ON, then execute Listing 2's
queries, you'll see that the queries each take 10 logical reads-one for each
page in the table. The first query returns 58 rows. If the optimizer had
decided to use the nonclustered index on Quantity, SQL Server would have had
to perform 58 bookmark lookup operations, a much higher cost than the 10
logical reads of the table scan. The second query returns 33 rows, so it,
too, would have cost more than 10 logical reads if the optimizer had decided
to access the nonclustered index and perform bookmark lookups."
But when executed my queries had around 60 logical reads since it was using
the index with a bookmark lookup
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O19nzqynDHA.2536@.tk2msftngp13.phx.gbl...
> Hi Hassan
> Can you be more specific? I usually write my articles about 3 months in
> advance (I just submitted February's article yesterday), so which is the
> latest? October or November?
> I'll take a look at it.
> Thanks!
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > btw , Kalen, in your latest article in SQL Mag about the optimiser,
> listing
> > 2 did take an index scan when you mentioned that it would take a table
> scan
> > bcos of less reads. My optimiser is not smart enough I guess as yours
:-)
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > > There are definitely reasons why you wouldn't want to use an index,
but
> > most
> > > of them can be solved by making sure your statistics are updated. If
the
> > > optimizer is incorrectly choosing to use a nonclustered index, you can
> far
> > > far more reads than a simple table scan would take. You might also
just
> > want
> > > to run the query without the index for testing and comparison
purposes,
> to
> > > find out how much the index is really saving you, to see if it's worth
> the
> > > cost of its maintenance.
> > >
> > > As Russell pointed out, you can use WITH INDEX(0) to force no index to
> be
> > > used on a particular table.
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "London Developer" <dev@.nowhere.com> wrote in message
> > > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > > Geeze why would you want to?!
> > > > I think your only way would be to drop the index. I don't think you
> can
> > > > force the optimiser not to use an index...
> > > >
> > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > > I have a query thats using a particular index and I would like to
> > force
> > > > the
> > > > > query to do a table scan instead. How can i do so ?
> > > > >
> > > > > Heres a sample query
> > > > >
> > > > > select * from tableA where col1= 5
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, I just got that in the mail today!
I'll take a look and see if I can figure out why you might have gotten
different behavior than I did.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#XajESznDHA.3700@.TK2MSFTNGP11.phx.gbl...
> I think it was November..the latest issue on the newstand... that had
Yukon
> on the cover plus the listing that had examples of orderdetails table
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39906&
> These lines in particular
> "If you run the command SET STATISTICS IO ON, then execute Listing 2's
> queries, you'll see that the queries each take 10 logical reads-one for
each
> page in the table. The first query returns 58 rows. If the optimizer had
> decided to use the nonclustered index on Quantity, SQL Server would have
had
> to perform 58 bookmark lookup operations, a much higher cost than the 10
> logical reads of the table scan. The second query returns 33 rows, so it,
> too, would have cost more than 10 logical reads if the optimizer had
decided
> to access the nonclustered index and perform bookmark lookups."
> But when executed my queries had around 60 logical reads since it was
using
> the index with a bookmark lookup
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:O19nzqynDHA.2536@.tk2msftngp13.phx.gbl...
> > Hi Hassan
> >
> > Can you be more specific? I usually write my articles about 3 months in
> > advance (I just submitted February's article yesterday), so which is the
> > latest? October or November?
> > I'll take a look at it.
> >
> > Thanks!
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > > btw , Kalen, in your latest article in SQL Mag about the optimiser,
> > listing
> > > 2 did take an index scan when you mentioned that it would take a table
> > scan
> > > bcos of less reads. My optimiser is not smart enough I guess as yours
> :-)
> > >
> > >
> > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > > > There are definitely reasons why you wouldn't want to use an index,
> but
> > > most
> > > > of them can be solved by making sure your statistics are updated. If
> the
> > > > optimizer is incorrectly choosing to use a nonclustered index, you
can
> > far
> > > > far more reads than a simple table scan would take. You might also
> just
> > > want
> > > > to run the query without the index for testing and comparison
> purposes,
> > to
> > > > find out how much the index is really saving you, to see if it's
worth
> > the
> > > > cost of its maintenance.
> > > >
> > > > As Russell pointed out, you can use WITH INDEX(0) to force no index
to
> > be
> > > > used on a particular table.
> > > >
> > > > --
> > > > HTH
> > > > --
> > > > Kalen Delaney
> > > > SQL Server MVP
> > > > www.SolidQualityLearning.com
> > > >
> > > >
> > > > "London Developer" <dev@.nowhere.com> wrote in message
> > > > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > > > Geeze why would you want to?!
> > > > > I think your only way would be to drop the index. I don't think
you
> > can
> > > > > force the optimiser not to use an index...
> > > > >
> > > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > > > I have a query thats using a particular index and I would like
to
> > > force
> > > > > the
> > > > > > query to do a table scan instead. How can i do so ?
> > > > > >
> > > > > > Heres a sample query
> > > > > >
> > > > > > select * from tableA where col1= 5
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>