View is not schema bound?

Question

I have a select query to retrieve data from tables. It is working fine, but when there's a condition to select some 3 values, it is not giving a result. Error message;

Query processor ran out of Internal resources

I looked through INDEX seems to work fine, then I created view with that select statement, but couldn't create an index. Error message;

View is not schema bound

Solution 1

In order to create an indexed view the view needs to be schema bound to the entities that it is a view over.

To make a view schema bound, simply specify simply use WITH SCHEMABINDING in the view CREATE / UPDATE query, for example:

CREATE VIEW MyView
WITH SCHEMABINDING 
AS
-- SELECT

See this link for more information on schema binding, or the MSDN page on the CREATE VIEW statement.

However from what you have said I don't think the indexed view will necessarily help you - the message "Query processor ran out of Internal resources" means that the query processor failed to produce an execution plan for your query, which I would guess only happens with extremely complex queries.

You should try to reduce the complexity of your query somehow.

Solution 2

I would guess that you are trying to create the index on the view instead of the underlying tables. if you truly need to index the view it must meet these criteria:

http://technet.microsoft.com/en-us/library/cc917715.aspx

Comments

[Neru-J]

Schema Binding only allows for two part names. This means that we can only schema bind within our database. This also means that if we wanted to use an Indexed View on the Cross Database View, we could not do that either.

[Andrew Hill]

that restriction makes lots of sense, as an index is roughly a local copy of pointers to the data. If the target is a remote server, the local server would have no idea if what the index points to changed between when the index was last updated, and when it tried to fetch the data over the network