Synonyms SQL Server – Exam Tips

synonyms

You will face a few synonym related questions in the SQL Server exams so it’s a good idea to ensure that you know some of the key facts about them, and what differentiates them apart from other database objects.

Synonyms SQL Server 70-461 Tips

Some quick but important things to remember about synonyms which may pop up in the 70-461 exam.

  • Synonyms always require a schema name
  • They cannot match the names of other objects in the database. E.g if you have a table called dbo.users you can’t have a synonym by the same name.
  • You cannot chain synonyms, so one synonym cannot refer to another, yet it can refer to a view.
  • A synonym can only refer to ONE object. (think of it like another name for something)
  • The object name the synonym is referring to, does not actually have to exist. This can be perfect if you are developing an application, and the tables do not exist yet
  • You cannot alter a synonym, you must DROP and CREATE it

In this instance we will create an example of a synonym that references a temporary table.


-------------------------------------------------

USE DBAFire_DB

CREATE SYNONYM MyTempTable FOR ##mytemptable

SELECT * FROM MyTempTable

--Msg 5313, Level 16, State 0, Line 1
--Synonym 'MyTempTable' refers to an invalid object.

CREATE TABLE ##mytemptable (id int)

SELECT * FROM MyTempTable

-------------------------------------------------

Using SQL Server Synonyms In The Real World

I have seen these used in an enterprise database as a replacement for replication across databases.

In the initial design of the db in question, a table was used in one database for inserts/updates/deletes etc, and a replicated version of the table used as a read only on the another database.

The replicated table was easily replaced with a synonym, with no impact to the application and was a much more stable solution than replication.

For more information on sysnonyms check out BOL.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.