Share via

Using Synonyms (Database Engine)

  • Article

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

See Also

Concepts

Synonyms (Database Engine)
Ownership Chains

Other Resources

CREATE SYNONYM (Transact-SQL)
DROP SYNONYM (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added information about using synonyms with the OBJECT_ID function.
Changed content:
  • Removed DDL from the list of statements that are valid for use with synonyms.