Synonyms in SQL Server

Hi friends,

In this blog I will discuss about Synonyms in SQL Server.

Synonyms are the database object which is used to provide an alternative name to other database objects.

We can use synonyms so that user does not need to remember the full part name of the object.

Synonym can be used to specify with objects like:

  • Tables
  • Views
  • Stored Procedure
  • User-Defined Function

Some points to be remember about synonyms:

  1. A synonym cannot refer to another synonym.
  2. Synonyms cannot be altered using ALTER statement. To modify the synonym you must DROP it and recreate.
  3. Synonyms names must be T-SQL identifiers just as for other database objects.

Create Synonym

To create a synonym we simply have to specify a synonym name and name of database object to which it will be assigned.

Syntax:

Create SYNONYM schema_name.synonym_name FOR object_name

Now, we will create a synonym –

Create SYNONYM dbo.test FOR Sales.Orders

Here instead of specifying Sales.Orders in our query we can directly use the synonym dbo.test.

Now, we can write our query as:

SELECT categoryid, categoryname 
FROM dbo.test

Img_Synonym1

Drop Synonym

We cannot ALTER the synonym so to change synonym we need to drop and recreate it.

Syntax:

DROP SYNONYM synonym_name

As we have created synonm dbo.test above, we can drop that synonym using:

DROP SYNONYM dbo.test

Hope you like this post.

Regards,

Kapil Singh

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Follow me on Twitter

Avatar

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

3 Comments on “Synonyms in SQL Server”

  1. I have never been a fan on synonyms as it makes reviewing code harder ut I understand why some people use it from a security perspective.

Leave a Reply

Your email address will not be published. Required fields are marked *