使用同义词与视图的优缺点是什么? [英] What are the pros/cons of using a synonym vs. a view?

问题描述

这是一个通用的数据库设计问题 - 在数据库开发中使用同义词比简单的视图有什么好处?在两者之间进行选择时要记住的主要考虑因素是什么?

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?

示例视图:

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

和等价的同义词:

CREATE SYNONYM Users 
FOR IdentitySystem.dbo.LCTs

推荐答案

它们是不同的东西.同义词是对象的直接别名，视图是一个或多个表的构造.

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

使用视图的一些原因:

• 可能需要对结果集的结构和语义进行过滤、连接或其他操作

• 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.

...还有更多.

使用同义词的原因:

• 您可能希望为另一个数据库中的对象取别名，在那里您不能(或不想)对特定数据库的引用进行硬编码.

• 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.

...还有更多.

这篇关于使用同义词与视图的优缺点是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！