We have already explained you regarding RANK(), DENSE_RANK(), ROW_NUMBER() , Aggregate functions in sql server and Identity column in sql server. Today i got one question from my friend that i have created synonym for database, but not able to use it.! So I google it and got some interesting thing about it. I thought to share it with everyone because don’t know how many of you aware with it or not.

Synonym in SQL server is alias for database object which is either available on local or another server, referred as base object, that can exist on another server. It’s name is unique. If base object is not available while creating synonym, it will not throw any error, because it will check existence of base object on run time. Synonyms can be created for the following database objects:

• Store Procedure
• Tables
• Views
• CLR store procedure
• CLR functions
• SQL scalar function
• SQL table-valued function
• SQL inline-table-valued function

In Synonyms, name is the main factor. It will work with name. It will check everything on run time like type, existence, permissions. For example, synonym ‘Employee’, that references the ‘EmployeeDetail’ table of ‘Test_db’ database. Now, if you dropped ‘EmployeeDetail’ table and created view named as ‘EmployeeDetail’, then synonym ‘Employee’ will reference ‘EmployeeDetail’ view. Because it will check existence at run time.

One important note is that if you are not able to CREATE SYNONYM then don’t forget to check that you have right permission to for it. Because to create a synonym, a user must have CREATE SYNONYM permission in that schema.

You can use Synonym in SQL server in following SQL Statements:

• Select
• Update
• Insert
• Delete
• Execute

Following is the syntax for CREATE SYNONYM :

/****** Object:  Synonym [dbo].[testingsyn]******/
CREATE SYNONYM [dbo].Synonym name
FOR [server Name].[Database Name].[dbo].[Database object]
GO

For example, I have one database in which table name as student has some record, which i want to use it in another database then here I have created one synonym in present database. If I have to create synonym from external server then I have to write server name before database name.

/****** Object:  Synonym [dbo].[testingsyn]******/
CREATE SYNONYM [dbo].[testingsyn] 
FOR [ManageStudent].[dbo].Student
GO

SELECT * FROM testingsyn

You can create synonym of database object only, if you want to create synonym of database then you need to use trick like create all synonym object of all table,view and store procedure, if database is not too big.

Try with creating synonyms of view and store procedure and share what you are getting.
Thanks 🙂