Overview of Synonyms in MS SQL Server

Hi Friends,

Hope you are doing fine, while reading to this post!

Today, I will try to write about the overview of SYNONYMS in MS SQL Server.

Synonyms are the nothing but the database object, which is used to specify/provide an alternative name to other database objects. We can use synonyms so that user does not need to remember the full name of the database object, as somewhere we have seen the complexity within the object's name.

For more details, please refer the MSDN Link .

Going forward, Synonyms can be used for the database objects mentioned below:

1. User-defined Tables
2. User-defined Views
3. User-defined Stored Procedures
4. User-defined Functions

Also please note the below points about the SYNONYMS:

1. A synonym can not refer to another synonym.
2. You can not be alter SYNONYM. 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 <[schemaName].[synonymName]> FOR <[SchemaName].[TableName]>

DROP SYNONYM:

Since, we can not ALTER the synonym in MS SQL Server, we need to drop and re-create it, if we wish to change the SYNONYM.

Syntax#

DROP SYNONYM <[schemaName].[synonymName]>

Let's understand this with below T-SQL Script:

-- SELECT DATABASE 
USE AdventureWorks2012
GO

-- CREATE SYNONYM FOR THE TABLE 
--  '[Production].[ProductModelProductDescriptionCulture]'
CREATE SYNONYM [dbo].[ProdDescCulture] 
    FOR [Production].[ProductModelProductDescriptionCulture]
GO

-- SELECT QUERY ON THE ORIGINAL TABLE 
-- '[Production].[ProductModelProductDescriptionCulture]' 
--(Extracting only TOP 2 Rows)
SELECT TOP 2 * FROM [Production].[ProductModelProductDescriptionCulture]
GO

-- SELECT QUERY ON THE ORIGINAL TABLE 
--'[Production].[ProductModelProductDescriptionCulture]' 
--(Extracting only TOP 2 Rows)
SELECT TOP 2 * FROM [dbo].[ProdDescCulture]
GO

-- DROP SYNONYM QUERY
DROP SYNONYM [dbo].[ProdDescCulture]
GO

Snap-shot:

Hope you will like this post!

Happy Coding :-)