Ahmad Yaseen

How to simplify SQL Server Database Object usage with Synonyms

July 20, 2017 by

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:

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:

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

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:

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

Instead of using the full four-part name:

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 free 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:

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

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

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:

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 free 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:

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:

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:

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.

See more

To view and analyze SQL Server query execution plans for free, check out ApexSQL Plan

Useful Links:

Ahmad Yaseen

Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience.

He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs

View all posts by Ahmad Yaseen
Ahmad Yaseen
Synonyms

About Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience.

He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs

View all posts by Ahmad Yaseen

705 Views