This was so interesting 😮 went through it along with the simple-talk tracing articles & just finished (slow reader -_). I'm gonna read through your other articles tomorrow (or as far through as I can get) unless someone here has a good TV show to recommend 😛 a few questions, in the article you said:
If SQL considers the index (or the subset of the index keys that the query would be seeking on) insufficiently selective then it is very likely that the index will be ignored and the query executed as a clustered index (table) scan.
It is important to note that this does not just apply to the leading column. There are scenarios where a very unselective column can be used as the leading column, with the other columns in the index making it selective enough to be used.
In these "scenarios" the result would then be a non-clustered full scan right?
Sure, the clustering key will be there (though whether or not it can be used for seeks depends on a property of the index, specifically unique)
So it will only allow you to seek on a clustering key if the key is unique?
creating and dropping indexes will cause plan recompilation, extensive locks, etc.
I guess the locks are only relevent for the creating rather than the dropping right? (since I guess SQL wouldn't attempt to make new queries use an index while its in the process of being dropped)
You also talked about the fillfactor & page splits in the thread and how 80% would cause more I/O reads as a result of there being more pages. How do you personally approach index fill factors? I guess the cost of excessive page splits & the desire to avoid them would outweigh the desire to minimise the number of pages (by settings 100%)?
Seems 2.5 months is inadequate study time for MSSQL...still learning about the fundamentals of indexes T_T