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 |
|
TC125
Starting Member
4 Posts |
 Posted - 2008-09-26 : 05:10:52
|
| If I do a select max on a numeric or int column that is not indexed, I'm going to cause a table scan.If the column IS indexed, am I still going to cause a table scan? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-26 : 05:20:54
|
What did your tests tell you?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
TC125
Starting Member
4 Posts |
Posted - 2008-09-26 : 05:23:01
|
| I don't know how to do the tests yet. That's why I posted on the "New" forum. If you tell me how to test it, I will get the info for myself. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-26 : 05:24:52
|
Create an index over the column.run the query again in ssms (sql server management studio).make sure you have clicked the button that says "inlcude actual execution plan".
E 12°55'05.63"N 56°04'39.26" |
|
|
|
TC125
Starting Member
4 Posts |
Posted - 2008-09-26 : 07:24:36
|
| OK. It tells me that this causes an "index" scan as opposed to a "table" scan. Obviously better than a table scan.How bad is it? The tables involved should "never" have more than 20000 rows or so. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-26 : 07:32:56
|
It depends on the granularity of the indexed columns.If you have indexed a column which is used for "gender" and the values are only Male and female, the index scan is only a little better than a table scan.If you have indexed a column for "age" which has integer values from 0 to 99 (or more) and the values are evenly distributed an index scan is much better than a table scan.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
select max(foo) and table scans