Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Index Types Expand / Collapse
Author
Message
Posted Thursday, July 22, 2010 6:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:23 PM
Points: 2,601, Visits: 3,949
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 2,917, Visits: 2,531
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 Jimmo
Sr 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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:16 AM
Points: 2,917, Visits: 2,531
Also, to use Paul Randall's method from a couple of weeks ago - this is for my point I lost

Steve Jimmo
Sr 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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 9:59 AM
Points: 1,264, Visits: 726
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
Post #957142
Posted Thursday, July 22, 2010 6:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 11:35 AM
Points: 311, Visits: 284
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
- XML

I assume, though, that this is another wording issue ; nevertheless an excellent lesson having to recap what index types there are.

Thanks,
Michael


And XML index may be
- Primary
- Secondary

And secondary XML index can be
- for Path
- for Value
- for Property

As for me the question is not formalized properly
Post #957148
Posted Thursday, July 22, 2010 7:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
Good question.

Filtered index....who knew.
Post #957159
Posted Thursday, July 22, 2010 7:43 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall 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 Qo
D from July 22) doesn't that imply that they are separate index types?



Post #957187
Posted Thursday, July 22, 2010 8:44 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 17,814, Visits: 15,741
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
Post #957240
Posted Thursday, July 22, 2010 9:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:26 PM
Points: 880, Visits: 608
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.'
Post #957276
Posted Thursday, July 22, 2010 10:02 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 9:11 AM
Points: 719, Visits: 695
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
Post #957312
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse