Synonyms in SQL Server

Synonyms in SQL Server

Synonyms in SQL Server

In this post, I will explain all about Synonyms in Microsoft SQL Server end to end along with appropriate examples. So let’s start –

Synonyms in SQL Server

A synonym is an alternative name or an alias for any object such as tables, views, sequences, stored procedures, user-defined functions, and other database objects. We generally use synonyms when we are granting access to an object from another schema and we don’t want users to have to worry about knowing which schema owns the object.

Uses of Synonyms in SQL Server

We can use the synonyms if we refer to an object from another database which can be from a remote server then we can create a synonym in our database and reference to this object as it is in our database and when we want to rename a table, views, sequences, stored procedures, user-defined functions, and other database objects that reference to this table need to be manually modified to reflect the new name. In addition all current applications that use this table need to be changed and possibly to be recompiled. to avoid all this hard work we can rename the table and create a synonym for it to keep existing applications function properly.

Benefits of Synonyms in SQL Server

We can get the following benefits if we use synonyms properly like – a Synonym gives a layer of abstraction over the base objects, it shorts the length of the name i.e. [server_name].[database_name].[schema_name].[object_name] with a simplified alias and it allows backward compatibility for the existing applications when we rename database objects such as tables, views, sequences, stored procedures, user-defined functions, and other database objects

CREATE SYNONYM

We may wish to create a synonym so that users do not have to prefix the table name with schema name when using the table in a query –

Syntax:

CREATE SYNONYM <schema_name> synonym_name

FOR object_name

Object may be into following form –

[server_name].[database_name].[schema_name].[object_name]

In the above syntax, we have to specify the target object that we want to assign a synonym in the FOR clause and provide the name of the synonym after the CREATE SYNONYM statement

(A) SYNONYM within the same database

We will create a synonym for our existing table Students using below CREATE SYNONYM statement –

CREATE SYNONYM Studen

FOR [AdventureWorks2012].[dbo].[Students]

After creating this synonym Studen, we can reference it in anywhere which we use the target object [AdventureWorks2012].[dbo].[Students] table and also we can use the following query instead of [AdventureWorks2012].[dbo].[Students] table –

Syntax:

SELECT * FROM Studen

(B) SYNONYM with another database

We have to create a new database named MS_SQL_World and will set the current database to MS_SQL_World

Syntax:

CREATE DATABASE MS_SQL_World

USE [MS_SQL_World]

Now we will create a new schema named Education inside the MS_SQL_World database using below query –

CREATE SCHEMA Education

Now we will create a table in the Education schema under the MS_SQL_World database using below query –

CREATE TABLE [MS_SQL_World].[Education].[Students](
[ID] [int] NOT NULL,
[Name] varchar NULL,
[email] varchar NULL,
[Mobile] varchar NULL,
[Address] varchar NULL,
[City] varchar NULL,
[State] varchar NULL,
[PinCode] varchar NULL,
[email_local_Part] AS (substring([email],(0),charindex(‘@’,[email],(0)))),
)

Now we will insert few rows into the newly created table Students using below query –

INSERT INTO [MS_SQL_World].[Education].Students
VALUES (1, ‘Ram’,’ram.ji@gmail.com’,9956267687,NULL,’Delhi’,’NCR’,110054)

INSERT INTO [MS_SQL_World].[Education].Students
VALUES (2, ‘Shyam’,’sh.yam@yahoomail.com’,NULL,NULL,’Newada’,’Siq’,110054)

INSERT INTO [MS_SQL_World].[Education].Students
VALUES
(3, ‘Siya’,’SI.YA@rediffmail.com’,9956267687,NULL,’Satna’,’MP’,110054)

After inserting few rows into [MS_SQL_World].[Education].Students table, now we will go again in our previous database AdventureWorks2012 and will create a synonym for the [MS_SQL_World].[Education].Students table using below query –

CREATE SYNONYM Stu FOR [MS_SQL_World].[Education].[Students]

Now finally from the AdventureWorks2012 database we can view the records referring to the [MS_SQL_World].[Education].Students table using Stu synonym using below query –

USE [AdventureWorks2012]
SELECT * FROM Stu

Listing of all synonyms within a database

We can view the lists of all synonyms within a database using below TSQL transact query –

SELECT name,base_object_name,type

FROM sys.synonyms

ORDER BY name

Result:

Synonyms in SQL Server

We can view the lists of all synonyms within a database using SQL Server Management Studio as shown in below snapshot –

Synonyms in SQL Server

DROP a Synonym

To drop an existing synonym we can use the DROP SYNONYM statement using below syntax –

DROP SYNONYM [ID EXISTS] [schema_name].synonym_name

In the above syntax we have to specify the synonym name that we want to drop after the DROP SYNONYM keyword and use the IF EXISTS to conditionally drop the synonym only if it exists, if we will try to drop a non-existing synonym without IF EXISTS option will result in an error. So now we will try to drop a non-existing synonym let’s see what happens –

DROP SYNONYM [IF EXISTS] xyz

After executing the above query we found an error because we know that there is no synonym named – Xyz as shown below –

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘xyz’.

Now we will try to delete/drop an existing synonym which should be dropped because it already exists in the current database named – Studen

DROP SYNONYM [IF EXISTS] dbo.Studen

We can see that the query executed successfully and resulted below –

Command(s) completed successfully.

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc as it may help some other needy peoples too.

Leave a Reply