Using Synonyms (Database Engine)
You can use synonyms in place of their referenced base object in several SQL statements and expression contexts. The following table contains a list of these statements and expression contexts:
When you are working with synonyms in the contexts previously stated, the base object is affected. For example, if a synonym references a base object that is a table and you insert a row into the synonym, you are actually inserting a row into the referenced table.
You cannot reference a synonym that is located on a linked server.
You can use a synonym as the parameter for the OBJECT_ID function; however, the function returns the object ID of the synonym, not the base object.
You cannot reference a synonym in a DDL statement. For example, the following statements, which reference a synonym named dbo.MyProduct, generate errors:
ALTER TABLE dbo.MyProduct ADD NewFlag int null; EXEC ('ALTER TABLE dbo.MyProduct ADD NewFlag int null');
The following permission statements are associated only with the synonym and not the base object:
Synonyms are not schema-bound and, therefore, cannot be referenced by the following schema-bound expression contexts:
For more information about schema-bound functions, see Creating User-Defined Functions (Database Engine).
The following example creates a synonym that will be used in subsequent examples.
USE tempdb; GO CREATE SYNONYM MyAddressType FOR AdventureWorks2008R2.Person.AddressType; GO
The following example inserts a row into the base table that is referenced by the MyAddressType synonym.
USE tempdb; GO INSERT INTO MyAddressType (Name) VALUES ('Test'); GO
The following example demonstrates how a synonym can be referenced in dynamic SQL.
USE tempdb; GO EXECUTE ('SELECT Name FROM MyAddressType'); GO