December 19, 2008 at 2:19 pm
Steve Jones - Editor (12/19/2008)
We can argue about whether an indexed view should be a type of index or not, but right now MS is choosing to call it that. Which means when you were working with support or someone else, that would be a "type of index" you have on your data.I agree that it's sketchy, but I don't think this is an invalid question given the way the documentation is written.
Depends on which documentation you read. If you read the documentation about indexed views it never once refers to it as an "index type". Rather it says the thing that MAKES it an indexed view is a clustered index, which is an index type. Without a clustered index it's just a view. Should we start referring to views as query types? After all, it isn't a view without a query.
"Beliefs" get in the way of learning.
December 19, 2008 at 2:20 pm
Lynn Pettis (12/19/2008)
But if you read the documentation, SQL Server will use the index on an indexed view when querying the base table if it the optimizer determines that it would be beneficial for the query (Enterprise Edition). If that isn't "an index type", what is.
And SQL will use an index on a table when querying the table if the optimiser determines that it will be beneficial for the query.
An index on a view is a straightforward clustered or nonclustered index. There's no difference in architecture between then and the clustered/nonclustered indexes on tables.
A rose by any other name....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2008 at 2:24 pm
We'll have to agree to disagree on this one.
December 21, 2008 at 9:14 am
Ah yes, typology is always good for a donnybrook or two...
Well, I got it wrong, but I am going to go with Steve on this one. The issue as I see it, is not whether an Indexed View index is an index or not (I mean obviously an Indexed View has an index), but whether it is a separate type of index. Although I initially said "No", when I looked at the BOL article two things immediately became clear:
First, the article makes a good presentation that it is the Indexed View index itself that cause the Indexed View to be materialized and indeed that the Indexed View index actually creates the Indexed View. This would indeed make it a substantially different kind of index.
Secondly, however, the table of "Index Types" lists not just different "types" of indexes, but the types themselves are not mutually exclusive, indeed that are not even all the same "kind" of "type". Even more, they do not list every "type" for every "kind" of index that they include. For instance:
"Clustered" is a listed as a type. So is "Non-clustered". And so is "Unique". But Clustered and non-clustered enumerate an entirely separate (and independent) attribute of indexes (clustering) from the attribute indicated by Unique (uniqueness). Bothe clustered and non-clustered indexes can be either unique or non-unique. Thus it would be more precise to say that "Clustered" and "Non-clustered" were two types of index clustering. And that "Unique" was a type of index uniqueness.
However, the index type entries include not only different settings of different attributes as distinct "types", it also includes index "types" that index entirely different things in radically different ways with substantially different interfaces. So we find Full-Text indexes, XML indexes, and Spatial indexes and we might include Indexed View indexes in this category of "types".
Interestingly, we cannot really regard this table a complete either, because many of the attributes found here do not have every possible type listed. For instance, "Unique" is listed, but "Non-Unique" is not, but obviously if non-clustered is an index type, then so is non-unique. And missing from our list of different index interfaces/content "types" of Full-text, XML, Spatial, and Indexed View indexes would be "normal table data" indexes.
So by the loose transitive logic of natural language you could indeed call these different Types of indexes, but they are different kinds of index types. This could be called an example of "Category Error" because of the attempt to equate things from different categories ("kinds") as comparable things. The only question remains is as to whether the error is Microsoft's for presenting them this way or ours for assuming that that they were all the same kind of "type".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 9:28 am
Barry, nice note and a good explanation. We are definitely looking more at kinds and attributes here, despite the BOL title.
December 21, 2008 at 9:40 am
Thanks, Steve. Stuck at home alone with a bad cold here on Christmas Sunday, I am definitely waxing philosophical. 😎
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 1:47 pm
Hugo Kornelis (12/19/2008)
Sorry Steve, but I have to disagree with you on this one.Indexed views are not an index type. They are a special type of view, created by creating an index on them. (And incidentally, this index has to be a clustered one, though you can add other, nonclustered indexes to it later).
I agree with Hugo. The indexes on views (at least the first one) is a clustered index, not a different type of index.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 21, 2008 at 8:01 pm
RBarryYoung (12/21/2008)
Ah yes, typology is always good for a donnybrook or two...Well, I got it wrong, but I am going to go with Steve on this one. The issue as I see it, is not whether an Indexed View index is an index or not (I mean obviously an Indexed View has an index), but whether it is a separate type of index. Although I initially said "No", when I looked at the BOL article two things immediately became clear:
First, the article makes a good presentation that it is the Indexed View index itself that cause the Indexed View to be materialized and indeed that the Indexed View index actually creates the Indexed View. This would indeed make it a substantially different kind of index.
Secondly, however, the table of "Index Types" lists not just different "types" of indexes, but the types themselves are not mutually exclusive, indeed that are not even all the same "kind" of "type". Even more, they do not list every "type" for every "kind" of index that they include. For instance:
"Clustered" is a listed as a type. So is "Non-clustered". And so is "Unique". But Clustered and non-clustered enumerate an entirely separate (and independent) attribute of indexes (clustering) from the attribute indicated by Unique (uniqueness). Bothe clustered and non-clustered indexes can be either unique or non-unique. Thus it would be more precise to say that "Clustered" and "Non-clustered" were two types of index clustering. And that "Unique" was a type of index uniqueness.
However, the index type entries include not only different settings of different attributes as distinct "types", it also includes index "types" that index entirely different things in radically different ways with substantially different interfaces. So we find Full-Text indexes, XML indexes, and Spatial indexes and we might include Indexed View indexes in this category of "types".
Interestingly, we cannot really regard this table a complete either, because many of the attributes found here do not have every possible type listed. For instance, "Unique" is listed, but "Non-Unique" is not, but obviously if non-clustered is an index type, then so is non-unique. And missing from our list of different index interfaces/content "types" of Full-text, XML, Spatial, and Indexed View indexes would be "normal table data" indexes.
So by the loose transitive logic of natural language you could indeed call these different Types of indexes, but they are different kinds of index types. This could be called an example of "Category Error" because of the attempt to equate things from different categories ("kinds") as comparable things. The only question remains is as to whether the error is Microsoft's for presenting them this way or ours for assuming that that they were all the same kind of "type".
"different kinds of index types."
Different kinds of types. Now we're playing a little loose and fast with the Queen's English. 🙂 (They are synonyms.)
Is that like different flavors of tastes? Chuckle.
On the topic of the Queen's English, my complaint is with the way those items are categorized in BOL. I believe they are mixing types and properties of types in the same column. That being said, as Steve pointed out, if he said that indexed views weren't valid index types he would've had just as many complaints because "that isn't what BOL says".
I think it's safe to say that we have officially finished beating this dead horse and I have to go and dig out of another 15 inches of snow. Have a good week everyone.
"Beliefs" get in the way of learning.
December 21, 2008 at 8:59 pm
Robert Frasca (12/21/2008)
Different kinds of types. Now we're playing a little loose and fast with the Queen's English. 🙂 (They are synonyms.)...
I believe they are mixing types and properties of types in the same column.
Um, I am sorry to tell you this, but "property" and "type" are synonymous concepts also. They just reverse the roles of their subjects and objects.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 11:31 pm
I agree with the point that Microsoft documentation is confusing. Even in .NET framework documentation I found they lack consistency. I started wondering what happen to them. 🙂
Barry your explanation is great.
December 22, 2008 at 7:45 am
Thanks for the feedback, Anirban. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 22, 2008 at 8:34 am
RBarryYoung (12/21/2008)
Robert Frasca (12/21/2008)
Different kinds of types. Now we're playing a little loose and fast with the Queen's English. 🙂 (They are synonyms.)...
I believe they are mixing types and properties of types in the same column.
Um, I am sorry to tell you this, but "property" and "type" are synonymous concepts also. They just reverse the roles of their subjects and objects.
Continuing our discussion on English...
Property and type are not synonyms in any thesaurus that I can find. Type and category are synonyms. Property and attribute are synonyms. However, the two pairs of synonyms are not synonymous themselves.
As I'm sure you're aware, in relational theory the "columns" of a table are referred to as attributes of a relation. In other words, is_unique is an attribute of the view sys.indexes so a unique index is not a "type" of index. It is an attribute of various "types" of indexes. However, to be fair, in this context, index type is also an attribute of sys.indexes so I could see why you might think that type and property are synonymous. Where that argument breaks down is that sys.indexes is a view but type is almost certainly a foreign key rather than an attribute. Unfortunately, I can't see the query that defines the sys.indexes view but I'm confident that the underlying tables are normalized, i.e. somewhere there's a table called IndexTypes with a primary key of type and an attribute of type_desc.
Why am I arguing about this? :hehe: I think it's important to identify things properly or misunderstandings can occur. That is, after all, why we define taxonomies. To refer to an indexed view as an index type is to call an apple an orange. Actually, even that's a bad analogy because apples and oranges are both fruits. Indexed views and index types aren't related in any way since an indexed view represents the implementation of a view and an index type. A view has no common relationship with an index type. It's like a nut and a bolt. They are singularly identifiable objects that have no relationship until they are assembled, i.e. implemented. It is fundamentally incorrect to refer to an indexed view as an index type no matter how you slice it or, in this case, document it.
"Beliefs" get in the way of learning.
December 22, 2008 at 9:17 am
Robert Frasca (12/21/2008)
On the topic of the Queen's English, my complaint is with the way those items are categorized in BOL.
On this note, it should be known that Books Online isn't written in Queen's English but American English (and then translated into the numerous other languages that Microsoft supports).
December 22, 2008 at 9:29 am
Lynn Pettis (12/22/2008)
Robert Frasca (12/21/2008)
On the topic of the Queen's English, my complaint is with the way those items are categorized in BOL.On this note, it should be known that Books Online isn't written in Queen's English but American English (and then translated into the numerous other languages that Microsoft supports).
Excellent point. So, does the word synonym mean something different in the Queen's English versus American English?
😉
Bob Frasca
"Beliefs" get in the way of learning.
December 22, 2008 at 9:42 am
I'm not sure it's even good English, and it's certainly not consistent.
I would agree it is important that we understand what we are talking about and use consistent terms. It's one reason I hate having so many acronyms that are re-used. DAC is being reusdd in SQL 11 as something other than the Dedicated Administrator Connection. How will we talk about that?
My point would be that BOL is our source of reference. Since it lists these are "types" of indexes, if we are to discuss this, we can't have people randomly determining when they think BOL is wrong and expecting everyone else to follow that logic. Some people would see these as types, some won't. So until BOL is changed, I would use that as the source of reference. You can comment and submit feedback to them, and add a note that you don't agree, but you can't use your own terminology outside of the only reference we have.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply