Pressing the Red Button

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.