SQL Server Synonyms Explained In Simple English [SQL Server 2005]

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.

Example

Querying a table located on a remote server without a synonym

SELECT * FROM Server1.AdventureWorks.Production.ProductCategory

GO

INSERT INTO Server1.AdventureWorks.Production.ProductCategory(……) VALUES(…..)

GO

…

Querying a table located on a remote server with a synonym

--Create a synonym

CREATE SYNONYM ExTbl_ProdCat FOR Server1.AdventureWorks.Production.ProductCategory

GO

--Query data with the help of synonym

SELECT * FROM ExTbl_ProdCatGOINSERT INTO ExTbl_ProdCat (……) VALUES(…..)

GO

What are the different Database Objects that can be synonymised?

1. Assembly (CLR) Stored Procedure
2. Assembly (CLR) Table-valued Function
3. Assembly (CLR) Scalar Function
4. Assembly Aggregate (CLR) Aggregate Functions
5. Replication-filter-procedure
6. Extended Stored Procedure
7. SQL Scalar Function
8. SQL Table-valued Function
9. SQL Inline-table-valued Function
10. SQL Stored Procedure
11. View
12. Table (User-defined)

Want to read more?

Follow these links to read more about synonyms

• http://www.developer.com/db/article.php/3613301
• http://msdn2.microsoft.com/en-us/library/ms190626.aspx