SQL Server Performance Forum – Threads Archive

Referring to tables in other databases via views

Stored procedures on our SQL Server often refer to tables in other databases. eg. create proc Proc1 as UPDATE OtherDatabase..Sales SET Flag = 1. We want to change things so that when a table in another database is referred to, it only happens in views and then the stored procedures refer to those views. The reason for doing is to make it easy to change database names and that is not what I am asking. What I need to know is whether doing this will affect performance. BEFORE:
create proc Proc1 as UPDATE OtherDatabase..Sales SET Flag = 1 AFTER:
create view Sales as SELECT ProductID, SalesQty, SalesAmt, Flag FROM OtherDatabase..Sales
create proc Proc1 as UPDATE Sales SET Flag = 1 My question is : will this affect performance ? An extra question is : would the stored procedure (that calls the local view that calls the table in the other database) be able to use the indexes in the table (that is in the other database) or would the view in between prevent that from happening. Any help would be greatly appreciated,
Craig

---

You can even create view in its parent database and then call the view in your databse.
so instead of this
create view Sales as SELECT ProductID, SalesQty, SalesAmt, Flag FROM OtherDatabase..Sales
create proc Proc1 as UPDATE Sales SET Flag = 1 you can even think of this
use OtherDatabase
go create view Sales as SELECT ProductID, SalesQty, SalesAmt, Flag FROM Sales use yourdatabse
go create proc Proc1 as UPDATE otherdatabse..Sales SET Flag = 1

---

Well, if you change those database names without changing the views, then you have a broken application. I know it’s not what you were asking, but it is begging for trouble.

---

]]>