Index Structures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » Index Structures

47 posts, Page 5 of 5

«««1 2 3 4 5

Index Structures

Rate Topic

Display Mode

Topic Options

Author

Message

The Dixie Flatline

Posted Thursday, May 27, 2010 3:26 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 3,788, Visits: 5,543

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? -- Stephen Stills

Post #929331

WayneS

Posted Thursday, May 27, 2010 3:38 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 6,367, Visits: 8,228

The Dixie Flatline (5/27/2010)

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

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

Post #929336

Paul White

Posted Thursday, May 27, 2010 8:46 PM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #929417

UMG Developer

Posted Tuesday, June 01, 2010 8:40 PM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148

Great questions thanks!

I got it correct, but not for the right reason. Smile

Post #931071

Paul Randal

Posted Friday, June 11, 2010 9:59 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

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

Post #936397

L' Eomot Inversé

Posted Friday, October 01, 2010 7:15 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 7,084, Visits: 7,137

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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #997131

khullargirish02

Posted Friday, May 27, 2011 2:35 AM

Old Hand

Group: General Forum Members
Last Login: Monday, February 18, 2013 10:54 PM
Points: 371, Visits: 110

got it right... this time with my understanding.......... Cool

Post #1116059

« Prev Topic | Next Topic »

47 posts, Page 5 of 5

«««1 2 3 4 5

Permissions