7 Facts About SQL Server Synonyms You Should Know

Before SQL Server Synonyms appeared, everyone wished to simplify and enhance their database experience.

Imagine you have an application with a database that references another database from the same server. Then, a major reorganization forces your team to transfer the other database to another server.

There is no doubt your application will break. But what will you do in that case? Link the 2 servers and hardcode all the references (again) to point to the new server?

You could do that if you want to and forget if you have just a few or a dozen references to it. But if another transfer or a rename occurs, you’ll have to repeat the same nightmare.

Nonetheless, there’s a better way to deal with this.

Introducing SQL Server Synonyms

Before we dive in on what you can do with SQL Server Synonyms, let’s describe what they are.

A synonym in any spoken and written language refers to a word or phrase that has the same meaning as another word or phrase. So, the word gorgeous is the synonym to beautiful and attractive.

Similar to what we know about synonyms of words and phrases, SQL Server Synonyms refer to an alternative name of a database object residing in a local or remote server. Read more here.

As you will see in the following facts, SQL Server Synonyms can make your application maintenance a lot easier.

So, let’s get started!

1. SQL Server Synonyms Can Simplify Your Work When Base Objects Are Transferred or Renamed

Firstly, they will save you from the trouble of code changes when a database is moved to another server or renamed for whatever reason. Let’s refer to the scenario we mentioned in this post opening.

A major reorganization forces your team to change a reference to all objects in mydatabase2 into prodserver2.mydatabase2.

So, you query sys.sql_modules with all occurrences of mydatabase2 from mydatabase1.

USE mydatabase1
GO
SELECT
 b.name
,a.definition
,b.type_desc
FROM sys.sql_modules a
INNER JOIN sys.all_objects b on a.object_id = b.object_id
WHERE a.definition like '%mydatabase2%'
GO

Now, the output of the script above will list down all objects having references to mydatabase2. Nice, huh? And this will help define the scope of work that must be done.

But that’s just the beginning. You also need to check if there is code in your client app or any other code that references the same outside your database.

The amount of code that is affected shows how big your new issue is.

Now, here are a few more tidbits on what’s going on in that script:

• sys.sql_modules include SQL objects that are SQL-defined modules like views, stored procedures, functions, etc.
• The name column is where the name of the object is.
• The SQL code for the object is in the definition column of sys.sql_modules.
• sys.all_objects include all the objects in your database like tables, views, etc.
• We took the type_desc column to determine what type of object it is (view, stored procedure, etc.)
• The where clause filters the query for any SQL code that includes a reference to mydatabase2.
• To have a satisfying result using the script above, you need to have permission for all of the objects. Check with your Database Administrator or someone with a similar role for this. You can also check this out.

Now that you know how to get the scope of your work, it’s time to fix it.

How to Fix This Mess of Code So It Won’t Happen Again

All right. I have a confession to make.

Using SQL Server Synonyms will only reduce your problems to a minimum, but not eliminate them.

Now that it is out of the way, here’s the good news: If you have 10 references to a remote object before using synonyms, you modify all 10. But once you start using a synonym for that remote object, instead of modifying 10 occurrences, you only change 1. Nothing more.

Now, here are the steps to fix this using synonyms:

1. Create a synonym for each of the remote objects. So instead of prodserver2.mydatabase2.schema1.object1 you can refer to it using the synonym.
2. Modify the references to each object into its synonym.

Later on, you will find out the specifics of how to do this.

The Takeaway:

Synonyms provide a layer of abstraction to protect references to base objects from any portion of your code, whether inside your database, client app, or anywhere else. So, you can rejoice when another change occurs, whether it’s an object transfer or a rename.

2. You Can Create SQL Server Synonyms for Most Objects

Next, let’s figure out what objects can have synonyms?

• Tables
• Views
• Stored Procedures
• Functions

Now that you know the object types, you may have an idea of what you can do with synonyms. Let’s get more specific.

3. You Can Issue Appropriate Commands for the Object Synonym

