Index Types

Question

Post reply

Index Types

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

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

Daniel Bowlin

SSC-Dedicated

Points: 34566

July 22, 2010 at 7:16 am

#1196829

Good question.

Filtered index....who knew.

jarney Hall of Fame Points: 3699 More actions · Answer 2

If an XML Primary Index and an XML Secondary Index have different index structures (see Qo

D from July 22) doesn't that imply that they are separate index types?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Thanks for the QOD wayne.

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

weitzera Hall of Fame Points: 3442 More actions · Answer 4

For a different take on essentially the same question, see the discussion from May 21st

http://www.sqlservercentral.com/Forums/FindPost925652.aspx

Dan Guzman - Not the MVP (7/22/2010)
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.'

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 5

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 Lincoln

Dan

UMG Developer SSChampion Points: 13482 More actions · Answer 6

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

weitzera Hall of Fame Points: 3442 More actions · Answer 7

Dan Guzman - Not the MVP (7/22/2010)
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 Lincoln
Dan

Hahaha, sigged:

Dan Guzman - Not the MVP (7/22/2010)
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.'

Steve Jones - SSC Editor SSC Guru Points: 736198 More actions · Answer 8

cengland0 (7/22/2010)
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.

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.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

magasvs (7/22/2010)
I new I will get incorrect answer 🙂
I based my answer on sys.indexes "type" column:
Type of index:
0 = Heap
1 = Clustered
2 = Nonclustered
3 = XML
4 = Spatial

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.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

cdesmarais 49673 SSCommitted Points: 1863 More actions · Answer 10

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.aspx

http://technet.microsoft.com/en-us/library/ms173760.aspx

Steve Jones - SSC Editor SSC Guru Points: 736198 More actions · Answer 11

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.

weitzera Hall of Fame Points: 3442 More actions · Answer 12

Grant Fritchey (7/22/2010)
magasvs (7/22/2010)
I new I will get incorrect answer 🙂
I based my answer on sys.indexes "type" column:
Type of index:
0 = Heap
1 = Clustered
2 = Nonclustered
3 = XML
4 = Spatial
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.

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.

Dan Guzman - Not the MVP (7/22/2010)
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.'

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Dan Guzman - Not the MVP (7/22/2010)
Man you guys are finicky.

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.

Tom

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

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... 😉

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

cdesmarais 49673 SSCommitted Points: 1863 More actions · Answer 15

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.