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

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