为什么存储过程会使 SQL 缓存依赖项失效？

Question

经过几个小时后，我终于意识到我可以正确使用 ASP.NET 应用程序中的 Cache 对象，但我的存储过程阻止它正常工作。

这个存储过程可以正常工作：

CREATE PROCEDURE [dbo].[ListLanguages]
@Page INT = 1,
@ItemsPerPage INT = 10,
@OrderBy NVARCHAR (100) = 'ID',
@OrderDirection NVARCHAR(4) = 'DESC'
AS
BEGIN
    SELECT ID, [Name], Flag, IsDefault FROM dbo.Languages
END

但是这个（我想要的）却不能：

CREATE PROCEDURE [dbo].[ListLanguages]
@Page INT = 1,
@ItemsPerPage INT = 10,
@OrderBy NVARCHAR (100) = 'ID',
@OrderDirection NVARCHAR(4) = 'DESC',
@TotalRecords INT OUTPUT
AS
BEGIN
    SET @TotalRecords = 10

EXEC('SELECT ID, Name, Flag, IsDefault FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' ' + @OrderDirection + ') as Row, ID, Name, Flag, IsDefault
    FROM dbo.Languages) results
    WHERE Row BETWEEN ((' + @Page + '-1)*' + @ItemsPerPage + '+1) AND (' + @Page + '*' + @ItemsPerPage + ')')
END

我给 @TotalRecords 参数设置了值 10，这样你就可以确定问题不是来自 COUNT(*) 函数，我知道该函数不受支持出色地。

另外，当我从 SQL Server Management Studio 运行它时，它完全执行了它应该执行的操作。在 ASP.NET 应用程序中，结果可以正确检索，只是缓存不知何故无法工作！

你能帮忙吗？

也许是一个提示

我相信依赖项HasChanged属性的原因与从ROW_NUMBER生成的列Row只是临时的这一事实有关，因此，SQL SERVER无法判断结果是否更改。这就是 HasChanged 始终设置为 true 的原因。

有谁知道如何在不使用 COUNT 或 ROW_NUMBER 函数的情况下对 SQL SERVER 的结果进行分页？

Answer 1

缓存大小不够。

Answer 2

.NET 3.5 的 Sql 缓存依赖项仅适用于简单查询。也许.NET 4 会让我感到惊讶。

Answer 3

1 - 你能复制&粘贴您实际用于缓存该存储过程结果的代码？
2 - 您是否尝试过使用直接查询而不是 EXEC 字符串的存储过程？

是的，#2 意味着您不能动态更改查询的结构:-)，但除非您在#1 中计算自己的缓存标准，否则一般来说这是您必须遵守的缓存规则。任何缓存机制都不会从您那里解析来自 EXEC 的字符串。

EXEC 存储过程中的字符串使得该存储过程在每次运行时都相当于掷硬币，甚至对于 SQL Server 本身也是如此。它还会让您容易受到脚本注入攻击，因为您的查询在运行时仍然由字符串组成 - 这与在 C# 中组成整个字符串并将其传递给 sproc 来“仅执行其中的任何内容”没有任何不同