39

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
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
jamz
  • 4,991
  • 4
  • 24
  • 19

8 Answers8

44

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.

ConcernedOfTunbridgeWells
  • 64,444
  • 15
  • 143
  • 197
  • 1
    Sorry to drag up an old thread, but I have a similar dilemma as to whether to use a view or synonym. That's a great answer, thanks, but it doesn't really answer the question – DarthPablo Feb 24 '14 at 13:24
  • 1
    Take 2 :-C Sorry to drag up an old thread, but I have a similar dilemma as to whether to use a view or synonym. Thanks for your good explanation, too. The question I have is whether there are any reasons _not_ to use a "straight" view doing "select * from table"? Is that the bit you mention to do with the query optimiser? One problem I've found with synonyms is that they cause Hibernate to complain when schema validation is turned on using `hibernate.hbm2ddl.auto=validate`. Regarding your reasons to use a synonym, surely the first two could be addressed with a view, could they not? – DarthPablo Feb 24 '14 at 13:33
  • Of the 3 reasons to use a synonym, the first two are just as applicable to proximity views (```create view X_table as select * from another_database.dbo.X_table```) As for the third reason - 99% of the time there is probably no penalty either. So what are the mysterious ```...Plus many more.```? and do they matter? One good anwer is given by @Jimmy Zimms – Konstantin Apr 16 '15 at 08:55
9

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

spencer7593
  • 106,611
  • 15
  • 112
  • 140
4

I use synonyms to share objects from other databases so that when I use .Net Entity Framework I can use a single ObjectContext to access all of the required data from many databases.

Josh
  • 41
  • 1
  • Can you provide a bit more detail on how you are accomplishing this? This answer provides one way, but it's less-than-ideal. http://stackoverflow.com/questions/6036357 – JoeBrockhaus Nov 19 '14 at 17:04
4

A view primarily is a simple/complex "select" statement. Essentially you use a view as a mask, and show only those column values which are of use. You use a view with an intention to not show extra information to the end-user.

Whereas a synonym is an alternative name for the database objects.

  • it allows you to use tables in other schema without prefixing the schema name in the table name with a dot as in (user.tab_name can be replaced by some_synonym_name)
  • you are not interested to share the actual object with others,
Krishna Chaitanya
  • 255
  • 1
  • 3
  • 11
3

The column projection from a view is established at create time. Therefore if you add a column to the underlying view it will not be exposed until you alter the view. Not so with a synonym. Think of it as a simple name replace in your tsql, usually to hide complexity.

Jimmy Zimms
  • 121
  • 1
  • 4
1

I hope it could help someone, I took a look at this article and I found a reason to use a View instead of a synonym.

When you are using Sql server as Db server and SAS as client. If you use a Synonym it won't be recognized in your SAS library. I had to create a view.

It's not optimized but at least Windows sas with Sql server is not the best neither :)

Mathese F
  • 559
  • 4
  • 9
1

Please correct me if I'm wrong, but I think I see another use for a synonym (at least in Progress OpenEdge), that I don't see documented anywhere, that can make it even more secure than a view. The DML SELECT statement syntax allows you to use a table, view or synonym, but the INSERT, UPDATE and DELETE statements allow only a table or view. Some views, if they meet certain criteria, provide updatable, insertable, and deletable access to the data. The synonym seems like a good way to provide read-only access to the data, without having to mess with granting (or denying) privileges on views.

Arthur
  • 11
  • 1
  • 1
    For SQL Server, you can grant SELECT, INSERT, UPDATE, DELETE to a SYNONYM for a table. (grant EXEC for SP or UDF, etc). – tgolisch Aug 13 '12 at 17:31
0

We recently moved some tables and replaced them with synonyms. Then we found out that:

  • Synonyms are not working when data is inserted via SqlBulkCopy from C# code
  • Synonyms are not working for queries from a linked server using 4-part naming conventions (select * from servername.dbname.schemaname.synonymname)

In these cases we had to change to views. For everything else we still prefer synonyms for their simplicity.

Vadim
  • 394
  • 1
  • 7