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 6 (of 6 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

Creating indexed views

Cookies on SQLServerCentral