What are Synonyms?
SYNONYMS in SQL Server 2005 allows developers to create alias names for database objects and refer the database objects with alias names where ever required.
What is the benefit of using Synonyms?
Lengthy database objects names can be referred with simple and small alias names. Increases readability of the script.
Querying a table located on a remote server without a synonym
SELECT * FROM Server1.AdventureWorks.Production.ProductCategory
INSERT INTO Server1.AdventureWorks.Production.ProductCategory(……) VALUES(…..)
Querying a table located on a remote server with a synonym
--Create a synonym
CREATE SYNONYM ExTbl_ProdCat FOR Server1.AdventureWorks.Production.ProductCategory
--Query data with the help of synonym
SELECT * FROM ExTbl_ProdCatGOINSERT INTO ExTbl_ProdCat (……) VALUES(…..)
What are the different Database Objects that can be synonymised?
- Assembly (CLR) Stored Procedure
- Assembly (CLR) Table-valued Function
- Assembly (CLR) Scalar Function
- Assembly Aggregate (CLR) Aggregate Functions
- Extended Stored Procedure
- SQL Scalar Function
- SQL Table-valued Function
- SQL Inline-table-valued Function
- SQL Stored Procedure
- Table (User-defined)
Want to read more?
Follow these links to read more about synonyms