Using Synonyms to Decouple

Synonyms were first introduced in SQL 2005 and prove to be a great feature so far. What they basically do is decouple the database you are working in from other databases or objects you reference. This helps because it allows you to move objects (tables, procedures) to other locations without having to change the existing objects that reference them.

I used this recently in a project where I implemented a new system within an existing system. From a purist standpoint, the system should be on it’s own, but since there was no time to create subscriptions, we implemented it where most of the data was. While I did not like this method, I still to reference the data that should be external via synonyms.

To create a synonym, you can either use the dialog, or code. The dialog is pretty intuitive. Synonyms are created within a database, so expand the database and right click on Synonyms.

Once you do that, a dialog pops up:

Synonym Name	Akin to the table or procedure name
Synonym Schema	Akin to the schema
Server Name	Server to reference, blank for current
Database name	Database to reference
Schema	external schema to reference
Object Type	[Drop down values]
Object Name	[Drop down values]

And the code to create a synonym is:

[cc lang=”sql”]
CREATE SYNONYM [Subs].[Employees]
FOR [ServerX].[AdventureWorks].[dbo].[Employee]
[/cc]

Reference it in your code the same way you do a table:
[cc lang=”sql”]
SELECT *
FROM Subs.Employees
[/cc]

*Intellisense doesn’t work with it as of this writing.

To see what synonyms are defined for the database, you can query the following system view:
[cc lang=”sql”]
SELECT *
FROM sys.synonyms
[/cc]