SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index types 1


Index types 1

Author
Message
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11016 Visits: 11994
sknox (8/6/2012)
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.)

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


m mcdonald (8/6/2012)
Nice. I found this link very helpful...

http://msdn.microsoft.com/en-us/library/ms175049.aspx

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


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2608 Visits: 2832
Hugo Kornelis (8/6/2012)
sknox (8/6/2012)
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.)

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


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.
m mcdonald
m mcdonald
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1900 Visits: 1682
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 :-D


Cheers.
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9862 Visits: 10572
Hugo Kornelis (8/6/2012)
m mcdonald (8/6/2012)
Nice. I found this link very helpful...

http://msdn.microsoft.com/en-us/library/ms175049.aspx

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


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?

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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11016 Visits: 11994
WayneS (8/7/2012)
Hugo Kornelis (8/6/2012)
m mcdonald (8/6/2012)
Nice. I found this link very helpful...

http://msdn.microsoft.com/en-us/library/ms175049.aspx

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


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?

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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2775 Visits: 8084
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 ;-)
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5048 Visits: 2373
Nice question! Learned something new. Thanks Hugo!
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14314 Visits: 12197
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.

Tom

Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11016 Visits: 11994
L' Eomot Inversé (8/11/2012)
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.

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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Miles Neale
Miles Neale
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3036 Visits: 1694
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!

Not all gray hairs are Dinosaurs!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search