Synonyms, how and why.
8February 15, 2018 by Kenneth Fisher
Did you know SQL Server has a thing called a synonym? It’s not something you see used very often even though it’s been around for >10 years (SQL 2005). In fact, I’d be willing to bet that a good chunk of the people reading this have never (or just recently) heard of them.
Let’s start with how to create one (example from BOL):
CREATE SYNONYM dbo.MyProduct FOR AdventureWorks2014.Production.Product;
Simple enough right? You give it a name and point it at an object. You can use a one, two, three or even four-part name for the object you are pointing at. As best I can tell that object can be anything owned by a schema. So tables, stored procedures, views, functions etc.
Simple enough, and it does exactly what it sounds like it does. It creates a new name for the existing object.
-- These will produce the same results SELECT TOP 10 * FROM MyProduct; SELECT TOP 10 * FROM AdventureWorks2014.Production.Product;
So why is that useful? Well, let’s say you use linked servers (we don’t judge here) (except NOLOCK, then we judge hard). Your code has to change at each SDLC (system development life cycle) level.
SELECT * FROM DevServer.AdventureWorks2014.Production.Product; SELECT * FROM TestServer.AdventureWorks2014.Production.Product; SELECT * FROM ModelServer.AdventureWorks2014.Production.Product; SELECT * FROM ProdServer.AdventureWorks2014.Production.Product;
The same thing goes if you have multiple databases on a single instance for different SDLC levels (judging just a little bit here).
SELECT * FROM AdventureWorks2014_Dev.Production.Product; SELECT * FROM AdventureWorks2014_Test.Production.Product; SELECT * FROM AdventureWorks2014_Model.Production.Product; SELECT * FROM AdventureWorks2014_Prod.Production.Product;
By creating different synonyms, with the same name, at each level, your code no longer has to change as you move it from dev, to test, to prod. Just the synonyms, and even then only if the location of the base object changes. So the synonym dbo.MyProduct exists in dev and points to DevServer.AdventureWorks2014.Production.Product, the one in test points to TestServer.AdventureWorks2014.Production.Product etc. And just to point it out, this means if the location of the base object changes, then there is no need to change all of the code, just the synonym.
Now, where it doesn’t work. You can’t schemabind anything with them, you can’t use a synonym as the base for another synonym, you can’t make any DDL changes through a synonym, and you can’t reference them across a linked server. Just to be clear, that means if I’m on a different server I can’t do this:
select * from [(local)\sql2016cs].AdventureWorks2014.dbo.MyProduct;
Cannot process the object “”AdventureWorks2014″.”dbo”.”MyProduct””. The OLE DB provider “SQLNCLI11” for linked server “(local)\sql2016cs” indicates that either the object has no columns or the current user does not have permissions on that object.
Last but not least, permissions etc are handled through the base object.
Handy tool, if somewhat uncommon.
SQL Studies 
We require that all 3rd party stuff installed here uses SYNONYMS instead of any 3 or 4 part naming. Had stuff hardwired by low calibre suppliers (and probably some high calibre ones too!) too many times in the past, and then had the nightmare of trying to sort it all out at some future point when we needed to make some alterations …
Synonyms are indeed quite usefull. Important will however be to realize these are more than an alias-name. All queries created via a synonym will actually be executed via a new connection to the (other) system. This connection can be inside or outside your existing transaction (depending on configuration of your alias).
A new (external) connection will even be made when this synonym is referencing the same database. As a result the performance might be less that joining / querying the tables directly.
Creating a new connection might for example be an option in the situation where you want to write logging inside a transaction, then persist this logging data, even after a roll-back.
By best practice you should see these synonyms like it is a linked server or odbc connection. Try limiting the number of cross-system calls, and expect complexity when joining data from separate sources…
Hmm, I didn’t notice any settings related to the synonyms? I certainly haven’t noticed a new connection when using a synonym on a DB that exists in the current instance? i.e. creating a synonym for DB2.dbo.TableA as TableA within DB1 where both DB1 and DB2 exist in the same instance.
I’ve been using synonyms since they came out. Is is important to note that they don’t only apply to tables and views, I have used them on Stored Procedures as well.
They are very handy for encapsulating code inside the database and eliminating three, or four, part names in queries. They enable you to make databases more easily movable in the event that you have to migrate to a new infrastructure as you only need to redefine your synonyms and all the code works (I make no claims on performance if you are now calling through a linked server however).
Also, in those rare cases where an object needs to be renamed, usually at the insistence of a managed but sometimes third party products that you rely on change between versions, you can create an alias to keep all of your original code working, without resorting to views and the resulting problems of nested views.
However, you must use a lot of discipline when using synonyms. It is way too easy to use them for multiple purposes, and create horrible, confusing, spaghetti of aliases.
Hi,
We use them as means of updating our site. We have a databases that contains all the procedures call it MainDB. then we have two databases with essentially the same data. One of them is the database used by our product the other is used to switch over when we update our data.
There are synonyms in the MainDB and we can simply re-point the MainDB to either of the other databases which takes seconds. The good thing is execution plans are not affected, well we haven’t seen any detrimental effect.
It’s not brilliant but it works.
Graeme
Yea, I’ve heard of people using them that way before. It’s a great usage for it IMHO 🙂
[…] like aliases and synonyms have made this less of an issue but it’s still an important consideration. Long and easy to […]
[…] I use impersonation a lot. It’s a really easy way to check if someone has the permissions they are supposed to. That said, a co-worker recently had an interesting question. They were testing permissions on a synonym. […]