Synonyms give us an extra layer of abstraction in SQL Server if we don’t want to expose our meta data. Synonyms unlike view can encapsulate tables, view, stored procedures, user defined functions but it cannot abstract other synonyms.
Let us see how synonyms can make life simpler while writing tsql in action:
Let us say we have the following meta data in the AdventureWorks database
If we try to query the employee table as follows we will get an error
SELECT * FROM Employee; |
That means we always have to use the two part name or the table with the schema with the table name as
--table with schema SELECT * FROM HumanResources.Employee; |
If we actually don’t want to expose that we could hide this detail by creating a synonyms as
--=========================== -- Create synonym --=========================== CREATE SYNONYM dbo.Employee FOR AdventureWorks2012.HumanResources.Employee GO |
Now if we query the table as one part name “Employee” it is going to work.
SELECT * FROM Employee; |
Synonyms are more advantageous when we work with long object names and SQL objects with multiple parts such as Linked Servers and CLR functions. There is no alter statement for synonyms so if we have to change them we have to drop and recreate a new one. We could drop a synonym as
--=========================== -- Drop synonym --=========================== IF EXISTS( SELECT * FROM sys.synonyms WHERE name = N'dbo.Employee' ) DROP SYNONYM dbo.Employee GO |
The problem with synonym is there is no schema binding like view that means we could drop a table while having a synonym pointing to it and we could create a synonym for a table that does not exists.
