Indexed View

Question

Indexed View

rafael_si

Ten Centuries

Points: 1059
More actions
May 20, 2010 at 7:11 am

#144921

Hi folks,
I have a doubt. When I create a view "WITH SCHEMABINDING" referencing a table which has already an cluster index, should I need to create an index in my view too? or the view uses the index from that table?
p.s: the index which the table has is the index I need in my view...
Regards,
Rafael.

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

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

It depends on the query and your goal. SCHEMA_BINDING and indexed views are different critters. An indexed view is, effectively, a second table because the query that defines it is used to create a clustered index which stores the data retrieved by that query. Then queries that can use the indexed view refer only to that index. A view is just a query. It has to go to the underlying tables that define it to retrieve data. Depending on that query and the queries against it, it will use appropriate indexes on the tables, where possible.

SCHEMABINDING makes it so that base tables can't be modified in order to protect the select statement within the view. It has nothing to do with index use or non-use.

"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

rafael_si Ten Centuries Points: 1059 More actions · Answer 2

Thanks a lot for you reply. I was thinking that "schemabinding" had something to do with index use becase I can't create an cluster index (sql server 2005) without declaring the clause schemabinding.

Regards,

Rafael Melo - BR

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

rafael_si (5/20/2010)
Thanks a lot for you reply. I was thinking that "schemabinding" had something to do with index use becase I can't create an cluster index (sql server 2005) without declaring the clause schemabinding.
Regards,
Rafael Melo - BR

Yes, yes, of course you're right. Sorry, I wasn't thinking about the needs for creating the indexed view. It doesn't allow the underlying structures to change, for obvious reasons. But that has nothing to do with the use of indexes, as I stated. Sorry I wasn't clear.

"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

rafael_si Ten Centuries Points: 1059 More actions · Answer 4

Thanks anyway Grant Fritchey, I was really in doubt about "schemabinding" concept. You make it me clear.

Regards,

Rafael - BR