Using the Server Explorer to View Synonyms

One of the more useful (and unsung) features of SQL Server 2005 is the ability to define Synonymswhile the Server Explorer can't create Synonyms, it can view the Synonyms defined in a selected database. Apparently, Oracle has supported Synonyms for some time, but this feature is more than just another "check-off" item. Synonyms permit developers to create an abstract layer over schema-owned database objects like Tables, Views, stored procedures, and user-defined and CLR Functions.

In earlier versions of SQL Server, developers often created Views to alias Tables, but this approach does not work for Functions or stored procedures. No, Synonyms can't be used to alias lower-level objects (like columns) or higher-level objects like databases, but a Synonym can be used to dereference (alias) local SQL Server objects. You can dereference objects on remote SQL Servers as wellassuming you've already set up the remote server as a linked server. For example, to reference a remote database table, you can set up the linked server (a one-time operation) and code a T-SQL query to reference the remote SQL Server database Table (as shown in Figure 4.74).

Figure 4.74. Add a linked server and reference it in a SELECT query.

In the query, the FROM clause references the Authors table on a remote (linked) server. To make coding easier, you can create a Synonym to address the linked server table, as shown in Figure 4.75.

Figure 4.75. Creating a Synonym to address a linked server's table.

Once the Synonym is created, you can code your T-SQL to reference the linked server's table via the Synonym (as shown in Figure 4.76).

Figure 4.76. Referencing a table via its Synonym.

Sure, you can create Synonyms for other objects, such as stored procedures, user-defined (and CLR) Functions, and Views.