Summary

• The primary reason for caching data is performance, but caching data requires a means to expire the cache when the data is stale.

• The decision to cache different types of data should be based on frequency of change, the amount of reuse, and the size of the data.

• The SqlDependency object allows you to cache data and be notified when the data changes. This object is best suited for Web applications and middle-tier components with a small number of subscribers to the change notification event.

• When you use the SqlDependency object, your queries cannot contain the asterisk (*) to retrieve all fields, and the table names must use two-part naming (for example, dbo.Customers).

• You must set up the database server properly for the notification events to work properly, by granting send permissions to the guest account on the SQL QueryNotificationService service that is in the MSDB database, enabling the SQLCLR, and enabling the service broker for each database that will use the SqlDependency object.

• For SQL Server 7 and later, ASP.NET can perform cache invalidation by using polling. The database and each table must be configured using the aspnet_regsql.exe utility.

• ASP.NET also provides cache invalidation by command notification when you use SQL Server 2005. Command notification, which is also called change notification, uses the SqlDependency object; queries must be written using the same guidelines.