37  

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.

  您希望以不影响查询optimiser的方式对某些内容进行别名。

... Plus many more.

…+更多。

6  

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
• 添加谓词(WHERE子句)来限制行。
• rename columns
• 重命名列
• give a column name to a SQL expression
• 为SQL表达式提供一个列名

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.

可能还有更多的同义词可以使用。在(Oracle数据库)应用程序的设计中，我们对所有数据库对象(表、视图、触发器等)使用“所有者”模式(user)，并将这些对象的特权授予其他“应用程序”用户。在每个“app”用户模式中，我们都创建同义词来引用“所有者”对象。

HTH

HTH

3  

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)
• 它允许您在其他模式中使用表，而无需在表名中加上一个点(user)作为模式名的前缀。可以用some_synonym_name替换tab_name)
• you are not interested to share the actual object with others,
• 你不愿意和别人分享真实的东西，

3  

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.

我使用同义词来共享来自其他数据库的对象，以便当我使用。net实体框架时，我可以使用单个ObjectContext来访问来自多个数据库的所有所需数据。

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.

视图中的列投影是在创建时创建的。因此，如果您向底层视图添加一列，那么在您修改视图之前，它不会被公开。同义词则不然。可以将它看作是tsql中的一个简单名称替换，通常是为了隐藏复杂性。

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.

如果我错了，请纠正我，但是我认为我看到了同义词的另一个用法(至少在OpenEdge中是这样)，我在任何地方都看不到文档，这会使它比视图更安全。DML SELECT语句语法允许使用表、视图或同义词，但是INSERT、UPDATE和DELETE语句只允许使用表或视图。有些视图(如果满足某些条件)提供可更新的、可插入的和可延迟的数据访问。同义词似乎是一种提供对数据的只读访问的好方法，而不需要对视图授予(或拒绝)特权。

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.

当您使用Sql server作为Db服务器，使用SAS作为客户端时。如果使用同义词，则在SAS库中不能识别同义词。我必须创建一个视图。

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

它没有优化，但至少使用Sql server的Windows sas不是最好的:)