Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Index Structures Expand / Collapse
Author
Message
Posted Thursday, May 27, 2010 3:26 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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
Posted Thursday, May 27, 2010 3:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 5,367, Visits: 8,987
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
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
Posted Thursday, May 27, 2010 8:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 9,928, Visits: 11,199
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
Posted Tuesday, June 1, 2010 8:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Great questions thanks!

I got it correct, but not for the right reason.
Post #931071
Posted Friday, June 11, 2010 9:59 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Posted Friday, October 1, 2010 7:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 7,874, Visits: 9,613
Interesting question and discussion.

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

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).


Tom
Post #997131
Posted Friday, May 27, 2011 2:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 21, 2014 12:18 AM
Points: 375, Visits: 116
got it right... this time with my understanding..........
Post #1116059
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse