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 Monday, May 24, 2010 12:54 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Post #927033
Posted Monday, May 24, 2010 1:01 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #927037
Posted Monday, May 24, 2010 1:59 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Post #927062
Posted Monday, May 24, 2010 2:24 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #927072
Posted Thursday, May 27, 2010 10:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:25 AM
Points: 1,105, Visits: 4,902
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...
Post #929128
Posted Thursday, May 27, 2010 10:32 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:48 AM
Points: 3,956, Visits: 3,644
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.
Post #929130
Posted Thursday, May 27, 2010 10:33 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #929133
Posted Thursday, May 27, 2010 12:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
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
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 #929204
Posted Thursday, May 27, 2010 12:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
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
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 #929208
Posted Thursday, May 27, 2010 1:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
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
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 #929239
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse