“The SQL Guy” Post #5: SYNONYMously Reference SQL Server Objects

  • Article

SQL08_v_webContinuing on a topic that includes some more content aimed at DBAs and developers, this week we’ll look at how SYNONYMs can make your life easier.

 

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?

Friends, let’s welcome Synonym to the SQL Server features family. 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:
· 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 on how to use synonyms in SQL Server.

 

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

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

INSERTING A ROW USING A SYNONYM

INSERT INTO [CUST_ADD]

VALUES

('ONE WAY'

,'MICROSOFT WAY'

,'REDMOND'

,'WASHINGTON'

,'USA'

,98052

,NEWID()

,GETDATE())

GO

DamirB-BlogSignature