November 22, 2014
Hot Topics:

Using Synonyms in SQL Server 2005

  • June 14, 2006
  • By Mike Gunderloy
  • Send Email »
  • More Articles »

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



Click here for a larger image.

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.






Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel