#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.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.
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 EXISTSsyntax 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
SELECTon a synonym that references a table, but you can't do things like
DENY SELECTon 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.
Would love to hear about how you use synonyms in your environment. Have an interesting implementation? Let me know in the comments!
Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com. Aaron's blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.