July 22, 2010 at 11:27 am
The Dixie Flatline (7/22/2010)
Oh for some better documentation.
Greg E
I heard there was a recently deciphered passage from the Dead Sea Scrolls that discussed such a parameter, but the fragment containing the ARID (alternate reality ID) was missing, so there is no way to be sure that it would even be applicable to the reality context you are currently operating under, Greg.
I don't think that the aliens from Andromeda who left us the procedure left the documentation with it. 🙁
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 22, 2010 at 11:36 am
GilaMonster (7/22/2010)
...And if we want to reference BoL, from the page on sys.indexes:Description of index type:
* HEAP
...
Heap? As an index type? My brain hurts.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 11:42 am
I tend to agree with you, Gail, but a reference is a reference for purposes of discussion.
I do think this is a thorny issue. Would welcome debate, and a Connect item on how to better describe indexes. FWIW, I don't like "property" as a way to look at unique, clustered, non-clustered, etc. I'd prefer structure (CI, NCI, XML, Spatial) and attribute (unique) instead.
July 22, 2010 at 11:50 am
Steve,
Even 'structure' of an index gets controversial (remember Wayne's QotD?)
It's all a bit loose and fluffy, and open to personal interpretation.
A primary XML index is clearly different from a clustered index...right?
Nope 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 11:58 am
Paul White NZ (7/22/2010)
GilaMonster (7/22/2010)
...And if we want to reference BoL, from the page on sys.indexes:Description of index type:
* HEAP
...
Heap? As an index type? My brain hurts.
In sys.indexes yes, because there has to be an entry for a table without a clustered index. I wouldn't call it an index though (more like the absence of an index) but it has to be there for all the other system views and DMVs that have index_id in them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2010 at 11:59 am
GilaMonster (7/22/2010)
Jack Corbett (7/22/2010)
I got the answer right because of Grant's recent SQL University blog post[/url] which also lists 8 and references BOL.I must admit, I don't agree with that. I don't view unique indexes or indexes with include columns as separate types. Unique is just a property of a clustered or nonclustered index, it's not an alternative index type. If you say CREATE UNIQUE INDEX, you're getting a nonclustered index, not a separate type that's at the same level as clustered/nonclustered
As for indexes with include columns considered separate types, well only nonclustered indexes can have include columns, so how can that be considered a 'type' equivalent to clustered or nonclustered. Same with filtered, only nonclustered indexes can be filtered and you don't say CREATE FILTERED INDEX. you say CREATE NONCLUSTERED INDEX ... WHERE ...
And if we want to reference BoL, from the page on sys.indexes:
type_desc
Description of index type:
* HEAP
* CLUSTERED
* NONCLUSTERED
* XML
* SPATIAL
is_unique and has_filter are properties of the index, not types of indexes.
Two questions about this list:
1. Isn't HEAP a characteristic of the table, meaning that the table has no index?
2. Where is full-text index?
FWIW, I agree that Unique, filtered and include are properties of the index, not a type of the index.
Heck, even the system views would agree with this:
select * from sys.indexes has an is_unique column and a has_filter column.
select * from sys.index_columns has an is_included_column column.
[frustrated vent mode = ON]
I just gotta say, I understand why people don't write QotD entries. I researched the heck out of the index structures one, and every single index type has a reference where BOL says it's one type or another. Then to have someone of the (IMO, extremely high) caliber of Paul Randall come along and tell me that BOL is all wrong, and that all the indexes (except full-text) are a B-+Tree structure, and that BOL and everyone (including him) just shortens it to B-Tree. Heck, how can you write a QotD accurately unless you have insider knowledge? How do you know which to really trust - BOL or Kalen Delaney's book?
I really thought that today's QotD (on the types of indexes) would be less controversial than the first one I did on the different types of index structures. Silly me. :angry: :crazy:
I guess I'll just stick to writing articles...
[frustrated vent mode = OFF]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 22, 2010 at 12:02 pm
Steve Jones - Editor (7/22/2010)
I tend to agree with you, Gail, but a reference is a reference for purposes of discussion.I do think this is a thorny issue. Would welcome debate, and a Connect item on how to better describe indexes. FWIW, I don't like "property" as a way to look at unique, clustered, non-clustered, etc. I'd prefer structure (CI, NCI, XML, Spatial) and attribute (unique) instead.
How does one go about creating a connect item? I think I've got about a dozen to submit on indexes.....
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 22, 2010 at 12:04 pm
GilaMonster (7/22/2010)
In sys.indexes yes, because there has to be an entry for a table without a clustered index. I wouldn't call it an index though (more like the absence of an index) but it has to be there for all the other system views and DMVs that have index_id in them.
Yeah - I was just being silly.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 12:05 pm
Paul White NZ (7/22/2010)
A primary XML index is clearly different from a clustered index...right?
Yes it is, because it can only be defined on an XML column, because it doesn't affect nonclustered indexes, because it doesn't count to the 'one clustered index per table' rule.
Behind the scenes, under the covers is a different story, but if we looked just at the internal structure of the indexes, we'd have very few index types. Spatial and (afaik) full text are b-trees too (in 2008 that is), clustered and nonclustered look near-identical on disk, XML are just clustered and nonclustered on internal hidden tables so we're pretty much down to one index type.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2010 at 12:09 pm
WayneS (7/22/2010)
Two questions about this list:1. Isn't HEAP a characteristic of the table, meaning that the table has no index?
2. Where is full-text index?
As mentioned to Paul, HEAP is there because there has to be an entry in sys.indexes for a table without a cluster
Full text has it's own system views - sys.fulltext_indexes so not in sys.indexes, which is where the list came from.
Not saying that's right or comprehensive, just giving a BoL reference that gives a different view to the first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2010 at 12:09 pm
GilaMonster (7/22/2010)
Paul White NZ (7/22/2010)
A primary XML index is clearly different from a clustered index...right?Yes it is, because it can only be defined on an XML column, because it doesn't affect nonclustered indexes, because it doesn't count to the 'one clustered index per table' rule.
But not if we're talking about structure...which we were.
Behind the scenes, under the covers is a different story, but if we looked just at the internal structure of the indexes, we'd have very few index types. Spatial and (afaik) full text are b-trees too (in 2008 that is), clustered and nonclustered look near-identical on disk, XML are just clustered and nonclustered on internal hidden tables so we're pretty much down to one index type.
Exactly my points on Wayne's first QotD.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 12:12 pm
WayneS (7/22/2010)
How does one go about creating a connect item? I think I've got about a dozen to submit on indexes.....
It used to be quite easy - though you had to do a search first. The interface changed just recently and doesn't really work for me at all now (probably because I use an alpha of Chrome).
Fire up IE or Firefox and go to connect.microsoft.com and browse around. Feel free to tell @BuckWoody how much the new design sucks (or otherwise).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 12:14 pm
Paul White NZ (7/22/2010)
GilaMonster (7/22/2010)
Paul White NZ (7/22/2010)
A primary XML index is clearly different from a clustered index...right?Yes it is, because it can only be defined on an XML column, because it doesn't affect nonclustered indexes, because it doesn't count to the 'one clustered index per table' rule.
But not if we're talking about structure...which we were.
Depends how deep into the engine you want to go. So deep that they all look the same and talking about types of indexes becomes pointless. There's one - the b-tree.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2010 at 12:17 pm
GilaMonster (7/22/2010)
Paul White NZ (7/22/2010)
A primary XML index is clearly different from a clustered index...right?Yes it is, because it can only be defined on an XML column, because it doesn't affect nonclustered indexes, because it doesn't count to the 'one clustered index per table' rule.
Behind the scenes, under the covers is a different story, but if we looked just at the internal structure of the indexes, we'd have very few index types. Spatial and (afaik) full text are b-trees too (in 2008 that is)
I have heard that full-text now uses the same underlying index structure as all other indexes, but I've been unable to find a reference on this. If anyone finds one, please let me know.
(FYI: Actually, (according to Paul Randall) spatial (and all other indexes except for full-text) is a B-+ tree)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 22, 2010 at 12:18 pm
GilaMonster (7/22/2010)
Depends how deep into the engine you want to go. So deep that they all look the same and talking about types of indexes becomes pointless. There's one - the b-tree.
I guess that's why all 'index type' discussions are doomed 🙂
There are multiple right answers.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16,636 through 16,650 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply