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:
- Stored Procedure
- User-Defined Function
Some points to be remember about synonyms:
- A synonym cannot refer to another synonym.
- Synonyms cannot be altered using ALTER statement. To modify the synonym you must DROP it and recreate.
- Synonyms names must be T-SQL identifiers just as for other database objects.
To create a synonym we simply have to specify a synonym name and name of database object to which it will be assigned.
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
We cannot ALTER the synonym so to change synonym we need to drop and recreate it.
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.