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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 sql index structure

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.
Go to Top of Page

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?
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -