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 |
|
cjp
Yak Posting Veteran
69 Posts |
 Posted - 2009-11-25 : 06:47:57
|
| I am running SQLS 2005, SP 3.I have been reading many articles - BOL and various forums - about indexing.1 - Several articles say that, in effect, the table is the index when a clustered index is set. What precisely does this mean? For this kind of index, is a copy of the key data held at leaf level in a separate index table or is the table simply rebuilt in order of the indexed columns?2 - Is it possible to say at what point an index will not be used? I am running a large procedure that will eventually output a table containing perhaps 70% of all the available records in a demographic table (one row per patient). The demograpic table is joined to a table holding consultation details for patients, of whom perhaps 80% will have an entry for body mass index - so both contributing tables are delivering a very high proportion of their available data. My impression is that indexes are abandoned in this kind of scenario and that the query optimiser (effectively) resorts to table scans - but the execution plan shows that 88% of the load is given to a clustered index seek on the larger of the tables (it has over 500 million rows). Joining this (nested loop) to the output from the demographic table costs only 1%. Is it likely that the clustered index seek is, in effect, the equivalent of a table scan when such a high proportion of the data is to be extracted?Thanks.Chris |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2009-11-29 : 08:38:34
|
| Many thanks for the link and for the series of articles: this work on indexes is some of the clearest and most helpful I have read on any topic in SQL Server. Highly recommended to anyone who, like me, needs real information and guidance communicated accessibly.Chris |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-11-30 : 02:06:10
|
| Glad you found them useful.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Underlying structure of indexes