Skip Navigation Links
SQL Server 2012
SQL Server 2014
SQL Server 2016
Practice Test
Tip of the Day : Convert Oracle String Functions to SQL Server String Functions
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

A synonym is a database object that servers the purpose of providing an alternative name for another database object, referred to as the base object, that can exist on a local or remote server as well as the purpose of providing a layer of abstraction that protects a client application from changes made to the name or location of the base object.

A synonym belongs to a schema, and like other objects in a schema, the name of the synonym must be unique. References to synonyms are not schema-bound. Therefore, a synonym can be dropped at any time. However, dropping a synonym runs the risk of leaving dangling references to the synonym that was dropped. These references will only be found at run time.

One restriction when creating a synonym is that a synonym cannot be the base for another synonym, otherwise the following error message is generated:

Msg 470, Level 16, State 1, Line 1
The synonym "<Synonym Name>" referenced synonym "<Synonym Name>". Synonym chaining is not allowed.

To overcome this restriction and be able create a synonym that references another synonym indirectly is with the user of a view, as can be seen from the following script:

CREATE SYNONYM [dbo].[OldStudents] FOR [SchoolArchive].[dbo].[Students]

CREATE VIEW [dbo].[OldStudents2]
SELECT * FROM [dbo].[OldStudents]

CREATE SYNONYM [dbo].[Alumni] FOR [dbo].[OldStudents2]

SELECT * FROM [dbo].[Alumni]

This work around for synonym chaining will only work if the base object used in the synonym is either a table or a view.

Back to Tip of the Day List Next Tip