Export (0) Print
Expand All

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.

NoteNote

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).

The following example creates a synonym that will be used in subsequent examples.

USE tempdb;
GO
CREATE SYNONYM MyAddressType
FOR AdventureWorks.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

Community Additions

ADD
Show:
© 2014 Microsoft