SYNONYMOUSLY REFERENCE SQL SERVER OBJECTS

What would you do when an object that is referenced inseveral places and in several databases (Stored Procedures, Functions, etc.) isbeing moved to a different database or is being renamed due to a new objectnaming 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 ofthese changes automatically?

 

Friends, let’s welcome Synonym to the SQL Server featuresfamily. A synonym is an alternative name (think of it like an alias) forobjects in SQL Server that provides a single-part name to reference a baseobject that uses long object names,  a two-part, three-part, or evenfor-part names object names.

 

Synonyms can reference objects that exist on local orremote servers. They provide a layer of abstraction to protect clientapplication from any changes made to the name or the location of the baseunderlying object. The binding between a synonym and its underlying object isstrictly by name only which means the underlying object can be modified,dropped or dropped and replaced by another object. You can also perform any DMLoperations on a synonym which actually gets performed on the underlying table.

 

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

 

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

SYNONYM can be created onthe following object types:
· User Tables (permanent and temporary)
· Views
· Stored procedures (TSQL & CLR)
· Extended Stored Procedures
· Replication Filter Procedures
· Assembly Aggregate Functions (CLR)
· Assembly Table Valued Functions (CLR)
· Scalar Functions

· Inline Table Valued Function, etc.

 

Let’s now look at some of the examples onhow to use synonyms in SQL Server.

 

1. CREATING A SYNONYM FOR LOCAL OBJECT

CREATE SYNONYM CUST_ADD
FOR ADVENTUREWORKSLT2008R2.SALESLT.ADDRESS;
GO

--QUERYING THIS SYNONYM
SELECT * FROM [CUST_ADD]
GO

--DROPPING THIS SYNONYM
DROP SYNONYM [CUST_ADD]
GO

2. CREATING A SYNONYM FOR 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

 

3. INSERTING A ROW USING A SYNONYM

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