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:
SELECT |
INSERT |
UPDATE |
DELETE |
EXECUTE |
Sub-selects |
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.
Note
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:
GRANT |
DENY |
REVOKE |
|
Synonyms are not schema-bound and, therefore, cannot be referenced by the following schema-bound expression contexts:
CHECK constraints |
Computed columns |
Default expressions |
Rule expressions |
Schema-bound views |
Schema-bound functions |
For more information about schema-bound functions, see Creating User-Defined Functions (Database Engine).
Examples
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
See Also