查看或临时表 - 在 MS SQL Server 中使用哪个? [英] View or Temporary Table - which to use in MS SQL Server?

问题描述

我在决定是使用视图还是临时表时遇到问题.

I have a problem to decide whether to use a view or a temp table.

我有一个从程序调用的存储过程.在该 SP 中，我将长查询的结果存储在临时表中，命名列并对该表进行另一个查询，将结果存储在标签或 gridview 或其他任何内容中，然后删除临时表.我还可以将查询结果存储在视图中并对该视图进行查询.那么什么更好，或者在什么情况下我必须使用视图/临时表.

I have a stored procedure that i call from program. In that SP i store the result of a long query in a temp table, name the columns and make another queries on that table store the results in labels or a gridview or whatever and drop the Temp Table. I could also store the query-result in a view and make queries on that view. So what is better or in what case do i HAVE to use a VIEW/ Temp Table.

根据我的研究，视图具有以下优点:安全性、简单性和列名规范.我的临时表也满足了这一切(根据我的意见).

According to my research a view has the benefit of: Security, Simplicity and Column Name Specification. My temporary table fulfills all that too (according to my opinion).

推荐答案

如果查询是长"的并且您正在访问多个查询的结果，那么临时表是更好的选择.

If the query is "long" and you are accessing the results from multiple queries, then a temporary table is the better choice.

一般来说，视图只是select 语句的捷径.If 并不意味着结果会被运行和处理.如果使用视图，则每次使用时都需要重新生成结果.尽管视图的后续运行可能会更高效(比如因为视图查询使用的页面在缓存中)，但临时表实际上存储了结果.

A view, in general, is just a short-cut for a select statement. If does not imply that the results are ever run and processed. If you use a view, the results will need to be regenerated each time it is used. Although subsequent runs of the view may be more efficient (say because the pages used by the view query are in cache), a temporary table actually stores the results.

在 SQL Server 中，您也可以使用表变量(declare @t table . . .).

In SQL Server, you can also use table variables (declare @t table . . .).

在单个存储过程中使用临时表(或表变量)在安全性、简单性和列名方面似乎没有什么影响.安全性将通过对存储过程的访问来处理.任一解决方案都需要列名.如果没有更多信息，很难判断简单性，但没有什么特别复杂.

Using a temporary table (or table variable) within a single stored procedure would seem to have few implications in terms of security, simplicity, and column names. Security would be handled by access to the stored procedure. Column names are needed for either solution. Simplicity is hard to judge without more information, but nothing sticks out as being particularly complicated.

这篇关于查看或临时表 - 在 MS SQL Server 中使用哪个?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！