SQL Server 2005 synonyms

SQL Server 2005’s synonyms allow you to give an alias to an already existing object.

There are two benefits to doing this:

1. You have a very long name for some tables, such as OrdersHistoryYear2007. Microsoft SQL Server Management Studio Express 2005 doesn’t have IntelliSense, so you need to remember this name and type it correctly. Management Studio 2008 is going to have IntelliSense, but still better to have a friendly name.

2. The primary benefit of a synonym is realized when you use them as a layer of abstraction between the underlying objects and the synonym. You can have the underlying table to be located on a different database and reference it as a local table. For example, you can have three different databases on the same server:

dbProject1

dbProject2

dbProject3

and

dbCommon

dbCommon would have common tables for all three projects, for example, zip codes or states. So, each dbProject would have synonym referencing tables in dbCommon.