Category Archives: Synonyms

Synonyms in SQL Server to give short name to your fully qualified object name

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

Replication-filter-procedure

Extended Stored Procedure

SQL Scalar Function

SQL Table-valued Function

SQL Inline-table-valued Function

SQL Stored Procedure

View

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:

SynonymsOutput

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.