Synonyms, how and why.

Kenneth Fisher, 2018-02-26 (first published: 2018-02-15)

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads