Indexed views and Indexes

Question

Indexed views and Indexes

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
July 14, 2009 at 1:40 pm

#208127

Hello All,
Tables may have have Indexes (Clustered and Non Clustred).
sp_help ' Do the Indexes created for "Tables" take any seperate
space?
Question :2 --> Views donot physically store any space as they get the data from physical tables. What about the Indexed views? Do they also refer to the "Indexes of that table" or the Indexes viewes are created seperately only for the Views?
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

The answer to both of your questions is yes. Indexes take up space. You can even create a seperate filegroup and move them if you want. With an Indexed View, the data for the view is actually materialized and stored on disk. That is why you should not have Indexed Views if the data changes a lot because it will take a lot of resources to keep the data up to date.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

Thanks Ten for your response. I had one more query to you ..

1. While creating the "Indexed View", do the index available as a seperate entity or does this refer to the "Table Index".

Actually, if you see the View gets the data from Table and doesn;t additionally store the data. Therefore is it something in "Index Views" also the indexes of the views are referring to Table indexes? Or created and stored seperately?

Regards

Sourav

Thanks.

DCPeterson SSCoach Points: 15266 More actions · Answer 3

The indexes created on a view are completely separate from the indexes on any of the base tables the view queries. The first index that MUST be created on a view is a unique clustered index and any subsequent nonclustered indexes on the view will be tied to the clustered index of the view, again having nothing to do with any indexes on the base table(s).

I wish to clear up a pretty common misconception that indexes on views are somehow "more expensive" to maintain than indexes on the base tables, this is not the case in my testing and there's really no reason that they would be. Any index costs something both in disk space and overhead to maintain. This cost is dependent on the definition of the index and the properties of the data, but is not related to whether the index is against a view or a base table.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/