Database Using Synonyms in SQL Server 2005

Using Synonyms in SQL Server 2005

If you’ve been developing with SQL Server for any length of time,
you’re undoubtedly used to referring to objects with four-part
identifiers:

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

As the square brackets show, different parts of this syntax can be
omitted, as long as you supply enough to unambiguously identify what
you’re talking about. For example, all of these might refer to the same
object:


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

For the most part, you can get by with three-part names, omitting the
server name – unless you’re dealing with objects on a linked server. By
default, the context of all objects is the local database where your SQL
statements are executing. But sometimes, to be precise, you have no choice
but to use the four-part name (otherwise known as a fully-qualified
name
. Or do you? In SQL Server 2005, the story changes a bit.

Enter the Synonym

SQL Server 2005 introduces the concept of a synonym: a
single-part name that can replace a two-, three-, or four-part name in
many SQL statements. Using synonyms lets you cut down on typing (always a
welcome advance for developers!) and can also provide an abstraction layer
to protect you against changes in underlying objects. To understand how
this works, let’s start with the syntax for creating synonyms. Not
surprisingly, this is the CREATE SYNONYM statement:


CREATE SYNONYM [schema_name.]synonym_name FOR object_name

Here object_name is the name of a SQL Server object (specified
fully enough to identify the object) and synonym_name is the new
name that you want to assign to it. If you don’t specify a schema for the
synonym, then SQL Server uses the current user’s default schema. The
object doesn’t need to exist when you create the synonym, because synonyms
are late bound: SQL Server only checks the base object when you actually
use the synonym.

For example, in the AdventureWorks sample database you can create a
synonym this way:


CREATE SYNONYM ProdCat
FOR AdventureWorks.Production.ProductCategory

Having done this, you can now proceed to use the synonym in SQL
statements. For example:


SELECT * FROM ProdCat

Figure 1 shows the results

When you’re done with a synonym, you can get rid of it using the
DROP SYNONYM statement, which has the syntax you expect:


DROP SYNONYM [schema_name.]synonym_name

A Few of the Details

Naturally, there are some limits to synonym use. For starters, you
can’t create a synonym for just anything. SQL Server 2005 contains more
objects than ever before, but synonyms are limited to a core set of the
most useful objects:

  • Tables
  • Views
  • Stored procedures
  • CLR stored procedures
  • CLR functions
  • Replication filter procedures
  • Extended stored procedures
  • SQL scalar, table-valued, and inline table-valued functions

There are also limits on where you can use synonyms. Generally
speaking, you can use a synonym in these T-SQL statements:

  • SELECT
  • sub-selects
  • UPDATE
  • INSERT
  • DELETE
  • EXECUTE

If you think about it, that second list shouldn’t be too surprising: it
encompasses the most common statements where you’d use the objects from
the first list. Note that you cannot reference a synonym in a
DDL statement. If you want to use ALTER TABLE to change
something about the ProductCategory table, for example,
you need to work with the base table, not with the
ProdCat synonym.

To create a synonym, you need CREATE SYNONYM permission.
After a synonym has been created, it has its own
associated GRANT, DENY, and REVOKE
permissions.

Synonyms as an Abstraction Layer

Besides saving you typing, synonyms can also serve as an abstraction
layer over unstable or frequently updated portions of your database.
That’s because the bindings between a synonym and its base object are only
checked at runtime, not at design time.

To illustrate this, examine what happens if you redefine the
ProdCat synonym:


DROP SYNONYM ProdCat
CREATE SYNONYM ProdCat
FOR AdventureWorks.Production.ProductCategory
SELECT * FROM ProdCat
DROP SYNONYM ProdCat
CREATE SYNONYM ProdCat
FOR AdventureWorks.Production.Culture
SELECT * FROM ProdCat

Note that you need to use DROP and CREATE
whenever you want to change a synonym; there’s no ALTER
SYNONYM
statement. Figure 2 shows the result of running this batch
of statements.

Redefining a synonym

Note the very different results from the second execution of
SELECT * FROM ProdCat, because the synonym was redefined to
refer to a different table in the interim.

You can take advantage of this behavior in your own applications. For
example, suppose you’ve got an application that needs to analyze customer
data from a variety of different databases. Rather than linking and
unlinking the base tables from the individual databases, or writing
complex SQL that selects the tables to analyze, you could use synonyms.
Define a synonym such as DataTable to refer to the four-part name of the
data that you want to work with, and redefine it whenever you want to
switch source data.

Of course, this late binding is a two-edged sword. Because SQL Server
doesn’t track whether a synonym points to anything, it also doesn’t do any
sort of schema binding. If you drop a synonym that’s used in another
statement, you won’t find out that anything is wrong until you try to run
that statement.

File Under “Nice to Have”

Could you develop your next SQL Server application without synonyms?
Sure. They don’t bring anything fundamentally new to the table; you could
always use fully-qualified names anywhere that you could use a synonym.
But sometimes it’s nice to have these little amenities in the product,
just because they make for less typing and an easier development
experience. Perhaps synonyms will only save you ten minutes over the
course of an entire application, but still, those little ten-minute
savings add up. Let’s hope Microsoft keeps delivering nice little
improvements like this along with the big headline features.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the Senior Technology Partner for Adaptive Strategy, a
Washington State consulting firm. When
he’s not writing code, Mike putters in the garden on his farm in eastern
Washington state.

Latest Posts

Related Stories