SQL Server - Synonyms Tips & Tricks?

sql-server performance database-design synonym
Chad Grant picture Chad Grant · Apr 28, 2009 · Viewed 12.9k times · Source

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

Answer

John Sansom picture John Sansom · Apr 28, 2009

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.

Related questions

Why isn't index likely to do much if it's DATETIME or DATETIME2 because they include the time portion?

Comments to question "How to decrease response time of a simple select query?" tell: "What is the data type on LaunchDate? An index isn't likely to do much if it's DATETIME or DATETIME2 because they include the time portion – OMG …

Read More
LIKE vs CONTAINS on SQL Server

Which one of the following queries is faster (LIKE vs CONTAINS)? SELECT * FROM table WHERE Column LIKE '%test%'; or SELECT * FROM table WHERE Contains(Column, "test");

Read More
What do Clustered and Non clustered index actually mean?

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was : A clustered index is a special type …

Read More