September 11, 2014 at 2:28 pm
We also use only synonyms when accessing data from a different database. Use of a synonym for objects in a different database is required and the objects can only be stored procs, views or functions. This allows abstraction of the other database so the developers don't need to know the schema of the other DB; it is a black box. If they need data from a different database, they define what they need and a stored proc, or view is created for which they can create a synonym. If they require a query where all the data comes from a different database, usually a stored proc is used. If the query requires data from the main DB and a different DB, then a view is usually created.
September 11, 2014 at 2:32 pm
Wilfred van Dijk (9/11/2014)
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!
I bet that saved a lot of disk space also!
The Redneck DBA
September 11, 2014 at 2:41 pm
As in this same situation, synonyms allowed us to quickly copy live to test and training environments, especially when the environment contains two new servers having to communicate with each other.
September 11, 2014 at 2:56 pm
I can't recall a single environment in the past 10 years I have worked in that allowed OPENQUERY. It was always considered a no no due to security risk....
September 11, 2014 at 3:22 pm
copling (9/11/2014)
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.
I found all of that IntelliSense, error checking, and auto indent to be annoying, so I just have all of that turned off in my environment. My co-workers like to tease me about that, but I've just never gotten used to it all.
The Redneck DBA
September 11, 2014 at 11:11 pm
Wow. I just created my first SYNONYM just a couple weeks ago. What timing. We have a case where we replicate stuff from a couple databases from our OLTP server to our OLAP server. The stuff includes modules like views and functions. In one of the databases there was a view that referenced a table in the other database, and the databases have slightly different names in the two servers, so the cross database call broke in the OLAP subscriber. So I just put the same SYNONYM name in the publisher and subscriber, with the different database names for the referenced object - problem fixed.
I saw the potential for confusion with a synonym. I know whether to use hungarian notation prefixes is one of the eternal debates in the programming world, but the place I work uses them. In my databases I generally use the sys.objects type code as a prefix. So I named the synonym like sn_WhatEver (and tagged on the comment "-- a synonym" since it was something new.
Sincerely,
Daniel
September 12, 2014 at 2:01 am
kenambrose (9/11/2014)
I can't recall a single environment in the past 10 years I have worked in that allowed OPENQUERY. It was always considered a no no due to security risk....
Unfortunately, I happen to work in such no no No NO environment. There is no need - users say - to develop a proper GUI, as we can use DBA tools and write SQL queries against the database. Madness...
September 12, 2014 at 3:23 am
I would be concerned with the example that "hides" the fact that a table is actually on a different server. That could cause huge performance issues if the real location of the table weren't taken into consideration when designing a query.
September 12, 2014 at 3:31 am
nharris (9/12/2014)
I would be concerned with the example that "hides" the fact that a table is actually on a different server. That could cause huge performance issues if the real location of the table weren't taken into consideration when designing a query.
I would say: use with a great deal of good judgment and common sense.
If there are small amounts of data involved and non-frequent queries then it is OK for synonyms to point to some other server.
If there are big amounts of data and/or frequent queries (like lookups) then the synonyms should only be used in "local" context.
The person who takes the decision to implement synonyms should be the one that is also aware of how/when/by whom they will be used.
Then - of course - what is "small" and what is "non-frequent"? 😉
September 12, 2014 at 3:36 am
I like the ones where you only have to use a small amount of judgment and common sense, being a bit short in both departments myself
September 12, 2014 at 4:38 am
I played around with synonyms for a while, then came to the conclusion that they really offer no advantages over views.
So can someone explain how a synonym is superior to a view?
I could be missing something... 😉
September 12, 2014 at 5:39 am
Then there's the way today's small, infrequently-accessed tables can sometimes become tomorrow's big and/or frequently-accessed ones...
😉
September 12, 2014 at 5:51 am
mjbrichards (9/12/2014)
I like the ones where you only have to use a small amount of judgment and common sense, being a bit short in both departments myself
What would life be without some thrill? 🙂
September 12, 2014 at 6:05 am
nharris (9/12/2014)
Then there's the way today's small, infrequently-accessed tables can sometimes become tomorrow's big and/or frequently-accessed ones...😉
Yes, it's why you have to keep pace with the requirements that the system is expected to fulfill.
What you wrote above goes for any data: Today it is OK that the query against a view executes for 5 seconds, because there are only 50 users who look at the data 2-3 times a day. Tomorrow it will not be OK with such slow query, because somebody has decided to implement the query on 100 huge monitor screens that update every 5 seconds with no caching inbetween...
Well, things change. Usage of a set of data can change. So can the access paths. That means that you will have to restructure the way you access data and/or the physical implementation of data containers (tables, views, materialized views = indexed views in MS SS terminology) etc.
The above is one of the reasons why database guys really hate users with their own ad-hoc SQL queries who have tools that enable them to turn those queries into applications with frequent database accesses that hammer the data every few seconds. 🙁
September 12, 2014 at 6:21 am
Lawrence Moore (9/12/2014)
I played around with synonyms for a while, then came to the conclusion that they really offer no advantages over views.So can someone explain how a synonym is superior to a view?
I could be missing something... 😉
A synonym is not superior or inferior to a view, both have different use.
A view is a way to "restructure" data in one or several data containers (tables/views/materialized views) to better suit some specific needs. You can make the data appear as something else by the use of joins, aggregates etc. Accessing data through views gives you some additional overhead of parsing and running the WHERE clauses in the view besides the WHERE clauses in the SELECT that accesses the view. When you have views that build upon views that build upon views then you get the full picture of potential performance problems. Seen it, it was not beautiful.
A synonym is generally a way to put a "local" label for something that generally speaking is not in your usual working scope, e.g. a stored procedure or a table not present in your own database. You need that piece of something and the DBA does not want you to know (or maybe you don't want to know) where it is: you just want to run the code or update the data. Btw, synonyms point to one object at a time, e.g. one table. As a synonym has no WHERE clause of its own, there is not extra overhead from parsing that one.
Viewing 15 posts - 31 through 45 (of 80 total)
You must be logged in to reply to this topic. Login to reply