SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more… This part shows why cross database queries are not allowed in an indexed view.

One of the requirements of Indexed View is that it has to be created ‘WITH SCHEMABINDING’. If the View is not created with that clause, it would not let you create an index on that View. Moreover, if you try to create a View with schemabinding, it would not allow you to create the database.

-- Create DB
USE MASTER
GO
CREATE DATABASE TEST1
CREATE DATABASE TEST2
GO
-- Table1
USE Test1
GO
CREATE TABLE TABLE1 (ID INT)
GO
USE Test2
GO
-- Table2
CREATE TABLE TABLE2 (ID INT)
GO
USE Test1
GO
-- Create View
CREATE VIEW CrossDBView
WITH SCHEMABINDING
AS
SELECT t1.ID AS t1id, t2.ID AS t2id
FROM Test1.dbo.Table1 t1
INNER JOIN Test2.dbo.Table2 t2 ON t1.ID = t2.ID
GO
/*
Error:
Msg 4512, Level 16, State 3, Procedure CrossDBView, Line 4
Cannot schema bind view 'CrossDBView' because name 'Test1.dbo.Table1' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
*/
-- Clean up
DROP VIEW CrossDBView
GO
USE MASTER
GO
DROP DATABASE TEST1
DROP DATABASE TEST2
GO

When you try to create the View, it would throw the following error:

Msg 4512, Level 16, State 3, Procedure CrossDBView, Line 4
Cannot schema bind view ‘CrossDBView’ because name ‘Test1.dbo.Table1’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

After looking at all these limitations, have you thought of other constraints that View has? If you know any other interesting limitations, please share them here through your comments.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.