SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 When does an indexed table use a table scan?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lfmn
Posting Yak Master

USA
141 Posts

Posted - 01/28/2003 :  12:14:17  Show Profile  Reply with Quote
I know I've seen this somewhere, but can't find it. I'm sure I read that if a table has under a certain number of rows, it uses a table scan regardless of whether or not the table has an index because it's more efficient. If anyone know the anwer to this, it will save me much head scratching!

SQL is useful if you don't know cursors :-)

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 01/28/2003 :  13:17:40  Show Profile  Visit nr's Homepage  Reply with Quote
I may be wrong but I don't think there is a number of rows involved.
The optimiser will take the plan with the minimum number of page accesses to get the data.
If the index is covering then it should always use the index.
If the table is small and you are accessing data not in the index then it may be that reading the index pages will cause more reads.

The optimiser may also get it wrong.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

lfmn
Posting Yak Master

USA
141 Posts

Posted - 01/28/2003 :  13:29:00  Show Profile  Reply with Quote
According to SQL Server 7.0 DBA Survival Guide page 590, you should not index tables with a small number of rows. I guess I was looking for something more specific. We have tables with 20 million rows. I guess I'm thinking anything under 10,000 is small.

SQL is useful if you don't know cursors :-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 01/28/2003 :  13:31:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
Doesn't it also check the density and if the density isn't low enough then it does a table scan? Density relates to number of records in the table and number of distinct values in the first column of the index.

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/28/2003 :  13:42:43  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
I know I had a case where I had a table of 5 lookup records, and a table of 100,000 transactions. The lookup table had an index.

When I wrote a simple INNER JOIN between the two tables, and SQL wanted to use an index on the small lookup table so it sorted the entire 100,000 transactions in the same order as the lookup table so it could do this. This was very slow.

Then, when I used the INDEX(0) hint, SQL just did a table scan on the small lookup table and the query execute something like 100 times faster because it didn't have to first sort 100,000 records.

- Jeff
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 01/28/2003 :  13:50:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
I am very surprised that SQL used the index on a table with five records. I have never seen that happen.

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 01/28/2003 :  13:57:12  Show Profile  Visit nr's Homepage  Reply with Quote
quote:

The optimiser may also get it wrong.



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/28/2003 :  14:14:02  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
I noticed that this was fine in SQL 2000 -- the original problem I had on certain Views was in 7.0. After I upgraded, I took out all of my optimizer hints and SQL seems to be doing a much better job in the new version.

- Jeff
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000