Systems Engineering and RDBMS

Views and Synonyms

Posted by decipherinfosys on May 30, 2007

Synonyms have existed in Oracle since long – however, they are pretty new to SQL Server world as this feature was introduced in SQL Server 2005.  One of the common questions asked is what is the difference between the two…here are a couple of those:

  • A view is created on one or more objects (tables, views, functions) and can include more than one object type as well as more than one object (example: 3-4 tables, 1 function, 1 view can be combined together to form a single view – a logical definition).  Synonyms are created on only one single object – a table, a view, a function, a stored procedure etc.
  • Not all views are updateable by definition.  If a view is a single table view, it would be updateable, however, if it is comprised of complex calculations and joins, it is not directly updateable – one has to write Instead Of triggers (IO triggers) in order to make them updateable.  Synonyms (when created on tables or single table views) are updateable since they are created on a single object.
  • An index can be created on a view thus forming an indexed view (SQL Server) or a materialized view (Oracle) which materializes the data to the disk – this is pretty common in data warehousing and BI applications.  Synonyms are just a logical definition.
  • Synonyms (in Oracle) can be created as a public or a private synonym.  For a view, one needs to specifically grant privileges since there is no such thing like a public or a private view.

Both views and synonyms can be created over a db-link (Oracle) or linked servers (SQL Server) and are very commonly used for enforcing a security layer in the schema.

One Response to “Views and Synonyms”

  1. […] and assuming that we have select privileges in place already on that object, we can either create views/synonyms for those objects in our schema or simply refer to those objects if the permissions are available […]

Sorry, the comment form is closed at this time.

%d bloggers like this: