Problems creating a index on view

Question

Problems creating a index on view

Victor Sebastiani

SSC Veteran

Points: 251
More actions
December 29, 2004 at 6:47 am

#60584

Ok, I anybody can help me on this will be nice. I´m trying to create a index on a view in order to improve the performance of the querys that I'm running against this view, but this is the error message that I get when I try to create the index : "Cannot create index on view ´name of the view´because the view is not schema bound". Please someone that explain what is the mind of the message and how I can solve this situation. Thanks on advance. AH! and Happy New Year

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Have a look at BOL for "CREATE VIEW" -> "Creating an indexed view". The chapter about requirements for an indexed view should help you.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 2

You'd be better off to run the SQL from the view in Query Analyzer once with the Index Wizard to create indexes on the underlying tables of the view.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

Schema bound means the view is bound to the current schema of the underlying tables and you cannot change them. But I agree with jeff, to improve performance, be sure the underlying tables are properly indexed.

k_vasanthkumar Old Hand Points: 349 More actions · Answer 4

Hi Victor Sebastiani,

I think that while creating the index on the view u have not mentioned (with schemabinding) option.You can create a index on view only with the schema binding option.This would surely increase ur performance.

But if the table is regularly inserted/Updated with a huge set of data.I think it would be a better option to create an index on the underlying tables....

Regards,

K.Vasanth Kumar