What are the pros/cons of using a synonym vs. a view?

Question

This is a generic database design question - What are the benefits of using a synonym in database development, over a simple view? What are the main considerations to keep in mind when choosing between the two?

An example view:

CREATE VIEW Users AS SELECT * FROM IdentitySystem.dbo.Users 

And the equivalent synonym:

CREATE SYNONYM Users  FOR IdentitySystem.dbo.LCTs 

Answer 1

They are different things. A synonym is an alias for the object directly, a view is a construct over one or more tables.

Some reasons to use a view:

• May need to filter, join or otherwise frig with the structure and semantics of the result set

• May need to provide legacy support for an underlying structure that has changed but has dependencies that you do not want to re-work.

• May provide security where some of the contents of the table should be visible to a class of users but not all. This could involve removing columns with sensitive information or filtering out a subset of the records.

• May wish to encapsulate some business logic in a form that is accessible to users for reporting purposes.

• You may wish to unify data from more than one source.

... Plus many more.

Reasons to use a synonym:

• You may wish to alias an object in another database, where you can't (or don't want to) hard code the reference to the specific database.

• You may wish to redirect to a source that changes over time, such as an archive table.

• You want to alias something in a way that does not affect the query optimiser.

... Plus many more.

Answer 2

There are lots of considerations. In short, use the tool that works best for each situation.

With a view, I can

• hide columns
• add predicates (WHERE clause) to restrict rows
• rename columns
• give a column name to a SQL expression

With a synonym, I can:

• reference objects in other schemas and databases without qualifying the name

There's probably more that can be done with synonyms. In the designs of our (Oracle database) applications, we use an "owner" schema (user) for all of the database objects (tables, views, triggers, etc.), and we grant privileges on those objects to other "app" users. In each of the "app" user schemas, we create synonyms to reference the "owner" objects.

HTH