How to simplify SQL Server Database Object usage with Synonyms

The concept of SQL Server Synonyms was introduced the first time in SQL Server 2005 as an alias name that references an existing database object, replacing its fully qualified name. In this way, it makes the database object more portable and provides more flexibility for the clients to reach and maintain it. You can imagine Synonyms as a layer of abstraction that provides us with an easy way to connect to and manage the database objects without the need to identify the real name and location for these objects.

Synonyms are useful in simplifying complicated and lengthy object names by providing short and friendly alternative names for these database objects. You can benefit from Synonyms by providing backward compatibility for the database objects that are used by legacy systems in case you drop or rename that objects. It may be found to be very simple from the definition, but for database administrators and developers, it would be very useful and simplify their jobs if it is used in a correct way.

Synonym changes are also transparent from the client application perspective, as no change required from the client side if the Synonym is changed to reference a different database object, as long as the column names are not changed.

Assume that you plan to change the name of a database object that is used heavily in your queries. It would seem like a very difficult task, as you need to go through all places in which this object is used. An easy way to perform that is to create a Synonym that references the database object and use that Synonym in your queries. If you need to change the database object name, you need only to change the referenced object only in the Synonym definition, by dropping and recreating the Synonym, without the need to visit all places in which object is mentioned. You can also move the base object easily to another database on the same server or to another SQL server without performing any change from the application side. Just you need to drop the Synonym and recreate a new one that points to the new location of that object.

Synonyms also help also in obscuring the name of the database objects, for security purposes, by creating a Synonym that references the database object and allows the users to query the Synonym rather than querying the base table directly.

The Synonym, like any other database object, should belong to a database schema and should be provided a unique name that follows the T-SQL identifiers rules. The naming convention rules can be also applied to the Synonym, such as using a prefix or suffix with the Synonym name to make it easy to recognize that the database object is a Synonym. A Synonym can be used to reference the following database objects types:

Assembly (CLR) stored procedure
Assembly (CLR) table-valued function
Assembly (CLR) scalar function
Assembly (CLR) aggregate functions
Replication-filter-procedure
Extended stored procedure
SQL scalar function
SQL table-valued function
SQL inline-tabled-valued function
SQL stored procedure
View
User-defined table

On the other hand, you cannot use a Synonym to reference other Synonyms or to reference a user-defined aggregate function. The object that is referenced by a Synonym will be checked at runtime, which means that the Synonym can be created with spelling or referencing errors, but you will get these errors while using that Synonym.

You can easily drop a Synonym without getting any error or warning messages that it is being referenced by any database object, or you can modify the base object without affecting the Synonym, due to the fact that the Synonyms and the base objects are loosely bonded. Synonyms cannot be referenced in a DDL T-SQL statement, such as modifying using an ALTER statement. Having the fact that the Synonyms are not schema-bound database objects, it cannot be referenced by schema-bound expressions such as:

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

To create a Synonym that references objects across schemas, databases and servers, you need to specify the schema and the name of the Synonym and the schema and the name of the database object that the synonym references. The syntax that is used to create a new Synonym is as shown below:

CREATE SYNONYM schema_name_1. synonym_name FOR server_name. database_name. schema_name_2. object_name

For a new Synonym, you need to provide the schema_name_1 that specifies the schema in which the synonym will be created, with the default schema of the current user will be used if the schema is not specified in the CREATE SYNONYM statement and the synonym_name that specifies the name of the new synonym.

For the referenced object, you can provide the server_name that specifies the name of the server on which base object is located, the database_name that is the name of the database in which the base object is located, the schema_name_2 that is the name of the schema of the base object, with the default schema of the current user will be used if it is not provided, and the object_name that specifies the name of the base object that will be referenced by the Synonym.

To be able to create a Synonym in the provided schema, you should have CREATE SYNONYM permissions with db_owner permissions in that schema or, at least at a minimum, ALTER SCHEMA permissions. Synonyms can be also created with the New Synonym window using SQL Server Management Studio, by right-clicking on the Synonyms node under the current database as shown below:

Where you can provide the previously described parameters, such as the Synonym schema and name and the server name, database name, schema name and the name of the referenced object, in order to create the Synonym as follows:

Once created, you can perform SELECT, INSERT, UPDATE, DELETE or EXECUTE operations on that Synonym. To be able to perform these operations, a number of permissions should be granted on the Synonym, such as:

CONTROL
DELETE
EXECUTE
INSERT
SELECT
TAKE OWNERSHIP
UPDATE
VIEW DEFINITION

Synonym owners or users with db_owner or db_ddladmin permissions can GRANT, DENY or REVOKE permission at the Synonym level, with no effect at the base table level. The below script is used to create a new database user, and grant it SELECT permission at the Synonym level:

USE [SQLShackDemo]

CREATE USER [suheir] FOR LOGIN [suheir]

GRANT SELECT ON [dbo].[MySynonym] TO [suheir]

Creating a synonym to reference a local object

The below T-SQL statement is used to create a Synonym to reference a local table in the same SQL Server instance, where the base database name, the schema and the table name are provided in the CREATE SYNONYM statement:

USE SQLShackDemo

CREATE SYNONYM dbo.MySynonym

FOR SQLShackDemo.dbo.SynTestNew;

After creating the Synonym, you can easily retrieve data from it directly using the SELECT statement shown below:

SELECT TOP 10 * FROM MySynonym

The SELECT statement result will retrieve the data from the base table directly as follows:

Creating a synonym to reference a remote object

