Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Using Multiple non clustered Index Expand / Collapse
Posted Thursday, January 26, 2012 3:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, March 1, 2014 12:37 PM
Points: 75, Visits: 208
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
Post #1242008
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse