Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Schema binding across multiple databases Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 4:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 20, 2014 4:47 PM
Points: 11, Visits: 122
quote taken from http://www.sqlballs.com/2012/05/cross-database-views-and-schema-binding.html

An Indexed View is essentially a Materialized View. All of the data in the view instead of existing as a select statement is persisted to the physical disk. The way the data is read for an index view is quicker because you are performing a seek or a scan against one object that is dependent upon its base table.

Too much bad information on web. All starts with people not knowing what they are talking about.
Post #1340936
Posted Tuesday, November 20, 2012 11:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:58 PM
Points: 367, Visits: 614
Books online list the following requirements/limitations for indexed views:


The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.

The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.

The view must not reference any other views, only base tables.

All base tables referenced by the view must be in the same database as the view and have the same owner as the view.

The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.

User-defined functions referenced in the view must have been created with the SCHEMABINDING option.

Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.

All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports whether a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.


Note that the limitation of referencing tables in a different database is specifically mentioned.

My suggestion would be to create the indexed view in the database containing the tables. Then create a synonym in the database you originally wanted the view in and have it point to the new view in the other database.

This is probably the only workaround to your issue.

I do have a few questions for you about your plan to use an indexed view.

First, you realize that an indexed view creates a copy of the entire result set and does not necessarily query the involved tables for each query?

Is the data you wish to include in the indexed view highly transactional or fairly static? If highly transactional, you will probably see a performance hit rather than improvement due to the requirement for the system to update the result set with each data change.
Post #1387076
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse