Synonyms in SQL Server are database objects that act as alternative names for other database objects, known as base objects. These base objects can reside either on the local server or a remote one. Synonyms primarily serve two critical functions, enhancing database management and application flexibility.
Firstly, a synonym provides a different name to refer to a database object. This is particularly useful when dealing with complex naming conventions or when simplifying access to frequently used objects. Instead of repeatedly using long, fully qualified names, a shorter, more user-friendly synonym can be employed. Secondly, and perhaps more importantly, synonyms introduce a layer of abstraction. This abstraction shields client applications from the impact of changes in the underlying base object’s name or location. This isolation is crucial for maintaining application stability and reducing the need for code modifications when database structures evolve.
Consider a scenario where you need to access the Employee table in the AdventureWorks database located on Server1. Without a synonym, you would have to use the four-part name Server1.AdventureWorks.Person.Employee every time you wanted to query this table from Server2. This lengthy name is not only cumbersome but also tightly couples your application to the specific server and database structure. Furthermore, if the Employee table were moved to a different server, say Server3, all applications referencing it would need to be updated to reflect this change.
To mitigate these challenges, synonyms offer a practical solution. You can create a synonym named EmpTable within a schema, for instance, RemoteObjects, on Server2. This synonym points to the Employee table on Server1. Now, client applications on Server2 can simply use the two-part name RemoteObjects.EmpTable to access the Employee table. This significantly simplifies object references and improves code readability.
More crucially, if the Employee table’s location changes – for example, it’s moved to Server3 – you only need to update the synonym EmpTable to point to the new location. Since there is no ALTER SYNONYM statement in SQL Server, this update involves dropping the existing synonym RemoteObjects.EmpTable and recreating it with the same name but pointing to the Employee table’s new location on Server3. Client applications using the synonym remain unaffected by this backend change, demonstrating the power of synonyms in decoupling applications from the physical database structure.
Objects Compatible with Synonyms
Synonyms can be created for a wide array of database objects, providing flexibility across different SQL Server functionalities. These objects include:
- Stored Procedures: Both Assembly (CLR) stored procedures and SQL stored procedures.
- Functions: Encompassing Assembly (CLR) scalar functions, SQL scalar functions, SQL inline table-valued functions, Assembly (CLR) table-valued functions, Assembly (CLR) aggregate functions, and SQL table-valued functions.
- Replication Filter Procedures: Used in database replication scenarios.
- Tables: User-defined tables, including both local and global temporary tables.
- Views: Virtual tables based on SQL queries.
It’s important to note that four-part names are not supported for function base objects when creating synonyms. Additionally, a synonym cannot be based on another synonym, preventing circular dependencies. Synonyms also cannot reference user-defined aggregate functions.
How Synonyms Work: Binding and Runtime Resolution
The link between a synonym and its base object is established solely by name. This means that SQL Server only checks the existence, type, and permissions of the base object when the synonym is actually used at runtime. This late binding approach has important implications.
The base object referenced by a synonym can be modified, dropped, or even replaced with a different object as long as the new object retains the same name as the original base object. Consider a synonym dbo.MyContacts that initially points to the Person.Contact table in AdventureWorks. If the Contact table is dropped and a view named Person.Contact is created in its place, dbo.MyContacts will now automatically reference the Person.Contact view. This dynamic resolution happens without any need to alter the synonym definition itself, showcasing the adaptability that synonyms offer.
However, this runtime binding also means that references to synonyms are not schema-bound. Consequently, a synonym can be dropped at any time. Dropping a synonym can lead to “dangling references” – places in your code that still attempt to use the now-removed synonym. These broken references will only be detected when the application attempts to execute the code at runtime, potentially leading to errors. Therefore, careful planning and impact analysis are crucial before dropping synonyms, especially in production environments.
Synonyms and Schemas: Ownership and Creation
Synonyms are schema-scoped objects, meaning they are contained within a database schema. Like other schema objects, a synonym’s name must be unique within its schema. When creating synonyms, schema ownership is a key consideration.
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 that you do own. For instance, if your default schema is S2, but you own schema S1, you must prefix the synonym name with S1 during creation. Instead of using a single-part name for the synonym, you would use a two-part name like S1.MySynonym. This ensures proper object ownership and schema management within the database. For detailed instructions on synonym creation, refer to the CREATE SYNONYM (Transact-SQL) documentation.
Managing Permissions on Synonyms: Security Control
Granting permissions on a synonym is essential for controlling access and ensuring database security. Only synonym owners, members of the db_owner database role, or members of the db_ddladmin database role have the authority to grant permissions on a synonym.
You can manage permissions on synonyms using the standard SQL permission statements: GRANT, DENY, and REVOKE. The permissions applicable to synonyms include:
CONTROL: Provides comprehensive control over the synonym.EXECUTE: Allows users to execute the synonym if it refers to an executable object like a stored procedure or function.SELECT: Permits users to select data through the synonym if it points to a table or view.UPDATE: Allows users to update data through the synonym.DELETE: Permits users to delete data through the synonym.INSERT: Allows users to insert data through the synonym.TAKE OWNERSHIP: Enables users to take ownership of the synonym.VIEW DEFINITION: Grants users the permission to view the definition of the synonym.
These granular permissions allow database administrators to precisely control who can interact with the underlying base objects through the synonyms, enhancing security and data governance.
Utilizing Synonyms in SQL Statements and Expressions
Synonyms can be seamlessly integrated into various SQL statements and expression contexts, effectively acting as replacements for their base objects. This versatility makes synonyms a powerful tool for simplifying SQL code and enhancing maintainability. Synonyms can be used in the following SQL constructs:
SELECTstatements: To query data from tables or views referenced by synonyms.UPDATEstatements: To modify data in tables through synonyms.EXECUTEstatements: To execute stored procedures or functions via synonyms.INSERTstatements: To insert data into tables using synonyms.DELETEstatements: To remove data from tables through synonyms.- Subqueries: Synonyms can be used within subqueries for more complex data retrieval and manipulation.
When you use synonyms in these contexts, the actions are effectively performed on the underlying base object. For instance, if you insert a new row into a synonym that points to a table, the row is actually inserted into the referenced base table. This transparent redirection simplifies interactions and reinforces the abstraction provided by synonyms.
However, there are limitations to synonym usage. Notably, you cannot directly reference a synonym that is located on a linked server. For cross-server object access, you would typically use fully qualified names or other techniques.
Furthermore, while you can use a synonym as a parameter for the OBJECT_ID function, it’s important to understand that this function will return the object ID of the synonym itself, not the object ID of the base object it references.
Restrictions on DDL Statements and Schema Binding
Synonyms cannot be used in Data Definition Language (DDL) statements. DDL statements are used to define and modify database objects. For example, attempting to use a synonym named dbo.MyProduct in ALTER TABLE statements will result in errors:
ALTER TABLE dbo.MyProduct ADD NewFlag int null;
EXEC ('ALTER TABLE dbo.MyProduct ADD NewFlag int null');These operations must be performed directly on the base object, not through the synonym.
Similarly, certain permission statements are specifically associated with the synonym object itself and do not propagate to the base object. These include:
GRANT(permissions on the synonym)REVOKE(permissions on the synonym)DENY(permissions on the synonym)
Synonyms are inherently not schema-bound. This characteristic prevents them from being referenced in schema-bound expression contexts. Schema-bound objects are tightly linked to the schema they are created in, and their definitions are fixed relative to that schema. Synonyms, with their dynamic runtime resolution, are incompatible with schema binding. Therefore, you cannot use synonyms in:
CHECKconstraints- Default expressions
- Schema-bound views
- Computed columns
- Rule expressions
- Schema-bound functions
For more information about schema-bound functions and their characteristics, refer to the documentation on Create User-defined Functions (Database Engine).
Retrieving Information About Synonyms: Metadata and Properties
SQL Server provides system catalog views and functions to retrieve information and metadata about synonyms. The sys.synonyms catalog view is a primary source of synonym information.
The sys.synonyms catalog view contains a row for each synonym defined within a given database. This view exposes essential metadata, including the synonym’s name and the name of its base object. For comprehensive details on the structure and columns of this view, consult the sys.synonyms (Transact-SQL) documentation.
Extended properties can further enhance synonym metadata. You can use extended properties to add descriptive or instructional text, input masks, and formatting rules as properties directly attached to a synonym. Because these properties are stored within the database itself, all applications that access the synonym and read these properties can interpret and utilize the object in a consistent manner. For details on adding extended properties to synonyms, see sp_addextendedproperty (Transact-SQL).
To determine the base object type of a synonym, you can use the OBJECTPROPERTYEX function. This function provides various properties of database objects, including the base type. For detailed usage and available properties, refer to OBJECTPROPERTYEX (Transact-SQL).
Examples: Determining Base Object Type
The following examples demonstrate how to use OBJECTPROPERTYEX to retrieve the base type of a synonym’s base object.
Example 1: Local Base Object
This example retrieves the base type for a synonym that points to a local object within the same database.
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 Base Object
This example demonstrates retrieving the base type for a synonym that references a remote object located on a linked 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;
GOThese examples illustrate how to programmatically determine the underlying object type that a synonym represents, which can be useful for dynamic code generation or metadata-driven applications.