Thirdly, here are some specific commands for each object type.

Tables

As much as you can do a SELECT, INSERT, UPDATE, and DELETE to a table, you can do the same to its synonym.

So, instead of:

SELECT * FROM prodserver2.mydatabase2.schema1.table1

You can have a shorter version that will be resistant to the next change:

SELECT * FROM synonym1

Since this is the case, you can also do this:

UPDATE synonym1
SET column1 = <value>

And the same goes for INSERT and DELETE.

However, please be aware of the following:

• Inserting a new record through a synonym will add a new record to the base table. In our case, prodserver2.mydatabase2.schema1.table1.
• Updating and deleting will have the same effect.

So far, we have shown only DML commands. How about DDL commands like DROP?

Unfortunately, you can’t perform them. Here’s the reason why:

Dropping a synonym will not drop the base table. It will drop the synonym. I will go into detail about this in a moment.

But here’s one more thing you can do to SQL Server Synonyms of tables: JOINs.

How convenient can this be? Instead of issuing this:

SELECT
 a.column1
,b.column1
FROM table3 a
INNER JOIN prodserver2.mydatabase2.schema1.table1 b on a.id = b.id

You can perform this:

SELECT
 a.column1
,b.column1
FROM table3 a
INNER JOIN synonym1 b on a.id = b.id

Is that any simpler? Sure thing!

Stored Procedures

One relevant command you can do with a synonym of a stored procedure is EXEC.

So, instead of invoking a remote procedure like this:

EXEC prodserver2.mydatabase2.schema1.spProcedure1

You can create a synonym for the procedure above. Let’s call it synProcedure1. And invoke it this way:

EXEC synProcedure1

Shall we continue? You can do much the same with VIEWs and FUNCTIONs. Of course, if you have the required permissions. But first, let’s discuss how to create SQL Server Synonyms.

4. You Can Start Your Quest with SQL Server Synonyms With CREATE

We’ve reached the point of how you can create synonyms. Here’s how we can do it using T-SQL for a table:

CREATE SYNONYM synonym1 FOR prodserver2.mydatabase2.schema1.table1

But take note of this caveat:

Checking for the existence of and permission for mydatabase2.schema1.table1 in prodserver2 is deferred until runtime.

That means you won’t know if:

• the 2 servers (prodserver1 and prodserver2) are already linked.
• the database mydatabase2, the schema schema1, and the table table1 actually exist.
• your access to those resources is permitted.

So, after creating the synonym, test it by running the commands you expect to work.

And with stored procedures, views, and functions, you should act in the same way.

But that’s not all. If we can create a synonym, we can delete it as well using:

DROP SYNONYM synonym1

And here’s another caveat: Since you can drop synonyms anytime, references to dropped synonyms will only be checked at runtime.

So before dropping a synonym, check sys.sql_modules for if there are any references to it.

Using SQL Server Management Studio (SSMS) to Create and Drop Synonyms

So far, we have used T-SQL to create and drop SQL Server Synonyms. You may prefer to use a graphical interface when doing the same.

Let’s get the ball rolling.

Creating Synonyms

If typing the commands isn’t your thing, here are the steps to follow when it comes to creating synonyms:

1. Run SSMS and login to your SQL Server.
2. Look for the database where you want to create a synonym.
3. Expand it.
4. Right-click the Synonyms folder and select New Synonym.
5. Fill in the form with the information required for synonyms, including the name, schema, etc.
6. Click OK.

Below is a screenshot of the form in SSMS:

Dropping Synonyms

Speaking of preferences, typing the commands for creating synonyms is my kind of thing. But dropping them at will is simpler than typing the commands. Of course, that’s just my liking. Anyway, below are the steps to follow when dropping a synonym:

1. Run SSMS and login to your SQL Server.
2. Look for the database where the synonym you want to drop is residing.
3. Expand it.
4. Expand the Synonyms folder.
5. Look for the synonym you want to drop.
6. Right-click the synonym you want to drop and select Delete.
7. Click OK.

