Using Synonyms for Long Object Names in SQL Server

What would you do when an object that is referenced in several places and in several databases (Stored Procedures, Functions, etc.) is being moved to a different database or is being renamed due to a new object naming policy? Think of how much work this change would generate for you?

Wouldn’t it be nice if there was a way to take care of these changes automatically?

A synonym is an alternative name (think of it like an alias) for objects in SQL Server that provides a single-part name to reference a base object that uses long object names,  a two-part, three-part, or even for-part names object names.

Synonyms can reference objects that exist on local or remote servers. They provide a layer of abstraction to protect client application from any changes made to the name or the location of the base underlying object. The binding between a synonym and its underlying object is strictly by name only which means the underlying object can be modified, dropped or dropped and replaced by another object. You can also perform any DML operations on a synonym which actually gets performed on the underlying table.

Synonyms can be very helpful in the above scenario. Once you create synonyms for objects, you can move or rename your base object without affecting its reference or use.

Synonyms can also be used for creating short-cuts for long object names or the object names used in four-part queries for example linked servers. (ex: ServerName.DatabaseName.OwnerName.ObjectName)

Synonym can be created on the following object types:

1. User tables (both permanent and temporary)
2. Views
3. Stored Procedures (T-SQL and CLR)
4. Extended Stored Procedures
5. Replication Stored Procedures
6. Replication Filter Procedures
7. Assembly Aggregate Functions (CLR)
8. Assembly Table Valued Functions (CLR)
9. Scalar Functions
10. Inline Table Valued Function, etc.

Lets now look at some of the examples on how to use Synonyms in SQL Server.

Creating a Synonym for an object:

CREATE SYNONYM CUST_ADD
  FOR ADVENTUREWORKSLT2008R2.SALESLT.ADDRESS
GO

Query newly created Synonym:

SELECT * FROM [CUST_ADD]
GO

Deleting a Synonym:

DROP SYNONYM [CUST_ADD]
GO

Let’s now create a Synonym for a Linked Server object. Make sure the linked server is already established. Once the Synonym is created for Linked Server, you can refer to the linked server object using the Synonym (short name)

--CREATE A SYNONYM ON A LINKED SERVER OBJECT
CREATE SYNONYM CUST_ADD
FOR SQLTIPS.ADVENTUREWORKSLT2008R2.SALESLT.ADDRESS
GO
 
--QUERYING THIS SYNONYM
SELECT * FROM [CUST_ADD]
GO
 
--DROPPING THIS SYNONYM
DROP SYNONYM [CUST_ADD]
GO

Let’s now look at inserting data in to a table using a Synonym:

INSERT INTO [CUST_ADD] VALUES
(
   'ONE WAY'
   ,'MICROSOFT WAY'
   ,'REDMOND'
   ,'WASHINGTON'
   ,'USA'
   ,98052
   ,NEWID()
   ,GETDATE()
)
Go