Synonyms for T-SQL

This topic provides reference information about the compatibility of synonyms between Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can understand the differences in how these database systems handle synonyms, which are alternative identifiers for database objects.

SQL Server Usage

Synonyms are database objects that server as alternative identifiers for other database objects. The referenced database object is called the base object and may reside in the same database, another database on the same instance, or a remote server.

Synonyms provide an abstraction layer to isolate client application code from changes to the name or location of the base object.

In SQL Server, synonyms are often used to simplify the use of four-part identifiers when accessing remote instances.

For example, table A resides on server A, and the client application accesses it directly. For scale out reasons, table A needs to be moved to server B to offload resource consumption on server A. Without synonyms, the client application code must be rewritten to access server B. Instead, you can create a synonym called table A and it will transparently redirect the calling application to Server B without any code changes.

You can create synonyms for the following objects:

Syntax

CREATE SYNONYM [ <Synonym Schema> ] . <Synonym Name>
FOR [ <Server Name> ] . [ <Database Name> ] . [ Schema Name> ] . <Object Name>

Examples

Create a synonym for a local object in a separate database.

CREATE TABLE DB1.Schema1.MyTable
(
    KeyColumn INT IDENTITY PRIMARY KEY,
    DataColumn VARCHAR(20) NOT NULL
);
USE DB2;
CREATE SYNONYM Schema2.MyTable
FOR DB1.Schema1.MyTable

Create a synonym for a remote object.

-- On ServerA
CREATE TABLE DB1.Schema1.MyTable
(
KeyColumn INT IDENTITY PRIMARY KEY,
DataColumn VARCHAR(20) NOT NULL
);

-- On Server B
USE DB2;
CREATE SYNONYM Schema2.MyTable
FOR ServerA.DB1.Schema1.MyTable;

For more information, see CREATE SYNONYM (Transact-SQL) in the SQL Server documentation.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) doesn’t support synonyms and there is no known generic workaround.

For accessing tables or views, a partial workaround is to use encapsulating views as an abstraction layer. Similarly, you can use functions or stored procedures that call other functions or stored procedures.

For more information, see Linked Servers, Views, User-Defined Functions, and Stored Procedures.