SentryOne Team Blog (blogs.sentryone.com)

#BackToBasics : The Beauty of the Synonym

At the end of last year, in response to a challenge from Tim Ford, I pledged to contribute an entry-level-ish post per month, where I take less of the reader's existing knowledge for granted.

This month, I though I would talk about a SQL Server feature that I think is quite underused and relatively unknown: the synonym. You can look up the syntax diagram in the CREATE SYNONYM topic in Books Online, but you won't get much explanation about what it does or when you might want to use one.

To me, a synonym is just a pointer or a redirect, kind of like having an entry in your hosts file, or forwarding calls from your office line to your cell phone. In a database, there are some use cases where synonyms provide great benefit.

Making a change transparent to applications

Let's say you have a table called dbo.BugReports, and you need to change it to dbo.SupportIncidents. This can be quite disruptive if you have references to the original name scattered throughout stored procedures, views, functions, and application code. Modern tools like SSDT can make a refactor relatively straightforward within the database (as long as queries aren't constructed from user input and/or dynamic SQL), but for distributed applications, it can be a lot more complex.

A synonym can allow you to change the database now, and worry about the application later – even in phases. You just rename the table from the old name to the new name (or use ALTER TABLE ... SWITCH and then drop the original), and then create a synonym named with the old name that "points to" the new name:

BEGIN TRANSACTION;
  EXEC sys.sp_rename N'dbo.BugReports', N'SupportIncidents', 'OBJECT';
  CREATE SYNONYM dbo.BugReports FOR dbo.SupportIncidents;
COMMIT TRANSACTION;

Now, if I select from dbo.BugReports or dbo.SupportIncidents, the results will be the same, and the execution plans will be identical. Inserts, updates, and deletes all work the same way, but there are some things that may have to change (details below).

Keeping code consistent through dev -> QA -> production

If you have an application that involves anything more than a simple, self-contained database, there are inevitably going to be differences between environments. Maybe the dev instance actually needs to use the customers table from QA to avoid having to keep them in sync, or – more likely – the central server with the billing information is on a different linked server and in a different database in each environment. I have seen shops that maintain different copies of a view or stored procedure per environment:

CREATE PROCEDURE dbo.GetBillingInfo -- dev
AS
    SELECT cols FROM QAinstance.Customers.dbo.CustomerDetails;
    SELECT cols FROM BillingServerDev.CentralBilling.dbo.BillingInfo;
CREATE PROCEDURE dbo.GetBillingInfo -- QA
AS
    SELECT cols FROM Customers.dbo.CustomerDetails;
    SELECT cols FROM BillingServerQA.BillingDB.dbo.BillingInfo;
GO

Maintaining a different copy of this code for each environment can be cumbersome and problematic – there are exceptions all over the place for deployments, and I have seen many cases where bugs were introduced because a QA-specific procedure . To avoid all of this, we can create the following synonyms in each environment:

-- dev
CREATE SYNONYM dbo.CustomerDetails FOR QAinstance.Customers.dbo.CustomerDetails;
CREATE SYNONYM dbo.BillingInfo     FOR BillingServerDev.CentralBilling.dbo.BillingInfo;
-- QA
CREATE SYNONYM dbo.CustomerDetails FOR Customers.dbo.CustomerDetails;
CREATE SYNONYM dbo.BillingInfo     FOR BillingServerQA.BillingDB.dbo.BillingInfo;

Now we can alter the procedure in both environments to the simpler:

ALTER PROCEDURE dbo.GetBillingInfo -- both
AS
    SELECT cols FROM dbo.CustomerDetails;
    SELECT cols FROM dbo.BillingInfo;
GO

Now the only thing that is different between the environments is the definition behind the synonyms, and this is better because the location of these centralized objects is likely to change a lot less often than the code in the stored procedures. I used this methodology quite a bit at my previous job, and I set my schema comparison scripts to simply ignore synonyms.

Using two-part names for central lookup tables or functions

I'm a big fan of consistency. If I have a numbers table, I want to always be able to reference dbo.Numbers, and if I have a table-valued function that returns a series of dates, I want to always be able to reference dbo.GenerateDateSequence(). The DBA shouldn't have to create a copy of these things everywhere the developer is working, and the developer shouldn't have to care about where that table or function exists – it could be on another database or another server altogether (though don't ignore any relevant performance considerations). Synonyms allow you to have exactly one copy of these things somewhere, and always reference them consistently in code. If my numbers table is in a central UtilityDB database, for example, I can make a two-part name that can be placed in any database where I want to reference that numbers table:

CREATE SYNONYM dbo.Numbers for UtilityDB.dbo.Numbers;

And if my date series function is on a central server:

CREATE SYNONYM dbo.GenerateDateSequence for CentralServer.SomeDB.dbo.GenerateDateSequence;

Now, all of the code in these databases can access the central or external objects with simple, consistent, two-part names. This can also ensure that everyone is using the same lookup data or functions and make inadvertent tampering much more difficult.

But it's not all roses

