A synonym is a database object that serves the following purposes:
Provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.
For example, consider the Employee table of the AdventureWorks2008R2 sample database, located on a server named Server1. To reference this table from another server, Server2, a client application would have to use the four-part name Server1.AdventureWorks.HumanResources.Employee. Also, if the location of the table were to change, for example, to another server, the client application would have to be modified to reflect that change.
To address both these issues, you can create a synonym, EmpTable, on Server2 for the Employee table on Server1. Now, the client application only has to use the single-part name, EmpTable, to reference the Employee table. Also, if the location of the Employee table changes, you will have to modify the synonym, EmpTable, to point to the new location of the Employee table. Because there is no ALTER SYNONYM statement, you first have to drop the synonym, EmpTable, and then re-create the synonym with the same name, but point the synonym to the new location ofEmployee.
A synonym belongs to a schema, and like other objects in a schema, the name of a synonym must be unique. You can create synonyms for the following database objects:
Assembly (CLR) stored procedure
Assembly (CLR) table-valued function
Assembly (CLR) scalar function
Assembly (CLR) aggregate functions
Extended stored procedure
SQL scalar function
SQL table-valued function
SQL inline-tabled-valued function
SQL stored procedure
1 Includes local and global temporary tables
Four-part names for function base objects are not supported.
A synonym cannot be the base object for another synonym, and a synonym cannot reference a user-defined aggregate function.
The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time. Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object. For example, consider a synonym, MyContacts, that references the Person.Person table in Adventure Works. If the Person table is dropped and replaced by a view named Person.Person, MyContacts now references the Person.Person view.
References to synonyms are not schema-bound. Therefore, a synonym can be dropped at any time. However, by dropping a synonym, you run the risk of leaving dangling references to the synonym that was dropped. These references will only be found at run time.
If you have a default schema that you do not own and want to create a synonym, you must qualify the synonym name with the name of a schema that you do own. For example, if you own a schema x, but y is your default schema and you use the CREATE SYNONYM statement, you must prefix the name of the synonym with the schema x, instead of naming the synonym by using a single-part name. For more information about how to create synonyms, see CREATE SYNONYM (Transact-SQL).