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.

Create Synonym

Once you do that, a dialog pops up:

SQL 2008 Create Synonym Dialog

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]

6 comments
Travis 01 Jan 2012 at 1:06 pm

In SQL Server 2005/8 synonyms can be associated to remote tables (i.e. tables in a different instance – referenced via a 4-part name, which includes the link server name). Do you know whether SQL Server 2012 has any new functionality to associate a synonym directly to a Linked Server name ?

Derek Dieter 23 Jan 2012 at 10:14 am

I don’t know Travis. I’ll update you if I find out if it can. I believe I needed that functionality at one point myself.

Derek

Simon 14 Sep 2011 at 7:10 am

Noticed a couple of typos just reading your article –

..decouple the database you are working in from other databases or objects you *are* reference.
This helps because it allows you to *more* objects..

cheers, Simon

Derek Dieter 16 Sep 2011 at 12:14 pm

Thank you Simon.. I need to work on my editing… Which I think is directly related to my lack patience sometimes 🙂

Chris H 12 Oct 2010 at 12:31 am

Synonyms are not new to SQL Server 2008, they are already available in SQL Server 2005.

Derek Dieter 12 Oct 2010 at 11:26 am

Thanks Chris.. I updated the article.

Featured Articles

 Site Author

  • Thanks for visiting!
css.php