I have demonstrated some of the benefits of synonyms, but there are also a few downsides associated with their use, or at least some side effects and attributes you should be aware of.

  • They introduce a layer of abstraction. Looking up the definition of a synonym in order to identify an issue in code is an extra step, and can be confusing. sp_help, for example, shows the details about the synonym itself, not the underlying object, even if the object is local (I asked for a fix here). Synonyms for tables aren't listed under the "Tables" node in Object Explorer (I asked for that here), and the type of object they reference aren't indicated in any way under "Synonyms" (I asked for that here).
  • IntelliSense won't always work. IntelliSense does not recognize synonyms in certain versions (see here and here), so there may be cases where auto-complete lists won't populate, or red squiggly lines will appear when they shouldn't. I'm not sure exactly when this was fixed, but it no longer seems to be an issue in the latest builds of Management Studio for SQL Server 2016.
  • You can't ignore performance. While going across databases rarely introduces issues (and they would mostly be of the security/ownership variety), if you place reference data or functions on other servers, this can affect performance, the same way it would if you weren't using synonyms (such as simply going over the network, having rights to remote statistics, and making the optimizer choose where to join). It can also impact the ability to troubleshoot any performance-related issues, because the execution plans will hide lot of detail away behind operators like "Remote Query."
  • You can't alter a synonym. In order to change the target of a synonym, you need to drop and re-create. As a side note, the new DROP SYNONYM IF EXISTS syntax works in SQL Server 2016, but changing the synonym's reference still requires two statements (and proper transaction isolation). I asked for a fix here.
  • You can only point to certain entity types. A synonym can reference schema-level entities like views, stored procedures, and tables, but I think it would be useful if you could also create a synonym that points to a table-level entity (like a column), a database-level entity (like a schema), an instance-level entity (like a database), or a server / linked server. I asked for some of this here.
  • Permissions can get messy. You can grant things like SELECT on a synonym that references a table, but you can't do things like DENY SELECT on specific columns when using the synonym name:
    /* DENY SELECT ON dbo.synonym(column) TO principal; */

    Msg 1020, Level 16, State 3
    Sub-entity lists (such as column or security expressions) cannot be specified for entity-level permissions.

    In this case, you would have to reference the base table, rather than the synonym, in order to apply column-level permissions. In fact, there are many data definition language (DDL) commands that need to be applied to the base object rather than through the synonym (again, due to the inherent layer of abstraction). Hopefully, if your app can benefit from the use cases above, DDL operations against the objects aren't a major part of the requirements.

Have other use cases?

Would love to hear about how you use synonyms in your environment. Have an interesting implementation? Let me know in the comments!

10 replies on “#BackToBasics : The Beauty of the Synonym”

10 Comments (Comments are now closed.)
  1. This is one of the tips I give in my "Tips that Saved my Bacon" SQL Saturday talks.
    Used this to solve a major issue for a client of mine. Saved them 100s of hours of dev time.

  2. We have always made fairly extensive use of synonyms and the experience has been extremely good… until we found a problem – or obstacle – with Azure SQL.

    Azure SQL does now support cross database queries using Synonyms, but unfortunately does not support cross database Inserts.

    I'm not really a technical resource on our team, so may be missing something… but right now it's a real pity as the lack of this capability is not allowing us to move from Azure VM + SQL to a usage of the "native" Azure SQL solution.

    Anybody have any experience or input that might help on this?

  3. I use them extensively for testing our application database. I create a Test database which contains my data-driven test harness/procedures, etc. Then I create synonyms to the objects in the application database that I want to test. I can create as many test objects as I like without needing to exclude those test objects in the application database. They never exist there. I created a procedure in the Test Db that sync's synonyms with the objects in the App DB called Synonym_sync and I pass the name of the App DB.

    It works pretty slick.

    1. Hi Dave,

      I don't think your issue is exclusive to synonyms. Creating 2+ million of any type of entity in a single SQL Server database is going to have some unfavorable symptoms exposed somewhere.

      1. Good point, Aaron. I should have worded my first comment better. :(
        I don't mean to discourage use of SYNONYMs because of my one oddball experience. But I though it was still worth sharing.

  4. I use synonyms in stored procedures for ETL. These procedures are used to create a data mart that combines data from two databases used by our products. You would think that the name of the databases would be unchanged–that they would keep the names our company gave them. But that’s not true…Our customers’ databases can have different names (and often do when they are in hosted environments shared by other customers using the same product). The stored procedure code remains the same and all I have to do is change the synonyms.

  5. Great post! I will add one more caution on synonyms though. They don't work across a linked server. In other words if I set up a synonym on ServerA

    USE Test
    CREATE SYNONYM dbo.Numbers for UtilityDB.dbo.Numbers;

    And then try to call it from ServerB across a linked server

    SELECT * FROM ServerA.Test.dbo.Numbers

    I'll get an error.

    1. Yes, it's an odd one, too:

      Msg 7357, Level 16, State 2
      Cannot process the object ""database"."schema"."synonym"". The OLE DB provider "SQLNCLI11" for linked server "linked_server_name" indicates that either the object has no columns or the current user does not have permissions on that object.

      A couple of related Connect items (which may or may not be issues, depending on your version):

    2. Wouldn't creating the Synonym with the server name in it resolve this problem? If you replace "ServerA.Test.dbo.Numers" in your TSQL with "UtilityDB.dbo.Numbers", you're now trying to reference a database on Server B.