Search This Blog & Web

Sunday, February 13, 2011

SQL SERVER 2008 Indexed Views Limitations

I have recently faced a problem for creating Materialized (Indexed) View using Microsoft SQL SERVER 2008.
I have found some limitations for creating Indexed view.

1-  Cross Database  Query Not Allowed
You cannot create a SCHEMABINDING view calling a query from other database. You can see error in attached image


















2 - * Cannot be Used in Query of an Indexed View












3- Schema name must be mentioned before creating view
you can create a normal view without mention table schema if it is calling from same database. but while creating Indexed view you have to mention schema name before table name as shown in figure.














4 - All Views must be Schema bound if used in your Indexed View
If you are creating a view using another view as your From clause then you have to re-create your already created view as SCHEMABINDING view. As you can see in figure if you want to bind your CrossDbView2 as Indexed then you have to create CrossDBView as Indexed.


















5 - Indexed View cannot be Altered. You cannot drop a View if it is using in any other Indexed View.

I have found these limitations while creating Indexed view. If you have more information please share with me.

No comments: