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

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 (http://blog.SQLAuthority.com)

About these ads

5 thoughts on “SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7

  1. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 20 of 31 Journey to SQLAuthority

  2. Pingback: SQL SERVER – Indexed Views and Restrictions – Quiz – Puzzle – 7 of 31 « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s