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


Index Structures


Index Structures

Author
Message
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12456 Visits: 6903
At the last PASS summit, I had a chance to talk for about an hour at breakfast with a man from Microsoft who is part of the BOL team. According to him, so many new things are coming out so fast that just keeping the documentation up to date is a real challenge.

I thought that the question was a good one, and the discussion even better. (I got it wrong because of my interpretation, but so what?) A lot of information about index structures and index types came out in the discussion.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
WayneS
WayneS
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20806 Visits: 10652
The Dixie Flatline (5/27/2010)
At the last PASS summit, I had a chance to talk for about an hour at breakfast with a man from Microsoft who is part of the BOL team. According to him, so many new things are coming out so fast that just keeping the documentation up to date is a real challenge.

I thought that the question was a good one, and the discussion even better. (I got it wrong because of my interpretation, but so what?) A lot of information about index structures and index types came out in the discussion.

Thanks.

One thing that I've noticed is that MS doesn't annotate when the pages change. So, in the case of this QotD, several references have changed since I originally submitted the question. I've also noticed that BOL doesn't have links to previous versions like they used to anymore.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34252 Visits: 11359
WayneS (5/27/2010)
Question for you Paul:
It is stated in BOL:
Instead of constructing a B-tree structure based on a value stored in a particular row, the Full-Text Engine builds an inverted, stacked, compressed index structure based on individual tokens from the text being indexed.
Now, I'm not about to question Kalen, but where MS is stating so specifically that it's not a B-tree, what gives here?

Hey Wayne,

This is just wording again - and it is confusing. My guess is that the intention of the writer was to show that full-text indexes have a different internal arrangement from 'ordinary' indexes. The distinction is between a B+ tree based on a value stored in a particular row, and a B+ tree built on an inverted, stacked, compressed structure build on parsed tokens.

If you have Kalen's 2008 Internals book, the relevant sections are on pages 345-346. A key part (talking about FT indexes) is "...their space usage is tracked in exactly the same way (using IAM pages) and their structures are the same as regular indexes."

As I keep saying, though, whether you consider full-text indexes and 'regular' indexes to have the same structure or not depends a lot on what level of structure we are talking about.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
UMG Developer
UMG Developer
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3936 Visits: 2204
Great questions thanks!

I got it correct, but not for the right reason. :-)
Paul Randal
Paul Randal
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7900 Visits: 1719
Another question with an arbitrary, not-well explained answer. The actual index structures are exactly the same for all index types except full-text indexes. So how is 4 the correct answer? And I know index structures inside and out...

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25378 Visits: 12489
Interesting question and discussion.

I got it wrong (said 4) so I got my point. w00t

edit: But I'm still not sure whether the right answer is 1 or 2, even after reading all the discussion. I guess it depends on whether a B+tree indexing rows by column values is the same structure as a B+tree indexing some sort of pile of tokens attached to pointers (if that's what a full text index is - I don't really understand that either). Ermm

Tom

khullargirish02
khullargirish02
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 126
got it right... this time with my understanding..........Cool
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