Index Structures

Question

Index Structures

Viewing 15 posts - 16 through 30 (of 46 total)

You must be logged in to reply to this topic. Login to reply

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 1

honza.mf (5/21/2010)
I guessed right. Great aid for me was the gaps between neumbers. If the row of numbers was complete...

I got it right because of the gap as well also because I counted 4. The 4 I counted were wrong but close. My guess on the structure was clustered, non-clustered, XML and spatial. As I write this I remember that I did know that clusterd and non-clustered indexes share the same structure. What I didn't know was about the structure of XML secondary.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Excellent question Wayne.

Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95460 More actions · Answer 3

About 3 weeks ago, I was doing some research on a related topic and found that this answer is wrong. I had asked Steve to change this question then.

This QotD should be:

Correct Answer: 3

Explanation: Index types Clustered, Non-clustered, Unique, Non-Clustered with Included columns, Indexed Views, Spatial and Filtered indexes have a B-Tree index structure.

Full-text indexes have a token-based functional index with an inverted, stacked, compressed index structure.

XML Indexes (Primary and Secondary) have a B+-Tree index structure.

References:

Clustered Index Structures: http://msdn.microsoft.com/en-us/library/ms177443.aspx

Non-Clustered Index Structures:http://msdn.microsoft.com/en-us/library/ms177484.aspx

XML Primary Indexes:http://msdn.microsoft.com/en-us/library/ms345121(SQL.90).aspx

XML Secondary Indexes:http://msdn.microsoft.com/en-US/library/ms187508(v=SQL.90).aspx

Spatial Indexes: http://msdn.microsoft.com/en-us/library/bb964712.aspx

Full-Text Indexes: http://technet.microsoft.com/en-us/library/cc879306.aspx

Unique, Filtered, Indexed Views and Indexes with included columns: http://msdn.microsoft.com/en-us/library/ms175049(v=SQL.100).aspx

B-Tree index structures: http://en.wikipedia.org/wiki/B_tree_Indexing

B+-Tree index structures: http://en.wikipedia.org/wiki/B%2B_tree

I'm very sorry for the confusion. I'll get with Steve to see what can be done to get this resolved.

Edit: fixed links to work correctly

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

KevinC. SSCommitted Points: 1570 More actions · Answer 4

KevinC.

SSCommitted

Points: 1570

May 21, 2010 at 11:23 am

#1170996

Great question. I learned something new.

Cliff Jones SSChampion Points: 10547 More actions · Answer 5

atikdesai (5/21/2010)

I count 9.

Clustered, Nonclustered, Unique, Index with included columns, Indexed views, Full-text , Spatial, Filtered, XML

http://msdn.microsoft.com/en-us/library/ms175049.aspx%5B/quote%5D

I agree, this was my count also. I guess some of these different index types could have the same structure?

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

A thought provoking question anyway....

I think "unique" should count twice or not at all. You can have unique clustered indexes and unique nonclustered indexes.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

deepak.a SSCertifiable Points: 5330 More actions · Answer 7

deepak.a

SSCertifiable

Points: 5330

May 22, 2010 at 2:24 am

#1171196

Excellent Question , Great 🙂

WayneS SSC Guru Points: 95460 More actions · Answer 8

The Dixie Flatline (5/21/2010)
A thought provoking question anyway....
I think "unique" should count twice or not at all. You can have unique clustered indexes and unique nonclustered indexes.

"Unique" is a type of index, not an index structure. Both of those (clustered, nonclustered) have a B-Tree index structure.

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

And we all know that the "B" in "B-Tree" as defined by Microsoft, means balanced, and not binary. Right?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

ziangij SSCrazy Eights Points: 8066 More actions · Answer 10

thanks...didn't know about the index structures... lucky to get this one correct..:-)

Paul White SSC Guru Points: 150467 More actions · Answer 11

WayneS (5/21/2010)
About 3 weeks ago, I was doing some research on a related topic and found that this answer is wrong. I had asked Steve to change this question then.
This QotD should be:
Correct Answer: 3
Explanation: Index types Clustered, Non-clustered, Unique, Non-Clustered with Included columns, Indexed Views, Spatial and Filtered indexes have a B-Tree index structure.
Full-text indexes have a token-based functional index with an inverted, stacked, compressed index structure.
XML Indexes (Primary and Secondary) have a B+-Tree index structure.

That's still not right, to the best of my knowledge, though a 'correct' answer does depend on the exact meaning attached to the phrase 'index structure'.

From one point of view, there is only one type of index structure in SQL Server: the B-plus tree, where all records are stored at the leaf, and the leaf level incorporates a doubly-linked list to make partial forward and backward scans efficient (SQL Server only uses B-plus trees, not B-trees).

A primary XML index is nothing more than a 'normal' clustered index on a shredded representation of the XML data. The main difference is that the index is created on an internal table (type IT) rather than a user table or view (types U and V). There's no structural difference between a clustered index on an internal table, user table, or view. All three can be examined in detail using the familiar DBCC commands to verify that.

Similarly, a secondary XML index is just a regular non-clustered index on a clustered index (which happens to be on an internal table representing shredded XML).

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vk-kirov SSCertifiable Points: 7686 More actions · Answer 12

Paul White NZ (5/24/2010)
All three can be examined in detail using the familiar DBCC commands to verify that.

What is the list of these commands?

1) DBCC PAGE;

2) ... ?

Paul White SSC Guru Points: 150467 More actions · Answer 13

vk-kirov (5/24/2010)
Paul White NZ (5/24/2010)
All three can be examined in detail using the familiar DBCC commands to verify that.
What is the list of these commands?

EXTENTINFO, IND, and PAGE.

You also need TRACEON(3604) with PAGE of course.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vk-kirov SSCertifiable Points: 7686 More actions · Answer 14

Paul White NZ (5/24/2010)
EXTENTINFO, IND, and PAGE.
You also need TRACEON(3604) with PAGE of course.

Thanks for the information. I definitely need to play with these commands for better understanding of table and index structures.

Paul White SSC Guru Points: 150467 More actions · Answer 15

vk-kirov (5/24/2010)
Thanks for the information. I definitely need to play with these commands for better understanding of table and index structures.

No worries. Some people find Internals Viewer (an SSMS plug-in) easier for some tasks:

http://internalsviewer.codeplex.com/

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi