Share via


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