What are the limitations of SqlDependency?

by

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

  • 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.

Additional reference:

More Related Contents:

  1. How to get the date of a day in a month in certain year? [closed]
  2. How do parameterized queries help against SQL injection?
  3. How do I clean SqlDependency from SQL Server memory?
  4. When using Trusted_Connection=true and SQL Server authentication, will this affect performance?
  5. What represents a double in sql server?
  6. SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance
  7. MultipleActiveResultSets=True or multiple connections?
  8. What’s the fastest way to bulk insert a lot of data in SQL Server (C# client)
  9. How should I multiple insert multiple records?
  10. C# SqlCommand – cannot use parameters for column names, how to resolve?
  11. Received an invalid column length from the bcp client for colid 6
  12. How can I convert ticks to a date format?
  13. Can’t get sql server compact 3.5 / 4 to work with ASP .NET MVC 2
  14. Entity Framework – default values doesn’t set in sql server table
  15. Entity framework: StoreGeneratedPattern=”Computed” property
  16. Linq to Entities : using ToLower() on NText fields
  17. CMD command on c#
  18. What is the purpose of “return await” in C#?
  19. Creating a DPI-Aware Application
  20. Why does the EF 6 tutorial use asynchronous calls?
  21. What are the true benefits of ExpandoObject?
  22. “Invalid JSON primitive” in Ajax processing
  23. Deserializing JSON to abstract class
  24. Post parameter is always null
  25. How do I map XML to C# objects
  26. Consume a SOAP web service without relying on the app.config
  27. How to deserialize only part of an XML document in C#
  28. Overriding Extension Methods
  29. ASP.NET 5 Authorize against two or more policies (OR-combined policy)
  30. C# how to use WM_GETTEXT / GetWindowText API / Window Title

Leave a Comment