Export (0) Print
Expand All
1 out of 4 rated this helpful - Rate this topic

Using Synonyms (Database Engine)

Updated: 14 April 2006

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.

ms190626.note(en-US,SQL.90).gifNote:
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

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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.