Server Synonym: Simplifying Database Object Access and Management

In the realm of database management, particularly within systems like SQL Server and Azure SQL Database, navigating and referencing various database objects efficiently is crucial. This is where the concept of a Server Synonym, or more accurately a database synonym, becomes invaluable. Synonyms act as alternative names or aliases for database objects, simplifying complex references and enhancing code maintainability.

This article delves into the world of server synonyms, exploring their purpose, creation, benefits, and practical applications within SQL Server and Azure SQL Database environments. We’ll unpack the syntax of the CREATE SYNONYM command, discuss the types of objects that can be aliased, and illustrate their usage with clear examples.

Understanding Server Synonyms: Aliases for Database Objects

At its core, a synonym in database systems is much like a nickname for a database object. Instead of repeatedly using long, fully qualified names to refer to tables, views, stored procedures, functions, and other objects, you can create a shorter, simpler synonym. This abstraction offers several advantages, particularly in complex database environments:

• Simplifying Object References: Synonyms reduce the need to remember and type out lengthy object names, especially when dealing with objects located in different databases, schemas, or even on remote servers.
• Enhancing Code Maintainability: If the actual name or location of a base object changes, you only need to update the synonym definition, rather than modifying every piece of code that references the original object. This significantly reduces the effort and risk associated with database refactoring.
• Improving Code Readability: Using meaningful synonym names can make your SQL code more intuitive and easier to understand, as they can be chosen to reflect the purpose of the aliased object.
• Abstraction and Decoupling: Synonyms provide a layer of abstraction between your code and the physical database objects. This decoupling can be beneficial when you want to change the underlying database structure without impacting applications that rely on the synonyms.

Creating Server Synonyms: The CREATE SYNONYM Command

The foundation of working with server synonyms is the CREATE SYNONYM Transact-SQL command. The syntax is straightforward, allowing you to define a synonym within a specific schema and link it to a base object.

Here’s the basic syntax for creating a synonym in SQL Server and Azure SQL Database:

CREATE SYNONYM [ schema_name_1. ] synonym_name FOR { [ server_name. ] [ database_name. ] [ schema_name_2. ] object_name }

Let’s break down the components of this syntax:

• CREATE SYNONYM: This is the command that initiates the creation of a new synonym.
• [ schema_name_1. ]: This optional parameter specifies the schema where the synonym will be created. If you omit schema_name_1, the synonym is created in the default schema of the current user.
• synonym_name: This is the name you choose for your new synonym. It should be a valid SQL Server identifier.
• FOR: This keyword indicates that you are defining the base object that the synonym will point to.
• [ server_name. ]: This optional parameter specifies the name of the remote server where the base object is located. This is necessary when referencing objects on linked servers.
• [ database_name. ]: This optional parameter specifies the database where the base object resides. If you don’t provide database_name, the current database is assumed. For Azure SQL Database, you can use three-part names when database_name is the current database or tempdb.
• [ schema_name_2. ]: This optional parameter defines the schema of the base object. If omitted, the default schema of the current user is used.
• object_name: This is the name of the actual database object (table, view, stored procedure, function, etc.) that you want to create a synonym for.

This icon indicates the CREATE SYNONYM feature is applicable across SQL Server, Azure SQL Database, and Azure SQL Managed Instance.

It’s important to note that the base object doesn’t need to exist when you create the synonym. SQL Server only checks for the existence of the base object when the synonym is actually used at runtime.

Types of Objects Supported for Synonyms

Synonyms are versatile and can be created for a wide range of database object types, including:

• Tables: User-defined tables, including local and global temporary tables.
• Views: Both standard and indexed views.
• Stored Procedures: T-SQL stored procedures, extended stored procedures, and assembly (CLR) stored procedures.
• Functions: T-SQL scalar functions, T-SQL table-valued functions (inline and multi-statement), assembly (CLR) scalar functions, and assembly (CLR) table-valued functions.
• Aggregates: Assembly (CLR) aggregate functions.
• Replication-filter-procedures: Procedures used in SQL Server replication.

