SQL Server SYNONYM

First introduced in SQL Server 2005.

SQL Server SYNONYM's can be very useful and can be created for

• Tables
• Views
• Assembly Stored Procedures, Table Valued Functions, Aggregations
• SQL Scalar Functions
• SQL Stored Procedures
• SQL Table Valued Functions
• SQL Inline-Table-Valued Functions
• Local and Global Temporary Tables
• Replication-filter-procedures
• Extended Stored Procedures

Benefits:

• SYNONYMs provide a layer of abstraction over the referenced object
• Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.
• Provides flexibility for changing the location of objects without changing existing code.
• SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.
• SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.

Limitations:

• SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.
• Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.
• Obviously consumes possible object names, as you can not create a table with the same name of a synonym
• The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.
• SYNONYM can not be referenced in a DDL statement

If we use this option frequently, then keping in mind the facts that:

• you can delete a SYNONYM without getting any warning that it is being referenced by any other database object
• the object for which the SYNONYM is being created is checked at run time. It is not checked at creation time.
• It makes it imperitive that we should plan and monitor synonyms bit more than other objects/references in SQL Server.
• An additional limitation is that you can't use TRUNCATE TABLE on a synonym.
• The SSIS Data Profiling task cannot see synonyms

SQL Server synonym links:
http://www.mssqltips.com/sqlservertip/1576/benefits-and-limitations-of-using-synonyms-in-sql-server-2005/
http://connect.microsoft.com/SQLServer/feedback/details/311079/expand-synonym-to-other-entities-database-linked-server
http://msdn.microsoft.com/en-us/library/ms187552.aspx