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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s