SqlDependency的局限性是什么？ [英] What are the limitations of SqlDependency?

问题描述

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

I am using a table as a message queue and "signing up" for updates by using a SqlDependency. Everywhere I read, people are saying "look out for the limitations of it" but not specifically saying what they are. From what I've gathered, you will have problems when the table has very high update frequency; fortunately, I'm only looking at 10 - 20 values per minute maximum.

对SqlServer的其他限制/影响是什么？

What are the other limitations/impact on the SqlServer?

推荐答案

我可以找到的最完整列表（从此处）如下：

The most complete list I can find (from here) is as follows:

• 必须明确声明SELECT语句中的预计列，并且表名必须用两个限定部分名称。请注意，这意味着该语句中引用的所有表必须位于同一数据库中。


• 该语句不得使用星号（*）或table_name。*语法指定列。
  
• 该语句不能使用未命名的列或重复的列名。


• 该语句必须引用基表。


• 该语句不能引用带有计算列的表。


• 除非该语句使用GROUP BY表达式，否则SELECT语句中的投影列不得包含聚合表达式。提供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锁定提示。


• 状态ent不得引用任何Service Broker队列。


• 该语句不能引用同义词。


• 该语句不能具有基于double /的比较或表达式真实数据类型。


• 该语句不能使用TOP表达式。

• The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
• The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
• The statement may not use unnamed columns or duplicate column names.
• The statement must reference a base table.
• The statement must not reference tables with computed columns.
• The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.
• A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
• The statement must not include PIVOT or UNPIVOT operators.
• The statement must not include the UNION, INTERSECT, or EXCEPT operators.
• The statement must not reference a view.
• The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
• The statement must not reference server global variables (@@variable_name).
• The statement must not reference derived tables, temporary tables, or table variables.
• The statement must not reference tables or views from other databases or servers.
• The statement must not contain subqueries, outer joins, or self-joins.
• The statement must not reference the large object types: text, ntext, and image.
• The statement must not use the CONTAINS or FREETEXT full-text predicates.
• The statement must not use rowset functions, including OPENROWSET and OPENQUERY.
• The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
• The statement must not use any nondeterministic functions, including ranking and windowing functions.
• The statement must not contain user-defined aggregates.
• The statement must not reference system tables or views, including catalog views and dynamic management views.
• The statement must not include FOR BROWSE information.
• The statement must not reference a queue.
• The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).
• The statement can not specify READPAST locking hint.
• The statement must not reference any Service Broker QUEUE.
• The statement must not reference synonyms.
• The statement must not have comparison or expression based on double/real data types.
• The statement must not use the TOP expression.

其他参考：

• 使用查询通知

• Working with Query Notifications

这篇关于SqlDependency的局限性是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！