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 Wednesday, July 21, 2010 8:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
Comments posted to this topic are about the item Index Types

Wayne
Microsoft Certified Master: SQL Server 2008
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
Post #956895
Posted Wednesday, July 21, 2010 8:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
Very nice question ... thanks Wayne

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #956896
Posted Thursday, July 22, 2010 12:34 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: Today @ 6:56 AM
Points: 3,352, Visits: 1,487
Thanks for the question. I'd not come across filtered indexes before, so it's only 7:30am and I've already learnt something new today. Can't ask for much more than that!

Duncan
Post #956950
Posted Thursday, July 22, 2010 2:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
Post #956985
Posted Thursday, July 22, 2010 2:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, Visits: 510
Would missing and unused make it 10?:)

Hrvoje Piasevoli
Post #957001
Posted Thursday, July 22, 2010 3:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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


I selected 5 as well and got it wrong. This question came up on another QOTD and there were disagreements on that one too.

The "Unique" index one is still a clustered or non-clustered type so it shouldn't be counted twice.

The "Index with included columns" is a non-clustered type.

The "Filtered" is a non-clustered type.

Taking the 8 listed, minus the 3 duplicates leaves 5.

If you want to argue this further, what about adding indexed views? What about Fragmented indexes (Not good but they do exist on tables updated frequently).

Post #957030
Posted Thursday, July 22, 2010 4:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
hrvoje.piasevoli (7/22/2010)
Would missing and unused make it 10?:)

Thanks for the early morning chuckle! Yes, you are right about this.


Wayne
Microsoft Certified Master: SQL Server 2008
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
Post #957058
Posted Thursday, July 22, 2010 4:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
I would agree that Unique, filtered and included indexes are a sub-type of the clustered (for Unique) or non-clustered index types. However, according to the latest Microsoft documentation, they are a type of index. I'm not going to base a QotD off of what I believe it should be without any documentation to back me up.

You might want to take a look at SQL 2008 (R1) BOL for index types at http://msdn.microsoft.com/en-us/library/ms175049.aspx%28sql.100%29 - here they list 9 (they included Indexed Views). Since an Indexed View is a type of a view, physically manifested by having a clustered index and optionally having non-clustered indexes, I believe that it is correct that it was removed from this list.


Wayne
Microsoft Certified Master: SQL Server 2008
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
Post #957061
Posted Thursday, July 22, 2010 4:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 5:21 PM
Points: 5,877, Visits: 13,005
just to get my point back I would have to agree some of the index types MS list are really properties of an index, but hey-ho, I guess you can say a unique index is 'different' to a non-unique one.

---------------------------------------------------------------------

Post #957063
Posted Thursday, July 22, 2010 4:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
WayneS (7/22/2010)
I would agree that Unique, filtered and included indexes are a sub-type of the clustered (for Unique) or non-clustered index types. However, according to the latest Microsoft documentation, they are a type of index. I'm not going to base a QotD off of what I believe it should be without any documentation to back me up.

You might want to take a look at SQL 2008 (R1) BOL for index types at http://msdn.microsoft.com/en-us/library/ms175049.aspx%28sql.100%29 - here they list 9 (they included Indexed Views). Since an Indexed View is a type of a view, physically manifested by having a clustered index and optionally having non-clustered indexes, I believe that it is correct that it was removed from this list.


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.
Post #957065
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse