3

If I join tables from database A to a view in database B (same server), are the joins within the view in database B processed on database B before joining to the tables in the current database?

1

Views are inlined into the query plan. Optimization then proceeds not even knowing that there once was a view.

The execution algorithm in general is not directly dictated by the way the query is written.

The join order in particular is very indirectly related to the way you wrote the joins.

So the answer depends on your particular schema, data and query. Likely, the answer is that the way you write the joins has no or has little impact.

You can find out by comparing the actual execution plans.

  • Thanks USR, could you explain how to check which database the various elements of a query were processed on when looking at a query plan. – chris Sep 15 '15 at 7:39
  • 1
    Remote elements are in a remote query operator. – usr Sep 15 '15 at 7:56
  • I created 2 queries one with the view in place and one where I broke it out into the underlying SQL. The query plans are identical. Nothing was performed remotely but wouldn;t I only see that if the view was on a different server rather than in this case where the view is in a different database on the same server? – chris Sep 15 '15 at 9:48
  • If you are querying on the same server then there will never be remote query operators. Maybe I misunderstood you. – usr Sep 15 '15 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.