SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Wayne Sheffield / Index Types / Latest Posts

RE: Index Types

codebyo — Wed, 11 Aug 2010 18:47:59 GMT

RE: Index Types

Hugo Kornelis — Sat, 24 Jul 2010 11:25:13 GMT

[quote][b]magasvs (7/24/2010)[/b][hr]It would be confusing to get this question on interview, is it? I wouldn't know what is the right answer, probably would give two answers :)[/quote]I would consider it as an excellent occasion to show off my knowledge of all the various indexes SQL Server uses. I'd probably talk for at least five minutes, cover most (but not all - I'm bound to forget some) index types, never really answer the actual question but still show how much I know about the subject.

RE: Index Types

magasvs — Sat, 24 Jul 2010 11:13:22 GMT

I have added my vote as well. It would be confusing to get this question on interview, is it? I wouldn't know what is the right answer, probably would give two answers :)

RE: Index Types

Steve Jones - SSC Editor — Fri, 23 Jul 2010 08:39:23 GMT

I've added my vote as well and will drop it on Twitter to scrounge for votes.

RE: Index Types

WayneS — Fri, 23 Jul 2010 05:07:38 GMT

[quote][b]cengland0 (7/22/2010)[/b][hr][quote][b]WayneS (7/22/2010)[/b][hr]For everyone that thinks that the BOL reference in the QotD is wrong and that there are just 5 types of indexes: I created a connect item at [url=https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexes][u]https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexes[/u][/url] to fix the documentation. Please go vote on it.[/quote]That's wonderful. So if it gets approved and the BOL is updated, do I get my point that I lost for this QOTD?[/quote]No... so far you've already gotten three for the posts in the discussion.:w00t:Edit: But, if you go vote for it and it does get approved, you get the satisfaction of knowing you helped change something.

RE: Index Types

cengland0 — Thu, 22 Jul 2010 19:43:23 GMT

[quote][b]WayneS (7/22/2010)[/b][hr]For everyone that thinks that the BOL reference in the QotD is wrong and that there are just 5 types of indexes: I created a connect item at [url=https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexes][u]https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexes[/u][/url] to fix the documentation. Please go vote on it.[/quote]That's wonderful. So if it gets approved and the BOL is updated, do I get my point that I lost for this QOTD?

RE: Index Types

WayneS — Thu, 22 Jul 2010 17:13:19 GMT

For everyone that thinks that the BOL reference in the QotD is wrong and that there are just 5 types of indexes: I created a connect item at [url=https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexes][u]https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexes[/u][/url] to fix the documentation. Please go vote on it.

RE: Index Types

cdesmarais 49673 — Thu, 22 Jul 2010 15:20:46 GMT

My brain is probably too rigid at categorizing things, but I would want to stick with the list of 5 as types and Unique, Filtered or Included columns as properties that are applicable to one or more of the listed index types.

RE: Index Types

Hugo Kornelis — Thu, 22 Jul 2010 15:13:15 GMT

I'd call this question a good question, based on bad documentation. BOL is certainly not flawless, but it's the best we have, so let's accept it as a reliable resource for these questions, and take it with a grain of salt everywhere else.And if Abe Lincoln were still alive today, he'd probably say that you can't please the QotD crowd at any time... ;-)

RE: Index Types

L' Eomot Inversé — Thu, 22 Jul 2010 13:23:08 GMT

[quote][b]Dan Guzman - Not the MVP (7/22/2010)[/b][hr]Man you guys are finicky.[/quote]I think I agree with that. And I would add that anyone who says "X can't be a type because it's a subtype of Y" (I've seen that claim in this topic) has completely missed the standard meaning of the prefix "sub" in algebra, mathematical logic, type theory, topology, and pretty well every other branch of mathematics and computer science.It was a good question, with a perfectly reasonable asnswer based on exactly the sort of MS documentation that QoTD answers about SQL Server are supposed to be based on (although coming down from 9 to 8 was maybe a little naughty). The fact that other MS documentation contradicts it is the fault of the MS documentation, not of the question and answer.

RE: Index Types

weitzera — Thu, 22 Jul 2010 11:51:41 GMT

[quote][b]Grant Fritchey (7/22/2010)[/b][hr][quote][b]magasvs (7/22/2010)[/b][hr]I new I will get incorrect answer :)I based my answer on sys.indexes "type" column:Type of index:0 = Heap1 = Clustered2 = Nonclustered3 = XML4 = Spatial[/quote]I think this is the better list, but working from MS documentation, they list eight. I don't agree either, but our lack of agreement is not enough.[/quote]This list includes heaps, which is a data structure but arguably not a "type" of index, and does not include full-text indexes. Edit: Interestingly, [primary]XML indexes share the same structure as clustered indexes, but are differentiated in their access -- I consider this a perfect example of a case where structure alone is not enough to differentiate "type."I think the problem with the question though is the mapping from english, which I think was steve's point.If the question had been type of variable, I think we all could have come to easy agreement, because the internal structure of the variable is pretty far removed in TSQL (unlike in c++ where we could have an argument) and also because we're used to using the word type for a specific meaning with regard to variables. The discussion that remains for Indexes is equivalent to whether VARCHAR is a different type from VARCHAR(8000) and from VARCHAR(MAX)Another edit: I had not seen reference to the is_hypothetical flag in sys.indexes before. From the link above:"Hypothetical indexes hold column-level statistics." I'd propose this as an index "type" that has not been previously mentioned in these lists.

RE: Index Types

Steve Jones - SSC Editor — Thu, 22 Jul 2010 11:40:59 GMT

BOL certainly isn't consistent, and we did submit a few changes for them to make the version pages more consistent.These two are certainly at odds with the other one. However that one is in there as a reference. I'd welcome suggestions on Connect about how to describe indexes better.

RE: Index Types

cdesmarais 49673 — Thu, 22 Jul 2010 11:24:50 GMT

Books online isn't completely consistent. The enumeration of index types depends on the context you are looking at it. Frex.http://technet.microsoft.com/en-us/library/ms181197.aspxhttp://technet.microsoft.com/en-us/library/ms173760.aspx

RE: Index Types

Grant Fritchey — Thu, 22 Jul 2010 11:24:48 GMT

[quote][b]magasvs (7/22/2010)[/b][hr]I new I will get incorrect answer :)I based my answer on sys.indexes "type" column:Type of index:0 = Heap1 = Clustered2 = Nonclustered3 = XML4 = Spatial[/quote]I think this is the better list, but working from MS documentation, they list eight. I don't agree either, but our lack of agreement is not enough.

