SqlDependency 有哪些限制？

Question

我使用表作为消息队列，并使用 SqlDependency“注册”更新。在我读过的所有地方，人们都在说“注意它的局限性”，但没有具体说明它们是什么。根据我的收集，当表的更新频率非常高时，您将会遇到问题；幸运的是，我每分钟最多只查看 10 - 20 个值。

SqlServer 上还有哪些其他限制/影响？

Answer 1

我能找到的最完整的列表（从这里）如下：

• 必须明确声明 SELECT 语句中的投影列，并且表名称必须使用两部分名称进行限定。请注意，这意味着语句中引用的所有表必须位于同一个数据库中。
• 该语句不得使用星号 (*) 或 table_name.* 语法来指定列。
• 该语句不得使用未命名的列或重复的列名。
• 该语句必须引用基表。
• 该语句不得引用包含计算列的表。
• SELECT 语句中的投影列不能包含聚合表达式，除非该语句使用 GROUP BY 表达式。当提供 GROUP BY 表达式时，选择列表可能包含聚合函数 COUNT_BIG() 或 SUM()。但是，不能为可为空的列指定 SUM()。该语句不能指定 HAVING、CUBE 或 ROLLUP。
• SELECT 语句中用作简单表达式的投影列不得出现多次。
• 该语句不得包含 PIVOT 或 UNPIVOT 运算符。
• 该语句不得包含 UNION、INTERSECT 或 EXCEPT 运算符。
• 该语句不得引用视图。
• 该语句不得包含以下任何内容：DISTINCT、COMPUTE、COMPUTE BY 或 INTO。
• 该语句不得引用服务器全局变量 (@@variable_name)。
• 该语句不得引用派生表、临时表或表变量。
• 该语句不得引用其他数据库或服务器中的表或视图。
• 语句不得包含子查询、外连接或自连接。
• 该语句不得引用大对象类型：text、ntext 和 image。
• 该语句不得使用 CONTAINS 或 FREETEXT 全文谓词。
• 该语句不得使用行集函数，包括 OPENROWSET 和 OPENQUERY。
• 该语句不得使用以下任何聚合函数：AVG、COUNT(*)、MAX、MIN、STDEV、STDEVP、VAR 或 VARP。
• 该语句不得使用任何不确定性函数，包括排名函数和窗口函数。
• 该语句不得包含用户定义的聚合。
• 该语句不得引用系统表或视图，包括目录视图和动态管理视图。
• 该声明不得包含“FOR BROWSE”信息。
• 该语句不得引用队列。
• 语句不得包含无法更改且无法返回结果的条件语句（例如，WHERE 1=0）。
• 该语句不能指定 READPAST 锁定提示。
• 该语句不得引用任何 Service Broker QUEUE。
• 该语句不得引用同义词。
• 语句不得包含基于 double/real 数据类型的比较或表达式。
• 该语句不得使用 TOP 表达式。

其他参考：

• 使用查询通知

Answer 2

除此之外，对于任何其他考虑使用 SqlDependency 接收有关更改的通知的人来说，我一直在生产中使用这种方法，并且我发现了它的问题。我正在调查它，看看问题是否与我的代码有关，但主要问题是：

• 如果您快速连续触发多个更改，您并不总是会获得相同数量的事件传递到代码。在我的代码中，如果一个接一个地插入 2 个新记录，我只会收到一条通知（针对最后一条）。

  如果

• 无法知道添加的记录。因此，如果您添加一条新记录，并且代码触发以接收通知，则代码中无法知道该新记录的 id，因此您需要查询数据库来获取它。

Answer 3

花了一天时间追查 SQL Service Broker 无法工作的问题，根本原因是在存储过程中引用数据库。

例如，此 select 在 SQL Management Studio 中工作正常：

select [MyColumn] from [MyDatabase].[MySchema].[MyTable]

但是，这会被 SQL Service Broker 拒绝，因为我们在 select 语句中引用数据库，以及来自 SqlDependency 的回调> 在 SqlNotificationEventArgs e 中返回 Invalid，请参阅 http://msdn.microsoft.com/en-us/library/ms189308.aspx< /a>.

将传递到 SqlDependency 的 SQL 更改为以下语句消除了错误：

select [MyColumn] from [MySchema].[MyTable]

Update

上面的示例只是 SQL Service Broker 所依赖的 SQL 语句的众多限制之一。有关限制的完整列表，请参阅SqlDependency 有哪些限制。

原因是什么？ SQL Service Broker 使用的 SQL 语句在后台转换为指令以监视 SQL 事务日志，用于记录对数据库的更改。这种监视是在 SQL Server 的核心中执行的，这使得在检测表的更改时速度非常快。然而，这种速度是有代价的：您不能只使用任何 SQL 语句，您必须使用可以转换为指令来监视 SQL 事务日志。

Answer 4

请注意，您不能在存储过程中使用 nolock 提示，否则依赖项将始终保持无效状态，因此您对其进行的任何缓存都将永久重新查询数据库。

with (NOLOCK) 

文档中似乎没有提到这一点（据我所知）。

在过程脚本之前需要以下 SET 选项

SET ANSI_NULLS ON
SET ANSI_PADDING ON  
SET ANSI_WARNINGS ON

其他人认为这些 SET 选项也是必需的，但我认为并非如此。无论如何，像这样设置它们是个好主意。

SET CONCAT_NULL_YIELDS_NULL ON 
SET QUOTED_IDENTIFIER ON 
SET NUMERIC_ROUNDABORT OFF 
SET ARITHABORT ON

Answer 5

这项技术的另一个大问题是：订阅者连接需要具有创建过程权限。我的应用程序的 Web 服务层目前以受限用户身份运行。要使用 SQLDependency 设置通知，我必须打开该用户来创建过程。听起来像是在拥有自己的道路上迈出了相当好的一步。

Answer 6

要克服这些限制，您可以尝试使用 SqlTableDependency。
看看 www.sqltabledependency.it

Answer 7

它使用服务代理。因此，它不适用于非托管 SQL Azure 实例。因此，如果您正在使用或可能使用 SQL Azure，请务必小心。

https://learn.microsoft.com/en-我们/azure/sql-database/sql-database-features

服务代理

由单一数据库和弹性池支持：

没有

受托管实例支持：

是的，但仅限于实例内。
请参阅 服务代理差异

因此，除非您的所有环境都可以使用它，否则可能不太适合！

Answer 8

SqlDependency 不适用于内存优化表。我在文档中没有找到它，但在我的应用程序中却没有。