Data Management

Cut development time with SQL Server 2005's synonyms

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.

Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

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.

About Tim Chapman

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

Editor's Picks

Free Newsletters, In your Inbox