Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How would you force a table scan instead of using an idx?

Status
Not open for further replies.

dinzana

Programmer
Joined
Nov 21, 2002
Messages
118
Location
US
I know that you can leave hints for using and index in the from clause of a query, but is it possible to force a table scan instead of using and index?

dinzana

 
I would like to add that there is already a clustered index on my table. I would like to bypass the use of it and force the table scan.

-dinzana
 
Not really sure what you're after. I have sometimes been able to defeat the compiler optimizer by using an expression on the left-hand side of the equal sign. E.g.,

select * from employees where SSN+0 = 123456789

instead of

select * from employees where SSN = 123456789

will change the execution plan from Index Seek to Index Scan.

I needed to use this in a different DBMS where the optimizer was choosing the wrong index in a complex join. By making it impossible to use the index chosen automatically, I was able to force the compiler to choose a different index that was better. But that DBMS did not have syntax for a compiler hint as to what index to use. SQL Server does.

So again, why do you think you need to do this?
 
A clustered index is on the same pages as the data. Therefore, a clustered index scan is going to scan the same pages, in the same order as a table scan. The clustered index scan may be faster because not all pages are scanned because a range scan or an index seek is performed.

Why would you not want to use an index? Terry L. Broadbent - DBA
SQL Server Page:
If you want to get the best answer for your question read faq183-874.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top