July 22, 2010 at 12:19 pm
GilaMonster (7/22/2010)
WayneS (7/22/2010)
Two questions about this list:1. Isn't HEAP a characteristic of the table, meaning that the table has no index?
2. Where is full-text index?
As mentioned to Paul, HEAP is there because there has to be an entry in sys.indexes for a table without a cluster
Sorry about that... I guess you posted that as I was composing this post, and we overlapped.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 22, 2010 at 12:22 pm
WayneS (7/22/2010)
Steve Jones - Editor (7/22/2010)
I tend to agree with you, Gail, but a reference is a reference for purposes of discussion.I do think this is a thorny issue. Would welcome debate, and a Connect item on how to better describe indexes. FWIW, I don't like "property" as a way to look at unique, clustered, non-clustered, etc. I'd prefer structure (CI, NCI, XML, Spatial) and attribute (unique) instead.
How does one go about creating a connect item? I think I've got about a dozen to submit on indexes.....
It's not hard. Go to connect.microsoft.com and follow the prompts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2010 at 12:24 pm
WayneS (7/22/2010)
spatial (and all other indexes except for full-text) is a B-+ tree)
This is becoming the discussion from your original QotD again.
I'm sure I checked at the time and 2008 full-text indexes were just as B+ tree (with a leaf-level doubly-linked list) as any other index in SQL Server. I'd be absolutely amazed if full-text indexes were simple B-trees.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 12:56 pm
...and now for something completely different:
not sure if that's a good thing or not?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
July 22, 2010 at 1:11 pm
jcrawf02 (7/22/2010)
...and now for something completely different:not sure if that's a good thing or not?
Ooo I do like a good game of Risk. Not at one turn per day though - despite what the guy says on the link.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 1:18 pm
WayneS (7/22/2010)
<snip>
I guess I'll just stick to writing articles...
</snip>
Please don't.
For every whiner there are 100 people* who are glad to have a QOD to look forward to every day.
I got it wrong, and I'm not whining. I'm just glad it was there. THANK YOU.
Chad
*Did you know that 87.5% of all statistics are made up on the spot? I have a reference for that somewhere...
July 22, 2010 at 1:23 pm
Chad Crawford (7/22/2010)
WayneS (7/22/2010)
<snip>
I guess I'll just stick to writing articles...
</snip>
Please don't.
For every whiner there are 100 people* who are glad to have a QOD to look forward to every day.
I got it wrong, and I'm not whining. I'm just glad it was there. THANK YOU.
Chad
*Did you know that 87.5% of all statistics are made up on the spot? I have a reference for that somewhere...
I agree with Chad's sentiment.
I also agree with discussion on indexes where included, unique, etc... are properties of indexes and not different types. Like I said, I knew 8 becuahttp://scarydba.wordpress.com/2010/07/19/sql-university-indexes-part-the-first/se of Grant's post, otherwise I would have probably gone with 5 because Clustered, Non-Clustered, XML I knew immediately so it had to be more than 2.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 22, 2010 at 1:37 pm
Steve Jones - Editor (7/22/2010)
I tend to agree with you, Gail, but a reference is a reference for purposes of discussion.I do think this is a thorny issue. Would welcome debate, and a Connect item on how to better describe indexes. FWIW, I don't like "property" as a way to look at unique, clustered, non-clustered, etc. I'd prefer structure (CI, NCI, XML, Spatial) and attribute (unique) instead.
the trouble with tat approach is that there's more than one way of looking at structure: for example it would not be unreasonable to claim that a clustered index is just an index with all non-key columns included at root level, so that the underlying table is redundant and can be thrown away - it's just an extreme case of a non-clustered index. And it is certainly true that a unique index is not one which happens to have the property that each key is unique, it is one which is constrained to have that property (or I could say that a unique index is just a convenient and efficient way of expressing a uniqueness constraint, the fact that it can be used as an index as well is just a bonus).
And I agree with Paul' comment - the concept of "heap" as a type of index is amazing. I didn't know whether to puke or laugh or cry when I saw it proposed.
Tom
July 22, 2010 at 1:50 pm
Uh oh, bad blood starting to break out here . . .
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
July 22, 2010 at 1:50 pm
Wayne
Is it sufficient to base the answer off of BOL?
As one who has gotten mountains of grief from having QODs published, the mountain of grief you would have gotten if you had NOT cited BOL would make what you have gotten today to look like an ANT HILL.
July 22, 2010 at 1:50 pm
Grant Fritchey (7/22/2010)
WayneS (7/22/2010)
How does one go about creating a connect item? I think I've got about a dozen to submit on indexes.....It's not hard. Go to connect.microsoft.com and follow the prompts.
For those so inclined, the first one is up at https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexes
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 22, 2010 at 1:56 pm
WayneS (7/22/2010)
For those so inclined, the first one is up at https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexes
You have my up-vote 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 2:09 pm
Paul White NZ (7/22/2010)
WayneS (7/22/2010)
spatial (and all other indexes except for full-text) is a B-+ tree)This is becoming the discussion from your original QotD again.
I'm sure I checked at the time and 2008 full-text indexes were just as B+ tree (with a leaf-level doubly-linked list) as any other index in SQL Server. I'd be absolutely amazed if full-text indexes were simple B-trees.
But if all indexes are B-+Trees (re: Paul Randal statement that Wayne brought up), then wouldn't it make sense to say they are based on the same structure?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 22, 2010 at 2:15 pm
Jack Corbett (7/22/2010)
Chad Crawford (7/22/2010)
WayneS (7/22/2010)
<snip>
I guess I'll just stick to writing articles...
</snip>
Please don't.
For every whiner there are 100 people* who are glad to have a QOD to look forward to every day.
I got it wrong, and I'm not whining. I'm just glad it was there. THANK YOU.
Chad
*Did you know that 87.5% of all statistics are made up on the spot? I have a reference for that somewhere...
I agree with Chad's sentiment.
I echo that sentiment. It is nice to have the QOD and people need to submit something.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 22, 2010 at 2:18 pm
Paul White NZ (7/22/2010)
WayneS (7/22/2010)
For those so inclined, the first one is up at https://connect.microsoft.com/SQLServer/feedback/details/577460/documentation-types-of-indexesYou have my up-vote 🙂
And another up-vote.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 16,651 through 16,665 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply