Chapter Summary

• Page-level and user control-level caching are two output-caching methods that are easy to implement. Custom caching is done using the Cache API and the System.Web.Caching namespace. The Insert and Add methods are used to add items to the cache. Items are added using a key and value pair and can be removed with the Remove method.

• Query notifications, new with SQL Server 2005, allow your applications to receive notifications when the data in the cache has changed. Eventually, all caches will expire, and there must be a method in place for refreshing the cache. This can be done on demand by the user or at the point the application attempts to access the expired cache.

• A cursor is a server-side object that can be used to access multiple rows one at a time from a result set. Once the cursor is declared, it can be opened and then fetched one row at a time.

• When using cursors, you should try to reduce the amount of server memory they consume so your applications will perform better. This can be accomplished by reducing the amount of data in the cursor, using the READ_ONLY concurrency option, using the FAST_FORWARD or FORWARD_ONLY cursor type, and always remembering to close and deallocate your cursors.

• You can evaluate the efficiency of your cursors by looking at the execution time. This is done using the New Query window in Microsoft SQL Server Management Studio. You can also use the dynamic management function named sys.dm_exec_cursors to evaluate currently allocated cursors.

• Generally, you want to avoid the use of cursors whenever possible. Transact-SQL was designed as a set-based language and not an object-oriented language. Try to use alternatives to the cursor. If you are able to, measure the execution times for the cursor and the alternative and use the fastest method.