I've been doing a lot of DB refactoring lately and synonyms have come in incredibly useful. When I originally put in the synonyms I was thinking they would be very temporary while I refactor. Now I am thinking there might be some good reasons to keep some of these synonyms around.
Has anyone used them as full blow abstraction layer?
What are the performance costs?
Any gotchas with indexes?
Tips or Tricks?
My first question, so please be gentle.
Thanks
As a synonym is an abstraction/alternative name for an already existing database object, in the case of a table, index behaviour is identical to that of the underlying object i.e. when execution plans are generated, the same plan is generated irrespective of using the table name or corresponsing synonym.
Yes, synonyms can be used as an abstraction layer, or layer of indirection. For instance, if you need to access objects in an external database where the actual database name will not be known until runtime. You can write your sql referring to objects by synonym name, and then dynamically create the synonyms later.
There are no index gotchas: if the synonym refers to a table or indexed view, then whatever indexes are defined on those objects are in play.
Performance should be the same as explicitly referring to the object by fully-qualified name.
Actually, I have come across a gotcha when using indexes.... I'm not sure if there is a way to create related posts on this site, but here is the link to my issue with synonyms and table indexes.
http://stackoverflow.com/questions/1362916/sql-server-table-synonyms-with-indexes