One new scalability feature added in SQL Server 2005 is Query Notifications. This feature relies upon Service Broker, not on Notification Services. The idea behind it is to let SQL Server automatically notify applications when data changes, rather than relying on polling or time-based cache invalidation mechanisms.
It is best suited to read-mostly data, rather than highly volatile read/write data.
Sanchan Sahai Saxena has a nice article with sample code that explains the basic concepts.
Julia Lerman had a good article in Code Magazine with VB.NET sample code.
Bob Beacheamin wrote an excellent article in MSDN that explains Query Notifications in more detail.
Technorati Tag: SQL Server
Glenn Berry's SQL Server Performance 