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

问题描述

这是一个通用的数据库设计问题 - 在数据库开发中使用同义词的好处是什么？

示例查看：

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

和等效的同义词：

  CREATE SYNONYM用户
 For IdentitySystem.dbo .LCTs 
  

解决方案

同义词是对象的别名，视图是一个或多个表的构造。

使用视图的一些原因：

• 可能需要过滤，加入或以其他方式阻止结果集的结构和语义




• 可能需要为已更改但具有依赖关系的底层结构提供旧版支持。

安全性，其中表的一些内容应该对一类用户可见，但不是全部。这可能涉及删除包含敏感信息的列或过滤掉记录的子集。



• 可能希望将某些业务逻辑封装在用户可访问的表单中




• 您可能希望统合多个来源的资料。

...还有更多。

使用同义词的原因：

• 您可能希望对另一个数据库中的对象进行别名，您不能（或不想）硬编码对特定数据库的引用。



p>您想以不影响查询优化程序的方式对别名进行别名。

更多。

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

解决方案

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.

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