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…

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

Solarwinds

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.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
Next Post
SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8

Related Posts

6 Comments. Leave new

  • Anil Kumar Ravada
    June 3, 2015 9:34 pm

    Moreover, if you try to create a View with schemabinding, it would not allow you to create the database. You it mean “it would not allow you to DROP the database?”…Even if we try synonym for Test2.dbo.Table2, it would throw an error
    “Msg 2788, Level 16, State 1, Procedure CrossDBView, Line 6
    Synonyms are invalid in a schemabound object or a constraint expression.”

    Reply

Leave a Reply

Menu