Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Index Types Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, July 22, 2010 6:19 AM
 SSCrazy Group: General Forum Members Last Login: Friday, January 30, 2015 7:36 AM Points: 2,723, Visits: 4,152
 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. It made me review indexes and that's a good thing.
Post #957111
 Posted Thursday, July 22, 2010 6:24 AM
 Hall of Fame Group: General Forum Members Last Login: Tuesday, December 6, 2016 12:59 PM Points: 3,244, Visits: 2,784
 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 indexes identified as correct answers are truely modifiers of the basic index types. How can these be acceptable index types? Steve JimmoSr DBA“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #957120
 Posted Thursday, July 22, 2010 6:26 AM
 Hall of Fame Group: General Forum Members Last Login: Tuesday, December 6, 2016 12:59 PM Points: 3,244, Visits: 2,784
 Also, to use Paul Randall's method from a couple of weeks ago - this is for my point I lost Steve JimmoSr DBA“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #957122
 Posted Thursday, July 22, 2010 6:49 AM
 Ten Centuries Group: General Forum Members Last Login: Sunday, June 19, 2016 3:32 PM Points: 1,271, Visits: 752
 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
Post #957142
 Posted Thursday, July 22, 2010 6:56 AM
 SSC-Addicted Group: General Forum Members Last Login: Wednesday, December 7, 2016 1:00 PM Points: 408, Visits: 366
 michael.kaufmann (7/22/2010)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,MichaelAnd XML index may be - Primary - SecondaryAnd secondary XML index can be- for Path- for Value- for PropertyAs for me the question is not formalized properly
Post #957148
 Posted Thursday, July 22, 2010 7:16 AM
 SSCrazy Group: General Forum Members Last Login: Saturday, September 17, 2016 8:01 AM Points: 2,834, Visits: 2,629
 Good question. Filtered index....who knew.
Post #957159
 Posted Thursday, July 22, 2010 7:43 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, December 18, 2013 10:27 AM Points: 3,357, Visits: 675
 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?
Post #957187
 Posted Thursday, July 22, 2010 8:44 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 2:52 PM Points: 20,083, Visits: 18,257
 Thanks for the QOD wayne. Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail Shaw
Post #957240
 Posted Thursday, July 22, 2010 9:25 AM
 SSC Eights! Group: General Forum Members Last Login: Thursday, September 17, 2015 4:17 PM Points: 890, Visits: 629
 For a different take on essentially the same question, see the discussion from May 21sthttp://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.'
Post #957276
 Posted Thursday, July 22, 2010 10:02 AM
 Right there with Babe Group: General Forum Members Last Login: Friday, December 2, 2016 12:33 PM Points: 722, Visits: 735
 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
Post #957312

 Permissions