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 |
ara3n
Starting Member
8 Posts |
Posted - 2007-02-06 : 10:18:32
|
Hello I have a question about nonclustered index in sql.As I understand they are stored in B-Tree structure. This makes sense, if the index has only one field. My question is about having two or more fields in a index. Are they stored as one value in each cell? So if you are searching only for the first field, how does this work?Below is detailed structure.[url]http://www.bluerwhite.org/btree/[/url]Thank you. |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-02-06 : 10:22:37
|
>> Are they stored as one value in each cell?What's a cell?If you are searching for the first column then it can use the index. If you search for only the second column then it can't seek using the index but it can scan the index which may be faster than scanning the data.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
ara3n
Starting Member
8 Posts |
Posted - 2007-02-06 : 10:30:26
|
Are you saying that if you have an index (last name, first name)and you search for first name, sql cannot seek using the index?It can only seek by the first column? |
|
|
ara3n
Starting Member
8 Posts |
Posted - 2007-02-06 : 10:40:04
|
Is there a place I can read about in detail how an index with two fields are created and searched through? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-06 : 10:52:13
|
"and you search for first name, sql cannot seek using the index?"I suspect it depends. If your query is "covered" by the index it may be used, but if you use a bunch of additional columns in your query, and NOT including Last Name in the WHERE/JOIN, then I can't see SQL Server using the index.The reason (in rather simplistic terms) is that it will take longer to plough through the index trying to find all the matching "First Name" than ploughing through the data - and for every one it finds it has to do an additional index-lookup to get the location of the record on the disk (assuming this is not a clustered index) with inherent additional physical-disk-seeks.Also, as far as I know, the Statistics on the Index do not reveal how "popular" the second/subsequent keys in an index are, in their own right. So the query optimiser cannot gauge whether the index is going to speed up the query, or not. And that probably gets influenced by whether FULLSCAN was using during computation of the Statistics - and that's not the default. So, bottom line, I'm struggling to see how, in the majority of cases, SQL Server would have enough information to make traversal of the index for second/subsequent keys worthwhile.OTOH if you have two indexes - one for First name and one for Last name - I think there would be a good chance that SQL Server would use both of them when you did a query which used both columns in the WHERE/JOIN."It can only seek by the first column?"SEEK has special meaning as it is used to describe a Query Plan. It means to go "straight to the relevant record(s)", and in this example it can't do that! But I expect you were using SEEK in terms of "Find the stuff using the index" ... Now Nigel can tell me I've got that all completely wrong!Kristen |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-02-06 : 10:55:22
|
>> Is there a place I can read about in detail how an index with two fields are created and searched through?Inside SQL Server.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|