Indexing a View - Not Bound to Schema?

[JayKusch]

Morning Folk,

Trying to index a view but am recieving an error message that reads: Server: Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'vw_Invoice_Info' because the view is not schema bound. How do i go about binding the view to the schema in order to apply the index?

Thanks!!!


Thanks

J. Kusch

 

[JayKusch]

Found the Answer. Thanks!
Thanks

J. Kusch

 

[tlbroadbent]

Jay,

When you find an answer it is helpful to other members if you post it here. Here is my take on the question and answer. This applies to SQL 2000 only.

In order to create an index on a view, the view must be compiled with the SCHEMABINDING option. Using this option binds the view to the schema. (seems redundant!) When a view is bound to the schema, the underlying table cannot be dropped or altered if the alteration would affect the view.

The binding is required because dropping or changing the underlying table would invalidate the view indexes which are physical objects while the view itself is a virtual object. Imagine the problems that would arise if column data types or lengths were altered. SQL may attempt to use the index on the view and perform extremely poorly or return incorrect results. SQL Server may no longer be able to automatically update the indexes on a view.

Without schema binding, if a table was dropped the view indexes could remain. The view's definition doesn't occupy much space but the indxes could use a lot of space. All of the used spoace would be wasted. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[JayKusch]

Thanks Terry ... was planning on relaying my findings once the fire died down here. Just did not want folks to expend their thoughts on an issue I had already tackled.

You are correct in your explanation above. Could you further expound upon some of the caveats i have found in dealing w/ this "not yet" indexed view.

I have two tables that are causing me some issues. One table is in another DB, "Sales", on the same server but NOT in the same DB as the view is being created in.

The other problem table is on another Server/DB combination all together, "Server2.Mall.[dbo].Merchants".

When I apply the SchemaBinding option to the view, it produces the error:

Error 4512: Cannot schema bind view ‘ViewName’ because name 'Server2.Mall.[dbo].Merchants' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself..

The two-part format also causes problems with the Sales DB table I mentioned above since it is referenced as 'Sales.[dbo].reps'.

Any ideas as to a work-around ???

Thanks for all the great info and guidance.

Thanks

J. Kusch

 

[tlbroadbent]

Each database has its own schema. A view can only be bound to schema of the database where the view exists. SQL Server enforces that rule by forcing two-part naming. You can't even name another database or server.

The only work around that I know is thorough analysis of the views. Analyze the query execution plans and indexes. Make sure the query is as efficient as possible. The REMOTE JOIN hint may be helpful. It all depends on the type of join and other factors. See "Join Hints | REMOTE" in the topic "From" in SQL BOL or at the MSDN website.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_fa-fz_4ox9.asp

There are numerous other restrictions when trying to create indexed views. SQL BOL lists the restrictions. See the paragraph "Restrictions on indexed views" under the topic "Create Index" in SQL BOL or at the MSDN website.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_64l4.asp

We haven't created many indexed views in our databases because of the restictions. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.