SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 sql index structure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ara3n
Starting Member

8 Posts

Posted - 02/06/2007 :  10:18:32  Show Profile  Reply with Quote
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.
http://www.bluerwhite.org/btree/

Thank you.

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 02/06/2007 :  10:22:37  Show Profile  Visit nr's Homepage  Reply with Quote
>> 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 - 02/06/2007 :  10:30:26  Show Profile  Reply with Quote
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 - 02/06/2007 :  10:40:04  Show Profile  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 02/06/2007 :  10:52:13  Show Profile  Reply with Quote
"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

United Kingdom
12543 Posts

Posted - 02/06/2007 :  10:55:22  Show Profile  Visit nr's Homepage  Reply with Quote
>> 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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000