I'm looking into using Query Notifications as a light(er) weight way of identifying when data in a table changes. I know they use Service Broker, and (although I have difficulty explaining exactly why) they do look like they'd be preferable to something like CDC or Triggers.
Query Notifications get set up by providing the database with a query, which sends a notification "...when the results of [the provided] query change".
That to me sounds like they're saying whatever query you've given it, is going to be run over and over and over. I think I'm probably misinterpreting what precisely SQL does under the covers with that query to monitor said changes, but I couldn't find anything online about what's actually taking place. I'm hoping it's something like it parses the query and sets up super duper lightweight "triggers" on any object referenced in the query, but since I don't know, it's making me reticent to want to use the feature.
Is anyone familiar enough with Service Broker and the Query Notification functionality to explain how the database engine watches for changes in the provided query?