Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mivey4
Yak Posting Veteran
66 Posts |
 Posted - 2010-08-06 : 10:28:36
|
| I was recently asked a question that I really never seriously considered with regard for the use of synonyms.The person asked me "why would you use synonyms"? My simple reply was because it provides a shorthand notation for a multipart name within SQL.I know this answer was correct but then I questioned myself, "is there any other truly valid reason for why one would even consider the use of synonyms?"Anyone have additional reasons for why you would use synonyms in a practical manner other than what I noted?Oracle OCAAdaptec ACSP |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-08-06 : 10:57:10
|
Having a synonym is not just another name, but also a way to secure objects and abstract them from outside consumers.For instance, you can have a synonym named Customers to retrieve customer information. It could reference a table or a view (or user-defined function). As long as the synonym is used, you can change the underlying objects or even implementation (from table to view or vice versa) without impacting the users.When used in conjunction with schemas, you can isolate objects and make them invisible to other users, while making them available via synonyms. There's a good example here:http://msdn.microsoft.com/en-us/library/dd283095(SQL.100).aspxTaken to an extreme, it would be fun to put all tables, views, etc. under a separate schema and the user cannot see them. Nobody looks under the Synonyms.  |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-06 : 11:45:22
|
We use synonyms heavily. It makes moving code from dev to test to acceptance to production seamless.In some cases we have multiple test databases and just changing the target of the synonym is MUCH easier than search and replace a lot of code.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-06 : 11:49:24
|
| We have code that pulls data from external sources into a Staging Database, and then from there to the Live database. So the "name" of the Target / Live database has to be explicit in the code. In the old days we had to hand re-edit everything when we moved from TEST to Production environment just to change the name of the Database. |
|
|
|
mivey4
Yak Posting Veteran
66 Posts |
Posted - 2010-08-06 : 12:38:56
|
| Wow, lots of good points. I guess its true that neccessity is the mother of invention because since I've never been in a work environment that has seen an actual need to implement synonyms; I never fully had a need to explore the extensibility of the option to that degree.But thanks to everyones input here, I can see that there are scenarios where the implementation of synonyms would be of a great asset than simply a shorthand way of referencing objects.Thanks for all your input!Oracle OCAAdaptec ACSP |
|
|
|
|
|
|
|
|
Why use Synonyms?