This has been a excellent series of articles. from the days of dBase II number of us knew how bTree+ etc worked but to understand how it functioned beneath the surface of SQL server was excellent!!!!
I have assumed that if there were 2 non clusterd indexes the select statement would use both to help do the Select. Lete me explain if we had 2 indexes one on 'Lastname' and one on the 'county' to select all the 'Smiths' living in 'Surrey' I thought it will use both indexes. The article does not mention anything about this. Hence I must assume that SQL server will use only one or the other index depending on the statistics
However I am left wondering why not both.Surely we can select all the bookmarks of Lastnames with 'Smith' . Lets Call it Selection 1. at the end of that process then select all the bookmarks of People living in Surrey but discard it if it does not exist in selection 1. If it exists in Selection 1 move the bookmark to selection 2. At the end you will end up with the Bookmarks of the required selection just by using the selection 2
If this technique was used then it is not necessary to have a index of say Lastname+county or County+Lastname. also it will not be necessary to have include columns
with 2 Index columns the argument is not very apparent but with 4 or 5 Index columns it will be
Would like to know David Durant has to say about this