Journey to SQL Authority with Pinal Dave

One of my friend and extremely smart DBA Jonathan from Las Vegas has pointed out nice little enhancement in T-SQL. I was very pleased when I learned about SYNONYM feature in SQL Server 2005.

DBA have been referencing database objects in four part names. SQL Server 2005 introduces the concept of a synonym. A synonyms is a single-part name which can replace multi part name in SQL Statement. Use of synonyms cuts down typing long multi part server name and can replace it with one synonyms. It also provides an abstractions layer which will protect SQL statement using synonyms from changes in underlying objects (tables etc).

Create Synonyms :
USE AdventureWorks;
GO
CREATE SYNONYM MyLocation
FOR AdventureWorks.Production.Location;
GO

Use Synonyms :
USE AdventureWorks;
GO
SELECT TOP 5 *
FROM MyLocation;
GO

Drop Synonyms :
USE AdventureWorks;
GO
DROP SYNONYM MyLocation;
GO

Synonyms can be created on only following objects.

• 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
• Table (User-defined)

Additionally SYNONYMS can be used only to change data of object not the schema of the object. SYNONYMS can be used with only SELECT, UPDATE, INSERT, DELETE, EXECUTE commands.

Following is image demonstrates use of SYNONYMS.

An example of the usefulness of this might be if you had a stored procedure on a Users database that needed to access a Clients table on another production server. Assuming you created the stored procedure in the database Users, you might want to set up a synonym such as the following:
USE Users;
GO
CREATE SYNONYM Clients
FOR Offsite01.Production.dbo.Clients;
GO

Now when writing the stored procedure instead of having to write out that entire alias every time you accessed the table you can just use the alias Clients. Furthermore, if you ever change the location or the name of the production database location all you need to do is modify one synonym instead of having to modify all of the stored procedures which reference the old server.

Reference : Pinal Dave (http://blog.SQLAuthority.com), Jonathan (Las Vegas), BOL – SYNONYMS