SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Hugo Kornelis / Index types 1 / Latest Posts

RE: Index types 1

Miles Neale — Wed, 19 Sep 2012 17:57:05 GMT

Nice question! Had to dig a little to find out part of the XML index strategy but learned something new in the process. Thanks Hugo!

RE: Index types 1

Hugo Kornelis — Sat, 11 Aug 2012 09:47:57 GMT

[quote][b]L' Eomot Inversé (8/11/2012)[/b][hr]But one niggle - either the explanation is wrong about NONCLUSTERED being a mandatory keyword for CREATE COLUMNSTORE INDEX, of BoL (the page you reference in the explanation) is wrong about it being an optional keyword - it's shown in option brackets in the BNF description of the statement syntax, so that it appears that although the index must be nonclustered it isn't neccessary to supply the keyword. I don't have SQL 2012 handy so can't check whether this is a BoL error or not.[/quote]You are absolutely right, Tom. I made a mistake there. Luckily, the mistake doesn't affect the correctness of the answer. The keyword NONCLUSTERED is optional, but if you leave it out you still get a nonclustered columnstore index - and if you change it to clustered, you get an error message stating that clustered columnstore indexes are not supported.

RE: Index types 1

L' Eomot Inversé — Sat, 11 Aug 2012 07:49:05 GMT

Nice question and good explanation.But one niggle - either the explanation is wrong about NONCLUSTERED being a mandatory keyword for CREATE COLUMNSTORE INDEX, of BoL (the page you reference in the explanation) is wrong about it being an optional keyword - it's shown in option brackets in the BNF description of the statement syntax, so that it appears that although the index must be nonclustered it isn't neccessary to supply the keyword. I don't have SQL 2012 handy so can't check whether this is a BoL error or not.

RE: Index types 1

KWymore — Wed, 08 Aug 2012 17:43:53 GMT

Nice question! Learned something new. Thanks Hugo!

RE: Index types 1

Toreador — Tue, 07 Aug 2012 08:16:51 GMT

Well I got it wrong, despite already using XML indexes.I suspect this is because the question made no mention of the date format set on the database ;-)

RE: Index types 1

Hugo Kornelis — Tue, 07 Aug 2012 07:25:05 GMT

[quote][b]WayneS (8/7/2012)[/b][hr][quote][b]Hugo Kornelis (8/6/2012)[/b][hr][quote][b]m mcdonald (8/6/2012)[/b][hr]Nice. I found this link very helpful...http://msdn.microsoft.com/en-us/library/ms175049.aspx[/quote]I deliberately didn't include that link, as I think the information is presented there in a very confusing way. This has already resulted in two highly controversial QotD's. (One of them in fact was my inspiration for this question).[/quote]Well, I believe that I'm the author of one of those "highly controversial QotD's", and I just have to say... great question. It really makes one think. Especially with the newer index types (columnstore, and spatial (okay, 2008, but I've never used it or the XML ones yet)).Since the title is "Index types 1"... are there more coming?[/quote]For the record, I don't blame the authors of those questions; I blame the confusing language in the BOL article, since it confuses types with attributes. For instance, if a unique index is a type and an index with included columns is a type, then what about a unique index with included columns? And why would an index on a computed column be a seperate type, but an index on a view not? (In fact, why not go the whole way and say that an index on three columns is a seperate type, or an index on a varchar column?)I think most people would agree that spatial, xml, and freetext are all seperate index types. For clustered/nonclustered, you'd get more debate. And for unique, computed columns, included columns and filtered indexes, I'd expect most people to not consider them as index types, but rather as index attributes.(As such, you might argue that my use of the word "types" in the question is not correct - but I think it is clear enough for all that my question focuses on which combinations are correct and which are not).And to answer your last question: yes, I submitted a second, similar question. I thought it was supposed to be published next week, but when I just double checked I noticed that the status is "Submitted, awaiting approval" so I have no idea when it will run.

RE: Index types 1

WayneS — Tue, 07 Aug 2012 06:53:41 GMT

[quote][b]Hugo Kornelis (8/6/2012)[/b][hr][quote][b]m mcdonald (8/6/2012)[/b][hr]Nice. I found this link very helpful...http://msdn.microsoft.com/en-us/library/ms175049.aspx[/quote]I deliberately didn't include that link, as I think the information is presented there in a very confusing way. This has already resulted in two highly controversial QotD's. (One of them in fact was my inspiration for this question).[/quote]Well, I believe that I'm the author of one of those "highly controversial QotD's", and I just have to say... great question. It really makes one think. Especially with the newer index types (columnstore, and spatial (okay, 2008, but I've never used it or the XML ones yet)).Since the title is "Index types 1"... are there more coming?

