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
 |
| http://www.flickr.com/photos/incrediblehow/5714219510/ |
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')
BEGIN
DROP DATABASE
test1
END
CREATE DATABASE test1
/*
Now let's create
our base table
*/
USE test1
GO
IF EXISTS(SELECT name FROM sys.tables WHERE name='myTable1')
BEGIN
DROP TABLE
dbo.myTable1
END
CREATE TABLE myTable1(
myID INT IDENTITY(1,1)
,mychar CHAR(500) NOT NULL DEFAULT 'a'
,CONSTRAINT pk_myid_1 PRIMARY KEY CLUSTERED(myID)
)
/*
Let's insert
some rows
into our base
table
*/
DECLARE @i INT
SET @i=0
WHILE (@i<15000)
BEGIN
INSERT INTO
dbo.mytable1
DEFAULT VALUES
SET @i=@i+1
END
/*
Now let's create
our second database
that will hold
our view pointing to
the base table,
dbo.myTable1, in our
test1 Database
*/
IF EXISTS(SELECT name FROM sys.databases WHERE name='test2')
BEGIN
DROP DATABASE
test2
END
CREATE DATABASE test2
/*
Now let's create
our view
*/
USE test2
GO
IF EXISTS(SELECT name FROM sys.objects WHERE name='v_myTable1')
BEGIN
DROP VIEW
dbo.v_myTable1
END
GO
CREATE VIEW v_myTable1
AS
SELECT
myid
,mychar
FROM
test1.dbo.myTable1
GO
/*
Our Regular View
is created successfully
and we can do a
select from it and see
that data is
returned successfully
*/
SELECT
*
FROM
dbo.v_myTable1
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)
*/
USE test2
GO
IF EXISTS(SELECT name FROM sys.objects WHERE name='v_myTable1')
BEGIN
DROP VIEW
dbo.v_myTable1
END
GO
CREATE VIEW v_myTable1
WITH SCHEMABINDING
AS
SELECT
myid
,mychar
FROM
test1.dbo.myTable1
GO
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.
IT ALL MAKES SENSE
 |
| http://www.flickr.com/photos/dach_art/7126010381/ |
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.
Thanks,
Brad
