Overview

• Synonym is a database object that serves the following purposes:
  • Provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
  • Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

USE tempdb;
GO

-- Create a synonym for the Product table in AdventureWorks on tempdb database.
CREATE SYNONYM [MyProduct]
FOR [AdventureWorks].[Production].[Product];

-- Query the Product table by using the synonym.
USE tempdb;

SELECT [ProductId], [Name]
FROM [MyProduct]
WHERE [ProductId] < 5;

Remarks

• A synonym cannot be the base object for another synonym, and a synonym cannot reference a user-defined aggregate function.
• The binding between a synonym and its base object is by name only.
  • All existence, type, and permissions checking on the base object is deferred until run time.
  • Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object.
• References to synonyms are not schema-bound. Therefore, a synonym can be dropped at any time.
  • However, by dropping a synonym, you run the risk of leaving dangling references to the synonym that was dropped.
  • These references will only be found at run time.
• If you have a default schema that you do not own and want to create a synonym, you must qualify the synonym name with the name of a schema that you do own.
  • For example, if you own a schema x, but y is your default schema and you use the CREATE SYNONYM statement, you must prefix the name of the synonym with the schema x, instead of naming the synonym by using a single-part name.
• 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.