Learn SQL Synonyms: A Comprehensive Guide for Database Abstraction

Synonyms in SQL Server are database objects that act as alternative names for other database objects. These base objects can reside on either a local or a remote server. Essentially, a SQL synonym provides you with a different way to refer to a database object, offering a layer of abstraction that can be incredibly beneficial. This abstraction shields client applications from the impact of changes to the base object’s name or location. Understanding synonyms is a key step in mastering database management and ensuring application stability.

Let’s illustrate this with an example. Imagine you have an Employee table within the Adventure Works database, located on a server named Server1. To access this table from another server, say Server2, a client application would typically need to use the verbose four-part naming convention: Server1.AdventureWorks.Person.Employee. Furthermore, if the Employee table were to be moved to a different server, any client application referencing it would require modification to reflect this new location.

To simplify this and enhance flexibility, you can create a synonym. On Server2, within a dedicated schema like RemoteObjects, you could create a synonym named EmpTable for the Employee table residing on Server1. Now, client applications on Server2 can reference the Employee table on Server1 simply by using the two-part name: RemoteObjects.EmpTable. The beauty of this approach becomes apparent when the base object’s location changes. If the Employee table is moved, you only need to update the synonym EmpTable to point to the table’s new location. Since there isn’t an ALTER SYNONYM statement, this update involves dropping the existing synonym RemoteObjects.EmpTable and then recreating it with the same name, but directing it to the Employee table’s new location. This significantly reduces the application changes needed, showcasing the power of abstraction that synonyms provide.

Types of Objects Compatible with Synonyms

A synonym is schema-bound and, like other schema objects, must have a unique name within its schema. You can create synonyms for a wide range of database objects, including:

Programmability Objects:
- Assembly (CLR) stored procedures
- Assembly (CLR) scalar functions
- Replication-filter-procedures
- SQL scalar functions
- SQL inline-tabled-valued functions
- SQL stored procedures
- Assembly (CLR) table-valued functions
- Assembly (CLR) aggregate functions
- SQL table-valued functions
Data Storage Objects:
- Tables* (User-defined, including local and global temporary tables)
- Views
* Note: Synonyms can be created for both local and global temporary tables.

It’s important to note that four-part names are not supported for function base objects. Also, a synonym cannot serve as the base object for another synonym, and you cannot create a synonym that references a user-defined aggregate function.

Understanding Synonym Binding

The crucial aspect of synonym binding is that it’s based purely on the name of the base object. The system doesn’t verify the existence, type, or permissions of the base object until runtime. This deferred checking has important implications. It means that the base object can be altered, dropped, or even replaced by a different object with the same name without immediately affecting the synonym definition.

Consider a scenario where you have a synonym named dbo.MyContacts that points to the Person.Contact table in Adventure Works. If, for instance, the Person.Contact table is dropped and replaced by a view that is also named Person.Contact, the synonym dbo.MyContacts will now seamlessly reference the Person.Contact view. This dynamic binding provides flexibility but also necessitates careful management to avoid unexpected behavior if base objects are changed.

References to synonyms are not schema-bound, which offers the advantage of dropping a synonym at any time. However, dropping a synonym can lead to “dangling references”—references to the now-nonexistent synonym within your code. These dangling references will only surface at runtime, potentially causing errors in your applications. Therefore, while synonyms offer abstraction, it’s essential to manage them diligently to maintain database integrity.

Synonyms and Schema Considerations

When creating synonyms, schema ownership plays a crucial role. If you are working within a default schema that you do not own, and you wish to create a synonym, you must explicitly qualify the synonym name with a schema name that you do own.

For example, if your schema is S1, but your default schema in the current context is S2, and you execute a CREATE SYNONYM statement, you must prefix the synonym name with S1. This ensures that the synonym is created within your owned schema (S1) rather than in the default schema (S2). For detailed instructions on synonym creation, refer to the official CREATE SYNONYM (Transact-SQL) documentation.

Managing Permissions on Synonyms

