Synonyms, how and why.

1

February 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;
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.

One thought on “Synonyms, how and why.

  1. Kris says:

    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 …

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,163 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: