3

I've got a task where I need to migrate an existing database to a new server.

The database in question has stored procedures that query another database on the same server, this second database is not going to be moved.

I would love to use a synonym to point to the other database on the linked server as this will make it really easy to set up and maintain, however I want to know if the synonym is likely to cause performance problems with the execution plan, or if it's only the cross server querying that is the problem.

7

A synonym is just a pointer and gets simplified away prior to optimization. If you have this:

CREATE SYNONYM dbo.foo FOR OtherServer.db.dbo.blat;

Then these two will optimize the same, producing identical execution plans (both having a Remote Query operator that points at blat):

SELECT bar FROM dbo.foo;

SELECT bar FROM OtherServer.db.dbo.blat;

You can test this quite easily.

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.