哪一个是最好的 sql server 中用于数据访问的视图或存储过程 [英] Which one is best View or Stored procedure in sql server for data access purpose

问题描述

我经常使用存储过程来访问数据，但不知道哪个是最好的视图或 SP.

I often use stored procedure for data access purpose but don't know which one is best view or SP.

存储过程和视图都被编译，执行计划被保存在数据库中.所以请告诉我哪一个最适合数据访问目的，为什么最好列出原因.

Stored procedure and views are both compiled and execution plan is saved in database. So please tell me which one is best for data access purpose and why best list down the reason please.

我在谷歌上搜索，想知道哪个最好，但没有得到预期的答案.

I search google to know which one is best but got no expected answer.

推荐答案

视图和存储过程的用途完全不同.视图是将复杂的关系集(例如跨多个表连接的关系集)引用为平面表而不实际强制显示数据的一种方便方式.您使用视图来清理 SQL 代码.您的存储过程可以调用视图.视图通常用于权限控制.您可以授予数据库用户访问视图的权限，而不授予他们访问基础表的权限.这授予用户对视图中列的列级权限，这是一种比授予对整个表的访问权限更精细的权限控制方法.

Views and stored procedures serve entirely different purposes. Views are a convinient way to refer to a complex relational set (such as one that joins across many tables) as a flat table without actually forcing the data to be manifested. You use a view to clean up SQL code. Your stored procedures could call views. Views are often used for permission control. You can grant a database user access to a view without granting them access to the underlying tables. This grants the user column level permissions on the columns in the view which is a far more granular method for permission control than granting access to whole tables.

存储过程用于将经常使用的功能作为一个单元保存在一起.老实说，SP 正在失去许多程序员的青睐.虽然 SP 缓存了他们的执行计划是正确的，但动态 SQL 从 SQL Server 2000 开始就有执行计划缓存(我相信这是正确的版本).通过使用 SP，您将获得的唯一速度增益是通过网络发送更少的数据，而这将非常小.SP 倾向于使代码更脆弱，并要求在应用程序更改并不真正保证时对数据库进行更改.例如，如果您只想更改要选择的行的条件.使用 SP，您将不得不对应用程序和数据库代码进行更改.如果您使用的是动态 SQL 或 ORM 工具，则只需对应用程序进行更改即可简化部署.SP 绝对有时间和地点，但它们不一定是您与数据库交互的唯一方法.

Stored procedures are used to keep often used functionality together as a unit. To be honest, SPs are falling out of favor among many programmers. While you are correct that SPs have their execution plans cached, dynamic SQL has had execution plan caching since SQL Server 2000 (I believe that's the correct version). The only speed gain you're going to get by going with SPs is by sending less data over the network, and that's going to be extremely minimal. SPs tend to make code more brittle and require changes to the DB to occur when application changes don't really warrant it. For example, if you just wanted to change the conditions for which rows you're selecting. Using SPs, you're going to have to roll changes out to the application and the database code. If you're using dynamic SQL or an ORM tool, you only need to make changes to the application which simplifies deployment. There is absolutely a time and place for SPs, but they don't need to be your only method for interacting with the database.

此外，如果您担心性能，您可以具体化视图，从而减少重复查询基础表的需要.如果您觉得需要在物化视图引起的插入/更新上增加额外的开销，这可以大大提高您的性能.

Also, if you're worried about performance, you can materialize views which reduces the need to repeatedly query the underlying tables. This could greatly enhance your performance if you feel the need to add the extra overhead on inserts/updates that materializing views induces.

这篇关于哪一个是最好的 sql server 中用于数据访问的视图或存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！