It’s worth noting that four-part names are not supported for function base objects when creating synonyms.

Permissions Required for Creating Synonyms

To create a synonym within a specific schema, a user needs CREATE SYNONYM permission and must either own the schema or have ALTER SCHEMA permission.

The CREATE SYNONYM permission is grantable, allowing you to control which users can create synonyms within your database environment.

Interestingly, you do not need permissions on the base object itself to successfully execute the CREATE SYNONYM statement. Permission checks on the base object are deferred until runtime when the synonym is actually used.

Practical Examples of Server Synonym Usage

Let’s illustrate the creation and use of server synonyms with practical examples.

Example 1: Creating a Synonym for a Local Table

This example demonstrates creating a synonym named MyProduct for the Product table within the AdventureWorks2022 database. After creating the synonym, we query it as if it were the original table.

-- Create a synonym for the Product table in AdventureWorks2022.
CREATE SYNONYM MyProduct FOR AdventureWorks2022.Production.Product;
GO

-- Query the Product table by using the synonym.
SELECT ProductID, Name
FROM MyProduct
WHERE ProductID < 5;

This image represents a sample result set returned after querying the MyProduct synonym, showcasing the ProductID and Name columns for products with ProductID less than 5.

This query will return data from the Production.Product table, but using the simpler MyProduct synonym.

Example 2: Creating a Synonym for a Remote Object

In this scenario, we create a synonym MyEmployee that points to the Employee table located on a remote server named Server_Remote within the AdventureWorks2022 database.

EXEC sp_addlinkedserver Server_Remote;
GO

USE tempdb;
GO

CREATE SYNONYM MyEmployee
FOR Server_Remote.AdventureWorks2022.HumanResources.Employee;
GO

This example first sets up a linked server connection to Server_Remote (assuming it’s not already configured). Then, it creates the MyEmployee synonym in the tempdb database, referencing the remote Employee table. Now, you can query MyEmployee in tempdb to access data from the remote table.

Example 3: Synonym for a User-Defined Function

This example demonstrates creating a synonym for a user-defined function. First, we create a function dbo.OrderDozen that rounds up an order amount to the nearest multiple of 12. Then, we create a synonym dbo.CorrectOrder for this function.

-- Creating the dbo.OrderDozen function
CREATE FUNCTION dbo.OrderDozen (@OrderAmt INT)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    IF @OrderAmt % 12 <> 0
    BEGIN
        SET @OrderAmt += 12 - (@OrderAmt % 12)
    END
    RETURN (@OrderAmt);
END;
GO

-- Using the dbo.OrderDozen function
DECLARE @Amt INT;
SET @Amt = 15;
SELECT @Amt AS OriginalOrder, dbo.OrderDozen(@Amt) AS ModifiedOrder;

-- Create a synonym dbo.CorrectOrder for the dbo.OrderDozen function.
CREATE SYNONYM dbo.CorrectOrder FOR dbo.OrderDozen;
GO

-- Using the dbo.CorrectOrder synonym.
DECLARE @Amt INT;
SET @Amt = 15;
SELECT @Amt AS OriginalOrder, dbo.CorrectOrder(@Amt) AS ModifiedOrder;

In this example, both dbo.OrderDozen and its synonym dbo.CorrectOrder can be used interchangeably to call the same function, illustrating the flexibility synonyms provide.

Conclusion: Leveraging Server Synonyms for Efficient Database Management

Server synonyms, or database synonyms, are a powerful tool for simplifying database object references, enhancing code maintainability, and improving the overall organization of your SQL Server and Azure SQL Database environments. By providing aliases for complex object names, synonyms contribute to cleaner, more readable, and more adaptable database code. Understanding and utilizing synonyms effectively can significantly streamline database development and administration tasks.

Further Resources

For more in-depth information and the latest updates on the CREATE SYNONYM command and related features, refer to the official Microsoft SQL Server documentation.

Next Steps

Explore other Transact-SQL commands and database object management techniques to further enhance your database skills.