Practical Uses for Synonyms in SQL Server

Question

Practical Uses for Synonyms in SQL Server

Viewing 15 posts - 16 through 30 (of 80 total)

You must be logged in to reply to this topic. Login to reply

Mad Myche SSCommitted Points: 1593 More actions · Answer 1

martin.fay (9/11/2014)
If only there were synonyms for column names!

I would think that is where views come in. Yes you could call the view directly but the synonym allows you to use a very descriptive name reflecting the source tables the synonym can simplify it greatly

Director of Transmogrification Services

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 2

Nice, short, sweet, and well written article on the subject. Even some of the "old hands" at SQL Server still don't about them or what they'd be used for.

We use synonyms a fair bit for a lot of the reasons mentioned both in the article and posted on this discussion.

We enforce a very strict 2 part naming convention for some of the reasons (changing environments) that bob.probst and others posted. It also makes working with linked servers a whole lot easier.

One of the things that I didn't see in the article or the discussion that we use synonyms for is for when we load data from one system to another. We need to keep the original data online and active until the new data has been loaded and validated so we have two tables and flop a synonym between them, as needed. For example, TableA is online and being used. The synonym points to TableA. That allows us to truncate TableB, load it, clean it, validate it, etc. When we're done, we simply repoint the synonym to TableB and we're done. The data is never offline because the synonym repointing won't actually occur until no one using TableA. There's usually not much of a delay there. Next time around, TableB is online and we do the load on TableA. When done, we just flop the synonym back over to TableA. This also allows us to keep the original table available in the background just in case something unexpected happens to the new table and it allows us to easily do a comparison for what changed between yesterday's table and today's.

As for people getting confused as whether something is a table or a synonym, we don't actually have that problem because everyone is aware that synonyms are used for different reasons and know to look just like they know to look for other "hidden" objects such as triggers. Since there are times where a table might be in a different database in one environment and in the same database in another, we don't do anything insofar as naming to distinguish synonyms from tables from views.

Another use that we have is for quick disaster prevention of sorts. To streamline backups, we in the process of moving some huge audit tables to a different database. Yeah, we're partitioning them using multiple file-groups so we can do "piece-meal" restores, as well, but we're taking one extra precaution. We have an empty table defined in the main database so that if the "audit table database" ever goes down, we can continue to do business and not lose any audit info by simply repointing the synonym for the audit table to the local empty table until we can get the audit database back on its feet. It also works out real well for making a copy of the main database for other environments because we don't have to copy a hundred GB of audit data that won't be used to the Dev or Staging environments.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 3

waldodj2000 (9/11/2014)
What you think about synonyms vs views?
In a view i do: SELECT * FROM server.db.dbo.table
and then I use SELECT * FROM myVIEW
In a Synonyms i do the same.
what is the difference between the two methods???

I do the same except I never use SELECT * in the views. If someone decides they're going to drop a new column somewhere in the logical "middle" of the table columns, the view won't warn you that it has shifted data in the columns. You could end up with (as an over simplified example) the underlying data of a CreatedOn date column silently being moved over to a ModifiedOn column and there will be no error reported. Unless you have strict control over such things and you're guaranteed to include code to rebuild a SELECT * view, it's much safer to list all column names in the view. You might not get all the columns you want if you forget but at least you won't be getting silently incorrect data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Rueter SSCrazy Points: 2661 More actions · Answer 4

Brilliant! Yes, synonyms for vendor-provided tables.

In fact, a public repository of scripts to create synonyms for common applications could be a great thing:

Working with Great Plains? Run the appropriate script to create synonyms so that reports and integration could access synItemMaster instead of IV00101.

JD Edwards? Run the appropriate script to create synonyms so that reports and integration could access synItemMaster instead of F4101.

Of course this won't allow the same query or report to run across both systems (as the actual table definitions are different), but it would definitely be a time-saver being able to work with meaningful object names without having to look up cryptic vendor-specific names all the time.

waldodj2000 SSC Enthusiast Points: 174 More actions · Answer 5

Jeff Moden (9/11/2014)

waldodj2000 (9/11/2014)
What you think about
I do the same except I never use SELECT * in the views.

Yes, I don´t use SELECT *, only post here for the example

SQLEmil SSC Enthusiast Points: 188 More actions · Answer 6

As someone else mentioned, synonyms are fantastic for testing, as it makes it a lot easier to stub out tables. I'm actually going to work with that today to avoid a cross-database reference in a unit test and instead stand up a stub table with the same schema.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 7

