Have you ever found yourself typing long multi-part server names in your SQL statements? If so, you’ll be pleased to learn about a handy feature in SQL Server 2005 called synonyms. Synonyms provide a way to simplify your SQL statements and protect them from changes in underlying objects.

A synonym is a single-part name that can replace a multi-part name in a SQL statement. It acts as an abstraction layer, allowing you to use a shorter name instead of the full object name. This can greatly reduce the amount of typing required and make your code more readable.

Let’s take a look at how to create and use synonyms in SQL Server:

USE AdventureWorks;
GO

-- Create a synonym
CREATE SYNONYM MyLocation FOR AdventureWorks.Production.Location;
GO

-- Use the synonym in a query
USE AdventureWorks;
GO

SELECT TOP 5 * FROM MyLocation;
GO

-- Drop the synonym
USE AdventureWorks;
GO

DROP SYNONYM MyLocation;
GO

Synonyms can be created for various types of objects, including tables, views, stored procedures, and functions. However, they can only be used to change data of an object, not its schema.

When should you use synonyms? One example is when you have a stored procedure in one database that needs to access a table on another production server. Instead of typing out the full server name every time you access the table, you can create a synonym and use it as an alias.

USE Users;
GO

-- Create a synonym for the Clients table on another server
CREATE SYNONYM Clients FOR Offsite01.Production.dbo.Clients;
GO

Now, whenever you write a stored procedure that references the Clients table, you can simply use the synonym instead of the full object name. If the location or name of the production database ever changes, you only need to modify the synonym instead of updating all the stored procedures.

Synonyms provide a convenient way to simplify your SQL statements and make them more resilient to changes in underlying objects. Give them a try in your next SQL Server project!

Reference: Jonathan (Las Vegas), BOL – SYNONYMS