17

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

Chad Grant
  • 44,326
  • 9
  • 65
  • 80

3 Answers3

15

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.

John Sansom
  • 41,005
  • 9
  • 72
  • 84
  • we can get the list of tables by quering `SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES` same way >How can i get the list of synonyms using query??? – RSK Oct 05 '10 at 06:50
  • select schema_name(schema_id) + '.' + name from sys.tables select schema_name(schema_id) + '.' + name, * from sys.procedures select schema_name(schema_id) + '.' + name from sys.views – Sage May 04 '11 at 20:25
  • 1
    select schema_name(schema_id) + '.' + name, base_object_name, modify_date from sys.synonyms – Glen Little Oct 29 '13 at 15:27
6

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.

SQL Server Table Synonyms with Indexes

Community
  • 1
  • 1
Jarred Froman
  • 171
  • 2
  • 8
4

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.

wire science
  • 401
  • 2
  • 4