September 10, 2014 at 9:36 pm
Comments posted to this topic are about the item Practical Uses for Synonyms in SQL Server
The Redneck DBA
September 11, 2014 at 2:01 am
Great article Jason.
I think if used in the "correct" way synonyms can be useful as you illustrate here - but I think they need to be documented. Time and time again I go to a place that has had the same dev for years and hasn't documented anything - it's hard enough to pick apart "Russian doll" style implementations of stored procedures, functions and views without adding synonyms to the mix :crazy:
September 11, 2014 at 2:17 am
Not a fan of synonyms. They should only be used when you have a real need, and I don't know what that would be.
"Simplify object names" is according to me not a real need. This can end up unwillingly in obfuscation (the "Hide true object names" option) of your database model. This might be overcome by prefixing the object names, so that you directly see that you are using a synonym.
In none of my projects I use synonyms, besides cases where there were already synonyms.
September 11, 2014 at 3:35 am
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.
September 11, 2014 at 4:06 am
We use a naming standard that prevent the "gotchas" in synonyms: s_<objectname>
That obviously means that we do not simplify the names and we only use it when we are pointing to a third party system's DEV/QA or PROD. We also have a standard "stpSynonym" stored procedure that contains the code to recreate all the synonyms for that database and that makes it easy to switch a system. This is in an interface environment where the one side of the environment only has a production database, and I must admit, this was the only real practical example we found that works for us.
5ilverFox
Consulting DBA / Developer
South Africa
September 11, 2014 at 4:19 am
Synonyms solved a huge problem for me. We are developing a lot of reports in SSRS that will be distributed to different clients. All of the data is accessed via a linked server, so all of the linked server configurations and names are unique for each client.
All of our access is through SQL Server stored procedures. By using synonyms, I can create one version of the stored procedure, which uses the synonym name rather than the client-specific linked server name.
For deployment, we run a simple script that generates the synonyms, using the values for the client-specific linked server.
September 11, 2014 at 4:31 am
As a wierd coincidence, I'm giving a demo today on synonyms to my team, as they're going to have to start using them.
I use them for every access external to the current database. This means I can have only 2 part names in the current database (and I can and do enforce this.) As a result, to view inter-db dependencies I can just look at my synonyms.
Also it can be useful when in, for example, a dev environment you don't have all databases that your database links to. By modifying the synonym you can point these to tables in a sandbox etc.. so that you can run a 'standalone' version of the application.
Finally, you can 'swap out' a table, replacing it with another, by modifying the synonym to point elsewhere.
On the whole, I'm a fan, but can understand it's not everyone's cup of tea. 😀
David.
September 11, 2014 at 5:08 am
As Mighty pointed out, there no situations where synonyms are an unavoidable necessity.
I myself come from a different environment, which the Oracle side of data storage. 🙂 In Oracle, synonyms have been part of the database for many many years now and I have seen the upsides and downsides of using synonyms.
One of the most common uses for synonyms - in my case - is to hide the data (security by obfuscation). Please look at the following example:
- we have a schema that I usually call "container schema". That schema contains all the data tables and all the stored procedures used to operate on the data
- users that access the data will not be allowed to edit or read tables directly (for a plethora of varying reasons), but they will have to use the stored procedures as API against data
- users the need the API get the API procedures exposed into their schema with the help of synonyms
- users get execution rights on the underlying procedural objects
- thus, users make "local" calls inside their schema and think they work locally without being tempted to explore stuff they do not see at all. That is because they actually call synonyms for stored database code.
I know that the above may not be applicable to a SQL Server database, but that can maybe give some ideas to a "proper" SQL Server guy.
Another use is, as the article pointed out, to simplify data access by just constructing a "local" and "simple" name for an object that otherwise would have to be accessed by complex and dotted notation, e.g. a set of data tables. I situations when you have many users writing their own ad-hoc queries and even updates to predefined data containers it is a way to simplify their life.
If you replace synonyms by views, it might still work. But there is a penalty: one is the question of privileges and access rights. Second is the handling of an extra query used to construct the view instead of a simple object name translation. And the third: can you update views in SQL Server?
There is a serious downside of synonyms. Recently we had an big outsourcing of IT done at my previous employer. And all of a sudden we had a bunch of new guys that were constructing a new set of reports for one of the systems. The tool - or maybe the guys - were not even remotely aware of the concept of synonyms, so whatever they tried in a regular SQL tool worked. But when they tried to design their queries in drag-and-drop environment, nothing worked as the tables were not there. They were getting out of their senses trying to find their precious data and I was getting mad trying to convince them to actually _w_r_i_t_e_ queries instead of drag-and-drop.
Well, that was my penny on the subject.
September 11, 2014 at 5:59 am
My 2 cents:
1. The point about very unuseful object names is meant for the case when you don't have control over the object names, such as when working in a vendor's database product. I really don't want to have to memorize what table FA102 is. I think that table name makes things awefully complicated, but I can only control what I can control and so use my own name for it. I just have to make sure I document it AND communicate it to my team. (might turn out to be a bonus learning experience for them)
2. You can actually use a synonym as kind of a global string-valued variable. What is assigned to the synonym is a pointer - the name of the object. As the author stated, it doesn't matter whether the object exists, ie what the value of the synonym is, when it is defined. The system assumes the value will be relevent at runtime. The bonus is that you can control access to the "variable" all the way up to the server level. Same as above - be certain it is documented and communicated to your team.
September 11, 2014 at 6:41 am
If only there were synonyms for column names!
September 11, 2014 at 7:26 am
I use synonyms to refer to any object outside of the database that I'm working in. They make for a useful way to encapsulate an object that I otherwise have no control over. If a database moves or a foreign object gets renamed, I just update synonyms and move on. They are also very useful for moving between dev and prod environments where external objects may be in different locations.
I also keep this query at hand for handling synonym changes:
SELECT
synonym_schema = c.name,
synonym_name = s.name,
server_name= COALESCE(PARSENAME(s.base_object_name,4),@@servername),
database_name=COALESCE(PARSENAME(s.base_object_name,3),DB_NAME(DB_ID())),
schema__name=COALESCE(PARSENAME(s.base_object_name,2),SCHEMA_NAME(SCHEMA_ID())),
object__name=PARSENAME(s.base_object_name,1),
drop_statement = 'drop synonym '+ c.name+'.'+s.name+';',
create_statement = 'create synonym '+ c.name+'.'+s.name+ ' for '+
COALESCE(PARSENAME(s.base_object_name,4),@@servername)+'.'+
COALESCE(PARSENAME(s.base_object_name,3),DB_NAME(DB_ID()))+'.'+
COALESCE(PARSENAME(s.base_object_name,2),SCHEMA_NAME(SCHEMA_ID()))+'.'+
PARSENAME(s.base_object_name,1)+';'
FROM sys.synonyms s
INNER JOIN sys.schemas c ON s.schema_id=c.schema_id
September 11, 2014 at 7:49 am
martin.fay (9/11/2014)
If only there were synonyms for column names!
There kind of are... create a pass-through view, which is what I used until synonyms came out.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2014 at 8:00 am
We have a reporting system that contains nightly snapshots of our core system. These SQL databases are named with a datestamp, so each day the name of the current db is changing. In this case synonyms allow us to avoid the use of dynamic sql in stored procs and provide for much more readable code.
September 11, 2014 at 8:15 am
I've used them for years anytime I was accessing an object outside the database. That makes it very easy to find/handle dependencies when the db gets moved.
September 11, 2014 at 8:17 am
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???
Viewing 15 posts - 1 through 15 (of 80 total)
You must be logged in to reply to this topic. Login to reply