SqlCacheDependency via Service Broker Notification

Question

Is it possible to define a Notification on SELECT, but in that way: the Broker would reset Cache only if columns written in select has changed. So Column Sensitive approach. I don't want the cache resets if some unimportant column in the table are changed. I will have a SELECT with INNER JOIN.

Thanks in advance for help.

Answer 1

The theory goes that if you restrict your SELECT columns to contain only the columns of interest, you should be notified only if those columns changed. However the Understanding When Query Notifications Occur has this warning:

Notice that SQL Server may produce a query notification in response to events that do not change the data, or in response to a change that does not actually affect the results of the query. For example, when an UPDATE statement changes one of the rows returned by the query, the notification may fire even if the update to the row did not change the columns in the query results.

So you will get false positives.