SQL Server 2005 has introduced synonyms which enable the reference of another object (View, Table, Stored Procedure or Function) potentially on a different server, database or schema in your environment. In short, this means that the original object that is referenced in all of your code is really using a completely different underlying object, but no coding changes are necessary. Think of this as an alias as a means to simplify migrations and application testing without the need to make any dependent coding changes.
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