RE: Index Types

Steve Jones - SSC Editor — Thu, 22 Jul 2010 10:57:11 GMT

[quote][b]cengland0 (7/22/2010)[/b][hr]I don't subscribe to the logic that if it's on BOL, it must be true. If you want to include sub index types, you could include Ascending and Descending. The list could go on and on.[/quote]It's not "true", but BOL is a standard reference for all of us. If they list these are "types", then we can use "type" as a way of discussing things. Otherwise it becomes very difficult to ensure we are talking about the same thing.Wayne and I updated this question a few times as there used to be 9 types listed in older BOL docs. We use BOL as the reference from which we can base questions, and it is why we require an explanation and link for answers.If you disagree with BOL, and I'm with you on why, propose a change in the Community Content section or submit something on Connect.

RE: Index Types

weitzera — Thu, 22 Jul 2010 10:39:17 GMT

[quote][b]Dan Guzman - Not the MVP (7/22/2010)[/b][hr]Man you guys are finicky.Many of you prove your point by quoting MS documentation, but others of you claim MS Documentation isn't good enough.Which is it?So do all questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time' ?Someone also mentioned that unique indexes are clustered. Really? Non-clustered indexes can't be unique?Thanks for the question Wayne - "You can't please all the people all the time." - Abe LincolnDan[/quote]Hahaha, sigged:

RE: Index Types

UMG Developer — Thu, 22 Jul 2010 10:13:23 GMT

Interesting question, and discussion, I seem to recall a similar question not too long ago...

RE: Index Types

Dan Guzman - Not the MVP — Thu, 22 Jul 2010 10:02:06 GMT

Man you guys are finicky.Many of you prove your point by quoting MS documentation, but others of you claim MS Documentation isn't good enough.Which is it?So do all questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time' ?Someone also mentioned that unique indexes are clustered. Really? Non-clustered indexes can't be unique?Thanks for the question Wayne - "You can't please all the people all the time." - Abe LincolnDan

RE: Index Types

weitzera — Thu, 22 Jul 2010 09:25:05 GMT

For a different take on essentially the same question, see the discussion from May 21st[url]http://www.sqlservercentral.com/Forums/FindPost925652.aspx[/url]

RE: Index Types

SQLRNNR — Thu, 22 Jul 2010 08:44:22 GMT

Thanks for the QOD wayne.

RE: Index Types

jarney — Thu, 22 Jul 2010 07:43:15 GMT

If an XML Primary Index and an XML Secondary Index have different index structures (see QoD from July 22) doesn't that imply that they are separate index types?

RE: Index Types

Daniel Bowlin — Thu, 22 Jul 2010 07:16:13 GMT

Good question. Filtered index....who knew.

RE: Index Types

gchornenkyy — Thu, 22 Jul 2010 06:56:03 GMT

RE: Index Types

magasvs — Thu, 22 Jul 2010 06:49:47 GMT

I new I will get incorrect answer :)I based my answer on sys.indexes "type" column:Type of index:0 = Heap1 = Clustered2 = Nonclustered3 = XML4 = Spatial

RE: Index Types

sjimmo — Thu, 22 Jul 2010 06:26:22 GMT

Also, to use Paul Randall's method from a couple of weeks ago - this is for my point I lost

RE: Index Types

sjimmo — Thu, 22 Jul 2010 06:24:50 GMT

I must add my own .02 to this thread. I to come up with the correct answer being wrong. Using the reference given, I see that some of the [b]indexes[/b] identified as correct answers are truely modifiers of the basic index types. How can these be acceptable index types?

