0

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.

Barney
  • 154
  • 1
  • 3
  • 12

1 Answers1

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • "The theory goes that" In my tests its unfortunately only theory. I have a SELECT which JOINS three tables and every change on no matter which table, results in reseting the cache. No matter if changed column was in my Notification SELECT or wasn't. My Select is : "SELECT dbo.users.name, dbo.databases.connection_string FROM dbo.accounts INNER JOIN dbo.databases ON dbo.accounts.database_id = dbo.databases.id INNER JOIN dbo.users ON dbo.accounts.id = dbo.users.account_id" – Barney Feb 14 '13 at 15:09
  • 2
    Consider splitting the relevant tables vertically into 'stable' columns table and 'volatile' columns table. It *does* impact design, but if you have a highly volatile column that constantly invalidates your caches, it may worth it. There are obviously many more factors to consider and you should know better than me if is feasible in your case. – Remus Rusanu Feb 14 '13 at 19:26