Synonyms in SQL Server to give short name to your fully qualified object name
Synonyms is not a new enhancement in SQL Server 2012, it is long back introduced in SQL Server 2005 but I have seen many SQL Developer doesn’t know this feature so thought to give some light to this concept.
As per MSDN, A synonym is an alternative name for a schema-scoped object. In SMO, synonyms are represented by the Synonym object. The Synonym object is a child of the Database object. This means that synonyms are valid only within the scope of the database in which they are defined. However, the synonym can refer to objects on another database, or on a remote instance of SQL Server.
The object that is given an alternative name is known as the base object. The name property of the Synonym object is the alternative name given to the base object.
Synonym can be created on following:
Assembly (CLR) Stored Procedure
Assembly (CLR) Table-valued Function
Assembly (CLR) Scalar Function
Assembly Aggregate (CLR) Aggregate Functions
Extended Stored Procedure
SQL Scalar Function
SQL Table-valued Function
SQL Inline-table-valued Function
SQL Stored Procedure
User Defined Table (Including local and global temporary tables)
You can alter data of object via synonyms but you can’t alter schema of object via synonym. For example I have table named “AdventureWorks2012.Sales.SalesPersonQuotaHistory” in Adventureworks2012 database and I give synonyms “SalesPersonQuota” then I can execute any DML commands (INSERT / UPDATE / DELETE) on “AdventureWorks2012.Sales.SalesPersonQuotaHistory” table but I can’t Add/Remove column or change datatype of column in “AdventureWorks2012.Sales.SalesPersonQuotaHistory” table.
Nowadays, we used to create proper schema for each object and detail oriented name to object which makes object name longer so it is good to give short but meaningful name to object to increase productivity (though we have intelisense now).
By providing synonyms name to developer for use in their script, you are also ensuring that they will not be able to change definition of the object via synonyms (though we can handle this with proper login/user permission). This is one more advantage as per me. Each business process and environment has their own rules and regulation to apply. We may have debate on whether this is useful or not as I am sure few people would appreciate the usage of synonyms whereas few will stand against it. Personally I would like to use synonyms for the benefit I just explained.
Let us see how we can create and use synonyms. I will use “Adventureworks2012” database for this demonstration and table would be “AdventureWorks2012.Sales.SalesPersonQuotaHistory”.
USE AdventureWorks2012 GO CREATE SYNONYM SalesPersonQuota FOR AdventureWorks2012.Sales.SalesPersonQuotaHistory GO
Now if we query original table or query synonyms, we will get same results, let us confirm it.
SELECT TOP 10 * FROM AdventureWorks2012.Sales.SalesPersonQuotaHistory GO SELECT TOP 10 * FROM SalesPersonQuota GO
I have received the same output. Have a look:
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.