December 19, 2008 at 12:59 am
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).
December 19, 2008 at 1:12 am
I agree with Hugo.
I would rather call it a view-index if we are referring to the index itself.
But blame it on MS and their documentation / naming 😛
Best Regards,
Chris Büttner
December 19, 2008 at 3:00 am
Oh dear, another question where the Microsoft documentation triumphs over reality.
I have added some community content to http://msdn.microsoft.com/en-us/library/ms175049.aspx. If anyone wishes to add to it please do.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 19, 2008 at 3:30 am
EdVassie (12/19/2008)
Oh dear, another question where the Microsoft documentation triumphs over reality.I have added some community content to http://msdn.microsoft.com/en-us/library/ms175049.aspx. If anyone wishes to add to it please do.
I added my two cents to the community content, and filed a documentation bug as well.
Thanks for pointing this out. I missed it because I never even bothered to check BOL for this question... 🙂
December 19, 2008 at 6:57 am
Indexed views
An index on a view materializes (executes), the view and the result set is permanently stored in a unique clustered index in the same way a table with a clustered index is stored. Nonclustered indexes on the view can be added after the clustered index is created.
Sorry, I can't agree. Even though I guessed at the Indexed View portion of the answer, after reading the documentation I think the answer is correct. I don't see how you have given any proof that this is not an index.
December 19, 2008 at 7:31 am
Hi Scott,
As mentioned before, we assume that the documentation is bad.
But your opinon drew me in the other direction:
If the indexed view is indeed an index, then it should not be called "indexed view" but "view index" instead. In the end one of the two things is badly named. You may choose which.
Best Regards,
Chris Büttner
December 19, 2008 at 8:22 am
Hi folks,
Sorry, I can't agree with this. Just because it's listed in the documentation under a column labeled "index type" doesn't make it so. An indexed view is a view with a clustered index and, if you want, non-clustered indexes. An index with included columns isn't an index "type" either. It is an implementation of a non-clustered index. The type is non-clustered index, the "included columns" are an option available when creating a non-clustered index. I'd argue that the column should be more accurately labeled "index implementation options" rather than "index types".
I'd also argue that "unique" isn't a "type" of index either. It is an attribute of an index as is a "filtered" index.
If you select * from sys.indexes you'll see "is_unique" and "has_filter" are attributes.
If you enter:
select distinct type_desc from sys.indexes (for AdventureWorks2008)
I found heap, clustered, non-clustered, and XML as the only valid "types". It's interesting to note that "heap" always appears with an id of 0 and a name of null. It's also interesting to note that for two of those "heap" type indexes, there is a corresponding "non-clustered" index with the SAME object_id.
Before everybody responds that a heap object is a table without a clustered index, I know. I just find it interesting that it was implemented this way. I'm also wondering why the other two heap types don't have related objects.
"Beliefs" get in the way of learning.
December 19, 2008 at 8:29 am
I'd argue that a heap isn't an index either. I think an index has structure that is designed. A heap has no structure, it's determined by the users.
I'll admit that I went back and forth on whether an indexes view fits or not. We'll see what the documentation people say. Going on the reference it is, and if I'd left it as a non-answer, I'm sure I'd see complaints there.
Unique doesn't really fit either.
Spatial does, however.
December 19, 2008 at 8:32 am
Robert Frasca (12/19/2008)
I found heap, clustered, non-clustered, and XML as the only valid "types".
Heap, no. It doesn't have the b-tree structure, it's not an index.
Don't forget spatial indexes on 2008.
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 8:43 am
GilaMonster (12/19/2008)
Robert Frasca (12/19/2008)
I found heap, clustered, non-clustered, and XML as the only valid "types".Heap, no. It doesn't have the b-tree structure, it's not an index.
Don't forget spatial indexes on 2008.
I don't think a heap is an index "type" either. I just think it's interesting that the SQL Server team chose to implement heaps in this fashion. Seems like a bit of a kluge (sp?) from a data architecture point of view.
I wasn't arguing about spatial types or XML types for that matter as they appear to be valid types albeit with a limited scope, i.e. you have to have XML or Geometry data types to implement them. Unfortunately, I haven't seen an implementation of a spatial type index so I can't comment from personal knowledge but it's implementation appears to be similar to that of an XML type.
"Beliefs" get in the way of learning.
December 19, 2008 at 10:27 am
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 you Hugo, about the fact that indexed view is not a type of index. I was surprised when I got "Wrong Answer". I demand my points!!!! jeje
Regards
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
December 19, 2008 at 2:06 pm
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.
December 19, 2008 at 2:08 pm
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.
December 19, 2008 at 2:18 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.
I agree.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply