Dependency Notification in SQL Server 2005
The second model for setting up SQL dependencies is notification based.When I see the word notification related to SQL Server 2005, I immediately think of SQL Server Notification Services. But the focus here is on SQL Server's internal change notifications.When ASP.NET executes a command against SQL Server 2005, it can register for any changes related to that command.These notifications are like automatic transactions in that you start the process of listening for them by calling the System.Data.Sql-Client.SqlDependency.Start() method.
Because ASP.NET is a multi-threaded environment that calls individual pages repeatedly, you can put this declaration in only one place—the Application.Start method of your application's Global.ASAX page.Your application runs the code in this file based on application events, such as the application starting.When you tell ASP.NET and, by proxy,ADO.NET to listen for SQL Server's change notifications, you can start creating dependencies.
In this case, dependencies reference a generic dependency handler called command notification.To reference this handler, either in a cache declaration or as part of a SQLDataSource object, simply add the following property to your attribute:
SqlCacheDependency=
"CommandNotification"
Note that no table name is associated with this registration; you also don't need an entry in your web.config file or a custom aspnet_ table or access method. But there are other requirements. First, you must grant the account used for database access permission to subscribe to and send query notifications within SQL Server.You grant the permissions by using the T-SQL commands that Listing 4 shows.You don't need to execute these commands if you're using the sa account in your development environment. However, in production, where you're following security best practices and using an account with less permission, you'll need to run these commands to let your database user leverage query notification information.
When the account has permission to track query notification information, there are two primary limitations that restrict which changes it will detect. But first, let's look at what's tracked.Within either a Data-Source or Cache object, you execute queries to populate the data that's cached. As these queries execute and data accumulates, ASP.NET tracks the queries you used. It registers the results of the queries with SQL Server, then as other statements are executed, SQL Server determines whether the results of your queries of interest have changed. When SQL Server detects a change that affects one of these registered database queries, it notifies ASP.NET, which can trigger the invalidation of the Cache object associated with that query.
The good news is that this processing is all automatic—you don't have to do anything to reap the benefit. However, just how many queries can your ASP.NET application be registered for? This is where the two limitations I mentioned come into play. For starters, any query that uses the wildcard character (*) to retrieve all fields in a table (which is a poor practice, anyway) is always considered invalid. You can't register for notification of changes to that table and will instead retrieve the data with every request, eliminating caching. The second limitation is that to register for a query, you must use the owner-qualified name of the table within the SELECT statement, which can be embedded in a stored procedure. So for the example above, your query would need to reference dbo.orders as opposed to just orders. For more information about the potential limitations on query notifications, see the SQL Server 2005 Books Online (BOL) topic "Using Query Notifications" or the MSDN article "Creating a Query for Notification," at http://msdn2.microsoft .com/en-us/library/ms181122.aspx.
Optimizing Performance
ASP.NET 2.0 and SQL Server 2005 let you fully optimize your site's performance. In today's business world, it's not just about having a Web presence—it's about using the Web for business activity and profit. By taking advantage of performance-enhancing tools at every level of your application—display, business logic, and data store—you can create a dynamic Web application that minimizes the hardware you need to support hundreds or even thousands of users.