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

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