SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Schema binding across multiple databases


Schema binding across multiple databases

Author
Message
Champagne Charly
Champagne Charly
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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.
jerry-621596
jerry-621596
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1303 Visits: 649
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.
max.shrimps
max.shrimps
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 3
I have a similar need.

The view points at a table (a table that stores daily currency exchange rates) in a different database (the master database that stores all shared data).


Replies in this thread are confusing.

In short, can you create a view WITH SCHEMABINDING to a table in a different database?
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71318 Visits: 40930
you can create a view, but Not with schemabinding.

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.


since a differnet database is referenced via a three part name, for example, and since there's no way to create true referential integrity accross multipel databases in SQL server, it's not possible with schemabinding.

max.shrimps (2/18/2015)

I have a similar need.

The view points at a table (a table that stores daily currency exchange rates) in a different database (the master database that stores all shared data).


Replies in this thread are confusing.

In short, can you create a view WITH SCHEMABINDING to a table in a different database?





Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search