Posted in Uncategorized

Introduction to 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 Kumawat

Follow me on Twitter

Advertisements

Author:

I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on http://www.sqlservercentral.com/blogs/kapil-blogs/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s