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:
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:
- 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:
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
To create a synonym, you need
CREATE SYNONYM permission.
After a synonym has been created, it has its own
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
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
whenever you want to change a synonym; there's no
SYNONYM statement. Figure 2 shows the result of running this batch
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.