mjbrichards (9/11/2014)
One of the most useful things about synonyms that we've found is that using a synonym stops SQL Server escalating to MSDTC (with its associated performance overhead) when you are referring to objects in another database. Obviously, this needs to be approached with care, but it's a very useful feature in the right context.

I would have never though of that. Interesting!

The Redneck DBA

lebraix SSC Enthusiast Points: 192 More actions · Answer 8

David Rueter (9/11/2014)
Brilliant! Yes, synonyms for vendor-provided tables.
Working with Great Plains? Run the appropriate script to create synonyms so that reports and integration could access synItemMaster instead of IV00101.

This is the exact use case I was thinking of as I was reading the article. My disdain for Great Plains table names never goes away, I just learn to cope...

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 9

waldodj2000 (9/11/2014)
What you think about synonyms vs views?
In a view i do: SELECT * FROM server.db.dbo.table
and then I use SELECT * FROM myVIEW
In a Synonyms i do the same.
what is the difference between the two methods???

That's a question I sometimes ask myself when creating synonyms. It's one of those cases where then answer probably depends on who you ask more than anything else to be honest.

One main difference that jumps out at me is a synonym can be created to point to an object that doesn't exist, where with a view the object has to exist.

Certainly agree with Jeff's comments about avoiding the "Select *" in the views though.

The Redneck DBA

David Rueter SSCrazy Points: 2661 More actions · Answer 10

Jason Shadonix (9/11/2014)
One main difference that jumps out at me is a synonym can be created to point to an object that doesn't exist, where with a view the object has to exist.

Of course a view can include WHERE clauses, joined tables, can return columns with aliased names in the resultset, can be indexed, and more.

But in the simplest case of a view that returns all columns and all rows of a single table with no permissions changed on the view, I guess a view and a synonym could accomplish similar things.

copling Say Hey Kid Points: 696 More actions · Answer 11

Great article; however, I can't really think of need to use Synonyms for my line of business at this moment. However, I do have 1 question. Is there a way to remove the squiggle line on the synonym name? It gives me a false impression that the name of the synonym is wrong.

Nadrek SSC-Insane Points: 20039 More actions · Answer 12

For use inside a database, or even cross-database, I can absolutely see the use for synonyms (once you handle cross-database permissions, of course).

For cross-instance work, such as was presented in the example, I would be extremely concerned about performance issues caused by obfuscation, for instance:

SELECT X, Y

FROM S.Table1 T1

INNER JOIN S.Table2 T2

ON T1.A = T2.A

INNER JOIN S.Table3 T3

ON T3.B=T2.B

where it actually ends up that T2 and T3 are on a different instance, and the T2 to T3 (indexed) join is actually critical to reduce the total number of rows in play.

In this case, a much more efficient query would use OPENQUERY (or similar) to only pull back the relevant results of the T2 and T3 join to the local server, joining that much smaller dataset to T2, and incidentally causing less load on both servers and the network, as the T2, T3 join can use indexes to generate an optimal query plan on the remote server, and then only the required results are sent over the network to the local server, which then has less data to work with for the T1 to (T2+T3) join.

kenambrose SSCrazy Points: 2513 More actions · Answer 13

one technical limitation: I found out the hard way you can't truncate a table if referencing it through a synonym...

bob.probst+sqlservercentral SSC-Addicted Points: 412 More actions · Answer 14

Nadrek (9/11/2014)
For use inside a database, or even cross-database, I can absolutely see the use for synonyms (once you handle cross-database permissions, of course).
For cross-instance work, such as was presented in the example, I would be extremely concerned about performance issues caused by obfuscation, for instance:
SELECT X, Y
FROM S.Table1 T1
INNER JOIN S.Table2 T2
ON T1.A = T2.A
INNER JOIN S.Table3 T3
ON T3.B=T2.B
where it actually ends up that T2 and T3 are on a different instance, and the T2 to T3 (indexed) join is actually critical to reduce the total number of rows in play.
In this case, a much more efficient query would use OPENQUERY (or similar) to only pull back the relevant results of the T2 and T3 join to the local server, joining that much smaller dataset to T2, and incidentally causing less load on both servers and the network, as the T2, T3 join can use indexes to generate an optimal query plan on the remote server, and then only the required results are sent over the network to the local server, which then has less data to work with for the T1 to (T2+T3) join.

A completely valid concern. I prefix my synonyms so that I know what I'm working with.

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 15

We had an application which installed a database for every company defined in our organization. Each database had a postalcode table which was updated several times a year. So I dropped all the postalcode tables (except in one database 😛 ) and made a synonym in the remaining databases. Saves a lot of time!

Wilfred
The best things in life are the simple things