http://www.sqlservercentral.com/blogs/sqlballs/2012/05/31/cross-database-views-and-schema-binding/ Printed 2013/05/25 09:03PM
Cross Database Views and Schema Binding
One of the most interesting things I've found as a DBA is learning about features in other RDBMS platforms. Sure we all work with and love SQL Server, but quite often there are features that you hear about that you learn about that make you go, "Wow, wouldn't that be cool!". Sybase's ability to have multiple tempdb's, create a tempdb for a particular database to use, or create a tempdb for a specific user login come to mind. Oracles ability to make a metadata copy of a base table without causing locking or blocking on the original is another. But as cool as these features are sometimes they create confusion when working with the business. Often times you need to understand the internals of how SQL Server works to explain why a particular feature for another RDBMS platform doesn't work in SQL.
CROSS DATABASE VIEWS & SCHEMA BINDING
One I ran into recently was Cross Database views that allow update's and inserts on the base tables, (I'm not a Sybase guy so to my Sybase friends please feel free to correct me if I'm wrong). I was working on creating a distributed topology for two systems that are currently intertwined, that for performance reasons we are tying to separate. Part of the plan that was proposed was to have a number of cross database views that would allow us to avoid code changes in an application for this first phase of the project.
So when we started to discuss the actions that would take place against these views, very quickly it was discovered that we wanted to have inserts and updates used against these views. In SQL Server in order to update a base table from a View that View must be created specifying WITH SCHEMABINDING. The problem with the request is that the base table and the View are in two different databases. In SQL this doesn't work, in Sybase (which we are transitioning off of) it does.
I worked up this demo to show my friends this limitation for Views and just wanted to pass it along to you Dear Reader.
First Let's Create our Database that will hold our base table IF EXISTS(SELECT name FROM sys.databases WHERE name='test1') Now let's create our base table IF EXISTS(SELECT name FROM sys.tables WHERE name='myTable1') ,mychar CHAR(500) NOT NULL DEFAULT 'a' ,CONSTRAINT pk_myid_1 PRIMARY KEY CLUSTERED(myID) Now let's create our second database that will hold our view pointing to the base table, dbo.myTable1, in our IF EXISTS(SELECT name FROM sys.databases WHERE name='test2') Now let's create our view IF EXISTS(SELECT name FROM sys.objects WHERE name='v_myTable1')
Our Regular View is created successfully and we can do a select from it and see that data is returned successfully
Our view returns just fine. And if the business only wanted to perform read operations against the view, this would have met our requirements just fine. However we need to create a view that allows updates and inserts.
In order to make a view that can recieve inserts and updates we need to re-create our view and specify WITH SCHEMABINDING (This will fail in a cross database view) IF EXISTS(SELECT name FROM sys.objects WHERE name='v_myTable1')
When you run this statement it fails with the following error.
Msg 4512, Level 16, State 3, Procedure v_myTable1, Line 4 Cannot schema bind view 'v_myTable1' because name 'test1.dbo.myTable1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
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.
If you think about it, a View is just a select statement standing by waiting to be executed. When you allow Schema Binding you allow that view to be a pass through to the base table. This means you would need to give one database ownership of objects within another database. This is not how SQL Server works currently. Objects are allocated within a database, in SQL 2012 this is taken a step further with Contained Database.
When looking at Indexed Views it becomes even clearer. 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.
You couldn’t have a Materialized View dependent upon Base tables within another database. If a database when offline or they entered redo and recovery at different points you could potentially have transactions that were at different states within different databases, slight chance but still the implications are head-ache inducing.
So the long and short of it, you cannot do a cross database view using Schema Binding in SQL Server.
Thanks again for stopping by.