Simplifying SQL Server 2005 development with synonyms

I. INTRODUCTION

If you have ever used SQL Server to develop any software, you will certainly be accustomed to using the four-part identifier to refer to an object:

　　[[[server.][database].][schema_name].]object_name

As the square brackets above show, different parts of the syntax can be ignored, as long as you provide enough expression to identify your object without ambiguity. For example, all of the following expressions might refer to the same object:

　　Server1.AdventureWorks.Production.ProductCategory
AdventureWorks.Production.ProductCategory
AdventureWorks..ProductCategory
ProductCategory

In most cases, you can use only three parts of the name and ignore the server name-unless you are dealing with objects on a connected server. By default, the context of all objects is a local database-your SQL statement executes in it. However, in some cases, or more accurately, you must use the entire four-part name (or a full qualified name). However, in SQL Server 2005, this situation has changed.

Two. Familiarity with synonyms

SQL Server 2005 introduces the concept of a synonym, which is a one-part name that can be replaced by a two or three-or four-part name in many SQL statements. Using synonyms allows you to reduce input and provide an abstraction layer to help you protect the underlying object's changes. To understand how this works, let's look at the syntax for creating synonyms first. The following is the syntax for the CREATE SYNONYM statement:

　　CREATE SYNONYM [schema_name.]synonym_name FOR object_name

Here, object_name is the name of a SQL Server object that is sufficiently qualified to identify the object, and Synonym_name is the new name you want to assign to it. If you do not want to specify a pattern for synonyms, SQL Server uses the current user's default mode. When you create synonyms, the corresponding objects do not need to exist because the synonyms are late-bound: when you actually use synonyms, SQL Server simply checks the base object.