Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-21 : 00:24:51
|
| Seth writes "What are the general guidelines for determining whether or not you should lookup data via the index or by having sql do a table scan?" |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-21 : 00:50:47
|
| AFAIK , it depends a lot on the columns you are using in your where clause ,the existence and use of your clustered index and the data Sql Server holds in chache too. if Sql Server cant get the data from the above it does a table scan.-------------------------------------------------------------- |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-03-21 : 01:13:04
|
| Seth,One word... Size!!!Say you have a small "lookup" table (<10).An index on a table this size is almost overhead.In general though, always use an index..DavidMTomorrow is the same day as Today was the day before. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-03-21 : 02:51:46
|
| Size is the I'd go as well. On DB2 for OS/390, there is a heuristic that anything of 4 pages will always be read as tablespace scan. I think you'll find these is the same kind of measurement done in SQL Server - it'll be based on number of pages ( and thus IO) that would need to be done. At least that what my reasoning tells me... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-21 : 07:57:33
|
| You can always check the query plan generated by the optimizer. If it seems slow, or you just want to compare, you can use index hints to force the optimizer to use a particular index. You can even force it to do a table scan. Comparing the two for performance will give you some idea which is more efficient. Besides, queries are like snowflakes, no two are exactly alike.If you want to make sure of the validity of the query plan, do a DBCC DROPCLEANBUFFERS before you run the query. This will clear the data cache. |
|
|
|
|
|
|
Index or Table Scan?