Cut development time with SQL Server 2005's synonyms

by Tim Chapman | Apr 09, 2007 5:06:00 PM

Tags: Storage, Databases, Microsoft SQL Server 2005, Tim Chapman, Tim Chapman, SalesHistory, synonym, SalesHistory table, database, Microsoft SQL Server, SQL Server Newsletter

Takeaway: SQL Server 2005's synonyms allow you to give an alias to an already existing object. Realize the benefits of synonyms by using them as a layer of abstraction between the underlying objects and the synonym.

A synonym is a new object to SQL Server 2005. It is a way to give an alias to an already existing object. For example, if you have a table named SalesHistoryFromArchiveFiscalBusinessYear2005, you could create a synonym named Sales05 that points to that object. This means that instead of writing this query:

SELECT * FROM SalesHistoryFromArchiveFiscalBusinessYear2005

you could write the query like this:

SELECT * FROM Sales05

Benefits

The previous example demonstrates how synonyms can ease database development by allowing you to create more friendly names to objects with otherwise complicated names.

The primary benefit of a synonym is realized when you use them as a layer of abstraction between the underlying objects and the synonym. For example, you could have a synonym named SalesHistory that developers use for all Sales reporting. This synonym could reference a local view or table or a remote view or table via a linked server. This type of Synonym-Object correlation can be a very powerful tool when you combine it with data replication. The following example illustrates this point.

An example

Due to expanding business and scalability needs, you decide that it's a good idea to remove the SalesHistory table from your current database named Inventory and put it in a separate database named SalesData. There is a lot of code in the Inventory database that references the SalesHistory table. Most of this code is simple queries that only read data from the SalesHistory table. The project deadline is in the very near future, so it will be difficult to change all existing code to reference the table in the new database. I'll show how you can use a synonym to drastically reduce your development time.

Assume that you have already copied your SalesHistory table into the new SalesData database and have renamed the current SalesHistory table in the Inventory database. (I always prefer renaming a table that I intend to delete first. This gives me the opportunity to discover any errors on the system caused by removing a table.)

Once you rename the SalesHistory table, you can create a synonym that references to the SalesHistory table that you have placed in the new database. The script is below.

USE Inventory

GO

CREATE SYNONYM SalesHistory

FOR SalesData.dbo.SalesHistory;

GO

This synonym has created a pointer to the SalesHistory table in the new SalesData database; however, because the synonym is in the Inventory database and is named SalesHistory, any queries or DML statements executed on the SalesHistory synonym are actually executed against the SalesHistory table in the SalesData database.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

Print/View all Posts Comments on this article

Pro's and Con's..... chrisstyve@... | 04/10/07

Cons.. chapman.tim@... | 04/10/07

Cons slksport | 04/10/07

Is Your Enviroment Structured JohnnySacks | 04/11/07

hmmm chapman.tim@... | 04/11/07

They Grow Indefinitely JohnnySacks | 04/23/07