RE: Index Types

OCTom — Thu, 22 Jul 2010 06:19:05 GMT

I knew it had to be more than two and thought it was less than 10. So, I stabbed at it with eight and got it right. :-P It made me review indexes and that's a good thing.

RE: Index Types

cengland0 — Thu, 22 Jul 2010 04:54:52 GMT

[quote][b]WayneS (7/22/2010)[/b][hr]I would agree that Unique, filtered and included indexes are a sub-type of the clustered (for Unique) or non-clustered index types. However, according to the latest Microsoft documentation, they are a type of index. I'm not going to base a QotD off of what I believe it should be without any documentation to back me up.You might want to take a look at SQL 2008 (R1) BOL for index types at [url=http://msdn.microsoft.com/en-us/library/ms175049.aspx%28sql.100%29][u]http://msdn.microsoft.com/en-us/library/ms175049.aspx%28sql.100%29[/u][/url] - here they list 9 (they included Indexed Views). Since an Indexed View is a type of a view, physically manifested by having a clustered index and optionally having non-clustered indexes, I believe that it is correct that it was removed from this list.[/quote]I don't subscribe to the logic that if it's on BOL, it must be true. If you want to include sub index types, you could include Ascending and Descending. The list could go on and on.

RE: Index Types

george sibbald — Thu, 22 Jul 2010 04:52:39 GMT

just to get my point back I would have to agree some of the index types MS list are really properties of an index, but hey-ho, I guess you can say a unique index is 'different' to a non-unique one.

RE: Index Types

WayneS — Thu, 22 Jul 2010 04:46:33 GMT

I would agree that Unique, filtered and included indexes are a sub-type of the clustered (for Unique) or non-clustered index types. However, according to the latest Microsoft documentation, they are a type of index. I'm not going to base a QotD off of what I believe it should be without any documentation to back me up.You might want to take a look at SQL 2008 (R1) BOL for index types at [url=http://msdn.microsoft.com/en-us/library/ms175049.aspx%28sql.100%29][u]http://msdn.microsoft.com/en-us/library/ms175049.aspx%28sql.100%29[/u][/url] - here they list 9 (they included Indexed Views). Since an Indexed View is a type of a view, physically manifested by having a clustered index and optionally having non-clustered indexes, I believe that it is correct that it was removed from this list.

RE: Index Types

WayneS — Thu, 22 Jul 2010 04:34:43 GMT

[quote][b]hrvoje.piasevoli (7/22/2010)[/b][hr]Would missing and unused make it 10?:)[/quote]Thanks for the early morning chuckle! Yes, you are right about this. :-D

RE: Index Types

cengland0 — Thu, 22 Jul 2010 03:45:52 GMT

[quote][b]michael.kaufmann (7/22/2010)[/b][hr]Wayne,first of all thank you for compiling a QotD.However, I do not quite agree with the solution.'Unique' is just taking the definition of a clusterd or non-clustered index a step further.'with included columns' and 'filtered' again are non-clustered indexes (with advanced features).So I don't see them as individual types, but sub-types at best.Hence it comes down to 5 types (why I got it wrong):- clustered- non-clustered- full-text- spatial- XMLI assume, though, that this is another wording issue ;-); nevertheless an excellent lesson having to recap what index types there are.Thanks,Michael[/quote]I selected 5 as well and got it wrong. This question came up on another QOTD and there were disagreements on that one too. The "Unique" index one is still a clustered or non-clustered type so it shouldn't be counted twice.The "Index with included columns" is a non-clustered type.The "Filtered" is a non-clustered type.Taking the 8 listed, minus the 3 duplicates leaves 5.If you want to argue this further, what about adding indexed views? What about Fragmented indexes (Not good but they do exist on tables updated frequently).

RE: Index Types

hrvoje.piasevoli — Thu, 22 Jul 2010 02:45:25 GMT

Would missing and unused make it 10?:)

RE: Index Types

michael.kaufmann — Thu, 22 Jul 2010 02:14:42 GMT

Wayne,first of all thank you for compiling a QotD.However, I do not quite agree with the solution.'Unique' is just taking the definition of a clusterd or non-clustered index a step further.'with included columns' and 'filtered' again are non-clustered indexes (with advanced features).So I don't see them as individual types, but sub-types at best.Hence it comes down to 5 types (why I got it wrong):- clustered- non-clustered- full-text- spatial- XMLI assume, though, that this is another wording issue ;-); nevertheless an excellent lesson having to recap what index types there are.Thanks,Michael

RE: Index Types

Duncan Pryde — Thu, 22 Jul 2010 00:34:04 GMT

Thanks for the question. I'd not come across filtered indexes before, so it's only 7:30am and I've already learnt something new today. Can't ask for much more than that!Duncan

RE: Index Types

bitbucket-25253 — Wed, 21 Jul 2010 20:46:07 GMT

Very nice question ... thanks Wayne

Index Types

WayneS — Wed, 21 Jul 2010 20:45:01 GMT

Comments posted to this topic are about the item [B]Index Types[/B]