As mentioned previously within this article, Synonyms can be used to simplify the name of the base object by using a short alias instead of using the full object name. The below T-SQL statement is used to create a Synonym to reference a database table hosted in a remote SQL Server instance, where the server name, the base database name, the schema and the table name are provided in the CREATE SYNONYM statement:

USE SQLShackDemo

CREATE SYNONYM DevSyn

FOR DEV.Testdb.dbo.InvoiceTest;

Once created, you can easily retrieve the data by querying the Synonym simple name directly as in the SELECT statement shown below:

SELECT TOP 10 * FROM DevSyn

Instead of using the full four-part name:

SELECT TOP 10 * FROM DEV.Testdb.dbo.InvoiceTest;

Both queries will return the same result as shown below:

If you check the execution plan of the previous SELECT statement using ApexSQL Plan, the execution plan viewer and query optimization tool, you will see that the Synonym name will be translated to the base table name and location when the query is executed as clear from the execution plan below:

The screenshot is from ApexSQL Plan, a tool to view and analyze SQL Server query execution plans

Creating a synonym to reference a user-defined function

A Synonym can be also used to reference user-defined functions. That allows you to rename or move that function to another SQL Server instance without affecting the clients, by dropping and recreating the Synonym one time to reference the new user-function name or location.

Let’s create a simple user-defined function that will return the absolute value of the negative integers:

USE SQLShackDemo

CREATE FUNCTION dbo.TestSynFunction (@InputNum int)

RETURNS int

BEGIN

IF @InputNum < 0

BEGIN

SET @InputNum= abs(@InputNum)

END

RETURN(@InputNum);

END;

Then we will create a Synonym that references the created function:

CREATE SYNONYM dbo.AbsVal

FOR dbo.TestSynFunction;

We can easily call the function within our code by selecting from it, passing the required parameters, as shown below:

DECLARE @Num int;

SET @Num = -7;

SELECT dbo.AbsVal(@Num) AS ABSValue;

The absolute value of the passed negative integer will be like:

SQL Server allows you to perform different types of operations on the Synonyms, that change the data and not the schema of the object, such as SELECT, INSERT, UPDATE, DELETE or EXECUTE operations. When you perform a data modification operation, let’s say an UPDATE statement, the base object will be affected. For example, if you update a row in a Synonym that references a database base table, you will actually update that row in the referenced table too.

Assume that we need to update the EmpPhone of the below employee:

The following UPDATE statement is used to update that employee information from the Synonym itself:

USE [SQLShackDemo]

UPDATE [dbo].[MySynonym] SET EmpPhone='612-854-3528' WHERE ID = 2010001

Checking the execution plan of the previous UPDATE statement that is generated, the plan will show us that the query performed a clustered index update on the base table, SynTestNew table, as shown below:

The screenshot is from ApexSQL Plan, a tool to view and analyze SQL Server query execution plans

Which is also clear when we retrieve that employee’s information from the base table directly as shown below:

The sys.synonyms DMV can be queried for the synonyms information in a specific database. Useful synonym metadata information retrieved from that DMV such as the synonym name and the name of the base object. The T-SQL statement below is used to query the sys.synonyms DMV for the metadata information of the Synonyms created under the SQLShackDemo testing database:

USE SQLShackDemo

SELECT name, type_desc, create_date,base_object_name FROM sys.synonyms

The list of created Synonyms under SQLShackDemo database are shown below:

The base type of the base object referenced by a Synonym can be checked using the OBJECTPROPERTYEX function. The below T-SQL statement retrieve the type of the base object for the previously created Synonyms:

USE SQLShackDemo

SELECT OBJECTPROPERTYEX(OBJECT_ID('MySynonym'), 'BaseType') AS LocalSyn_BaseType;

SELECT OBJECTPROPERTYEX(OBJECT_ID('DevSyn'), 'BaseType') AS RemoteSyn_BaseType;

SELECT OBJECTPROPERTYEX(OBJECT_ID('AbsVal'), 'BaseType') AS FunctionSyn_BaseType;

The result shows that the type of the local and remote base objects that are referenced by the first two Synonyms is User Table, and the type of the base object referenced by the last Synonym is User-Defined Function as below:

You can also browse all Synonyms created under a specific database from the Synonyms node under the current database using SQL Server Management Studio as follows:

Where you can check the properties of each Synonym from the Synonym Properties window, including the referenced base object server, database, schema and name as clear from the image below:

A Synonym can be dropped at any time, without affecting the base object. Dropping the Synonym is required when you need to rename or move the Synonym referenced object in order to create a new Synonym with the new change, as you cannot use the ALTER statement to modify that Synonym. The Synonym can be simply dropped by passing the Synonym name to the DROP SYNONYM T-SQL statement as in the script below:

USE [SQLShackDemo]

DROP SYNONYM [dbo].[AbsVal]

Conclusion

SQL Server Synonyms are very useful in simplifying and shortening the database objects names, in a way it makes these objects usage easier and more flexible.

Synonyms also provide us with a layer of abstraction that helps us to use and manage these database objects without the need to identify the real name and location of these objects.

Within this article, we described how to use the Synonym to reference local and remote database objects, in addition, to reference a user-defined function. Enjoy using the Synonyms in your environment, in the correct way, with the best methods to get metadata about these Synonyms as mentioned within this article.

Author
Recent Posts

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Creating a synonym to reference a local object

Creating a synonym to reference a remote object

Creating a synonym to reference a user-defined function

Conclusion

Related posts: