As you probably already know, you can add indexes on temporary tables to improve the…
Did you ever think that it would be great to cache SQL Server database query results, and only go back to query the database when data has changed? Well, you can do that, using Query Notifications. Query notifications were introduced as early as in SQL Server 2005. They are built upon the Service Broker infrastructure introduced in SQL Server 2005. Query notifications allow applications to be notified when data has changed.
This feature is particularly useful for applications that provide a cache of information from a database, such as a Web application, and need to be notified when the source data is changed. You still have to connect to the Service Broker Queue using the RECIEVE command, but that query would usually be much lighter that the query that you want notification for. No point in using Query Notifications for small quick queries with rapidly changing results, but rather for a little heavier queries that does not change it’s results that often. Query notifications allow you to request notification within a specified time-out period when the underlying data of a query changes. The request for notification specifies the notification options, which include the service name, message text, and time-out value to the server.
Notifications are delivered through a Service Broker queue that your applications can poll for available notifications. Notifications are sent only once. For continuous notification of data change, a new subscription must be created by re-executing the query after each notification is processed. More information about Query Notification can be found here.
And dont hesitate to contact us if you have any SQL Server related questions!