Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Structures


Index Structures

Author
Message
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3494 Visits: 4408
Paul White NZ (5/24/2010)
Some people find Internals Viewer (an SSMS plug-in) easier for some tasks:
http://internalsviewer.codeplex.com/

Oh no... I knew it :-)

The Add-in 'InternalsViewer.SSMSAddIn.Connect' failed to load or caused an exception.
...
Error Message: The system cannot find the file specified.
Error number: 80070002


Happily, the plug-in is written on C#, so I can dig into its source code Whistling
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Might be the same issue as http://internalsviewer.codeplex.com/WorkItem/View.aspx?WorkItemId=5882. Make sure you download the correct version - for either SSMS 2005 or 2008.
Download choice page:http://internalsviewer.codeplex.com/releases/view/21139



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3494 Visits: 4408
Paul White NZ (5/24/2010)
Might be the same issue as http://internalsviewer.codeplex.com/WorkItem/View.aspx?WorkItemId=5882. Make sure you download the correct version - for either SSMS 2005 or 2008.
Download choice page:http://internalsviewer.codeplex.com/releases/view/21139

Thanks, this helped.
Nice plug-in. It's a little bit buggy ('Index was outside the bounds of the array' all the time, mess-up with the index types), it doesn't provide the functionality of 'DBCC PAGE (..., 3)', but it draws nice and easy-to-understand diagrams :-)
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
vk-kirov (5/24/2010)
Thanks, this helped.
Nice plug-in. It's a little bit buggy ('Index was outside the bounds of the array' all the time, mess-up with the index types), it doesn't provide the functionality of 'DBCC PAGE (..., 3)', but it draws nice and easy-to-understand diagrams :-)

It's *very* buggy :-D
Some people like it. I think it's better than nothing, and there's clearly a lot of work gone into the C# code.
It's open source, so feel free to improve it!



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
jts2013
jts2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 5009
A quick search on Microsft's website:

http://msdn.microsoft.com/en-us/library/ms175049.aspx

This clearly gives 8, which wasbn't even an option!

Not my favourite question, although it did make me think a bit more about indexes I suppose...
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4069 Visits: 3648
jts_2003 (5/27/2010)
A quick search on Microsft's website:

http://msdn.microsoft.com/en-us/library/ms175049.aspx

This clearly gives 8, which wasbn't even an option!

Not my favourite question, although it did make me think a bit more about indexes I suppose...


That lists the different 'types' of indexes. The question was about the structures. Several of these index types use the same structure. So it was a bit tricky.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
jts_2003 (5/27/2010)
A quick search on Microsft's website:
http://msdn.microsoft.com/en-us/library/ms175049.aspx
This clearly gives 8, which wasbn't even an option!
Not my favourite question, although it did make me think a bit more about indexes I suppose...

If only the question had been about index types!
As it was, it was about types of index structures.
Several people made the same error.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6226 Visits: 10398
Paul White NZ (5/27/2010)
jts_2003 (5/27/2010)
A quick search on Microsft's website:
http://msdn.microsoft.com/en-us/library/ms175049.aspx
This clearly gives 8, which wasbn't even an option!
Not my favourite question, although it did make me think a bit more about indexes I suppose...

If only the question had been about index types!
As it was, it was about types of index structures.
Several people made the same error.


One of the things that I'm "loving" is how MS changes their online documentation. For instance, that referenced link is for SQL 2008 R2. If you look at the SQL 2008 version, there's an additional type of index in this list. Since this link shows 9, that is why I had an option for 9 in the QotD (R2 wasn't released yet when I researched and wrote this.) The SQL 2005 link shows 7 (though the QotD did specifically mention SQL 2008!)

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

WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6226 Visits: 10398
Paul White NZ (5/27/2010)
jts_2003 (5/27/2010)
A quick search on Microsft's website:
http://msdn.microsoft.com/en-us/library/ms175049.aspx
This clearly gives 8, which wasbn't even an option!
Not my favourite question, although it did make me think a bit more about indexes I suppose...

If only the question had been about index types!
As it was, it was about types of index structures.
Several people made the same error.


Okay, I'm curious. What could I have done better to make it clearer that this question was about the different types of index structures, and not the different types of indexes? Should I have specifically said (NOT index TYPES, but types of index STRUCTURES)?

I did not want this to be a confusing question, and I didn't think it was, yet many people missed this and have complained about it.

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

WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6226 Visits: 10398
Paul White NZ (5/24/2010)A full-text index is the same, storage-wise, as any other non-clustered index. The parent object has an entry in sys.internal_tables, and the DBCC commands can be used to verify that it is a B-plus tree, with the same structure as any other non-clustered index. Ok, so a good fraction of the data stored within the index structure might be opaque to everything except the full-text engine, but that does not mean that the index structure is different - just how it is used.

Reference: Microsoft SQL Server 2008 Internals - K.Delaney et al.

Paul


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?

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

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