SQL Server 2005 - 存储 RowSet 的变量的数据类型

发布于 12-27 22:05 字数 297 浏览 9 评论 0原文

在我正在工作的典型存储过程中,有使用相同查询的各种检查,如下所示。

SELECT ... FROM Projects WHERE ProjectId IN (SELECT ProjectId FROM Tasks)

我想用变量替换查询(SELECT ProjectId FROM Tasks),但对它必须是什么数据类型感到困惑。 你知道吗?缓存这个结果也是有害的,或者是否有更简单的方法来做到这一点。

In a typical stored procedure i am working there are various checks with the same query as below.

SELECT ... FROM Projects WHERE ProjectId IN (SELECT ProjectId FROM Tasks)

i would like to replace the query (SELECT ProjectId FROM Tasks) with a variable but am confused as to what datatype it has to be. Do you know? also caching this result is detrimental or is there easier way to doing this.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

穿透光2025-01-03 22:05:59

您可以创建表类型变量并多次使用它,如下所示:

CREATE @Projects TABLE(Id INT NOT NULL)

INSERT @Projects SELECT ProjectId FROM Tasks

SELECT ... FROM Projects WHERE ProjectId IN (SELECT ProjectId FROM @Projects)
SELECT ... FROM Projects WHERE ProjectId NOT IN (SELECT ProjectId FROM @Projects)

- 它不是用变量替换查询,但它使子查询结果更可重用

但是

虽然 某些情况下这可能会降低查询的性能

You may cteate table typed variable and use it multiple times, like that:

CREATE @Projects TABLE(Id INT NOT NULL)

INSERT @Projects SELECT ProjectId FROM Tasks

SELECT ... FROM Projects WHERE ProjectId IN (SELECT ProjectId FROM @Projects)
SELECT ... FROM Projects WHERE ProjectId NOT IN (SELECT ProjectId FROM @Projects)

Althought - it is not replacement of query by variable, it is make more reusable the subquery results

But

under certain conditions this may degrade your queries' performance

萌吟2025-01-03 22:05:59

这种形式更可取(尽管优化器应该执行此优化):

SELECT ... FROM Projects p
INNER JOIN Tasks t ON t.ProjectID = p.ProjectId

然后将条件添加为 WHERE 子句(而不是执行内部选择并尝试将 ProjectId 列表放入变量中):

SELECT ... FROM Projects p
INNER JOIN Tasks t ON t.ProjectID = p.ProjectId
WHERE someCondtionOnTasksTable

This form is preferable (although optimiser should perform this optimisation):

SELECT ... FROM Projects p
INNER JOIN Tasks t ON t.ProjectID = p.ProjectId

Then add the condition as a WHERE clause (instead of performing the inner select and attempting to place a list of ProjectId's in a variable):

SELECT ... FROM Projects p
INNER JOIN Tasks t ON t.ProjectID = p.ProjectId
WHERE someCondtionOnTasksTable
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文