SQL Server Synonyms and Linked Servers

Wed, Oct 15, 2008

Tech Tips

Here is how you can create a synonym to a linked server in SQL Server 2005/2008:


CREATE SYNONYM LinkedTableA
FOR
ServerName.DBName.dbo.TableA


SELECT * FROM LinkedTableA

Some Background:
What are Synonyms?
They are a minor feature added in SQL Server 2005. They are used to create an alias (or easy to remember name) for a server object. Like a table for instance. Instead of using the 4 part naming scheme (ie: server.database.user.object) to reference a table, you can create a synonym for it. See example above.

When would you want to do this?
Well there are are few reasons. Besides having an easier name to use in your sql, synoymns are also a method to create a level of abstraction to your object model.

I had one case come up recently. I inherited a project that had stored procedures there were referencing table names from linked servers using the 4 part naming scheme. When it came time to push it to production, the linked database had to be updated also. This wasn’t taken into account (blame it on the original developers), so there were numerous hard coded references to linked table names.

The best fix was to use table synonyms for these linked servers. The synonyms are different on both the development, staging and production servers but they are never changed. Now when the stored procs are moved, there are not hard coded references to update.

Bookmark and Share
,

One Response to “SQL Server Synonyms and Linked Servers”

  1. what is a server Says:

    I loved as much as you’ll obtain performed proper here. The sketch is attractive, your authored material stylish. however, you command get got an nervousness over that you want be delivering the following. unwell unquestionably come more formerly once more since precisely the same just about a lot steadily inside case you protect this increase.