"Craig HB" wrote:

> 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

"Abhishek Pandey" <AbhishekPan***@discussions.microsoft.com> wrote in message
news:EC8B9EC6-FA58-4EF2-9E0E-35DDC41DEEE7@microsoft.com...
> Hi Craig,
> As per your first question: after all you are doing a overhead of creating a
> view to access the table in the other db. But then as far as i have
> observered, its not a big difference. You sproc should perform equally well
> (or atleast quite close to it)
>
> Your extra Question: If there are indexes on the table, the query behind
> your view will use it. But then if you are going to work on view columns then
> i doubt if it will use the indexes of the table. If that is and issue,
> consider indexed view.
>
> Hopefully the above helps. Maybe someone else can elaborate more on your
> first question.
>
> Abhishek
>
> "Craig HB" wrote:
>
>> 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