SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Synonyms, how and why.

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;
Msg 7357, Level 16, State 2, Line 2
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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...