The use of Synonyms

We know synonym is an alternate name for an object. If you want to know more about synonym then please visit my blog : http://sqlreality.com/blog/ms-sql-server-2008/synonyms-in-sql-server/

Here I will share some scenario where synonym helps in writing efficient coding.

1.       In most cases the development, testing and production server are different. In testing environment suppose you are using a table in your stored procedures from a linked server’s TestServer. Take the table’s 4 part name is TestServer.Sales.dbo.Employee. While for UAT you need to use the dbo.Employee table from another server take it UATServer and in production the server would be ProdServer.

Here while development you would use table TestServer.Sales.dbo.Employee, during UAT you would have to change the table name to UATServer.Sales.dbo.Employee and while moving into production table name would be changed to ProdServer.Sales.dbo.Employee. Replacing the name repetitively is a wastage of effort and increases the possibility of errors.

Here synonym can solve the issue at great extent. You just create a synonym as below:

CREATE SYNONYM Employee FOR TestServer.Sales.dbo.Employee

And in the coding in stored procedure, views and functions use the synonym Employee at the place of 4 part table name.  After this when you need to test the code in UAT just change the synonym to new server as below:

DROP SYNONYM Employee

GO

CREATE SYNONYM Employee FOR UATServer.Sales.dbo.Employee

In the same way the synonym can be changed to point the production server. This way a long and error prone process of find & replace table name has been eliminated with few lines of code.

NOTE: There is no ALTER SYNONYM statement to change the base object. You will have to drop and re-create the synonym.

So if there is any possibility of change the server, database, schema or object name at later time, synonym can be used to simplify the code change process.

2.       Repetitive use of 4 part name in t-sql statements makes the coding complex & harder to write & read. For example you use a table TestServer.Sales.dbo.Employee as below:

SELECT TOP 5 Emp.EmpName, Mng.EmpName 

FROM TestServer.Sales.dbo.Employee Emp INNER JOIN 

(SELECT EmpId, EmpName FROM TestServer.Sales.dbo.Employee) Mng

ON Emp.MngID = Mng.EmpID

If you create a synonym as below:

CREATE SYNONYM Employee FOR TestServer.Sales.dbo.Employee

The above statement can be replaced with below one.

SELECT TOP 5 Emp.EmpName, Mng.EmpName 

FROM Employee Emp INNER JOIN 

(SELECT EmpId, EmpName FROM Employee) Mng

ON Emp.MngID = Mng.EmpID

Here you can find the difference in the easiness while writing and reading the statement.