What is Synonym and how to Create it in SQL Server

Synonym in SQL Server
A Synonym is known as an alternate name that we create for another database object.In General, the Synonym used to simplify and shorten the names of objects with long names, by simply giving the Synonym to that Object fully qualified name.

We can create synonyms for Stored Procedures, Functions, Tables (even for temp tables!), and Views. The base object need not to be exist at Synonym at create time. SQL Server checks for the existence of the base object at run time.

To create a synonym in a given schema, a user must have CREATE SYNONYM permission and either own the schema or have ALTER SCHEMA permission.

Syntax :
CREATE SYNONYM <synonym_name>
FOR <server_name>.<database_name>.<schema_name>.<object_name> 

Scenario:

Suppose if we want to refer a table "tbl_Sales" which resides in the Customer Server > SalesDB , then we need to write that fully qualified name of that object every time in our Queries, that is a little difficult task.

Also if there is any change the Server or Object names, then we need make that change for every instance of occurrence in our Query, which is bit harder for a Developer.

To simplify that , we can create a Synonym which is actually referring to that Object, and we can use that in our Queries.Also, whenever there any change to source, we can simply make change to our Synonym, By Dropping and Recreating.

Example-I : Creating a Synonym for a Table ([tbl_Sales])

Create Synonym syn_tblSales
For [Customer-Server].[SalesDB].[dbo].[tbl_Sales]
GO

Now we can access the data from Target Server(Source) using the Synonym created in our DB, as shown below.

Select * From syn_tblSales

Example-II: Creating a Synonym for a View (vw_SalesByRegion)

Create Synonym syn_vwSalesByRegion

For [Customer-Server].[SalesDB].[dbo].[vw_SalesByRegion]

GO

Example-III: Creating a Synonym for a Function (fn_RollingQuarters)

Create Synonym syn_fnRollingQuarters

For [Customer-Server].[SalesDB].[dbo].[fn_RollingQuarters]

GO

Example-IV : Creating a Synonym for a Procedure(sp_SalesBySegment) 

Create Synonym syn_spSalesBySegment

For [Customer-Server].[SalesDB].[dbo].[sp_SalesBySegment]

GO

Advantage of Synonym:
Some times there may be necessary to rename SQL objects in our databases.  This will be a difficult task if we have a large number of queries that refer to that object. One way to ease that risk of renaming an object is to create a Synonym pointing to that object, and always use the Synonym in your queries from the beginning.  

This will allow you to change object names with a lot less effort.  Instead of hunting around and finding all references to that object and updating it, you simply need to drop and recreate the Synonym, as we cannot alter the Synonyms.

Thanks, TAMATAM