| Author |
Topic |
|
mtl777
Yak Posting Veteran
63 Posts |
 Posted - 2009-05-26 : 14:23:07
|
I read somewhere that for small tables, you do not need an index to optimize a query because the entire table can be loaded in memory and searched faster. Is that true? If so, how would you define "small", i.e., how many rows?Thanks!  |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-26 : 15:02:14
|
If depends on record size. A page can hold 8000 bytes. If complete table is 8000 bytes or less, then no index will help because the smallest amount of data SQL Server reads is 1 page anyway.In other cases, it depends too on the circumstances. In SQL Server 2000 you could use DBCC PINTABLE to force a table into memory.That option is not longer available in SQL Server 2005.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2009-05-26 : 15:50:23
|
| Thanks for replying. Are there any guidelines for this, like, if the table size or the number of pages a table can fit in is less than some value then no need for an index? I'm thinking that if a table could fit in two or even three pages, it would still be faster than if a non-clustered index were used because the index-based method still has to fetch the actual data page after finding the key in the index. Don't you think so? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-26 : 16:18:44
|
Execution plans for SQL Server are cost based. The query engine will decide, based on statistics and other sources, if a scan costs less than a seek. A seek is random and cost much more than a scan which is sequential.Also tables where the selectivity is about 25-30 percent or more, will not utilize and index even if it's present.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2009-05-26 : 18:22:22
|
| Sorry for my ignorance but what is selectivity? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-26 : 18:25:38
|
Of you have a large table (let's say 1 000 000 records) and one of the columns are designated for gender and have values of "F" or "M".If you create an index over that column, it won't be used since the probability is 50% for "F" and 50% for "M".So SQL Server thinks it's faster to scan the table if you do a SELECT * FROM Table1 WHERE Col1 = 'F'However, if selectivity is less than 20-25 percent, SQL Server will try to include the index in the execution plan.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-05-26 : 19:13:48
|
| All tables should have a primary key, and that is usually implemented as a clustered index, so you should have some kind of index on the table in any case.SQL Server may chose not to use the index for a small table and scan the leaf pages directly.CODO ERGO SUM |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-05-27 : 03:10:45
|
| To see if an index is useful, test the queries against the table and note their execution stats (statistics IO and statistics time). Add the index, test again. See if there's any improvement.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-05-27 : 03:12:47
|
quote:
Originally posted by PesoHowever, if selectivity is less than 20-25 percent, SQL Server will try to include the index in the execution plan.
< 1% if the index doesn't cover the query.--Gail ShawSQL Server MVP |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2009-05-27 : 11:56:09
|
| Thank you so much for your replies. This forum is great! |
|
|
|
|
Small tables don't need an index?