Granting permissions on synonyms is restricted to synonym owners, members of the db_owner database role, or members of the db_ddladmin database role. These authorized users can control access to synonyms using the GRANT, DENY, and REVOKE permissions.

The permissions applicable to synonyms include:

CONTROL: Provides full control over the synonym.
EXECUTE: Allows executing the synonym if it refers to a procedure or function.
SELECT: Permits selecting data through the synonym if it refers to a table or view.
UPDATE: Allows updating data via the synonym for tables or views.
DELETE: Permits deleting data through the synonym for tables or views.
INSERT: Allows inserting data via the synonym for tables or views.
TAKE OWNERSHIP: Enables taking ownership of the synonym.
VIEW DEFINITION: Allows viewing the definition of the synonym.

These permissions are managed independently for the synonym and do not automatically cascade to the base object. This granular control allows for precise security management when using synonyms.

Utilizing Synonyms in SQL Statements

Synonyms can be used interchangeably with their base objects in various SQL statements and expression contexts, simplifying your SQL code and enhancing readability. Synonyms are supported in the following statements and contexts:

SELECT
UPDATE
EXECUTE
INSERT
DELETE
Subqueries

When you use a synonym in these contexts, the action is directly applied to the underlying base object. For instance, inserting a row into a synonym that points to a table will result in a row being inserted into the actual base table.

It is important to note that you cannot directly reference a synonym that is located on a linked server. Synonym usage is confined to the local server and its directly accessible objects.

Interestingly, you can use a synonym as a parameter for the OBJECT_ID function. However, it’s crucial to understand that OBJECT_ID will return the object ID of the synonym itself, not the object ID of the base object it references.

Conversely, synonyms cannot be used in Data Definition Language (DDL) statements. For example, attempting to alter a table using a synonym like dbo.MyProduct will result in errors, as demonstrated by these statements:

ALTER TABLE dbo.MyProduct ADD NewFlag int null;
EXEC ('ALTER TABLE dbo.MyProduct ADD NewFlag int null');

Similarly, permission-related statements such as GRANT, REVOKE, and DENY are associated solely with the synonym and not propagated to the base object.

Because synonyms are not schema-bound, they cannot be referenced in schema-bound expression contexts. This limitation applies to:

CHECK constraints
Default expressions
Schema-bound views
Computed columns
Rule expressions
Schema-bound functions

For a deeper dive into schema-bound functions, consult the Create User-defined Functions (Database Engine) documentation.

Retrieving Information About Synonyms

To gather information about synonyms, the sys.synonyms catalog view is invaluable. This view provides an entry for every synonym within a database, exposing key metadata like the synonym’s name and the name of its base object. For more details, refer to sys.synonyms (Transact-SQL).

Extended properties offer another way to enrich synonyms with descriptive or instructional text, input masks, and formatting rules. These properties, stored directly in the database, allow applications to consistently interpret and utilize the synonym. Learn more about adding extended properties via sp_addextendedproperty (Transact-SQL).

To determine the base type of a synonym’s base object, the OBJECTPROPERTYEX function is the tool to use. Detailed information can be found in the OBJECTPROPERTYEX (Transact-SQL) documentation.

Examples Demonstrating Synonym Base Type Retrieval

The following examples illustrate how to retrieve the base type of a synonym’s base object.

Example 1: Local Object

This example demonstrates retrieving the base type for a synonym referencing a local object.

USE tempdb;
GO
CREATE SCHEMA SynSchema
GO
CREATE SYNONYM SynSchema.MyEmployee FOR AdventureWorks2022.HumanResources.Employee;
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID('SynSchema.MyEmployee'), 'BaseType') AS BaseType;

Example 2: Remote Object

This example shows how to retrieve the base type when the synonym points to a remote object located on a server named Server1.

EXECUTE sp_addlinkedserver Server1;
GO
CREATE SYNONYM SynSchema.MyRemoteEmployee FOR Server1.AdventureWorks2022.HumanResources.Employee;
GO
SELECT OBJECTPROPERTYEX(OBJECT_ID('MyRemoteEmployee'), 'BaseType') AS BaseType;
GO