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;

Last but not least, permissions etc are handled through the base object.

Handy tool, if somewhat uncommon.