SQL 使用同义词与视图的优缺点是什么？

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

提问by Jamey

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

回答by ConcernedOfTunbridgeWells

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.

... 还有更多。

回答by spencer7593

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

• 隐藏列
• 添加谓词（WHERE 子句）以限制行
• 重命名列
• 为 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 数据库）应用程序的设计中，我们对所有数据库对象（表、视图、触发器等）使用“所有者”模式（用户），并将这些对象的权限授予其他“应用程序”用户。在每个“应用程序”用户模式中，我们创建同义词来引用“所有者”对象。

HTH

HTH

回答by Krishna Chaitanya

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,

• 它允许您使用其他模式中的表，而无需在表名中使用点作为模式名称的前缀（user.tab_name 可以替换为 some_synonym_name）
• 您没有兴趣与他人分享实际对象，

回答by Josh

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 Entity Framework 时，我可以使用单个 ObjectContext 访问来自许多数据库的所有必需数据。

回答by Jimmy Zimms

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 中的简单名称替换，通常是为了隐藏复杂性。

回答by Mathese F

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 服务器作为数据库服务器和 SAS 作为客户端时。如果您使用同义词，则 SAS 逻辑库中将无法识别它。我必须创建一个视图。

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

它没有优化，但至少带有 Sql 服务器的 Windows sas 也不是最好的 :)

回答by Arthur

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.

如果我错了，请纠正我，但我认为我看到了同义词的另一种用法（至少在 Progress OpenEdge 中），我没有在任何地方看到记录，这可以使它比视图更安全。DML SELECT 语句语法允许您使用表、视图或同义词，但 INSERT、UPDATE 和 DELETE 语句仅允许使用表或视图。某些视图，如果满足特定条件，则提供对数据的可更新、可插入和可删除访问。同义词似乎是提供对数据的只读访问的好方法，而不必与授予（或拒绝）视图特权混淆。