RE: Index types 1

m mcdonald — Mon, 06 Aug 2012 10:08:26 GMT

Not so much the information on that "landing page" as much as the additional links to additional information regarding each index type.Worked for me this time :-DCheers.

RE: Index types 1

sknox — Mon, 06 Aug 2012 08:21:08 GMT

[quote][b]Hugo Kornelis (8/6/2012)[/b][hr][quote][b]sknox (8/6/2012)[/b][hr]Hugo, I can see why you didn't want the "choose 3" text there -- for many of us it takes a question with 31 possible answers (2^5 -1 since I believe choosing 0 answers is not an option in this system down to a choice of 3 (if you got the non-unique clustered index) or 6 (if you weren't sure of the non-unique clustered index.)[/quote]I prefer not to think of it as a single question with 31 possible answers, but as six simple yes/no questions (with the additional information that at least two of them need to have a "yes" answer).((And for the pedantic among us, it's actually 57 possible answers (2^6 - 1 - 6) as I did provide the note about "multiple answers" being correct - so all six options with only one tick can be discarded, as can the option with no correct answer. And for the record, the QotD software forces the author to mark at least one answer as correct, and will automatically present the answer options as either radio buttons or tick marks depending on the number of correct options.))[/quote]Actually by "for many of us" I was indicating the fact that the unique non-clustered option was pretty much a given (at this time 90% of respondents get this one correct.) That leaves 5 answers, of which at least one must be selected to make it a true multiple answer question -- that gives us the 2^5-1 = 31.But then I did mess up in the "choose 3" calculation. If you were correctly confident of the non-unique clustered index answer, then you'd have 2 and need one more out of 4. If you weren't confident of that one, you'd need 2 out of 5, of which there are 10 unique combinations.

RE: Index types 1

Hugo Kornelis — Mon, 06 Aug 2012 08:12:24 GMT

[quote][b]sknox (8/6/2012)[/b][hr]Hugo, I can see why you didn't want the "choose 3" text there -- for many of us it takes a question with 31 possible answers (2^5 -1 since I believe choosing 0 answers is not an option in this system down to a choice of 3 (if you got the non-unique clustered index) or 6 (if you weren't sure of the non-unique clustered index.)[/quote]I prefer not to think of it as a single question with 31 possible answers, but as six simple yes/no questions (with the additional information that at least two of them need to have a "yes" answer).((And for the pedantic among us, it's actually 57 possible answers (2^6 - 1 - 6) as I did provide the note about "multiple answers" being correct - so all six options with only one tick can be discarded, as can the option with no correct answer. And for the record, the QotD software forces the author to mark at least one answer as correct, and will automatically present the answer options as either radio buttons or tick marks depending on the number of correct options.))[quote][b]m mcdonald (8/6/2012)[/b][hr]Nice. I found this link very helpful...http://msdn.microsoft.com/en-us/library/ms175049.aspx[/quote]I deliberately didn't include that link, as I think the information is presented there in a very confusing way. This has already resulted in two highly controversial QotD's. (One of them in fact was my inspiration for this question).

RE: Index types 1

m mcdonald — Mon, 06 Aug 2012 07:52:15 GMT

Nice. I found this link very helpful...http://msdn.microsoft.com/en-us/library/ms175049.aspxCheers

RE: Index types 1

sknox — Mon, 06 Aug 2012 07:46:45 GMT

[quote][b] Jack Corbett (8/6/2012)[/b][hr]I over thought this one, and knew I was doing it, but still did it anyway. My thinking was that, while you do not have to define a clustered index as unique, SQL Server does add a uniqufier, thus I eliminated non-unique clustered index. See what I mean about over-thinking?[/quote]I was wondering why, at the time I answered, 45% of respondents didn't think a non-unique clustered index was possible. I'll give them the benefit of the doubt, and chalk it up to them all thinking as much as you. :-)Since the clustered index is referenced by other indexes as the row identifier (since the b-tree is organized by the clustered index and therefore that would be the most efficient reference to use) a uniquifier is necessary on a "non-unique" clustered index. But since that uniquifier is not directly accessible through DML, the clustered index appears to the client as non-unique.Hugo, I can see why you didn't want the "choose 3" text there -- for many of us it takes a question with 31 possible answers (2^5 -1 since I believe choosing 0 answers is not an option in this system down to a choice of 3 (if you got the non-unique clustered index) or 6 (if you weren't sure of the non-unique clustered index.)But I for one am glad it was there, as I haven't done much with XML or Spatial data as yet, so I had to guess for the last one. I got it right and learned something today!

RE: Index types 1

D.Oc — Mon, 06 Aug 2012 07:37:29 GMT

Good question, I got it wrong over XML part.

RE: Index types 1

mtassin — Mon, 06 Aug 2012 07:23:39 GMT

Put me in the group of learners...Knew the first two. Have never played with either XML or SPATIAL data types as of yet (curious about them, haven't had a problem to solve with them), so my practical experience was missing.Ah well... one day.

RE: Index types 1

Dana Medley — Mon, 06 Aug 2012 07:05:34 GMT

Thanks for the great question hugo. Got the first two, but the last one I was unsure of. Definitely learned something this morning.

RE: Index types 1

sestell1 — Mon, 06 Aug 2012 06:56:47 GMT

Thanks for the question Hugo! I had to research the XML indexes again... not something I use.

RE: Index types 1

Thomas Abraham — Mon, 06 Aug 2012 06:25:11 GMT

[quote][b]BrainDonor (8/6/2012)[/b][hr]The detailed disclaimer made me laugh - trying to pre-empt any complaints?[/quote]I don't blame him. I've decided I'll have to retain an attorney before I do another QotD. :-)Thanks, Hugo, for the question. But it was a bit too much for me for a Monday. I don't use non-unique clustered indexes, so I got lost looking for the right answer.

RE: Index types 1

Jack Corbett — Mon, 06 Aug 2012 06:19:16 GMT

I over thought this one, and knew I was doing it, but still did it anyway. My thinking was that, while you do not have to define a clustered index as unique, SQL Server does add a uniqufier, thus I eliminated non-unique clustered index. See what I mean about over-thinking?

RE: Index types 1

(Bob Brown) — Mon, 06 Aug 2012 05:12:40 GMT

Too much for me to handle on Monday morning. Nice question.

RE: Index types 1

Stewart "Arturius" Campbell — Mon, 06 Aug 2012 03:29:44 GMT

This one really got the grey matter exercising this morning.Thanks, Hugo

RE: Index types 1

DugyC — Mon, 06 Aug 2012 03:17:59 GMT

Oof! fluked this one.Knew the first 2 were right, and that columnstore was non-clustered. Guessed it was probably an XML index and decided "for value" would be more useful.Phew! :-)

RE: Index types 1

SQLPhil — Mon, 06 Aug 2012 01:51:08 GMT

Well that's got the brain ticking over quite nicely now. Thanks Hugo!

RE: Index types 1

Hugo Kornelis — Mon, 06 Aug 2012 01:44:38 GMT

[quote][b]BrainDonor (8/6/2012)[/b][hr]The detailed disclaimer made me laugh - trying to pre-empt any complaints?[/quote]Let's just say that this is not my first QotD submission... :w00t:By the way, the "please select 3" notice was added by the editor, not me. I only added the "multiple correct answers", but deliberately omitted the number of correct answers, and I am actually a bit disappointed that this number was added. :(

RE: Index types 1

BrainDonor — Mon, 06 Aug 2012 01:32:18 GMT

The detailed disclaimer made me laugh - trying to pre-empt any complaints?

RE: Index types 1

SQLRNNR — Mon, 06 Aug 2012 00:51:13 GMT

Good question - makes you think a bit.

RE: Index types 1

Michael Riemer — Mon, 06 Aug 2012 00:43:00 GMT

Great Question Hugo - thanks. Had to do a lot of reading to check what I already seemed to know, but taught me more about what I didn't know. :-)

RE: Index types 1

Koen Verbeeck — Mon, 06 Aug 2012 00:39:06 GMT

Very nice question Hugo, thanks.

RE: Index types 1

demonfox — Sun, 05 Aug 2012 22:31:52 GMT

thanks for the question;learned about xml indexes .

RE: Index types 1

Lokesh Vij — Sun, 05 Aug 2012 22:29:52 GMT

Nice question, Got to learn something new :)

Index types 1

Hugo Kornelis — Sat, 04 Aug 2012 11:52:26 GMT

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