Wednesday, January 23, 2008

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

1 Comment:

Anonymous said...

hey i need help gopi
i read ur great article on how to put expandable summary posts and i tried my hardest but man i couldn't accomplish my goal and couldn't do it.... is there a way we can contact and i give you the blog template's html code and u add the codes.... i would highly appreciate dat and love you forever and become a consistent user of ur great blog
my id is rizzy81@live.com