To summarize the steps above, simply right-click the synonym to drop, then select Delete and finally, click OK.

Below is a screenshot of the window that will appear prior to confirming deletion:

5. You Can Secure SQL Server Synonyms

Using GRANT, DENY, or REVOKE, you can control access to a synonym.

To GRANT a SELECT permission to the synonym synonym1 to a user named testuser, you do as follows:

GRANT SELECT ON synonym1 to testuser

Other permissions you can add to or remove from a synonym are the following:

• CONTROL
• EXECUTE
• UPDATE
• INSERT
• DELETE
• VIEW DEFINITION
• TAKE OWNERSHIP

6. Can’t Find That Table, View, or Procedure? It Might Be a Synonym

One of the problems a newcomer in your team may experience is a “missing” table, view, procedure, or function. Of course, if SELECT is issued on an object, it might be a table or view. But he can’t find it in the tables list or views list. And if he didn’t use SQL Server Synonyms before, that’s an additional problem.

Lack of orientation, a gap in the documentation, or a gap in your standards can be put in order. Here’s a nifty script that will help you present the list of synonyms and its base object to your new team member:

SELECT
 a.[name]
,a.[base_object_name]
,OBJECTPROPERTYEX(OBJECT_ID(a.[name]), 'BaseType') as BaseType
,b.type_desc
FROM sys.synonyms a
INNER JOIN (SELECT DISTINCT type, type_desc from sys.all_objects) b on 
           CONVERT(varchar(2),OBJECTPROPERTYEX(OBJECT_ID(a.[name]), 'BaseType')) = b.type

A “missing” object? Not anymore if it’s a synonym.

But before you get excited running this script, here are a few more things you should take into account:

• sys.synonyms is where all the SQL Server Synonyms are defined in the current database.
• We took the types and type descriptions (type and type_desc respectively) in sys.all_objects. If you have a better idea other than joining them with a subquery, please let me know.
• OBJECTPROPERTYEX is used to get the type of the base object if it’s a table, stored procedure, or otherwise.
• Finally, if you don’t have the minimum permission required to execute this script and get the desired output, it’s time to make friends with your DBA or someone with a similar role:)

But you may be wondering, why do all these if it won’t perform well?

7. Will SQL Server Synonyms Impact Performance?

This is a common concern. And to flesh out what’s going on behind the scenes, let’s have a look at the summary of what will happen in the execution plan:

1. When executing the simplest code with a synonym (e.g. SELECT * from synonym1), SQL Server will enter a binding phase where the base object will replace the synonym.
2. Next, whatever the best optimization plan there is for executing a command to the base object, it will be the same.

Here are some questions and answers regarding the 2 statements above:

1. How long does SQL Server do the binding phase? ANSWER: It doesn’t take long. It usually happens in a blink.
2. If the next step uses the same best optimization plan with the base object, and the query to the base object is “fast enough”, will it be slower? ANSWER: No, because it will use the same execution plan.
3. How about authentication from the new server? ANSWER: If there are some slight delays caused by the transfer of a database to a new server, they are not caused by the synonym. Query performance of calling it using a synonym or hardcoding the server.database.schema.object should be the same because the synonym is just an alternative name for the base object. Solve the slow performance from the base object.

But don’t take my word for it. You should check it for yourself with your query execution plan and actual performance.

Conclusion

All in all, we covered pretty much information on SQL Server Synonyms, so let’s recap.

Firstly, a synonym is simply an alternative name that will save you from object name changes and transfers. Secondly, good candidates for synonyms are tables, views, stored procedures, and functions. Next, you can do commands appropriate to its base object from a synonym. You can secure it, too. Then, if you need to see the list of synonyms, you have sys.synonyms to help you. Finally, performance should not be much of a trouble if there are no performance issues with the base object.

So why not try it out today?

What do you think? Let us know in the comments.

Tags: database administration, sql server Last modified: September 20, 2021