SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using 2 Non Clustered index


using 2 Non Clustered index

Author
Message
siva 20997
siva 20997
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search