Creating indexed views

Question

Creating indexed views

pat-261672

Hall of Fame

Points: 3325
More actions
January 18, 2009 at 11:57 am

#129481

Hi all,
I am trying to create index views but i get error as follow:
Remote access is not allowed from within a schema-bound object.
Why.. I am tryingto do follow:
create view v1 with schemabinding as
select t1.id,t1.name1, t2.name2
from table1 t1 join serverrname.dbname.dbo.table2 t2 on t2.id = t1.id
go
create unique clustered index idx on v1(id)
go

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

As the error says, you're not allowed to access a remove server in an object that's created WITH SCHEMABINDING.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pat-261672 Hall of Fame Points: 3325 More actions · Answer 2

I can access the same table with select from query but when i try to create indexed view it gives me error for remote server.

but with simple views it works fine!!

thanks

pat

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

A normal query isn't schema bound.

A simple view is not schema bound.

Both of those will run fine when referencing another server.

The restriction is that you cannot reference a remote server ina view that's created WITH SCHEMABINDING. Simple as that. There's a good reason. An indexed view is materialised and it's data stored as if it was a table. Whenever any of the base tables change, that indexed view has to be updated. The mechanisms used for that are the same ones that update the nonclustered indexes when a table change, and can only work within the same instance of SQL.

Bottom line. You cannot make an indexed view that references another server. Period.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pat-261672 Hall of Fame Points: 3325 More actions · Answer 4

ok,

Now i understand. but i changed the view now useing this

creaet view v1 as schemabinding as

select t1.name1, t2.name2

from

bdname.dbo.table2 t2 join dbo.table1 t1 on t1.id = t2.id

and i get this error. Now i changed the db on same server

Cannot schema bind view 'v1' because name 'dbname.dbo.table2 is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

pat (1/18/2009)
Names must be in two-part format.

As the error says. 3-part naming is also not allowed. Hence, only objects within the same database.

Edit: There's a long list of restrictions on indexed views in Books Online. I would suggest you read through it carefully, as well as the restrictions on Schema binding. The list of restrictions on what views can be indexes is somewhere around 2 pages long

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass